Step by step guide to take SQL Server 2012 new feature Available Groups (Hadr) to Production

Duane LawrenceSQL Server database administrator
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience.

Defining the name:
When I talk to people they say different names on this subject stuff like “Highly Available”, “Available Groups” or sometimes “Always On”, all can mean the same thing.  But in the SQL Server SSMS I find names like “AlwaysOn” or “Hadr” and again all can mean the same thing.  “Hadr” is an acronym for “High Availability – Disaster Recovery” and is a new feature only available in SQL Server 2012.

My Day Job:
      At my day job we have really cool toys like servers with 64 cores and all the hard drives are Solid State Drive (SSD).  We do crazy stuff like order a new server and have the hardware and SQL Server 2012 installed and running in production in 4 days, so yes it is a demanding job.

Big Thumbs Up Microsoft: AlwaysOn catches back up
      When the replica went down due to us having to change the hard drives for faster ones (see Pitfall1).  We did nothing but turn it back on and the AlwaysOn caught backup , we just sat there and watched the yellow triangles slowly one by turn green as they became current.  We also popped a breaker and the replica lost power, again we simply turned the server back on and watched all the yellow triangles one by one turn to the green check circle for good.

Big Thumbs Up Microsoft: Wizard to create AlwaysOn is rock solid:
      I am an old school DBA, if I do most functions in T-SQL and true to my nature I tried it this way.  While it will work and the wizard will script out the commands for you, I recommend using the wizard.  The wizard did throw errors due to me forgetting tiny details that were absolutely essential, the errors were legible so I could figure out the problem and correct it.  The bonus is all I had to do in most cases was hit the back button and then go forward again to re-try.  Totally awesome is all I have to say, well there was one pitfall so see Pitfall3 for details.

Building the Available Group cluster:
      For this I was given Domain Admin powers on top of my SA on the SQL Server databases.  I got all kinds of weird errors till the real Domain Admin gave up trying to find any documented from Microsoft that said what security privileges a person needs.  If someone finds a better way on security I would like to hear from them.  My domain admin gave me a stern lecture about if I touched anything and broke production he would come after me, to which I replied if I was not sure about something I would not do it and go talk to him.  We also had all instances of SQL Server running under a service account with the following list of privileges:
•      Act as Part of the Operating System = SeTcbPrivilege (only required for SQL Server 2000)
•      Adjust memory quotas for a process = SeIncreaseQuotePrivilege
•      Lock Pages In Memory = SeLockMemory (I used this by design on Server 2008 but did not chance it and followed the recommendation for 64 bit editions and only required on any 32 bit edition running with AWE for extended memory)
•      Log on as a Batch Job = SeBatchLogonRight (only required for SQL Server 2005)
•      Log on as a Service = SeServiceLogonRight
•      Replace a Process Level Token = SeAssignPrimaryTokenPrivil ege

Step 1: Build the cluster:
      This will not be like any other clustered SQL Server cluster you are familiar with, there is no shared drive (quorum) inside the cluster and SQL Server on each node is installed as a standalone install (not a SQL Cluster install).  On top of that the new Windows Server 2012 clustering can be done in Standard.   It just keeps getting better, when a “fail over” happens it can be just one service under Windows Server 2012.  This is similar to defying gravity to me, because under Windows Server 2000 when it failed over the whole server would fail over so if you were remote desktoped in the remote session would die and if you waited 30 seconds or so you could log back in and everything was the same yet if you looked at what node you were running on that had changed.  With individual services failing over the lag from the fail over is so small that a human almost perceive it happen.

      I prefer to do things the easy way, using PowerShell I can build a cluster in 3 minutes, so here we go.  One last detail on why PowerShell is easier, we are using Windows Server 2012 which has a Graphic User Interface (GUI) that is Window 8 and as such all the stuff that I knew how to do on any previous version of Windows Server is now absolute.  If you want to build it using the GUI one time so you can know what you are doing in PowerShell it will take 3 hours or that is what it took me.

1.      Install the window feature at the Operating System (OS) level.
Install-WindowsFeature –Name Failover-Clustering –IncludeManagementTools
2.      Test the servers to see if they are compatible and can go into the cluster.  Hint, without this report the command to build the cluster will fail.
Test-Cluster –Node sql3,sql9
3.      Create the cluster
New-Cluster –Name SQLCLS1P –Node sql3,sql9 –StaticAddress
4.      Add the quorum which in this type of cluster cannot be in the cluster. (see disclaimer1)
Set-ClusterQuorum –Cluster SQLCLS1P –NodeAndFileShareMajority \\\quorum

      One thing I would like to address with the quorum, this specific example puts the quorum on a share on the domain controller and I know this will make any domain admin cringe and I would agree with them.  We did it this way because we had to have it done and fast, I do not advocate this but it does work.  I did try to get it to work on a normal file share but I got error after error and when I did get it to work, I had to lower security to let anyone modify the share.  Imagine some normal person looking around on the company network to put their personal files and finding this share and deleting the stuff there and unknown to them all of the clusters go down.  So we chose the lesser of two evils and went with the secured quorum share on the domain controller.

