In the last weeks and months I have prepared tons of SQL Server 2012 content for my own SQL Server 2012 Deep Dive Days workshop that I’m currently running across Europe. Because this content shows in a very good way how you can use all the various new features of SQL Server 2012, I have decided to share over the year some of that content through my weblog. Today I want to start with SQL Server 2012 AlwaysOn Availability Groups, because that’s one of the hottest features in SQL Server 2012!

I want to take you from a zero knowledge level and show you step by step how you can configure, deploy, and manage a SQL Server 2012 AlwaysOn High Availability solution. In today’s first part I want to talk about the basic architecture of AlwaysOn and how you can prepare SQL Server for running an AlwaysOn solution.

Overview

AlwaysOn Availability Groups is the big new feature in the High Availability stack of SQL Server 2012. Simply said it is the successor of Database Mirroring that was introduced with SQL Server 2005 SP1. Database Mirroring is a great technology for achieving HA (High Availability) and DR (Disaster Recovery), but has some weaknesses and limitations:

• You can only mirror 1 database through a Database Mirroring session. When you have an application that consists of several database (just think of SharePoint Server), then you need to have several distinct Mirroring sessions.
• The Failover is done on the Mirroring session level, which means you can only failover 1 database at a given time. Again – if you have more than 1 database, you need different Mirroring sessions and you have to coordinate a multi-database failover at your own.
• There is only 1 Mirroring partner possible. You can’t mirror to different partners. The Mirroring partner can be run with Synchronous Commit for achieving High Availability or with Asynchronous Commit for achieving Disaster Recovery. If you are using Synchronous Commit you can get an Automatic Failover if you deploy a Witness instance. This witness instance is used for acquiring a quorum and avoiding a so-called Split-Brain scenario.
• The Mirroring database is always replaying Transaction Log records, which means that you are not able to access the database for read-only access (the undo part of Recovery has not yet run). The only possibility is to use Database Snapshots to get a consistent view of your database at a given point in time and refresh the Database Snapshot on a regularly basis, but again – you have to do this at your own.

Beginning with SQL Server 2012 Microsoft provides us now AlwaysOn Availability Groups which are the successor of Database Mirroring.

Side Note: Database Mirroring is still available in its original feature set, but it is marked as a Deprecated Feature, which means, that it will be removed in a future release of SQL Server.

AlwaysOn Availability Groups offers you the following advantages over traditional Database Mirroring:

• Multi-Database Failovers
• Multiple Secondaries
• Active Secondaries
• Integrated HA Management

The following figure gives you a basic overview about AlwaysOn Availability Groups.

As you can see from the previous figure, an AlwaysOn Availability Group is created between several standalone SQL Server instances. At least you need 2 different SQL Server instances. All instances that participating in an AlwaysOn Availability Group don’t need any Shared Storage – each SQL Server instance has its own local storage. It’s the same concept as with Database Mirroring. The only requirement is the fact that each participating SQL Server instance must be in the same Windows Domain, because you must create a Windows Server Failover Cluster (WSFC) that contains all participating SQL Server Instances. SQL Server uses underneath several WSFC features like the Quorum Model. Database Mirroring uses its own Quorum Model by using a dedicated Witness instance.

Side Note: In my opinion this one of the biggest disadvantages of AlwaysOn Availability Groups. I have a few customers which wanted to migrate from Database Mirroring to Availability Groups, but they can’t go that direction at this time, because their SQL Server instances are not in the same domain. Some even have deployed SQL Server instances in separate workgroups… But that’s the way how AlwaysOn Availability Groups are working, and that’s the price that you have to pay for that new feature.

An Availability Group itself can contain several databases, which can failover in a whole set – that’s a huge difference and improvement compared to Database Mirroring. Each participating SQL Server instance hosts a so-called Replica. There is always one Primary Replica and up to four Secondary Replicas. Up to 2 of these Secondary Replicas can use Synchronous Commit, and the other 2 Secondary Replicas can use Asynchronous Commit. When you run 2 Replicas with Synchronous Commit you can also configure an Automatic Failover between them. They are forming a so-called “Automatic Failover Pair”.

Another big advantage of the Secondaries is that they are readable. You don’t need a Database Snapshot anymore, as you would have done it with Database Mirroring. But you must be aware that there is an overhead when you are using Active Secondaries – but we will look into that topic in more detail at a later stage of this weblog series. You can also use Active Secondaries for doing backup jobs, which will be also a very nice way to move backup workload away from your Primary Replica.

