Building Always On Availability Groups—Part 3 of 3

At this point all of the instances in your cluster should be enabled for AlwaysOn. The first step is to take a full backup to a database. Ideally (but not a necessity) backup to a location that is accessible by all of the nodes in the cluster.

From the Node that you would like to initially be the Primary, expand AlwaysOn High Availability and select the “New Availability Group Wizard”

 Select a name for your Availability Group–note this will be the name internal to SQL Server, not the external name that clients connect to (we will address that later)

Select the databases you would like to add to the Availability Group. Note: In order to be eligible for an Availability Group a database must be in Full Recovery Mode and have a full backup completed and available.

Add the instances you would like in your Availability Group as Replicas. The option to add Automatic Failover and Synchronous commit are on this screen. Note: Automatic Failover should only be used within the same data center. Otherwise latency on the secondary will affect the primary. This is also where we set the ability to read the secondary replicas with the option Read-Intent only (Only allows Read Intent Connections from SQL 11 clients) or Yes (allows read-only connections from any client)

Verify the endpoints tab–these are the endpoints for the communication used by AlwaysOn. There isn’t a need to change them

For the backup preference tab, here I select primary. However if you choose to backup the secondary server, it  will need to have backup drives presented and created. There are other considerations to this–are you backing up to a common location, how you will manage distributed endpoints.


The Listener, this is the DNS name that your application will use to connect to the availability group. Note: This step requires a Virtual Computer Object (VCO) to be created in Active Directory. Put in a request for your listener name to the Windows SE to create the object. Additionally, a DNS request for this name will be required. Use the IP address and Name that you requested for DNS. And then go ahead and create the listener.

The next screen is the data synchronization screen. This is the wizard can restore you backup of the databases in your Availability Group or just skip the initial synchronization and restore afterwards. If the backups are in a shared location accessible by all nodes, I recommend doing the synchronization now.

To check on the status of your new Availability Group–right click on it in SSMS and launch the dashboard.

Windows MPIO Information–Encrypted, Really?

We are in the process of trying to get all of our Windows 2008 and higher servers of EMC PowerPath and onto the native Windows MPIO driver. While PowerPath has some benefits, it’s also very expensive, and we’ve run into some versioning issues that have caused a couple of outages. Since Microsoft started including a driver (that generally works really well) in Windows 2008, we are using that as a standard.

There were some questions from one of my engineers about the paths looking different from PowerPath to the native driver so I went first to the MPIO configuration tool (Control Panel > Admin Tools > MPIO). It doesn’t provide a great deal of detail, but there is the option to capture a snapshot of the current MPIO configuration. Well, I tried to run this and got “System Error 5 has occurred. Access is Denied”

Given that I was a local admin on the server, I was really curious as to why this was failing. So I went to the command line, where I could use the MPCLAIM tool. The –V flag gathers the config information and exports it to a file. Same error—Access is Denied, Error 5. So I went to the googles—and I found this from a Microsoft employee in a forum post.

Microsoft for whatever reason decided to encrypt the temp file for that is generated. It turned out in my case we had an issue with an expired Data Recovery Agent certificate that was preventing AD from encrypting that file. If you are trying to gather MPIO config information, you need to have the ability to encrypt. So if you run into this, you probably need to contact your friendly domain admin.

Does anyone have any ideas on why Microsoft would want to encrypt this file? The only logical thing I could conclude is that is could have configuration information, but that info tends to be accessible from other methods (HBA clients, WMI, etc)


Availability Group DMVs—Part 1 of many–sys.availability_databases_cluster

After some recent discussions with colleagues Tom LaRock (b|t) and Kendall VanDyke (b|t) about information around AlwaysOn availability groups and how it’s lacking in the GUI, I thought I would do some community service and start documenting the DMVs a little further than books online. One thing you will notice in this series is that the naming is not always consistent—some DMVs will reflect the original HADRON name that Microsoft was using for Availability Groups.

So let’s get into the DMVs. Starting with sys.availability_databases_cluster:

This DMV contains one record for each Availability Group and database hosted on a given Windows cluster—it should look the same on each member of your availability group. The GUIDs represent the availability group’s unique ID, and then the unique ID of the database within the group, and the name of the database. Here’s a look at a SharePoint AG.

This data isn’t too interesting—as we get further into this blog series, I’ll show how we can tie these DMVs together to get relevant data.

Appliances—Unitaskers or Problem Solvers?

What is a Unitasker?

