SQL Server with Mr. Denny


February 5, 2016  7:19 PM

DCAC Now Has 2 VMware vExperts

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL Server, VMware

I’m thrilled to report that Denny Cherry & Associates Consulting now has not 1, but 2 VMware vExperts in our ranks.  No we didn’t go off and hire another consultant.  VMware has decided that this year they will recognize both Myself (Denny Cherry) and Joey D’Antoni for all the community work that we’ve done with Virtualization by awarding us the VMware vExpert award for 2016.

You can see the list of 2016 vExperts on VMware’s blog or in the vExpert directory (won’t be updated for a couple of weeks).

I know that I can safely speak for Joey when I say that we are both honored to receive this award from VMware.  While there are a decent number of vExperts each year, the number of vExperts that are SQL Server Professionals is very small.  Looking though that list I can see only a couple of people besides myself who have deep knowledge of SQL Server, so it’s pretty exciting that we make up somewhere around 1/2 of the SQL Server expertise within the VMware vExpert program.

Thank you to VMware for recognizing myself and Joey. And thank you to the members of the community who attend our sessions and read the content we post about VMware and SQL Server.

Denny

February 3, 2016  7:00 AM

Fixing a storage pool that doesn’t have an read-write server

Denny Cherry Denny Cherry Profile: Denny Cherry
Cluster, SQL Server, Storage space

Recently I was working on a clients file server cluster and while doing some troubleshooting with Microsoft Support one of Storagethe clustered disks wouldn’t respond correctly. Now this file server cluster is setup in Azure so it is using SIOS Data Keeper to replicate the data between the nodes of the cluster.

The state that the cluster was in looked something like this.

sios

In this configuration the cluster has two nodes FILESERVER01a and FILESERVER01b. The cluster is named FILESERVER01 and the name that users access files through is FILESERVER01V01.  The top three drives were working exactly as expected, but the last one wasn’t working at all.  Nothing could talk to it, nothing could manage it.

Thanks to Dave Bermingham (Clustering MVP, SIOS Employee and all around good guy) we found a forum thread that talked about this same problem and that the root cause was that the pool was registered as a cluster object within Failover Cluster manager.

Once I went into the failover cluster admin and remove the failed object for that pool I could attach the virtual disk again.  Then I just needed to configure the disks to automatically attach on reboot.  That’s easy enough to fix with a little PowerShell.

get-virtualdisk {DiskName} | set-VirtualDisk -IsManualAttach $False

Then into computer management to bring the disk online (or reboot, but this is easier).  Then resetup the SIOS software to replicate and away the data went.

Thankfully it’s fixed, and I can get back to the problem at hand of figuring out the EFS problem on the cluster.

Denny


January 25, 2016  4:00 PM

Feed Monsters now supports Two Factor Authentication

Denny Cherry Denny Cherry Profile: Denny Cherry
RSS, SQL Server, Two factor authentication

In case you missed the big announcement last week, Feed Monsters was released to the public so that you can now do most6878554296_4c8918c9a9_m of what Yahoo Pipes allowed you to do with your RSS feeds back before it was shut down.

While the service has only been available for about a week now there have actually been several enhancements that have been made live.  The most important of these is two factor authentication, which includes support for emailing the two factor codes to you as well as supporting 3rd party applications for code generation on your phone like Google Authenticator, Duo Mobile, etc.  Having two factor authentication enabled for your account will help to ensure that no one but you can possibly make changes to your monsters or see what the settings for your monsters are.  Enabling two factor authentication is done via the settings page.  While it does take a couple of steps to get setup, it shouldn’t take more than 30 seconds to enable and begin using.

Denny


January 18, 2016  11:30 PM

Announcing Feed Monsters…

Denny Cherry Denny Cherry Profile: Denny Cherry

Did you use to use Yahoo! Pipes to manipulate RSS Feeds? Personal I did and I REALLY missed it when it was turned off by Yahoo. So like any good IT person I sat down (eventually) and did something about it. What I ended up building turned into what I’ve called “Feed Monsters“.  Feed Monsters is a web based service that lets you change values within RSS feeds, resort the values within the RSS feeds and filter the RSS feeds.

After you get signed up you create your monster and give it an RSS feed to chew on.  Then you add parts to your monster and those parts each make a different change to the RSS feed that you’ve given it.  As the service is being launched it supports 10 different parts that you can add to your monster.