As with Database Mirroring, Transaction Log records are transferred from one Replica to the others. To minimize network latency, SQL Server 2012 uses Build in Log Compression to transfer Transaction Log records as fast as possible.

Preparing your SQL Server for AlwaysOn

If you want to deploy an Availability Group solution you have to prepare your SQL Server instances in the first step. For this weblog posting I’m assuming that you have setup 5 different SQL Server installations across 5 physical servers/Virtual Machines. As an Operating System I’m assuming Windows Server 2008 R2 SP1 x64. My current Hyper-V based deployment looks like the following:

• Node1
  
  • DNS Name: ag-node1.sqlpassion.com
  • IP Address: 192.168.1.211
  • SQL Service Account: ag_node1_sqlsvc@sqlpassion.com
• Node 2
  
  • DNS Name: ag-node2.sqlpassion.com
  • IP Address: 192.168.1.212
  • SQL Service Account: ag_node2_sqlsvc@sqlpassion.com
• Node 3
  
  • DNS Name: ag-node3.sqlpassion.com
  • IP Address: 192.168.1.213
  • SQL Service Account: ag_node3_sqlsvc@sqlpassion.com
• Node 4
  
  • DNS Name: ag-node4.sqlpassion.com
  • IP Address: 192.168.1.214
  • SQL Service Account: ag_node4_sqlsvc@sqlpassion.com
• Node 5
  
  • DNS Name: ag-node5.sqlpassion.com
  • IP Address: 192.168.1.215
  • SQL Service Account: ag_node5_sqlsvc@sqlpassion.com

Each VM node is also part of my sqlpassion.com Windows domain, and I have performed a default SQL Server 2012 installation with a default instance of SQL Server 2012. Furthermore each SQL Server default instance uses a Windows Domain account for the SQL Server Service Account. You must also make sure during the installation that you place your MDF/LDF files on the same physical location on each VM node, otherwise you will run into troubles with the wizards provided by AlwaysOn Availability Groups.

The first step that you have to perform is the creation of a new Windows Server Failover Cluster (WSFC). In the first step this sounds like a huge step, but it is very easy to do because you don’t need any shared storage. One prerequisite of WSFC is the fact that you have to install the Failover Cluster Feature on each node. You can perform this task through the Server Manager of Windows Server 2008 R2.

After you have installed the Failover Clustering on EACH node, you have to log into one of these nodes through a Windows Domain user account. Otherwise you are not able to create the Windows Server Failover Cluster itself. To create the cluster itself you can use the Failover Cluster Manager, which can be found in the Administrative Tools of your Control Panel. On the right hand side you can see all available Actions, and here you choose the option “Create a Cluster…“. In the first step the wizard asks you which nodes should be part of the new WSFC Cluster. In my case I have selected all 5 VM nodes:

On the next wizard page you have to choose to run the Cluster Configuration Validation Tests. If you want to get support from Microsoft, you have to run them. For AlwaysOn Availability Groups you don’t have to include the tests regarding shared storage, because you are not using any form of shared storage for an Availability Group. After you have run your Cluster Configuration Validation Tests, you have to give your WSFC Cluster a name and an IP address:

With all these information in the hand, the wizard will finally create your WSFC cluster. After the successful creation you can see your new WSFC cluster in the Failover Cluster Manager:

As you can see from the previous figure, the Cluster is currently not hosting any Service or an Application yet. We just have created the skeleton so that an AlwaysOn Availability Group can be created and deployed. In the final step you have now to enable AlwaysOn Availability Groups on each SQL Server instance. This can be done through the SQL Server Configuration Manager and the tab AlwaysOn High Availability. You have to enable the checkbox “Enable AlwaysOn Availability Groups“. This option is only available when the computer where the SQL Server instance is running on, is part of a WSFC cluster:

After you have enabled that option, you must restart your SQL Server instance. Please make sure to do this configuration change on EACH node that is part of your WSFC cluster.

By now we have created the whole infrastructure that is necessary to run a SQL Server 2012 AlwaysOn Availability Group solution. In the next weblog posting you will then see, how you can deploy your first Availability Group with SQL Server 2012.

If you are interested in SQL Server 2012, I suggest looking on my “SQL Server 2012 Deep Dive Days Workshop”, which I run from May 28 – 30 in London/United Kingdom (see http://www.SQLpassion.at/academy, Early Bird price ends on March 31). This workshop was already running in Vienna/Austria and Munich/Germany with a huge success.

Stay tuned J

-Klaus