One of my favorite hobbies is cooking—I’ve blogged about how project planning isn’t that different from planning a dinner party. I am pretty into the technical side of cooking—one of ethos I try to follow in equipping my kitchen is to avoid the dreaded “unitasker” syndrome. For a tool to makes its way into my kitchen, it needs to either be versatile, or do what it does incredibly well. A good example of a bad unitasker is the Margarita Maker—I already have two other tools (a cocktail shaker and a blender) which could accomplish the task of making a margarita, so why do I need a specialized appliance that doesn’t really do anything else? My knives and my Vita-Prep do a lot of things really well—not just one thing. So why am I writing about this on my database blog? We have the same phenomenon going on in IT right now.

The Bad Old Days

Back in the bad old days, servers were really expensive, and came with even more expensive support contracts. This was especially true if you were on the dark side and managed UNIX servers which required RISC based hardware. In their time these servers brought really outstanding performance, but they also came with hefty vendor lock in. You paid for your O/S support from the hardware vendor and you had to buy any additional spare parts through the vendor. In 2009, which really isn’t that long ago, my former company had to pay $36,000 to put 16 GB of memory into a mid-range UNIX server. We could have purchased the same exact memory for the same vendors’ x64 server for about $2000. Support on those 4 servers was also north of $200k/yr. By now most enterprises have seen the light and are using commodity hardware—running some combination Linux and Windows as their application stack demands. The amount of hardware that can be purchased for $10,000 now completely floors me (I spec’ed some servers in this price range with 8 really fast cores and 256 GB of RAM the other week). So now that hardware is cheaper (and service on those commodity servers is really minimal–< $1000/yr in most cases) the margins of the hardware manufacturers are way down—so where do they make up that margin? Margarita machines, er hardware appliances.

Computing Appliances

The first exposure I had to dedicated computing platforms was when I worked in pharmaceutical manufacturing—the appliances in questions were hardened physically and protected against extreme conditions. Also, they were easy to deal with from a validation perspective. So these devices made sense—and since they more along the lines of workstations, they didn’t really affect our IT buying. The first big splashy product launch of an appliance I came across was Exadata—the HP Oracle Database Machine (this was prior to Oracle’s acquisition of Sun). The original specs for this machine really targeted large data warehouse environments (it’s still pretty good at that), but the sales and marketing folks came in, Oracle bought Sun, and now we have ads on the front of the Wall Street Journal proclaiming “the World’s Fastest Database Machine”. It’s really not—see my friend Kevin Closson’s blog if you are really interested.

Me too!!

Microsoft introduced a similar product with Parallel Data Warehouse in 2010. The Microsoft product is slightly different as the hardware is purchased through a hardware vendor, as opposed to the database vendor. SAP has their own offering with the BW accelerator—designed to speed memory processing of large data warehouse queries. One difference between these two products and Oracle’s is the option for different hardware vendors. There are numerous other appliances from numerous vendors. In the big data space, numerous vendors have solutions for Hadoop.

What Do Appliances Have in Common?

Some things are different, but most of the appliances I’ve seen have the following things in common:

  • Purchased as a whole unit, typically in half rack or whole rack configuration
  • A large upfront cost, either directly to the vendor, or to a third party consulting partner, to “configure” the appliance for your environment
  • Ongoing support costs that are much higher than on commodity hardware
  • An expansion plan that consists of—buy another really expensive appliance, with all of the above costs
  • Sales reps from major vendors seem heavily incentivized to sell them, even where they don’t fit—so be wary


I’ve been trashing these appliances throughout this post, but they do have some benefits. If you have the right type of workload (and from a database perspective this tends to be a large data warehouse) and more importantly, a singular workload (not mixed OLTP/DW) these machines can really offer some great performance gains, at the cost of flexibility and lots of money.

Things to Think About

I work for a large enterprise, we buy a whole lot of servers (this means our server costs are really low), and we have a couple of appliances. The appliances don’t fit well into our monitoring and standards, and when workloads get onto them they tend to get stuck there. Appliances represent the ultimate case of vendor lock-in to me.

While there can be performance gains, the main reason I see for these appliances is for hardware vendors to recoup some of the lost profit margin I mentioned above. The best example I’ve seen is with Hadoop—which was basically designed around cheap, commodity hardware. Every major HW vendor now has some sort of an appliance for Hadoop, which costs a ton more than building your own, with no real functionality improvements.

So think long and hard when your friendly sales rep or VP/CIO tries to convince you that an appliance is the solution that will resolve all of your data problems.






%d bloggers like this: