Always On Availability Group
1. It is a group of database that failover together
2. It have to continue to backup database and transcation logs
3. It supports 1 primary database and 1~8 secondary databases

Availability Replicas
1. It is components of the availability group
2. It has 2 types of replica: Primary replica, Secondary replica
3. Primary replica hosts read-write databases
4. Secondary replca hosts read-only databases

Availability Modes
1. It is 2 type of modes: Asynchronous-commit, Synchronous-commit
2. In Asynchronous-commit mode, primary replica will not wait for the acknowledgement for transcation commit after primary replica send transcation log to secondary replica
3. In Synchronous-commit mode, primary replica will wait for the acknowledgement for transcation commit after primary replica send transcation log to secondary replica

Failover types
1. Synchronous-commit mode has 2 types of failover: manual failover, automatic failover
2. Asynchronous-commit modes has 1 types of failover: forced failover

Manual failover & Automatic failover
1. It will not has data loss
2. Primary replica and secondary replica must be under synchronous-commit mode
3. Secondary replica must be synchronized
4. Manual failover occurs after a user issues a failover command
5. Automatic failover occurs after a synchronized secondary replica cannot connect to primary replica

Forecd failover
1. It will has data loss
2. It occurs after a user issues a filaover command
3. It is only failover type when secondary replica is not synchronized with primary replica

Lab Information

Availability Group 1 Name: AG1
Availability Group 1 include Database: AlwaysOn1
Availability Group 1 Primary Replica: DB01
Availability Group 1 Secondary Replica: DB02
Availability Group 1 Listener Name: AG1L
Availability Group 1 Listener IP: 10.10.10.200/24
Availability Group 1 Listener Port: 1433

Availability Group 2 Name: AG2
Availability Group 2 include Database: AlwaysOn2
Availability Group 2 Primary Replica: DB02
Availability Group 2 Secondary Replica: DB01
Availability Group 2 Listener Name: AG2L
Availability Group 2  Listener IP: 10.10.10.210/24
Availability Group 2 Listener Port: 1433

Preparation for Always On Availability Group
1. Create a cluster (CDB) with 2 nodes (DB01,DB02) — Failover Cluster

2. Create a Group Managed Service Accounts (gMSA-CDB) — Group managed service accounts

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

4. “Enable AlwaysOn High Availability Groups” and restart on DB01 & DB02

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

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

5. Add Group managed service account (gMSA-CDB) to local administrators group in DB01 & DB02

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

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

8. 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”

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

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

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

# SPN for Always On availability group listener
setspn -s MSSQLSvc/AG1L.joeschoice.com:1433 joeschoice\gMSA-CDB$
setspn -s MSSQLSvc/AG2L.joeschoice.com:1433 joeschoice\gMSA-CDB$

10. Create Database in DB01

CREATE DATABASE AlwaysOn1;
GO
USE AlwaysOn1;
GO
-- Create Table AlwaysOn1_A1
CREATE TABLE dbo.AlwaysOn1_A1
(
ID_A int not null IDENTITY(1,1) PRIMARY KEY,
Value_A nvarchar(80) NULL
);
-- Insert data to tables
INSERT INTO dbo.AlwaysOn1_A1(Value_A) VALUES ('ability'),('able'),('about'),('above'),('accept')

11. Create Database in DB02

CREATE DATABASE AlwaysOn2;
GO
USE AlwaysOn2;
GO
-- Create Table AlwaysOn2_B1
CREATE TABLE dbo.AlwaysOn2_B1
(
ID_A int not null IDENTITY(1,1) PRIMARY KEY,
Value_A nvarchar(80) NULL
);
-- Insert data to tables
INSERT INTO dbo.AlwaysOn2_B1(Value_A) VALUES ('ability'),('able'),('about'),('above'),('accept');

12. Full backup AlwaysOn1 & AlwasyOn2

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

BACKUP DATABASE [AlwaysOn2]
TO DISK = N'\\AD01\AG2\AlwaysOn2_Full.bak'
WITH NOFORMAT
GO

How to create Always On Availability Group on T-SQL
1. Create the Availability Group endpoint on DB01 & DB02

