Distributed Availability Groups

Distributed Availability Groups
1. It is an availability group of availability groups
2. It is used for 2 availability groups to communicate
3. It does not has availability listener
4. Underlying availability groups must have a listener
5. After failover, we need to change our DNS or application connection to the secondary availability group

Lab Information

Reference Links
1. Create Failover Cluster
2. Create Group Managed Service Accounts
3. Create Always On Availability Group

Perpare for Distributed Availability Groups
1. Create 2 Group Managed Service Account, add to local adminstrators group, add to Logins and grant “Connect SQL” permission to them
a. First GMSA names gMSA1 for DB01 & DB02
b. Second GMSA names gMSA2 for DB03 & DB04

2. Create 2 failover cluster
a. First names CDB1 with 2 nodes (DB01, DB02)
b. Second names CDB2 with 2 nodes (DB02, DB04)

3. In “AD01”, we use the following command to register SPN
[script]
setspn -s MSSQLSvc/db01.joeschoice.com joeschoice\gMSA1$
setspn -s MSSQLSvc/db01.joeschoice.com:1433 joeschoice\gMSA1$

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

setspn -s MSSQLSvc/db03.joeschoice.com joeschoice\gMSA2$
setspn -s MSSQLSvc/db03.joeschoice.com:1433 joeschoice\gMSA2$

setspn -s MSSQLSvc/db04.joeschoice.com joeschoice\gMSA2$
setspn -s MSSQLSvc/db04.joeschoice.com:1433 joeschoice\gMSA2$

# SPN for Always On availability group listener
setspn -s MSSQLSvc/AG1.joeschoice.com:1433 joeschoice\gMSA1$
setspn -s MSSQLSvc/AG2.joeschoice.com:1433 joeschoice\gMSA2$
[/script]

4. Enable SQLCMD mode in SSMS
a. If we does not enable SQLCMD mode, we will not allow to run “:CONNECT” cmd
b. Open “SSMS”, select “Tools” > “Options”
c. In “Options” box, select “Query Execution” > “SQL Server” > General”
d. Click “By default, open new queries in SQLCMD mode” > Click “OK”

5. Create Database in DB01

:CONNECT 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')

6. Take a Full backup of AlwaysOn1
a. Create shared folder on AD01
b. Grant read/write permission to gMSA1$, gMSA2$ & mssql-admin on shared folder

:CONNECT DB01

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

7. “Enable AlwaysOn High Availability Groups” and restart on DB01, DB02, DB03 and DB04

8. On DB01, DB02, DB03 and DB04, add gMSA1 and gMSA2 to Logins and grant it “Connect SQL” permission

Create Distributed Availability Groups
1. Create Frist AlwaysOn Availability Group on DB01 & DB02
a. Create endpoint which listen all IP

:CONNECT DB01

CREATE ENDPOINT [AG1_Endpoint]
STATE=STARTED
AS TCP (LISTENER_PORT=5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (
ROLE=ALL,
ENCRYPTION = REQUIRED ALGORITHM AES
)
GO

:CONNECT DB02

CREATE ENDPOINT [AG1_Endpoint]
STATE=STARTED
AS TCP (LISTENER_PORT=5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (
ROLE=ALL,
ENCRYPTION = REQUIRED ALGORITHM AES
)
GO

b. Grant group managed service account (gMSA1$ and gMSA2$) CONNECT permissions to the endpoint

:CONNECT DB01

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

:CONNECT DB02

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

c. Create availability group with replica

:CONNECT DB01
-- 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:5022',
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:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
FAILOVER_MODE = AUTOMATIC,
SEEDING_MODE = AUTOMATIC,
SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
);
GO

d. Joins secondary replica on DB02 to AG1 and Grant create database permission on availability group to DB02

:CONNECT DB02

ALTER AVAILABILITY GROUP [AG1] JOIN
ALTER AVAILABILITY GROUP [AG1] GRANT CREATE ANY DATABASE;
GO

e. Create Availability group listener (AG1L)

:CONNECT DB01

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

2. Create Second Availability Group on DB03 & DB04
a. Create endpoint which listen all IP

:CONNECT DB03

CREATE ENDPOINT [AG2_Endpoint]
STATE=STARTED
AS TCP (LISTENER_PORT=5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (
ROLE=ALL,
ENCRYPTION = REQUIRED ALGORITHM AES
)
GO

:CONNECT DB04

CREATE ENDPOINT [AG2_Endpoint]
STATE=STARTED
AS TCP (LISTENER_PORT=5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (
ROLE=ALL,
ENCRYPTION = REQUIRED ALGORITHM AES
)
GO

b. Grant group managed service account (gMSA2$) CONNECT permissions to the endpoint

:CONNECT DB03

USE [master];
GO
GRANT CONNECT ON ENDPOINT::[AG2_Endpoint] TO [JOESCHOICE\gMSA2$];
GO

:CONNECT DB04

USE [master];
GO
GRANT CONNECT ON ENDPOINT::[AG2_Endpoint] TO [JOESCHOICE\gMSA2$];
GO

c. Create availability group with replica

:CONNECT DB03
-- Create availability group named "AG1"
CREATE AVAILABILITY GROUP [AG2]
WITH
(
AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
DB_FAILOVER = ON,
DTC_SUPPORT = NONE
)
FOR
REPLICA ON
-- Create availability group replica in DB01
N'DB03' WITH
(
ENDPOINT_URL = N'TCP://DB03.joeschoice.com:5022',
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'DB04' WITH
(
ENDPOINT_URL = N'TCP://DB04.joeschoice.com:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
FAILOVER_MODE = AUTOMATIC,
SEEDING_MODE = AUTOMATIC,
SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
);
GO

d. Joins secondary replica on DB04 to AG2 and Grant create database permission on availability group to DB04

:CONNECT DB04

ALTER AVAILABILITY GROUP [AG2] JOIN;
ALTER AVAILABILITY GROUP [AG2] GRANT CREATE ANY DATABASE;

e. Create Availability group listener (AG2L)

:CONNECT DB03

ALTER AVAILABILITY GROUP [AG2]
ADD LISTENER N'AG2L' (
WITH IP
((N'10.10.30.200', N'255.255.255.0')
)
, PORT=1433);

3. Create Distributed Availability Group on first availability group on DB01
a. URL in the LISTENER_URL is the Listener URL of AG1 and AG2
b. Port in the LISTENER_URL is the Port of AG1’s and AG2’s endpoint

:CONNECT DB01

CREATE AVAILABILITY GROUP [DAG]
WITH (DISTRIBUTED)
AVAILABILITY GROUP ON
N'AG1' WITH
(
LISTENER_URL = N'TCP://AG1L.joeschoice.com:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
),
N'AG2' WITH
(
LISTENER_URL = N'TCP://AG2L.joeschoice.com:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
);
GO

4. Join Distributed Availability Group to second availability group on DB03

:CONNECT DB03

ALTER AVAILABILITY GROUP [DAG]
JOIN
AVAILABILITY GROUP ON
N'AG1' WITH
(
LISTENER_URL = N'TCP://AG1L.joeschoice.com:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
),
N'AG2' WITH
(
LISTENER_URL = N'TCP://AG2L.joeschoice.com:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
);
GO

Joe's choice

Distributed Availability Groups

Author: Joe Chan

Search Me

Recent Posts

Author: Joe Chan

Search Me

Recent Posts

Tag Cloud