Skip to main content

Building Always On Availability Groups - Part 1


By David Postlethwaite

Ensuring that your databases can survive a major outage such as a server failure and can continue to work is an important task of being a DBA.
Database Mirroring was introduced in SQL 2005 and was quite straight forward to setup.
Mirroring only allowed for one secondary server and there was no way of grouping databases to failover together.

In SQL Server 2012 mirroring was replaced with Always On Availability Groups (AOAG). It can have multiple secondary servers and databases could be grouped to failover together along with other feature making it much more resilient and reliable.

SQL Server 2014 increased support for up to eight secondary replicas that are still available for read-only workloads even when the primary has gone down. It also gave support for In-Memory OLTP databases.

SQL 2016 made improvements by allowing failover not just on server failure but optionally also at the database level. So if the server is still running but a database has become corrupt, say because a disk has gone down, then previously nothing would have happened. Now all the databases in the Availability Group can failover to the secondary.

Windows Server 2012 introduced the use of multi-site clusters. That is, servers that are not in the same location and not on the same network subnet and do not require shared storage.  SQL Always On Availability Groups can be built in a multi-site or single-site failover cluster.

Always On Availability Groups require a Windows server Failover Cluster and Active Directory domain which means the DBA is reliant on the Windows server team to build the Windows part correctly.

 The aim of this document is to provide the steps for a basic two node AOAG setup so the DBA has some understanding of what the Windows server team need to do.

This document describes the building of an AOAG on a two node Windows cluster. I’ve used SQL 2012 and Windows 2012 R2 because I was building a failover group for SCOM 2012 R2 which only support SQL 2012. But the process will be the same for later versions.

SQL Server 2017 will allow us to create Always On Availability groups without the need for a cluster or without the need for the servers to be on a Windows domain. There will also be equivalent features for Linux. That is for another day.


You will require

  • Two Windows 2012 R2 (Standard or Datacentre) servers that will be joined into a cluster. These two servers will be known as cluster nodes
  • SQL Server 2012 or above Enterprise Edition installed on both servers (nodes)
  • A file Share on another locations  (SMB 2 or above)
  • For a multi-site cluster the servers must be on separate subnets
  • A fixed IP address for each server
  • A second fixed IP address reserved for each server. This is for the Cluster Listener. The Listener is the name that you will use to connect to the cluster.
In my next post I will look at configuring the Windows Server Failover Over Clusetr


  1. Im a Windows Sysadmin with some SQL Server experience. I just built a 2 node AOAG on Server 2016 Failover Cluster. It was a major learning experience, but at the end of the day it wasn't as difficult as I feared. In fact, it gave me the confidence to ask my boss to allow me to get my hands in more database efforts.

  2. Is there an attachment? I see references to 'this document', but I do not see an attachment and the article ends with a short list of requirements and nothing else. Perhaps you are referencing the entire set of articles you plan on publishing as 'this document'?
    Jeff B

    1. Hi Jeff - There will be more posts to come


Post a Comment

Popular posts from this blog

SQL Server 2012 and Virtual Service Accounts

This post is written by David Postlethwaite
If you are using SQL Server 2012 you will probably have noticed that the default account for the SQL services has changed from that used in previous versions. With SQL 2005 and 2008 the default account for SQL service and SQL Agent service was “NT Authority\System”. This is one the built in accounts on a Windows machine, managed by the machine and selectable from a dedicated dropdown list

The Network Service account was introduced in Windows 2003 as an alternative to using the LocalSystem account, which has full local system privileges on the local machine, a major security concern.
The Network Service has limited local privileges easing these security concerns but when many services on a machine use the Network Service account it becomes harder to track which service is actually accessing resources and performing actions, because all the services are using the one Network Service account.
Also, this account, by default, has sysadmin per…

Always Encrypted

By David Postlethwaite

Always Encrypted is new features in SQL Server 2016 and it is also available in Azure SQL Database. Here you can encrypt columns in a table with a master key and a certificate so that they will appear as encrypted strings to those who don’t have the required certificate installed on their pc.
Once the certificate is installed on the computer then the unencrypted data can then be seen as normal.

The data passes from database to your application as the encrypted value, only the application with the correct certificate can unencrypt the data so it is secure across the wire. This will go some way to resolving the concern of people worried about putting their sensitive data on a shared server in the cloud such as Microsoft Azure and accessing the data across the Internet.

At the time of writing Always Encrypted is only supported with ADO.NET 4.6, JDBC 6.0 and ODBC 13.1 but expect other driver to become available.

The calling application (including SSMS) must also hav…

New in SQL Server 2017: Graph Databases

David has recorded and published a video of his presentation on SQL Server Graph Database. In his video which you can watch below, David provides an excellent introduction into SQL Server 2017 Graph Databases. In his presentation he looks at Tennis results at tournaments for  his favourite player "The Fed"  Rodger Federer.

David  shows how to set up graph database and work with them in SQL Server 2017.

Graph Database is not new. Other vendors have had graph database capabilities for some time so Microsoft are quite late to the market. In David presentation it appears that Microsoft have done a reasonable job of implementing some of the graph database features but he does point some of the limitations of the Microsoft product too and suggests that it is not ready for production yet but Microsoft seem serious about this feature.

Please watch the video and feel free to leave a comment or feedback - David is delivering a version of this talk on Graph databases in SQL Saturday Ka…