In this article, we will explore the concept of SQL Server Always On Availability Groups and its importance in ensuring high availability and disaster recovery in large companies.

Why Establish a High Availability and Disaster Recovery Plan?

High availability and disaster recovery plans are crucial for large companies to ensure that their database servers, which host critical operational data and serve main company systems, are available for the longest possible time with minimal downtime.

High Availability and Disaster Recovery Solutions in SQL Server

Microsoft SQL Server offers several high availability and disaster recovery solutions, including:

• Log Shipping
• Transactional Replication
• Database Mirroring
• Always On Failover Cluster
• SQL Server Always On Availability Groups

SQL Server Always On Failover Cluster vs SQL Server Always On Availability Groups

The main differences between SQL Server Always On Failover Cluster and SQL Server Always On Availability Groups are:

• In the Failover Cluster, resources such as storage, network name, and virtual IPs are shared and can be owned by only one cluster node at a time. In Availability Groups, each node has its own resources with no sharing concept.
• Failover Cluster does not support reading from secondary servers, while Availability Groups support up to eight secondary replicas for read operations or routing read-only workload.
• Failover Cluster can be used as a high availability solution only, while Availability Groups can be used for both high availability and disaster recovery.
• Failover Cluster is configured at the instance level and failover is performed at the instance level. Availability Groups are configured at the database level and failover is performed at the database group level.

SQL Server Database Mirroring vs SQL Server Always On Availability Groups

The differences between SQL Server Database Mirroring and SQL Server Always On Availability Groups are:

• In Database Mirroring, you cannot read from the secondary database, but in Availability Groups, you can read from the secondary databases.
• Database Mirroring can be configured between two servers only, while Availability Groups support up to eight secondary replicas.
• Automatic failover in Database Mirroring requires a witness server, but automatic failover is always possible between primary and secondary replicas in Availability Groups.
• Database Mirroring is configured for each database separately, while Availability Groups can involve multiple databases at the same time using a single wizard.

Synchronization Process in SQL Server Always On Availability Groups

In SQL Server Always On Availability Groups, the primary replica sends transaction log records to the secondary replica. The secondary replica writes these logs to its transaction log file for caching and redoing the transactions. With asynchronous-commit availability mode, the primary replica does not need to wait for the secondary replica to write transaction log records to disk.

Availability Group and Availability Modes

An Availability Group is a group of databases hosted in a SQL Server instance called the primary replica, which is copied and synchronized with up to eight servers called secondary replicas. The availability modes used to synchronize between the replicas are:

• Synchronous-commit mode: The primary replica waits for confirmation from secondary replicas that logs are written to the database transaction log file before committing the transaction.
• Asynchronous-commit mode: The primary replica commits the transaction directly without waiting for confirmation from secondary replicas.

Failover Process in SQL Server Always On Availability Group

The failover process in SQL Server Always On Availability Group involves interchanging the primary and secondary roles between availability replicas. Failover is triggered in case of server-level failure, but not for database issues like data loss or corruption unless the Database Level Health Detection feature is enabled.

Availability Group Listener

An Availability Group Listener is a virtual network name that allows users to connect to the primary or secondary replicas.

Configuring Windows Failover Cluster for SQL Server Always On Availability Group

When configuring a Windows Failover Cluster for SQL Server Always On Availability Group, it is important to uncheck the “Add all eligible storage to the cluster” option. This is because the Availability Group feature requires dedicated storage for each replica without shared storage between them.

Quorum Configuration Modes in Windows Failover Clustering

Windows Failover Clustering supports different quorum configuration modes:

• Node Majority: Each cluster node has one vote, suitable for clusters with an odd number of nodes.
• Node & Disk Majority: Each cluster node has one vote, with an additional vote for the cluster quorum disk. Suitable for clusters with an even number of nodes.
• Node & File Share Majority: Each cluster node has one vote, with an additional vote for a shared file.
• No Majority: The cluster’s status depends entirely on the availability of the quorum disk.

Best Practices for SQL Server Always On Availability Group Configuration

It is not considered a best practice to create an SQL Server Always On Availability Group with two replicas and configure the quorum using Node Majority mode. With each node having one vote, the cluster will be down when one of the nodes goes down, defeating the purpose of high availability.

Enabling Always On Availability Group for a SQL Server Instance

To enable Always On Availability Group for a specific SQL Server instance, you can use the SQL Server Configuration Manager. Expand the SQL Server Services, browse for the SQL Server Database Engine service, right-click on the service, select Properties, and move to the Always On Availability page. Check the “Enable Always On Availability Groups” option and restart the service for the changes to take effect.

Pre-requisites for Adding a Database to Availability Group

Before adding a database to an Availability Group, the following pre-requisites must be met:

• The database must be configured with a Full recovery model.
• A full backup must be taken from the database.
• The database must not be participating in another Availability Group.

Backup Preference Options in SQL Server Always On Availability Group

SQL Server Always On Availability Group supports different backup preference options:

• Preferred Secondary
• Secondary only
• Primary
• Any replica

Configuring SQL Server Always On Availability Group with Multi-Subnet Network

To configure an SQL Server Always On Availability Group with a multi-subnet network, the Availability Group listener should be configured with one IP from each subnet.

Conclusion

SQL Server Always On Availability Groups provide a robust solution for high availability and disaster recovery in large companies. By understanding the differences between various high availability and disaster recovery solutions in SQL Server, administrators can make informed decisions when implementing these features.