How to implement Always on Availability Groups in SQL Server 2019 on Windows?

Implementing Always On Availability Groups in SQL Server 2019 involves several steps. This feature provides high availability and disaster recovery by allowing multiple copies of a database to be synchronized and available across different servers. Let’s setting up using a step-by-step guide below:

Pre-requisites

1. SQL Server Editions: Ensure you have SQL Server 2019 Enterprise or Standard edition. Always On Availability Groups are not available in the SQL Server Express or Web editions.
2. Windows Server: The servers must be running a supported version of Windows Server (e.g., Windows Server 2016 or later).
3. Domain Configuration: All servers should be part of the same Active Directory domain.
4. Cluster Service: Install and configure the Failover Cluster feature on all nodes.

Step 1: Configure Windows Server Failover Clustering (WSFC)

Install Failover Clustering Feature:

• Open Server Manager.
• Go to Manage -> Add Roles and Features.
• Follow the wizard to install the Failover Clustering feature on all servers that will be part of the Always On Availability Group.

Create a Failover Cluster:

• Open the Failover Cluster Manager.
• Click on Create Cluster and follow the wizard to create a new cluster. You need to specify the cluster name and IP address.
• Validate the configuration and complete the creation.

Step 2: Configure SQL Server for Always On

Enable Always On Availability Groups:

• Open SQL Server Configuration Manager.
• Go to SQL Server Servicesand right-click on the SQL Server instance and select Properties.
• Go to the Always On High Availability tab.
• Check the box for Enable Always On Availability Groups.
• Specify the Windows Server Failover Cluster (WSFC) instance name and click OK.
• Restart the SQL Server service to apply the changes.

Step 3: Create the Availability Group

Create a Database Backup:

• Before you can add a database to an availability group, you need to back up the databases you want to include.
• Perform a full backup and a transaction log backup of each database.

-- Full Backup
BACKUP DATABASE [YourDatabaseName] TO DISK = N'\\YourBackupLocation\YourDatabaseName.bak' WITH FORMAT, INIT;

-- Log Backup
BACKUP LOG [YourDatabaseName] TO DISK = N'\\YourBackupLocation\YourDatabaseName_log.trn';

Restore Databases on Secondary Instances:

• On each secondary replica server, restore the full backup with the NORECOVERY option.
• Restore the transaction log backups with the NORECOVERY option.

-- Restore full backup on secondary with NORECOVERY
RESTORE DATABASE [YourDatabaseName] FROM DISK = N'\\YourBackupLocation\YourDatabaseName.bak' WITH NORECOVERY;

-- Restore log backup on secondary with NORECOVERY
RESTORE LOG [YourDatabaseName] FROM DISK = N'\\YourBackupLocation\YourDatabaseName_log.trn' WITH NORECOVERY;

Create an Availability Group:

• Open SQL Server Management Studio (SSMS) and connect to the primary SQL Server instance.
• Right-click on the Always On High Availability node and select New Availability Group Wizard.
• Follow the wizard:
• Specify Availability Group Name: Enter a name for the availability group.
• Select Databases: Choose the databases you want to add to the group.
• Specify Replicas: Add replicas by specifying the secondary servers. Configure settings such as automatic failover, synchronous or asynchronous commit mode, and endpoint URLs.
• Backups: Configure backup preferences and backup settings.
• Listener: Configure the availability group listener (optional but recommended). This provides a virtual network name for applications to connect to.

-- Example T-SQL script to create an availability group
CREATE AVAILABILITY GROUP [YourAGName]
FOR DATABASE [YourDatabaseName]
REPLICA ON
N'Server1' WITH (
   ENDPOINT_URL = N'TCP://Server1.domain.com:5022',
   FAILOVER_MODE = AUTOMATIC,
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
   BACKUP_PRIORITY = 1,
   SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
),
N'Server2' WITH (
   ENDPOINT_URL = N'TCP://Server2.domain.com:5022',
   FAILOVER_MODE = AUTOMATIC,
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
   BACKUP_PRIORITY = 2,
   SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
)
LISTENER = N'YourAGListener'
(WITH IP (('192.168.1.100', 255.255.255.0)));

Review and Create:

• Review the summary of your configuration.
• Click Next and then Finish to create the availability group.

Step 4: Configure Endpoints and Network

Create Endpoint:

• Ensure that the database mirroring endpoint is created on all replicas.
• You can check and create endpoints using T-SQL:

CREATE ENDPOINT [Hadr_endpoint]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022)
FOR DATA_MIRRORING (
  ROLE=ALL,
  AUTHENTICATION = WINDOWS NEGOTIATE,
  ENCRYPTION = REQUIRED
);

Configure Firewall:

• Ensure that the necessary ports (e.g., 5022) are open on all servers for communication.

Step 5: Test the Availability Group

Failover Testing:

• Use SSMS to manually failover the availability group to verify that everything is working correctly.

Monitor and Verify:

• Use the Always On Dashboard in SSMS to monitor the health and status of the availability group.
• Check the SQL Server logs and Windows Event Viewer for any issues.