-- Create Always on availability group endpoint named "AG1_Endpoint" on DB01
-- This endpoint uses TCP port 7022
-- P.S: Only one 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

-- Create Always on availability group endpoint named "AG1_Endpoint" on DB02
-- This endpoint uses TCP port 7022
-- P.S: Only one 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

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

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

3. Create availability group with replica
Availability Group has the following arguments:

a. AUTOMATED_BACKUP_PREFERENCE = { PRIMARY | SECONDARY_ONLY | SECONDARY | NONE }

— It specifies the automated backup preference for the availability group

“SECONDARY” is the default value
PRIMARY: backup always occue on the primary replica
SECONDARY_ONLY: backups are never performaed on primary replicas. If the primary replica is the only online replica, backup does not occur.
SECONDARY: backup occur on secondary replicas. If the primary replica is the only online replica, backup occurs on the primary replica.
NONE: Using backup priority and online status determine which replica performs backups

b. FAILURE_CONDITION_LEVEL = { 1 | 2 | 3 | 4 | 5 }

— It specifies automatic failover behavior of the availability group
— “3” is the default value

1: If the SQL Server service stops.
2: If any condition of lower value is satisfied, plus when the SQL Server service is connected to the cluster and the HealthCheckTimeout threshold is exceeded, or if the availability replica currently in primary role is in a failed state.
3: If any condition of lower value is satisfied, plus when an internal critical Server error occurs, which include out of memory condition, serious write-access violation, or too much dumping.
4: If any condition of lower value is satisfied, plus if a moderate Server error occurs, which includes persistent out of memory condition.
5: If any condition of lower value is satisfied, plus if a qualifying failure condition occurs, which includes engine worker thread exhaustion and unsolvable deadlock detected.

c. HEALTH_CHECK_TIMEOUT = 15000~4294967295

— It specifies the wait time (in milliseconds) for the sp_server_diagnostics system stored procedure to return server-health information before the WSFC cluster assumes that the server instance is slow or hung
— It can trigger an automatic failover only if both the primary and secondary replicas are configured for automatic failover mode and the secondary replica is currently synchronized with the primary replica
— 30000 (30 seconds) is the default value

d. DB_FAILOVER = { ON | OFF }

— It specifies the response to take when a database on the primary replica is offline

“ON”: any status other than ONLINE for a database in the availability group triggers an automatic failover
“OFF”: only the health of the instance is used to trigger automatic failover

e. DTC_SUPPORT = { PER_DB | NONE }

— It specifies whether cross-database transactions are supported through the distributed transaction coordinator (DTC)
— It support beginning in SQL Server 2016 (13.x)

“PER_DB”: the availability group with support for cross-database transactions
“NONE”: the availability group with not support for cross-database transactions

f. BASIC

— Used to create a basic availability group
— Basic availability groups are limited to one database and two replicas: a primary replica and one secondary replica
— It support beginning in SQL Server 2016 (13.x)

g. DISTRIBUTED

— Used to create a distributed availability group
— It support beginning in SQL Server 2016 (13.x)

h. REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0 ~ (Number of replicas – 1)

— Used to set a minimum number of synchronous secondary replicas required to commit before the primary commits a transaction
— It support beginning in SQL Server 2017 (14.x)
— “0” is the default value

i. CLUSTER_TYPE = { WSFC | EXTERNAL | NONE }

— Used to identify if the availability group is on a Windows Server Failover Cluster (WSFC)

“WSFC”: availability group is on a failover cluster instance on a WSFC
“EXTERNAL”: the cluster is managed by a cluster manager that is not a WSFC
“NONE”: availability group not using WSFC for cluster coordination

The availability replica has the following arguments:

a. ENPOINT_URL

— It specifies URL of endpoint which create at step 1

b. AVAILABILITY_MODE = { SYNCHRONOUS_COMMIT | ASYNCHRONOUS_COMMIT | CONFIGURATION_ONLY }

— It specifies whether the primary replica has to wait for the secondary replica to acknowledge the hardening (writing) of the log records to disk before the primary replica can commit the transaction on a given primary database

