Lab Information

Add a new member in Always On availability groups
1. We will add 2 new server (DB03 & DB04) in Always On availability groups
2. DB03 locat in same subnet
3. DB04 locat in different subnet
4. In this lab, DB04 simulate the server in different location.
5. DB04 can use 1 network for cluster and AlwaysOn Availability groups communication
6. In production environment, we can use “AlwaysOn Distributed Availability Groups” instead of “AlwaysOn Availability Groups”

Perparation
1. Change Service Account’s PrincipalsAllowedToRetrieveManagedPassword

a. Create a Security Group named “CDB”
b. Add DB01, DB02, DB03 & DB04 to CDB groups
c. Restart DB01~04 to apply membership information
d. Verify the membership information
I. Open “Command Prompt” with administrative privileges and run “gpresult /v”
II. We will find “cdb” under “The computer is a part of the following security groups”
e. Change Service Account setting

Set-ADServiceAccount gMSA-CDB -PrincipalsAllowedToRetrieveManagedPassword "CN=CDB,CN=Computers,DC=joeschoice,DC=com"

2. Install “gMSA-CDB” to DB03, DB04 — Group managed service accounts

3. Install MSSQL in DB01 & DB02 — Building MS SQL for Lab

4. Add Group managed service account (gMSA-CDB) to local administrators group in DB03 & DB04

5. Add Group managed service account (gMSA-CDB) to Logins and grant it “Connect SQL” permission

6. In “Server Configuration”, we enter group managed service accounts (joechoice\gMSA-CDB$) in “SQL Server Agent” & “SQL Servicer Database Engine” service

7. In “Local Security Policy”, we need to grant Group managed service account (gMSA-CDB) the following permission: “Log on as a service“, “Replace a process-level token“, “Bypass traverse checking” & “Adjust memory quotas for a process”

8. In “AD01”, we use the following command to register SPN

setspn -s MSSQLSvc/db03.joeschoice.com joeschoice\gMSA-CDB$
setspn -s MSSQLSvc/db03.joeschoice.com:1433 joeschoice\gMSA-CDB$
setspn -s MSSQLSvc/db04.joeschoice.com joeschoice\gMSA-CDB$
setspn -s MSSQLSvc/db04.joeschoice.com:1433 joeschoice\gMSA-CDB$

9. Install “Failover Clustering” feature on DB03 & DB04

-- Run the following Powershell on DB03 & DB04
Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools

10. Verify Cluster hardware and settings of DB03 & DB04

Test-Cluster -ReportName "C:\Test-Result\After-Create-Cluster"

11. Add DB03 & DB04 in to failover cluster “CDB.joeschoice.com”

Get-Cluster -Name CDB | Add-ClusterNode -Name DB03
Get-Cluster -Name CDB | Add-ClusterNode -Name DB04

12. “Enable AlwaysOn High Availability Groups” and restart on DB03 & DB04

# Run the following powershell at DB03
Enable-SqlAlwaysOn -ServerInstance DB03 -Force

#Run the following powershell at DB04
Enable-SqlAlwaysOn -ServerInstance DB04 -Force

13. Open Firewall on DB03 & DB04
a. Open Port 1433 and 7022
b. Open “Windows Management Instrumentation (DCOM-In)” and “Windows Management Instrumentation (WMI-In)“

Add a new member in Always On availability groups in same subnet (DB03)

1. Create the Availability Group endpoint on DB03

-- Create Always on availability group endpoint named "AG1_Endpoint" on DB03
-- This endpoint uses TCP port 7022
-- P.S: Only Always on availability group endpoint per MS SQL Server
CREATE ENDPOINT [AG1_Endpoint]
STATE=STARTED
AS TCP (LISTENER_PORT=7022)
FOR DATABASE_MIRRORING (
ROLE=ALL,
ENCRYPTION = REQUIRED ALGORITHM AES
)
GO

2. Grant group managed service account (gMSA-CDB) CONNECT permissions to the endpoint

USE [master];
GO
GRANT CONNECT ON ENDPOINT::[AG1_Endpoint] TO [JOESCHOICE\gMSA-CDB$];
GO

3. Create Availability Replica and add it to Availability Group

ALTER AVAILABILITY GROUP [AG1] ADD REPLICA ON 'DB03'
WITH (
ENDPOINT_URL = N'TCP://DB03.joeschoice.com:7022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
FAILOVER_MODE = AUTOMATIC,
SEEDING_MODE = AUTOMATIC,
SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
);

4. Joins secondary replica on DB03 to AG1

-- Run the following T-SQL on DB03
ALTER AVAILABILITY GROUP [AG1] JOIN;

5. Grant create database permission on availability group to DB03

