Kieran Jacobsen

Kieran Jacobsen

He/Him. Microsoft MVP and GitKraken Ambassador. ๐ŸŒ Poshsecurity.com. ๐Ÿณโ€๐ŸŒˆ Gay. ๐Ÿฑ Cat owner.

Tips For Managing Microsoft SQL 2012 Always On Availability Groups

Recently I have been spending a significant amount of time working with high availability and disaster recovery solutions. A large amount of this work has been around the deployment and use of SQL2012 AlwaysOn Availability Groups.

Availability Groups should not be confused with SQL Clusters, whilst both technologies make use of Windows Fail-over Clustering; they achieve their goals in some very different ways.

I am not going to go into specifics around the design and deployment of an AlwaysOn Availability Group solution; however, I have some design tips that should help you suffer any serious setbacks.

Firstly, confirm vendor application support. This might seem obvious but it is extremely important. There are a number of applications that do not support AlwaysOn AGs, the list includes Microsoft SCCM2012R2 (and other products in the System Centre Family) as well as some products from Citrix (patches on their way). If you are running any of the products that do not support these features, then you will need to revert back to a normal Fail-over Cluster (or in my case, a geo-cluster).

Secondly, find a reliable place for a File Share based Witness, preferably a second side. This tip is of particular importance if you are running across multiple sites. You should be also aware that the server hosting the share must to be in the same domain as the SQL servers.

Next, check the implications of using AlwaysOn AGs on your Microsoft Licensing. All of the new features come at a cost; you need ensure that you do not affect your overall solution costs. Remember, with AlwaysOn AGs, SQL is actually running on all of your nodes, all the time and this could affect you license requirements.

One annoying thing, you cannot easily create our listeners if you do not have any databases. My tip to bypass this is to simply create an empty database, and then create the AlwaysOn AG and its associated listener based on that database. Once you have that done, then your applications installers can be run against the listener. REMEMBER though, that if an application creates databases via the listener name, they are not configured as part of the AlwaysOn Group, you will need to go and do that manually! I was caught with this one.

I had some issues when creating AlwaysOn AG and the associated listener. These two articles really helped me out:

Welcome to Posh Security

Sending SYSLOG messages from PowerShell