In this post, we’ll walk through how to set up a SQL Server Always On Availability Group (AG).
I have two Windows Servers – SQL01 and SQL02. Both are set up in a Windows Server Failover Cluster (see this post for more detail on that). Now we’ll walk through the steps that are required within SQL Server to set up an Availability Group between the two SQL Server instances on SQL01 and SQL02. I will use the OLTP and OLAP WideWorldImporters databases in this demo. Both databases will be members of the Availability Group we create, meaning both databases will fail over at the same time if the AG fails over. An Availability Group with more than one database requires SQL Server Enterprise Edition. Both instances I will work on are SQL Server 2022 Developer Edition and so benefit from Enterprise Edition features. Both servers are Windows Server 2022.
Configuration Manager Settings
First up, there are a couple of things we need to do in Configuration Manager on each replica that will be participating in the Availability Group. We need to Enable Availability Groups at the service level. To do this we bring up the properties for the SQL Server service:

The cluster name is auto detected but we do need to click the box to enable Always On Availability Groups:

We do this on both replicas, which then requires a service restart on each:

For simplicity, I am running both instances under the same domain account, though it is possible to use local accounts (we’ll look at this in a later post):

Setting up the Availability Group
The rest of the process is from within the warm comfort of SSMS. Let’s connect to the node that will be our primary, SQL01, and start the availability group wizard:

Now let’s give our AG a sensible name. The cluster type will be WSFC and we’ll leave the other check-boxes un-ticked for simplicity at this stage:

Next, we select the databases that we want in our AG and we hit our first issue – the databases need to be in full recovery model as transaction log records are sent from the primary to the secondary to keep them synchronized. Simple recovery model truncates the transaction log when a checkpoint occurs and so is not supported.

This is easy enough to change:
ALTER DATABASE WideWorldImporters SET RECOVERY FULL;
ALTER DATABASE WideWorldImportersDW SET RECOVERY FULL;
Now if we click refresh, the status will update and we can see we need to take a full backup:

A full backup can be taken as below. D:\SQLBACKUP is my default backup location (obviously in a production environment you would not be backing up to the same drive as your data drive, though this backup is just for seeding purposes)
BACKUP DATABASE WideWorldImporters
TO
DISK = 'D:\SQLBACKUP\WWIFull.bak'
WITH
STATS = 1;
BACKUP DATABASE WideWorldImportersDW
TO
DISK = 'D:\SQLBACKUP\WWIDWFull.bak'
WITH
STATS = 1;
Once complete, click refresh and we can see the databases we want in our AG now meet the prerequisites and as such, we can select them:

Clicking Next moves us on and we now have to connect to the secondary replica (SQL02) by clicking Add Replica:

A login prompt appears and we need to log in to the secondary instance:

Once connection is successful, the instance shows in the replica list. I’ve also changed the availability mode to synchronous commit as this allows failover without data loss as well as being a requirement for automatic failover. I’ve also set both nodes to support being a readable secondary:

The next tab will set up the endpoints, which I have left as default:

The next tab allows us to set the replica we want to be the preferred backup replica. I’ll choose prefer secondary so automated backups occur on the secondary. There is more to this than initially meets the eye – more on that in a future post.

The next tab allows us to set up a listener. Connecting to a listener means we will automatically be connected to the primary replica without requiring any knowledge of which instance that is or needing to do anything special with connection strings. The DNS name will be created in DNS on the domain controller. An Active Directory computer object also gets created. The account I am using has permission to create the objects. I’ve set a port number and I’ll give it a static IP by clicking Add:

The resulting dialog allows us to create the static IP of the listener. Let’s give it a suitable IP address:

Clicking OK returns us to the wizard and we can see our changes have been reflected:

Finally, I’ll leave all the read-only routing settings as default. Like a lot of these settings I am skipping past, we will look at them in later posts.

Moving down the sections on the left to Select Data Synchronization, we specify how we will seed the secondary database. My two instances have the same data and log file directories so I can use automatic seeding. If not, we would have to restore the databases to the secondary and then manually add them to the availability group.

SQL Server now runs some validation:

Once we click Next, we get a summary of everything that is about to take place. We can also script this out if we need to at this point:


After clicking Finish, SQL Server finally sets up the AG for us:

Success!

Verifying the Setup
In my original attempt, we can see in our Object Explorer that the AG has been set up but on the secondary, both databases have exclamation marks against them:

When I tried to join the databases to the availability group, I got this hanging screen:

A perusal of the SQL Server error log gave the following error:

The issue was the Windows Firewall. I added a rule on both replicas which allows traffic on the endpoint port of 5022:

With the rule in place, both databases went green. If we expand the databases node in the Object Explorer we can see that the databases are now synchronised:

Testing Failover
As everything now looks to be OK, let’s test failing over. We can keep track of which node is the primary using this query:
SELECT g.name,
s.primary_replica
FROM sys.dm_hadr_availability_group_states s
JOIN sys.availability_groups g
ON g.group_id = s.group_id;
The same result is returned when executing the query against either replica:

We can fail over the AG by issuing the following command on the node we want to fail over to (SQL02 in our case):
ALTER AVAILABILITY GROUP [WideWorldImportersAG] FAILOVER;
After doing so, we can verify the AG has failed over using the same query as above:

Testing the Listener
We set up a listener as part of our setup, this is a virtual IP and DNS entry that we can connect to which will always point us at the primary replica.
We set our listener up with the DNS name WWIAGListener and so we can connect to that as though it were any other instance (though note that we do have to be on a machine that is connected to the domain as that is where the IP and DNS entry exist)

Now we can verify which instance we are actually connected to by executing:
SELECT @@SERVERNAME;

If we fail back to SQL01, by executing the following from a connection to SQL01:
ALTER AVAILABILITY GROUP [WideWorldImportersAG] FAILOVER;
Running the @@SERVERNAME check against the listener connection again, we can confirm that the replicas have failed over:

Asynchronous Commit
Up to now, the availability group has been in synchronous commit mode which means that a transaction that is committed on the primary replica must also be committed on the secondary replica (or replicas) before returning control back to the client.
We can change the mode to asynchronous commit, which can be done by executing the following against any of the replicas:
ALTER AVAILABILITY GROUP [WideWorldImportersAG]
MODIFY REPLICA ON N'SQL01' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);
ALTER AVAILABILITY GROUP [WideWorldImportersAG]
MODIFY REPLICA ON N'SQL02' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);
Now if we look at our Object Explorer, the state of the databases has changed – both databases now show SYNCHRONIZING on the secondary replica:

This is because as stated in the documentation:
Under asynchronous-commit mode, the secondary replica never becomes synchronized with the primary replica. Though a given secondary database might catch up to the corresponding primary database, any secondary database could lag behind at any point.
This mode does not support automatic failover and now when we use the same failover command as before…
ALTER AVAILABILITY GROUP [WideWorldImportersAG] FAILOVER;
…we get the error below.
Msg 41142, Level 16, State 34, Line 13
The availability replica for availability group 'WideWorldImportersAG' on this instance of SQL Server cannot become the primary replica. One or more databases are not synchronized or have not joined the availability group. If the availability replica uses the asynchronous-commit mode, consider performing a forced manual failover (with possible data loss). Otherwise, once all local secondary databases are joined and synchronized, you can perform a planned manual failover to this secondary replica (without data loss). For more information, see SQL Server Books Online.
The bit that applies to us is:
If the availability replica uses the asynchronous-commit mode, consider performing a forced manual failover (with possible data loss)
In asynchronous mode we need to specify data loss when failing over, which has the obvious downside that this can cause data loss!
ALTER AVAILABILITY GROUP [WideWorldImportersAG] FORCE_FAILOVER_ALLOW_DATA_LOSS;
Asynchronous-commit mode is something we may decide to use for DR purposes – e.g. we may have a primary and a secondary replica in close geographic proximity with low latency in synchronous-commit mode to allow for HA and then a third replica, a further geographic distance apart set to asynchronous-commit mode which could be failed over to in the event of a DR scenario. In this situation, we would lose some data but the business is still online despite some disaster taking out both the primary and the secondary.
Conclusion
In this post, we’ve set up an Availability Group in two failover modes – synchronous and asynchronous, we’ve then tested failing them over and connecting to the listener as well as looking at the prerequisites that were required to set up the AG.
References / Further Reading
Alejandro Cobar – Create an Always On Availability Groups using TSQL
Microsoft – Differences between availability modes for an Always On availability group
Microsoft – What is an Always On availability group?
Microsoft – What is an availability group listener?
Microsoft – Wide World Importers sample database v1.0
Rajendra Gupta – When and How to Use Asynchronous Commit Mode for MS SQL Server Always On
