By [Your Name]

Ensuring the availability and resilience of databases is a crucial responsibility for database administrators (DBAs). In the event of a major outage, such as a server failure, it is essential that databases can continue to function without interruption. In the past, SQL Server offered the Database Mirroring feature, which allowed for a single secondary server and did not support grouping databases for failover. However, with the introduction of Always On Availability Groups (AOAG) in SQL Server 2012, DBAs gained access to a more robust and reliable solution.

Always On Availability Groups provide several advantages over Database Mirroring. Firstly, AOAG allows for multiple secondary servers, enabling greater scalability and redundancy. Additionally, databases can be grouped together to failover as a unit, ensuring consistency and minimizing downtime. SQL Server 2014 further enhanced AOAG by increasing support for up to eight secondary replicas, which can still be utilized for read-only workloads even when the primary server is unavailable. Furthermore, SQL Server 2014 introduced support for In-Memory OLTP databases, improving performance for certain workloads.

SQL Server 2016 introduced additional improvements to Always On Availability Groups. Failover is no longer limited to server failures; it can now also occur at the database level. This means that if a database becomes corrupt due to a disk failure, for example, all the databases in the Availability Group can failover to a secondary server. This enhances the overall resilience and availability of the system.

Windows Server 2012 introduced the concept of multi-site clusters, which allows servers in different locations and on separate network subnets to form a cluster without the need for shared storage. SQL Server Always On Availability Groups can be built in both multi-site and single-site failover clusters. However, it is important to note that AOAG relies on a Windows server Failover Cluster and Active Directory domain. Therefore, DBAs must collaborate with the Windows server team to ensure the correct setup of the Windows components.

In this article, we will focus on the steps required to set up a basic two-node Always On Availability Group on a Windows cluster. Although we will be using SQL Server 2012 and Windows 2012 R2 as examples, the process remains largely the same for later versions. It is worth mentioning that SQL Server 2017 introduced the ability to create Always On Availability Groups without the need for a cluster or Windows domain. Additionally, SQL Server 2017 offers equivalent features for Linux, but that is a topic for another day.

Requirements

To set up an Always On Availability Group, you will need the following:

• Two Windows 2012 R2 (Standard or Datacentre) servers that will be joined into a cluster. These servers will serve as the cluster nodes.
• SQL Server 2012 or above Enterprise Edition installed on both servers (nodes).
• A file share on another location, preferably using 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 will be used for the Cluster Listener, which is the name used to connect to the cluster.

In the next post, we will explore the configuration of the Windows Server Failover Cluster, which is a prerequisite for setting up an Always On Availability Group.