Install Always On 2019 For Failover Clusters

Configure SQL Server

On each of the node servers (replica), turn on SQL Server Configuration Manager, SQL Server Services --> SQL Server (MSSQLSERVER) --> Properties --> Always on Availability Groups --> Enable Always on Availability Groups.

image-20220607094155497

Then we install a stand-alone SQL service for the two node servers (replica).

image-20220607113534697

image-20220607113818112

image-20220607114135531

image-20220607114435948

image-20220607114540380

image-20220607114626758

Also, we need to enable Always on Availability Groups service as we did before for the two stand-alone SQL services.

image-20220607132532145

Moreover, for the secondary replica, we need to make sure its local SQL service can be connected to the primary replica. In the SQL Server Configuration Manager of the secondary replica, SQL Server Network Configuration --> Protocols for SQL2 , enable Named Pipes and TCP/IP.

You need to restart the servers to make all the changes.

image-20220607151315019

Restore and Backup Databases

If you have databases that need to be added to the AG, make sure that they are in Full Recovery Mode and remove these databases from any tlog backup maintenance. Here I need to restore and back up AdventureWorks2019 database.

Restore the database on the primary replica, SQL 1.

image-20220607151437088

Change the Recovery Model to Full.

image-20220607103723163

Take Full and tlog backups of all databases before adding them to AG.

--Full Backups
BACKUP DATABASE [AdventureWorks2019] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL1\MSSQL\Backup\AdventureWorks2019_FullBackup_20220607.bak' 
WITH NOFORMAT, NOINIT,  
NAME = N'AdventureWorks2019-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10, CHECKSUM
GO

--Tlog Backups
BACKUP LOG [AdventureWorks2019] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL1\MSSQL\Backup\Adventureworks2019_TlogBackup_20220607.trn' 
WITH NOFORMAT, NOINIT,  
NAME = N'AdventureWorks2019-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10, CHECKSUM
GO

image-20220607140009216

Install Always On

Connect to your primary replica, and start the New Availability Group Wizard.

image-20220607142521671

image-20220607101944506

If the status of the database is not Meets prerequisites, check detailed information.

image-20220607104426006

Add secondary replica.

image-20220607140235168

In the Endpoints tab, to avoid the following warning, we need to set the SQL Server Service Account to the domain account instead of the local server service account. Note that the Port Number should be 5022

image-20220607141252469

In the SQL Service Configuration Manager of both node servers, change the account name into the domain account as the following.

image-20220607141542135

image-20220607141623518

Now, back to the wizard, refresh the page, and the account is successfully changed.

image-20220607141929009

image-20220607142141848

Note that the Port should be 1433 and the IP Address should be an unused IP Address on the network.

image-20220607142110076

image-20220607142223919

image-20220607142235554

If successfully installed, we can find an AG listener in the Failover Cluster Manager.

image-20220607143200288

Configure Read-Only Route

Configuration

In the Object Explorer, open the Properties of the AG. Change Readable Secondary in Availability Replicas to Read-intent Only.

image-20220607154139066

image-20220607161723678

For the two stand-alone SQL servers, in the SQL Server Configuration Manager, Protocols for SQL 1 --> TCP/IP --> Properties --> IP Addresses --> TPC Dynamic Ports, change the port to an unused one for both services. This step ensures we can find the servers using a Read-only routing URL with the same port.

image-20220608093558782

Fill in the Read-Only Routing URL with TCP://ComputerName.domain:port. The port here should be the number set in the previous step. Then, click on the first instance, there will be two available replicas shown, add SQL2 and then add SQL1. Then, click on the second instance, there will also be two available replicas shown, add SQL1 and then add SQL2.

image-20220608101300076

image-20220608101311244

Or, simply run the script.

USE [master]
 GO
 ALTER AVAILABILITY GROUP [jiachengAG]
 MODIFY REPLICA ON N'TJXIEM405VM\SQL1' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (N'TJXIEM406VM\SQL2',N'TJXIEM405VM\SQL1')))
 GO
 USE [master]
 GO
 ALTER AVAILABILITY GROUP [jiachengAG]
 MODIFY REPLICA ON N'TJXIEM406VM\SQL2' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (N'TJXIEM405VM\SQL1',N'TJXIEM406VM\SQL2')))
 GO

Test

Connect the AG listener. Click Options to configure the connection.

image-20220608093733764

Note that the connected database needs to be specified in Connection Properties

image-20220608093829901

Also, add ApplicationIntent=ReadOnly as connection strings to enable Read-only routing.

image-20220608093924655

New a query, run the script, the Read-only routing works as I am working in the primary replica while it routes read-only connections to the secondary replica.

image-20220608094019762