Data Change Parts

  • Add Category from Publish Date
  • Add Prefix to Title
  • Add Suffix To Title
  • Clip Content
  • Post URL
  • Publish Date
  • Replace field {x} with field {y}
  • Title

Filter Parts

  • Top {n} Posts

Sorting Parts

  • Random Resorting

There’s plenty of more parts which we’ll be adding to the system in order to make RSS feeds even more changeable in the near future.

Registration for the service is free, and lets you try the service out for 10 days without having to pay anything. After that it’s only $20 a year (paid for paypal) to continue using the service.  Hopefully you find the service as valuable to use as I do.  Check out the Feed Monsters website for the full details about the service.

Denny


January 13, 2016  7:00 PM

Creating a cluster without Domain Admin permissions

Denny Cherry Denny Cherry Profile: Denny Cherry
Cluster, Cluster management, Clustering, Clustering/High availability, Clusters, SQL Server, SQL Server availability

If you’ve ever watched a presentation when someone sets up a cluster you’ve probably noticed that it goes pretty smoothly. The reason for this is because the account which the presenter uses is a domain administrator.

But what about the real world?

In the real world unless you are a systems administrator you probably won’t be a Domain Admin when creating your cluster. There’s a couple of ways to get the cluster setup.

The first way is to be created the permission to create objects within the domain. This is the easiest option. If this isn’t an option then you have the second option available to you.

Pre-staging the objects is option #2. This option requires that a member of the systems administration team create the computer objects for the cluster (and any clustered resources like Availability Groups and Failover Clustered Instances). You also need to have the Domain Admin disable the accounts. This step is critical because if the computer accounts are enabled the Failover Cluster Manager won’t be able to use the computer account, and neither with the cluster when it comes to creating the computer objects for the FCI and/or the AGs.

You’ll also need to configure the computer objects for the actual cluster to be managed by the user who is configuring the cluster. For the other computer accounts (for the FCIs and the AGs) you need to setup the computer account for the cluster to be able to manage these other computer accounts.

Denny


January 5, 2016  4:51 PM

2015 By The Numbers

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL Server

This post is a little later than normal. It’s been a very busy few weeks with family over until the 3rd of January.

Well 2015 was a hell of a year for my blogging. I recorded just under 250k page views (244,305) which isn’t even all of them (2016 will be the first year that I get a truly complete set of data as I’ve not got page view images on every blog post that I’ve written).

This years top 10 posts look a lot more different from the prior year than has happened in earlier years.

  1. SQL Server 2014 Licensing (Number 3 in 2014)
  2. What Exactly Is MSDTC Any Way, and When Do I Need It? (Number 2 in 2014)
  3. Difference Between An Index and a Primary Key
  4. How To Configure MSDTC On Windows 2008 (Number 1 in 2014)
  5. What Does WITH (NOLOCK) Actually Mean
  6. Calculating Fibre Latency Correctly Is a Must When Trying To Determine Storage Performance Numbers
  7. The Process Could Not Execute sp_replcmds on x
  8. The Least Expensive SQL Server High Availability Solution (Number 22 in 2014)
  9. If you thought Database Restores Were Slow, Try Restoring From an EMC Data Domain
  10. SQL PASS Summit 2015 Karaoke Party

Of the top 10, only three were in the top 10 before (numbers 1, 2 and 3 from 2014 were 4, 2, and 1 in 2015 respectively).  Number 8 from 2015 was number 22 last year.  Everything else in the top 10 appears to be a new post from 2015 (or didn’t have a tracking image until this year).  This is pretty shocking to me.  I haven’t seen the numbers shift like this before, granted that could be do to better data.  Next years numbers will be interesting to see once I have a full years worth of data to look at.

Thanks everyone for reading, and enjoy 2016.

Denny


December 23, 2015  9:00 AM

Introducing Microsoft SQL Server 2016 ebook (Preview Edition)

Denny Cherry Denny Cherry Profile: Denny Cherry
eBook, SQL Server, SQL Server 2016