“SYNCHRONOUS_COMMIT”:
— Primary replica will wait for the acknowledgement
— You can specify SYNCHRONOUS_COMMIT for up to three replicas, including the primary replica
“ASYNCHRONOUS_COMMIT”:
— Primary replica will not wait for the acknowledgement
— You can specify ASYNCHRONOUS_COMMIT for up to five availability replicas, including the primary replica
“CONFIGURATION_ONLY”: Only for availability groups that are not on a WSFC

c. FAILOVER_MODE = { AUTOMATIC | MANUAL | EXTERNAL }

— It pecifies the failover mode of the availability replica

“AUTOMATIC”: Automatic failover
“MANUAL”: Manaul failover
“EXTERNAL”: Only for availability groups that are not on a WSFC

d. SEEDING_MODE = { AUTOMATIC | MANUAL }

— It specifies how the secondary replica is initially seeded

“AUTOMATIC”:
— It seeds the secondary replica over the network
— It does not require you to backup and restore a copy of the primary database on the replica
“MANUAL”:
— It is a default value
— It requires you to create a backup of the database on the primary replica and manually restore that backup on the secondary replica.

e. BACKUP_PRIORITY = 0 ~ 100

— It specifies your priority for performing backups on this replica relative to the other replicas in the same availability group
— The higher the better

“0”: the availability replica is not for performing backups
“1~100”: the availability replica could be chosen for performing backups

f. SESSION_TIMEOUT = integer

— It specifies the amount of time (in seconds) to wait for a response between replicas.
— Default value is 10
— Minimum vlaue is 5

g. SECONDARY_ROLE ( ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL } )

— “SECONDARY_ROLE” specifies role-specific settings when this availability replica currently owns the secondary role

“NO”:
— No user connections are allowed to secondary databases of this replica
— It is a default value
“READ_ONLY”:
— Only connections are allowed to the databases in the secondary replica where the Application Intent property is set to ReadOnly
“ALL”:
— All connections are allowed to the databases in the secondary replica for read-only access

h. PRIMARY_ROLE ( ALLOW_CONNECTIONS = { READ_WRITE | ALL } )

— “PRIMARY_ROLE” specifies role-specific settings when this availability replica currently owns the primary role
“READ_WRITE”:
— When the Application Intent property is set to ReadWrite or the Application Intent connection property is not set, the connection is allowed

“ALL”:
— All connections are allowed to the databases in the primary replica
— It is a default value

-- Create availability group named "AG1"
CREATE AVAILABILITY GROUP [AG1]
WITH
(
AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
DB_FAILOVER = ON,
DTC_SUPPORT = NONE
)
FOR DATABASE [AlwaysOn1]
-- This availability group includes databases "AlwaysOn1"
-- Multipe databases example: "DATABASE DB1, DB2"
REPLICA ON
-- Create availability group replica in DB01
N'DB01' WITH
(
ENDPOINT_URL = N'TCP://DB01.joeschoice.com:7022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
FAILOVER_MODE = AUTOMATIC,
SEEDING_MODE = AUTOMATIC,
SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
),
-- Create availability group replica in DB02
N'DB02' WITH
(
ENDPOINT_URL = N'TCP://DB02.joeschoice.com:7022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
FAILOVER_MODE = AUTOMATIC,
SEEDING_MODE = AUTOMATIC,
SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
);
GO

4. Create Availiability group listener (AG1L)

ALTER AVAILABILITY GROUP [AG1]
ADD LISTENER N'AG1L' (
WITH IP
((N'10.10.10.200', N'255.255.255.0')
)
, PORT=1433);

5. Joins secondary replica on DB02 to AG1

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

6. Grant create database permission on availability group to DB02

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

7. Full Backup database “AlwaysOn1” to shared folder
a. Create shared folder on AD01
b. Grant read/write permission to gMSA-CDB$ & mssql-admin on shared folder

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

8. Create Database AlwaysOn1 on DB02

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

9. Backup transcation log on DB01

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

10. Restore transaction log on DB02

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

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

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