With the introduction of SQL Server 2012, Microsoft brought the AlwaysOn Availability Group feature, which has undergone several changes and improvements since then. In this article, we will discuss the prerequisites and steps for installing AlwaysOn in your SQL Server 2019 environment.

Prerequisites

Before implementing AlwaysOn Availability Group (AG), it is important to ensure that your environment meets the necessary prerequisites for a successful deployment. Here are some key prerequisites:

• Windows:
  • Do not install AlwaysOn on a domain controller
  • The operating system must be Windows 2012 or later
  • Install all available Windows hotfixes on every server (replica)
  • Windows Server Failover Cluster (WSFC) must be installed on every replica
• SQL Server:
  • Each server (replica) must be a node in the WSFC
  • No replica can run Active Directory services
  • Each replica must run on comparable hardware that can handle identical workloads
  • Each instance must run the same version of SQL Server and have the same SQL Server collation
  • The account that runs SQL Services should be a domain account
• Network:
  • It is recommended to use the same network links for communication between WSFC nodes and AlwaysOn replicas
• Databases:
  • User databases (no system databases)
  • Read/write multi-user
  • AUTO_CLOSE disabled
  • Full recovery mode
  • Not configured for database mirroring

For a complete and detailed explanation of prerequisites, you can refer to the official Microsoft documentation.

Installation Steps

Now that we have ensured that our environment meets the prerequisites, let’s proceed with the installation of AlwaysOn Availability Group.

Step 1: Add Windows Failover Cluster (WSFC) to each replica

On each replica, open Server Manager, click on “Add Roles & Features,” and select “Add Failover Clustering.” Proceed through the wizard, selecting the Failover Clustering checkbox. If .NET Framework 3.5.1 or greater is not already installed, select that checkbox as well. Restart the server if necessary. Repeat this step for every replica in your AG.

Step 2: Configure WSFC on primary replica

From Administrative Tools, open Failover Cluster Manager and click on “Validate Configuration.” Add the names of all the SQL Servers you want to configure as replicas in your AlwaysOn group. Run all tests and review any warnings. After the validation and summary are complete, the Create Cluster Wizard will open. Enter the virtual cluster name and the virtual IP address of your cluster. Proceed through the wizard to create the cluster. The secondary nodes will be added automatically. Verify the cluster on all replicas through Failover Cluster Manager.

Step 3: Configure SQL Server

Assuming you are installing a traditional AlwaysOn Availability Group, make sure you have installed the Enterprise Edition of SQL Server on each replica as stand-alone instances. Open SQL Server Configuration Manager, right-click on SQL Server Services, and open the Properties dialog box. Navigate to the AlwaysOn High Availability tab and select the Enable AlwaysOn Availability Groups checkbox. Restart the SQL Server Service after making these changes. Repeat this step for all replicas.

Step 4: Create an Availability Group

Before creating the Availability Group, ensure that all databases are in Full Recovery mode. Take full and log backups of all databases you want to include in the AG. On your primary replica, open SQL Management Studio (SSMS), expand the AlwaysOn High Availability folder, right-click on Availability Groups, and select New Availability Group Wizard. Specify the AG name and select the checkbox for Database Level Health Detection. Select the databases to include in the AG. Add and connect the replicas, specifying failover, data replication, and connection preferences. Verify the port number, backup preferences, listener settings, and read-only routing. Select the automatic seeding option for data synchronization. Validate the configuration and correct any errors. Review the summary and click Finish to create the Availability Group.

Monitoring the Availability Group

SQL Management Studio (SSMS) provides a dashboard tool to monitor the current state and health of your Availability Groups. Right-click on the AG and select Show Dashboard to get an overview of the state of your AG. The dashboard displays primary/secondary replicas, databases in the AG, failover mode, and their online status. If any issues arise, a link will appear in the Issues column to help troubleshoot.

By following these steps, you can successfully install and configure AlwaysOn Availability Group in your SQL Server 2019 environment. This feature provides high availability and disaster recovery capabilities for your databases, ensuring minimal downtime and data loss.