SQL Server 2012 AlwaysOn Availability Groups In Action

I think of SQL Server 2012 AlwaysOn Availability Groups as both high availability and scale-out database. This is because you can identify multiple secondary servers in your Availability Group that can be marked for read-only. When you connect to the Availability Group Listener, SQL Server will redirect the request of any connection marked as READONLY to the secondary read-only database server automatically for you. Additionally, you can run common administrative functions that take up cycles and locks on your production database server such as backups and DBCC checks to a readable secondary, again making for a higher-performance scaled-out SQL Server infrastructure.

I just posted a short 10-minute video on our Microsoft East Region DW BI database group here: http://www.youtube.com/watch?v=TF9_Kn2Iqy8 which shows my AlwaysOn configuration from the perspective of an end-user writing and viewing reports. This highlights the high-availability capabilities that AlwaysOn AG’s provide, above and beyond the now-deprecated Database Mirroring feature.

One last thing that I want to re-emphasize which I call-out in the video a few times, is how easy it was set-up AlwaysOn and then Availability Groups. I didn’t have to configure shared storage for the cluster configuration, which makes it very easy to demonstrate on a VM, because I don’t need a SAN. There is no shared storage involved with AlwaysOn and the entire demo in the video is performed on Hyper-V.

Quick Note on Always On Availability Groups Performance in SQL Server 2012

It’s still early in the lifecycle of SQL Server 2012 (heck, we just RTM’d 2 weeks ago and not yet GA!) but some of the whitepapers, best practices, guidance, etc. are starting to flow out and I’ll make it a point of mine to try and highlight some of these as they come out. That is, the ones that effect projects or customers that I’m working on, anyway.

So here is one that caught my eye: I saw this on Books on Line about making AlwaysOn Availability Groups perform better in terms of resolving network names and IP addresses with the AG Listener:

Always specify MultiSubnetFailover=True when connecting to a SQL Server 2012 availability group listener or SQL Server 2012 Failover Cluster Instance. MultiSubnetFailover enables faster failover for all Availability Groups and failover cluster instance in SQL Server 2012 and will significantly reduce failover time for single and multi-subnet AlwaysOn topologies. During a multi-subnet failover, the client will attempt connections in parallel. During a subnet failover, SQL Server Native Client will aggressively retry the TCP connection.

I copied that directly from the BOL link above. The bolded areas in that passage are mine and I wanted to point out that it states that adding “MultiSubnetFailover=True” is the recommendation for clients connecting to AG Listeners even in single-subnet AlwaysOn topologies (FCI’s as well). If you scroll down in that full passage, you will also note the bit about OLEDB not supporting the “MultiSubnetFailover keyword”, which gives me the opportunity to again remind everyone that OLEDB for SQL Server client connectivity has been deprecated as of SQL Server 2012.

SQL Server 2012: What is “Always On” ??

This is not meant to be a posting to describe the details of the new SQL Server Always On High Availability (HA) capabilities. You can go here for a nice overview from Andrew Fryar’s blog.

What I want to make sure that you understand is that Microsoft has created a name for all of the SQL Server HA capabilities called “Always On”. I guess you can think of this as a marketing chore, or a more simple way to think about or name Log Shipping, Clustering, Mirroring and Availability Groups.

Notice that last item in my list above: Availability Groups. That is the real NEW capability in SQL Server that uses Database Mirroring features to allow multiple readable secondary replicas and groupings of databases for failover, backup and reporting.

Now, note that on the new SQL Server 2012 Books On Line page on MSDN for licensing Availability Groups, it is an Enterprise Edition only feature, while “Always On” includes Clustering, Mirroring & Log Shipping, all of which still partially support Standard Edition of SQL Server, click here.

Cool Features in SQL Server Denali HADR

If you haven’t been following the slowly oozing information spigot from the next SQL Server version, code name project “Denali”, then you may not be familiar with some really cool, new features implemented around high-availability or what is sometimes currently being referred to as HADR (High availability disaster recovery).

SQL Server has had log-shipping since I don’t remember when, database mirroring since SQL Server 2005 and Windows Clustering has been available to cluster SQL Server for many releases, too.

But these have existed as somewhat independent technologies which an administrator needed to learn about separately and an architect needed to design a SQL Server solution understand the risks, benefits and costs of each. Also, DBAs don’t always have the skills, experience, or network & system access to set-up and configure clustering software.

A lot of this has been consolidated and improved in Denali and I’ve been going through the latest features and roadmap leading us to the Always On HADR feature set in the next SQL Server. Below is my own compiled list of what I perceive to be the greatest benefits that a SQL Server DBA will receive from SQL Server Denali around high availability:

  1. You can have multiple mirrored databases from a single primary DB, known as failover replicas.
  2. You can now report from the mirrored database by connecting directly to the replica. Not need to take snapshots any longer.
  3. Geo-clustered SQL Server now supports multiple subnets
  4. Once you have a configured cluster, SQL Server can interop with the cluster software from T-SQL using the cluster API, keeping your hands clean from touching the Windows Cluster


For more details on the latest of the Denali high availability features, click here. BTW, straight-up mirroring, log shipping & Clustering will still exist by themselves, too, in SQL Server post R2.

A Database Mirroring Checklist

I just spent the week talking with 3 different customers about high availability (HA) for their SQL Server environment. In each case, we debated the merits, benefits, costs and architectures of database mirroring vs. Windows/SQL clusters vs. a combination of those approaches. I don’t want to say that after these architectural design sessions that I would reach a generalized position because mileage varies depending on many factors. A few factors that come into play in an HA decision are costs, network, workloads, DBAs, complexity, and many others. One thing that worked well in each of these sessions, which is a good general practice, is to work with your business, essentially your customers & users, and come to a contracted agreement of what your SLA and requirements will be in terms of downtime, maintenance windows and disaster recovery requirements.

Having those business requirements up-front will steer your decision toward clustering, mirroring, both, or some other solution.

Now, back to the topic at hand: database mirroring in SQL Server. Regardless of whether you decide to mirror in high-safety mode (synchronous), asych, witness … there is an EXCELLENT checklist from a Microsoft Premier Field Engineer (PFE), John Daskalakis here. I used this in each case of my architectural customer sessions. I also love to link to PFE blogs because Microsoft has some of the brightest, best and smartest SQL Server people in the world working for the Premier support team. Enjoy!