SQL Server DBA Tips and Tricks

Nowadays, most suitable high availability and disaster recovery solution is AlwaysOn. It is a mixture of SQL Server Failover Cluster Instance and Database Mirroring. Here we are going to see requirements to configure AlwaysOn.

Requirements from Windows server side to Configure AlwaysOn:

1. Ensure that the server is not a Domain Controller. Availability Group is not supported on Domain Controllers.
2. Also make sure that each server participating in this configuration is running on Windows server 2012 or Later.
3. Each server has to be the part of the Windows Server Failover Cluster (WSFC).
4. For best performance use separate network adapter for AlwaysOn availability group.
5. All the nodes must have sufficient disk space. As the primary database grows, their corresponding replica also grows by the same amount.
6. To manage the Windows Server Faiolver Cluster, the user must have administrator access on each node of the cluster.
7. The instances of SQL Server that hosts replicas for an Availability group resides on separate nodes of the same cluster.
8. In all the nodes you need to use same SQL Server Service Account.
9. You need to register the Service Principal Name (SPN) with Active Directory (AD). Do this on the SQL Server Service Account. This is required for the Virtual Network Name (VNN) of the availability group listener.
10. Keep in mind that, if you change the SQL Server Service Account, you need to manually re-register the SPN.

Requirements from SQL Server side:

1. All nodes of cluster need to have the same version of SQL Server.
2. Also, you must use same SQL Server Collation for all the instances.
3. Enable AlwaysOn feature on each server instance.
4. Availability replicas must be hosted on different nodes of one Windows Server Failover Cluster (WSFC).
5. Availability group name must be unique and length should not be more than 128 characters.
6. Each Availability group supports one primary replica and up to 8 secondary replicas.
7. Up to 3 replicas will run on Synchronous-Commit mode. This includes 1 primary and 2 secondary. Other replicas will run on Asynchronous-Commit mode.
8. Do not use the Failover Cluster Manager to fail over availability groups. You need to use the SQL server management studio to do it.

Note:

1. We can’t add System databases in availability group.
2. We can’t add read only databases to Availability group.

To know more about the SQL server availability groups in AlwaysOn Configuration, please check below article.

SQL Server Availability Groups in AlwaysOn Configuration

Patching of SQL servers in Availability Group

Check all related articles