Step 2: Build the AlwaysOn Group: Architect / design first
      Each database can be in a separate AlwaysOn group and each group can fail over independently from the others, but if you do this remember that each AlwaysOn group needs a separate IP.  You will also need one IP for the virtual address of the cluster.  So if you have 10 databases that you want to put into separate AlwaysOn groups then you will need 10 IP for the AlwaysOn groups (actually the IP is on the listener) and one additional IP for the clustered server virtual IP.  If you put all databases into the same group you should read Pitfall2 first.

The replica can take the read only report queries so you can off load some of the work load and more fully use the really expensive servers that the company is paying for, but design the replica hardware accordingly.  Do some math and make sure the hardware will have the capacity is the best way.  In comparison, under the old method of clustering on Windows Server 2000 the other servers were doing nothing and when the cluster failed over the whole server changed with a lag period that humans can perceive.  So the new method of individual services failing over to another server is indeed better because the lag period is barley noticeable.

Step 3: Build the AlwaysOn Group:
      Open up the SQL Server Configuration Manager and right click on the SQL Server service and choose properties.  Now click on the “AlwaysOn High Availability” tab and put the check mark next to “Enable AlwaysOn Availability Groups”.   If you don’t see “Enable AlwaysOn Availability Groups”, it means that something is not configured correctly at the cluster level and this will not show at all.  Yes, the cluster must be built for “Enable AlwaysOn Availability Groups” to show.

ScreenShot1: SQL Server Configuration Manager  (names were changed for security reasons)

      In SQL Server Management Studio (SSMS) for 2012 you will see and new branch in the tree called “AlwaysOn High Availability” expand this and right click on “Available Groups” and choose “New Availability Group Wizard”.  Please keep in mind the screen shots are from production so the “SQL2_...” is an already working AlwaysOn group, I simply took the screen shots during the writing of this after it was built and working.

ScreenShot2: SQL Server Management Studio (SSMS)
Now enter a name for the AlwaysOn group and copy the name into Notepad++ or another text editor because you will need it later.  This is another reason why I say this is well done, this gives you all the information you need.  For instance, it lists the size of each DB and tells you if it is in Full Recovery mode or not.  It was my experience that any database of 400 GB or less gets put on the replica in the wizard in 15 minutes or less, but the one that is just a little short of 1 terabyte in size took 4 hours with SAS drives and 3 hours with SSD drives so the moral of the story is for really big files the network speed matters more than the hard drive speed(see ScreenShot3).  Just pick one database by putting a check mark next to it, you only want to because you can add the rest after the AlwaysOn group is built.


This is where you add the replica by clicking on “Add replica…” button.  This is the server that will be the secondary and if it fails over it will become the primary.  Pay close attention to every detail on the “Replicas” tab.  At first we went with “Syncronous”, “Automatic Fail over” and “Read-intent only” on the secondary.   You can change these options after the fact but forgot details can come back to haunt you.  The “Read-intent only” is what will allow reports or other read only queries to connect to the replica and run queries, thus lightening the load on the primary server.  I skipped the listener tab because when I did it the first time it threw an error and I went back to my preference of doing stuff in T-SQL.  I tried the listener tab before I had domain admin privilege so it could work just fine.


When it comes to the “Select Initial Data Synchronization” screen, I tried the “Join Only” by manually backing up the DB and transaction log tail and copying them over and restoring them, then trying to sync them and it worked sometimes and sometimes not.  What ALWAYS worked was “Full” which is the default.  I did have to adjust the production transaction log backup to NOT backup the database I was adding, if I forgot it would break the correct sequence of transaction log restores.


Step 4: Build the listener:
      Remember that I had Domain Admin privilege because this command changes stuff on the domain controller.
ALTER AVAILABILITY GROUP HA_name_copied_from_specif y_name_tab
WITH IP ((N'', N'') ) , PORT=1433);

Note is the IP of the listener.

Step 5: Build the Read intent routing
      Without both the screen shot set correctly and the values in the table “sys.availability_read_onl y_routing_ lists” set correctly, we could not get the read intent only routing to work correctly.


select * from sys.availability_read_only _routing_l ists