I’m pleased to report that what is probably one of the first books on Microsoft SQL Server 2016 has been released, or at least the official 3 chapter preview of the book has been released by Microsoft Press.  In this three chapter book, written by myself, Stacia Varga and Joseph D’Antoni, we talk about some of the new security features in SQL Server 2016 including Always Encrypted, Row-Level Security and dynamic data masking.  In Chapter 4 we talk about some of the improvements to the database engine in SQL Server 2016 over SQL Server 2014 including the query store and Stretch Database.  While in Chapter 7 we talk about some of the improvements to SQL Server Reporting Services including the new data visualizations, support for mobile, KPIs and subscription enhancements.

If you are planning on deploying SQL Server 2016, or even if you just want to get a leg up on what’s coming in the new version this is the book for you.

The best thing about this book, is that it is free, there isn’t even a soul sucking registration to go through.  Just click and download.  It’s available is a standard 8.5×11″ PDF (standard US Pages) as well as a smaller PDF for mobile.  If you are looking for EPUB and MOBI files you’ll need to wait a few more weeks as they are supposed to be available starting in January (don’t hold me to that, I’m just going off the MSDN post.

So stop reading this, and go and download the ebook.  Now!

Denny


December 16, 2015  5:00 PM

Get Operation Status of an Azure Async Operation

Denny Cherry Denny Cherry Profile: Denny Cherry
Azure, Powershell, PowerShell Scripts, SQL Server, Windows Azure

Recently I was trying to make a drive bigger on an Azure VM by simply using Set-AzureOSDisk PowerShell commandlet (and the -ResizedSizeInGB switch). The problem is that this command is asynchronous meaning that it tells you that it’s done, even if it hasn’t finished yet.

The problem with this, is that there’s no way in Azure to see if the request has finished or not. All you can do is hope that it finishes without throwing an error.

Well, I’m pleased to say that (with some hacking of some other scripts that I found online) I was able to put together a PowerShell CmdLet that you can download and use to get this information from the Azure Portal. This PowerShell Commandlet uses the Microsoft Azure API to get the information about the request.

You can think of this as a PowerShell version of the Microsoft Azure Get Operation Status API.

function Get-AzureStatus
{
[CmdletBinding()]
param (
[Parameter(ParameterSetName="ByRequestId", Position=1, Mandatory)]
[string] $RequestId,
[Parameter()]
[string] $SubscriptionId
)

if (!$SubscriptionId)
{
$SubscriptionId = (Get-AzureSubscription | ? IsDefault).SubscriptionId
}

$account = Get-AzureAccount | ? { $_.Subscriptions.Contains($SubscriptionId) -and $_.Type -eq "Certificate" }

if (!$account)
{
throw "Can't find an account for Azure subscription $SubscriptionId"
}

$certificate = ls Cert:\CurrentUser\My | ? Thumbprint -eq $account.Id

if (!$certificate)
{
throw "Can't find the certificate for Azure account {0}" -f $account.Id
}

$uri = "https://management.core.windows.net/${SubscriptionId}/operations/${RequestId}"

try
{
$response = Invoke-WebRequest -Method GET -Uri $uri -Certificate $certificate -Headers @{ "x-ms-version" = "2014-06-01" } -

ErrorAction Ignore
}
catch
{
$message = ( $_.ErrorDetails.Message)
throw "{0}: {1}" -f $message.Error.Code, $message.Error.Message
}

$content = $response.Content
$content.ChildNodes
}

Now using this requires that you have a certificate setup for the subscription to allow access to the Microsoft Azure API. Thankfully Cindy Gross has a great post on how to get a certificate created and uploaded to Azure.

To summarize her post.

1. Create a certificate on your machine.
makecert -sky exchange -r -n "CN=" -pe -a sha1 -len 2048 -ss My "c:\temp\.cer"

2. Upload that certificate to the options section of the OLD PORTAL

3. Map the subscription to your certificate
$subID = "Your Subscription Id"
$thumbprint = "Your Cert Thumbprint (from the portal)"
$SubscriptionName = "Your Subscription"
$myCert = Get-Item cert:\\CurrentUser\My\$thumbprint
Set-AzureSubscription –SubscriptionName $SubscriptionName -SubscriptionId $subID -Certificate $myCert

4. Install the POwerShell CmdLet above and run it passing it your RequestId that you get from any Azure PowerShell cmdlet.

Denny


December 7, 2015  3:47 PM

Just how bad is the new Windows 2016 pricing?

Denny Cherry Denny Cherry Profile: Denny Cherry

3462607995_150a6b2624_zYou probably saw the new Windows Server 2016 Pricing FAQ that came out last week.  If not, you should go head through it.  It’s OK, I’ll wait.

So, now that you’ve read it you’re probably all worried about the new Windows Server 2016 pricing structure as it is core based, and you have to purchase a minimum of 16 cores per server.  What’s that? You didn’t actually read the FAQ?  No problem, here’s the relevant part.

Core licenses will be sold in packs of two licenses. Each processor will need to be licensed with minimum of 8 cores which is 4 two-core packs. Each physical server, including 1 processor servers, will need to be licensed with minimum of 16 cores which is 8 two-core packs. Additional cores can then be licensed in increments of two cores (one two core pack) for gradual increases in core density growth.

Needless to say people are beginning to think that this is another SQL Server Licensing disaster from when SQL Server 2012 went from socket to core based licensing.  And in reality this isn’t that big of a deal for most servers.  The way the FAQ states things you have to buy 8 cores per socket, with a minimum of 16 cores per server.  Now that sounds like a lot, but there’s a little chart in the FAQ that’s really important.  I’ve included it below.

licensing

So looking at this chart we can see that any machine with less than 8 cores per socket and two sockets or less will cost you exactly the same as it will today.  Also any machine with one socket and less than 16 cores will also cost the same as it costs today.

It’s important to remember that today dual socket machines are the bulk of the servers being purchased.  Most VM farms (vSphere or Hyper-V) work great on dual docket servers.  That’s the bulk of servers at companies right there.  It’s only when you get into the really big servers that things are going to change.  The really big database servers are going to have a cost increase, but that cost increase is going to be nothing compared to what you are already planning on spending on the SQL Server license.  The other big servers at most companies are things like Oracle and SAP, which are already running on something other than Windows.

In the long run, I see this change making almost zero impact on most companies IT expenses.

Denny


December 3, 2015  4:05 PM

Announcing Internals for Query Tuning Training Class By Kalen Delaney

Denny Cherry Denny Cherry Profile: Denny Cherry
DBA training, IT training, SQL Server, Training

We at Denny Cherry & Associates Consulting are thrilled to announce that we have partnered up with the SQL Server internals guru Kalen Delaney to deliver training classes.  Our first class will be Internals for Query Tuning and will be held Monday-Wednesday April 4th, 5th and 6th of 2016 in Orange County, CA.expert

This is an advanced course designed for advanced SQL Server professionals. The course includes extensive demonstrations that illustrate the details of SQL Server internals as well as tuning techniques.  While this course will focus on SQL Server 2014 the information presented will be relevant for older versions of SQL Server as well.  In addition, some of the changes due in the upcoming SQL Server 2016 will be described.

This three day course is made up of 6 parts which are outlined below.  The cost for this fantastic three day class is $2225 US.  Seating is limited, so get signed up today.

The outline for this class is:

Part 1: SQL Server Metadata

  • Architecture Overview
  • Metadata Overview
  • Dynamic Management Views

Part 2: Index Structures

  • Metadata for Storage
  • Space Allocation
  • Tools for Examining Physical Structures
  • Heaps and B-Trees
  • Clustered Indexes
  • Nonclustered Indexes
  • Fragmentation
  • Filtered Indexes
  • Rebuilding Indexes
  • Partitioning Overview
  • Creating and Maintaining Partitions
  • Metadata for Partitioning
  • Columnstore Index Storage

DAY 2

Part 3: Query Processing and Query Plans

  • SHOWPLAN Options
  • Query Plan Elements
  • Types of Joins
  • Aggregation
  • Sorting
  • Columnstore Indexes: Batch vs Row Mode
  • Data Modification

Part 4: Optimization and Recompilation

  • Optimization Overview
  • SQL Server’s Query Optimizer
  • Plan Management and Reuse
  • Causes of Recompilation
  • Forcing Recompilation
  • Plan Cache Metadata

DAY 3

Part 5: Index Tuning

  • Special Index Features
  • Statistics
  • Indexed Views
  • Covering Indexes
  • Filtered Indexes
  • Included Columns
  • Indexing Guidelines

Part 6: Query Tuning

  • Query Improvements
  • Search Arguments
  • Constants and Variables
  • User Defined Functions and Computed Columns
  • Plan Guides
  • Query Hints


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: