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 Maximum Availability: Denali & Private Cloud ?

As I am working my way through the latest updates in the betas of SQL Server Denali, I’ve been thinking about different architectures to maximize database and application availability with the new SQL Server platform. It is important to note that one the most hyped, popular and intriguing architectures for database and applications, both from my interactions with customers and Redmond, is utilizing what we call “Private Cloud”.

I’ve written about a SQL Server private cloud before in this blog and I often refer to it as “optimized data center” for customers. The reason for this is that for some, it can be confusing when software vendors are talking about public clouds like Azure, Amazon, RightScale, etc. or virutalized local environments in your data center. Private cloud is taking the concepts of Public Cloud like elastic scale, metered billing, self-provisioning, consolidated servers, maximum utilization of infrastructure to maximize, ROI, etc. and making those concepts viable in your own data centers.

SQL Server Denali introduces an update to database mirroring that combines the Microsoft technologies of mirroring, clustering and replication into a very simple easy-to-configure and maintain set of “availability groups”. With Denali, you can take databases that are part of an application, put them into this availability group and even have multiple replicas (aka database mirroring with multiple read-only partners) that you can report from or back-up from.

My thinking is that combining these 2 technologies will give the SQL Server DBA community 2 key things that were either not available before in SQL Server or are better now with this architecture:

  1. Combining applications into availability groups that allow me to move reporting and backups completely off the primary production database and to the replicas.
  2. Create a SQL Server farm where I can use the Microsoft System Center suite for server load balance, full server density, Live Migration for zero downtime patching and managing my instances as SQL Server virtual machines.

The slide below is from a presentation that you can download from the Microsoft SQL Server private cloud link that I included below. This architecture works with SQL Server Denali since I would configure availability groups for my application databases using AlwaysOn database replicas, which require Windows Cluster Server as shown below. The virtualization command and control software (Hyper-V and VMM in this case) will allow me generate a SQL Server farm where I can move the instances around from server to server, using Live Migration. So while HADR provides the tools for protection against failures and guest-level patching, Live Migration and VMM give me the ability to load balance, utilize full server resources and move running instances from server to server with no downtime. The assumption in this architecture, BTW, is that I am running a single SQL Server instance per VM, so that when I say “move an instance” in this grid architecture, I am moving a full guest OS VM.

Go here for more details on SQL Server Denali. And here is the Microsoft SQL Server private cloud home page.

Your thoughts and suggestions are very much welcome in the comments. Thanks! Mark

UPDATE: The SQLOS Team blog on SQLBlog added an excellent post that you should bookmark for SQL Server Private Cloud background info: http://sqlblog.com/blogs/sqlos_team/archive/2011/06/08/sql-server-virutlization-consolidation-and-private-cloud-resources.aspx