Scribd
Upload
18 views3 pages

Configuring DAG in SQL Server

The document outlines the steps to create and configure high availability groups (AG) in SQL Server across multiple nodes. It includes creating endpoints, granting permissions, setting up availability groups between pairs of nodes, and establishing listeners for each group. Additionally, it describes the process of joining these groups into a distributed availability group (DAG) for enhanced data mirroring and failover capabilities.

Uploaded by

nagarajduraipandian853
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
18 views3 pages

Configuring DAG in SQL Server

The document outlines the steps to create and configure high availability groups (AG) in SQL Server across multiple nodes. It includes creating endpoints, granting permissions, setting up availability groups between pairs of nodes, and establishing listeners for each group. Additionally, it describes the process of joining these groups into a distributed availability group (DAG) for enhanced data mirroring and failover capabilities.

Uploaded by

nagarajduraipandian853
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

--Create Endpoints in all the Replicas and in all the Nodes With LISTENER_IP = ALL

CREATE ENDPOINT [Hadr_endpoint]


STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

--Grant Connect permission to SQL Server Service Account on the Endpoint.


CREATE LOGIN [ABC\Administrator] FROM WINDOWS
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [ABC\Administrator]

--configure AG Between Node1 & Node2,


CREATE AVAILABILITY GROUP [AG1]
FOR DATABASE AGDB1
REPLICA ON N'Node1' WITH (ENDPOINT_URL = N'TCP://[Link]',
FAILOVER_MODE = AUTOMATIC,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
SEEDING_MODE = AUTOMATIC),
N'Node2' WITH (ENDPOINT_URL = N'TCP://[Link]',
FAILOVER_MODE = AUTOMATIC,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
SEEDING_MODE = AUTOMATIC);
GO

--Join Node2 replica and also join databases after joining replica.
ALTER AVAILABILITY GROUP [ag1] JOIN
ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE

--Create a Listener
USE [master]
GO
ALTER AVAILABILITY GROUP [AG1]
ADD LISTENER N'AGL1' (
WITH IP
((N'[Link]', N'[Link]')
)
, PORT=1433);
GO

--Create another AG (AG2) Between Node3 and Node4, .


CREATE AVAILABILITY GROUP [AG2]
FOR
REPLICA ON N'Node3' WITH (ENDPOINT_URL = N'TCP://[Link]',
FAILOVER_MODE = MANUAL,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
SEEDING_MODE = AUTOMATIC),
N'Node4' WITH (ENDPOINT_URL = N'TCP://[Link]',
FAILOVER_MODE = MANUAL,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
SEEDING_MODE = AUTOMATIC);
GO

--Join Node4 replica.


ALTER AVAILABILITY GROUP [ag2] JOIN
ALTER AVAILABILITY GROUP [ag2] GRANT CREATE ANY DATABASE

--Create listener

USE [master]
GO
ALTER AVAILABILITY GROUP [AG2]
ADD LISTENER N'AGL2' (
WITH IP
((N'[Link]', N'[Link]')
)
, PORT=1433);
GO

--Go to Global Primary Replica and configure DAG.


CREATE AVAILABILITY GROUP [distributedag]
WITH (DISTRIBUTED)
AVAILABILITY GROUP ON
'AG1' WITH
(
LISTENER_URL = 'TCP://[Link]',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
),
'AG2' WITH
(
LISTENER_URL = 'TCP://[Link]',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
);
GO

--Go to Forwarder (Node3) and join DAG to the Global Primary Replica.
ALTER AVAILABILITY GROUP [distributedag]
JOIN
AVAILABILITY GROUP ON
'AG1' WITH
(
LISTENER_URL = 'TCP://[Link]',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
),
'AG2' WITH
(
LISTENER_URL = 'TCP://[Link]',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
);
GO

You might also like