Introduction
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.
Requirements
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.