-- Run the following T-SQL on DB03
ALTER AVAILABILITY GROUP [AG1] GRANT CREATE ANY DATABASE;

6. Full Backup database “AlwaysOn1” to shared folder

BACKUP DATABASE [AlwaysOn1]
TO DISK = N'\\AD01\AG1\AlwaysOn1_Full_init_1.bak'
WITH NOFORMAT
GO

7. Create Database AlwaysOn1 on DB03

RESTORE DATABASE [AlwaysOn1]
FROM DISK = N'\\AD01\AG1\AlwaysOn1_Full_init_1.bak'
WITH NORECOVERY
GO

8. Backup transcation log on DB01

BACKUP LOG [AlwaysOn1]
TO DISK = N'\\AD01\AG1\AlwaysOn1_Log_init_1.bak'
WITH NOFORMAT
GO

9. Restore transaction log on DB03

RESTORE LOG [AlwaysOn1]
FROM DISK = N'\\AD01\AG1\AlwaysOn1_Log_init_1.bak'
WITH FILE=1, NORECOVERY
GO

10. Join database (AlwaysOn1) on DB03 to the availability group

ALTER DATABASE [AlwaysOn1] SET HADR AVAILABILITY GROUP = AG1;
GO

Add a new member in Always On availability groups in different subnet (DB04)

1. Create the Availability Group endpoint on DB04

-- Create Always on availability group endpoint named "AG1_Endpoint" on DB04
-- This endpoint uses TCP port 7022
-- P.S: Only Always on availability group endpoint per MS SQL Server
CREATE ENDPOINT [AG1_Endpoint]
STATE=STARTED
AS TCP (LISTENER_PORT=7022)
FOR DATABASE_MIRRORING (
ROLE=ALL,
ENCRYPTION = REQUIRED ALGORITHM AES
)
GO

2. Grant group managed service account (gMSA-CDB) CONNECT permissions to the endpoint

USE [master];
GO
GRANT CONNECT ON ENDPOINT::[AG1_Endpoint] TO [JOESCHOICE\gMSA-CDB$];
GO

3. Create Availability Replica and add it to Availability Group
a. Before SQL Server 2019 (15.x), the maximum number of synchronous replicas is 3.
b. This replica is forth replica, we need to change AVAILABILITY_MODE from “SYNCHRONOUS_COMMIT” to “ASYNCHRONOUS_COMMIT” and change FAILOVER_MODE from “AUTOMATIC” to “MANUAL”

ALTER AVAILABILITY GROUP [AG1] ADD REPLICA ON 'DB04'
WITH (
ENDPOINT_URL = N'TCP://DB04.joeschoice.com:7022',
AVAILABILITY_MODE = <strong>ASYNCHRONOUS_COMMIT</strong>,
BACKUP_PRIORITY = 50,
FAILOVER_MODE = <strong>MANUAL</strong>,
SEEDING_MODE = AUTOMATIC,
SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
);

4. Add new subnet to Availability Listener
a. Since DB04 is in different subnet, we need to add this subnet to availability listener

ALTER AVAILABILITY GROUP [AG1]
MODIFY LISTENER N'AG1L'
(ADD IP (N'10.10.30.200', N'255.255.255.0')
);
GO

5. Joins secondary replica on DB04 to AG1

-- Run the following T-SQL on DB04
ALTER AVAILABILITY GROUP [AG1] JOIN;

6. Grant create database permission on availability group to DB04

-- Run the following T-SQL on DB04
ALTER AVAILABILITY GROUP [AG1] GRANT CREATE ANY DATABASE;

7. Full Backup database “AlwaysOn1” to shared folder

BACKUP DATABASE [AlwaysOn1]
TO DISK = N'\\AD01\AG1\AlwaysOn1_Full_init_2.bak'
WITH NOFORMAT
GO

8. Create Database AlwaysOn1 on DB04

RESTORE DATABASE [AlwaysOn1]
FROM DISK = N'\\AD01\AG1\AlwaysOn1_Full_init_2.bak'
WITH NORECOVERY
GO

9. Backup transcation log on DB01

BACKUP LOG [AlwaysOn1]
TO DISK = N'\\AD01\AG1\AlwaysOn1_Log_init_2.bak'
WITH NOFORMAT
GO

10. Restore transaction log on DB04

RESTORE LOG [AlwaysOn1]
FROM DISK = N'\\AD01\AG1\AlwaysOn1_Log_init_2.bak'
WITH FILE=1, NORECOVERY
GO

11. Join database (AlwaysOn1) on DB04 to the availability group

ALTER DATABASE [AlwaysOn1] SET HADR AVAILABILITY GROUP = AG1;
GO