Pitfall1: Lag on the replica caused lag on OLTP servers
      We had big lag problems, to the extent that the query below showed (see SQL_Monitor1) us over an hour behind.  We had two SQL Server 2012 production servers each with an AlwaysOn group replicating to the same secondary node.  Now the pitfall comes down to the hardware, both of the production servers had all SSD drives and the replica only had SAS spinning media drives, so the Perfmon counters “Physical Disk”.”% Idle Time” was cratered to zero for hours, the “Physical Disk”.”Disk Transfers/sec” was running extremely high and finally “Physical Disk”.”Avg. Disk Queue Length” was running at 3.  We had a disk IO bottleneck so we put all SSD in the replica that is receiving both AlwaysOn groups and now the ”% Idle Time” is running where it should at 95% to 97% idle.  Normally on traditional spinning media drives I only use the last two counters, but with SSD I have to include all 3 to determine if we truly have a disk bottleneck.  So the lesson learned is architect your clustered server and fail over plan in every detail and do the math on the Input Output operations Per Second(IOPS).  Normally IOPS is a detail I leave to the network admin guys, but AlwaysOn is different.

SQL_Monitor1: (Based on a query from Offloading Read-Only Workloads to Secondary Replicas - Microsoft )
select, s.synchronization_state_de sc, last_redone_time, MinutesBehind= DATEDIFF(mi, last_redone_time, last_hardened_time), redo_queue_size
from sys.dm_hadr_database_repli ca_states s
inner join sys.databases d
on s.database_id = d.database_id
order by MinutesBehind desc

Pitfall2: On auto fail over one database with a large redo log
      One day, we had a fail over and because we had all databases in the same AlwaysOn group they all failed over.  An important detail to remember, one company had a large redo log and that database was not coming online because the redo log had not finished playing.  To make matters really go south not all of the web page code was failing over correctly so we had to fail over back to the original server but because the one database with the large redo log was not online, the AlwaysOn group would NOT allow a fail over.  We had to get back online, so I destroyed the AlwaysOn group and recovered the databases on the correct primary server and then rebuilt the AlwaysOn group in the next two days.  

Pitfall3: Lay out all drives and paths for the database files
      The create an AlwaysOn wizard while awesome rock solid working code, does not allow you to change the path of the database files at all.  This can be worked around by architecting you servers going into the cluster to all have the same drive layouts and all have the same path to the database files.  In most places I have ever worked, the network admin types build the servers and just name the drives as they see fit and each one will have a different opinion, be warned and reject the servers for rebuild and done exactly as specified.  The consequences are you will be up all night fixing the drive letters and moving data files like I was.

Pitfall4: Unexplained fail overs in Windows Server 2012
This has to do with the number of errors allowed in a period of time.   This setting is set in the Cluster Manager and if you are reading this and you as a DBA don’t have access to it, show them the screen shot 1.  The default is 2 cluster errors per block of 6 hours.  Let me explain, unknown to us till I dug into the logs, one of the servers was being removed from the cluster and when the cluster service restarted on it, it would rejoin.  Now this is where the rock solid ability of AlwaysOn is wonderful, it was catching back up or in other words running just fine.  We have yet to figure out why the cluster service on one server was crashing sometimes and others times just stopping and restarting.  When the number of cluster errors goes to 3 a chain of events happens.  The cluster service notifies SQL and then SQL has a series of state changes, but the one that you need to look for is “resolving”.  During the resolving period, nothing can connect to either the primary nor the replica, now it normally lasts for about 5 seconds so in many cases you may not know about the resolving state, but if it goes longer I promise you will hear about it.
In cluster admin in the OS, under roles you will find the AlwaysOn groups, right click and goto properties and we have changed the default "Maximum failures in the specified period" from 2 to 3 and the "Period (hours)" from 6 to 4.  (in cluster admin,).  To watch the AlwaysOn state that is directly effected by this cluster setting see the DMV "sys.dm_hadr_availability_ replica_st ates", in the column "role_desc" when it says "RESOLVING" nothing can connect to nether the primary nor the replica.  The RESOLVING state is when it is deciding whether it should fail over and this is based on the number of errors that the cluster has seen at the OS level.  See screen shot 1.
Screen shot 1:

This is a screen shot of the error one can see if you catch the AlwaysOn in the middle of deciding whether or not to fail over.

-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------
Please see the attached word document that is a prettier and master copy of what is here.
Duane LawrenceSQL Server database administrator

Comments (2)

Duane LawrenceSQL Server database administrator


1. I put a link in my bio.
2. I was under the impression that the reason I was asked for the original article in word format was because employees of EE had a method of importing a Word formated article?  I can still do as you ask, it just seems that importing the already pretty article would be easier.
3. see 2
4. Much of this is original work I perfected on the job.  I was not able to find this working code anywhere and I will not refer to something that is flat out wrong.  Any database admin can copy and paste this code and it will work.  Let me put it another way, the stuff on AlwaysOn already out on the web was done by professional writers that do not support production while I work for Exigo and really I am supporting this in production.  Exigo is an ASP model provider for sales commissions, which is nothing to do with the publishing industry.  Much of the stuff on this subject is dated around the same time, which is beta period of SQL Server 2012, so they are the ones that can write fast and not always correct.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Hi Duane

Looks like this draft is a year old, let us know what you want to do with it as I see editor comments that need attention.  


Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.