Walkthrough: Setting up SQL Server High Availability

In today’s technology-driven world, there are few parts of our lives that don’t depend on software and IT-related functions. As we have become more reliant on technology, we have become less tolerant of performance issues in the applications we use regularly.

One of the most critical performance criteria we have for applications is that we want them to always be available when we need them. Server and database downtime are unacceptable in today’s competitive business environment. To ensure users can access applications with little to no interruption, DBAs and IT teams must stay focused on maintaining high availability for their SQL Server databases.

What Is SQL Server High Availability?

SQL Server high availability is the practice of keeping SQL Server databases and instances up and running with minimal downtime in the event of a crash or failure. Many business applications rely on databases being up and running, so when databases are down, users get frustrated and you may lose their business. 

High availability is especially crucial for mission-critical applications and e-commerce sites where downtime can have an immediate negative effect on welfare or revenue.

SQL Server has several high availability technologies, including log shipping, transactional replication, and data mirroring, but for the sake of brevity, the focus of this post is how to achieve high availability using SQL Server Always On availability groups.

What Are SQL Server Always On Availability Groups?

Always On availability groups support a replicated environment for a defined set of availability databases that fail over together to an availability replica. 

As explained in the Microsoft SQL Server documentation, “an availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases.”

You also have the option of making the secondary databases available for read-only access and some backup operations. However, it’s important to note that secondary databases are not backups. You should continue to back up your databases and their transaction logs regularly.

Benefits of SQL Server Always On Availability Groups

SQL Server Always On availability groups provide numerous benefits that help organizations maintain high availability, including:

Availability replicas: The availability replicas are hosted by a specified SQL Server instance, which maintains a local copy of each availability database belonging to the availability group. Each availability group can support one primary replica and as many as eight secondary replicas. 

Alternative availability modes: Always On availability groups support two types of alternative availability modes: asynchronous-commit and synchronous-commit. 

The asynchronous-commit mode is a good disaster recovery option when the availability replicas are highly distributed. 

Synchronous-commit mode provides high availability and data protection, but can affect performance. An availability group can support up to three synchronous-commit availability replicas, including the primary.

Availability-group failovers: Always On availability groups provide several forms of failovers and failover modes with varying probability of data loss: automatic failover (no data loss), planned manual failover (no data loss), and forced manual failover (possible data loss). 

Availability group listener: Each Always On availability group provides an availability group listener, which is a server clients can connect to in order to access a database in the availability group’s primary or secondary replica. Listeners direct incoming connections to the primary or secondary replica, allowing fast application failover when an availability group fails.

Security features: SQL Server Always On availability groups support automatic page repair to protect against page corruption as well as encryption and compression for secure, high-performing transport.

Prerequisites and Restrictions for Always On Availability Groups

Before deploying SQL Server Always On availability groups, it’s important to understand the prerequisites and restrictions associated with them. You can find a full discussion on SQL Server Always On availability groups in Microsoft’s SQL Server documentation, but we touch on some of the highlights here: 

Availability Group Requirements and Restrictions

The process of setting up availability groups is governed by certain prerequisites and restrictions. You can learn more about the specifics (and how setting up a virtual environment can help circumvent some of the restrictions) here:

• Prerequisites
• Restrictions

Availability Database Requirements and Restrictions

Before adding a database to an Always On availability group, you need to be sure the database meets the requirements and restrictions outlined here:

• Requirements checklist
• Restrictions

SQL Server Instance Prerequisites and Restrictions

Availability groups fail over to a defined set of availability replicas that are hosted by SQL Server instances. These instances must be configured appropriately in order to effectively host the availability replicas. These resources provide specifics on the necessary prerequisites, thread usage requirements, and permissions needed: 

• Prerequisite checklist
• Thread usage
• Permissions

Using a SQL Server Failover Cluster Instance to Host an Availability Replica

A SQL Server failover cluster instance is a single SQL Server instance that is installed across Windows Server Failover Clustering nodes and provides recovery at the database level. There are some important things to know about hosting an availability replica on a failover cluster instance, so be sure to review the checklist and restrictions carefully:

• Prerequisite checklist
• Restrictions

Getting Started with Always On Availability Groups

Once the prerequisites and restrictions are sorted, you can move ahead with setting up and deploying SQL Server Always On availability groups. There are four primary steps in the initial setup and configuration of availability groups, each with several subtasks. The steps are listed below with the full details available in the Microsoft SQL Server documentation:

1. Configure an instance of SQL Server to support Always On availability groups
2. Create and configure a new availability group
3. Manage availability groups, replicas, and databases
4. Monitor availability groups

SQL Server Always On availability groups are a straightforward, reliable way to maintain high availability for your enterprise. Always On availability groups maximize your database availability and provide users with the seamless, dependable application experience they demand.