On the all new SQL Server 2016 we now have a feature which reminds me of the movie Inception with Leo DiCaprio & Tom Hardy. There you had the dreams in dreams in dreams, for the availability groups we now have AG in AG. This makes the Distributed Availbility groups a dream come true for Geographical differences between availability groups. Today’s post will show you how you set up a distributed availability group and what the options are.
I started with setting up 3 nodes in a Hyper-V environment on my local laptop, on these nodes SQL Server 2016 Developer edition was installed. After these installations I added them in my local domain name SQLSTIJN, and installed a failover cluster with the 3 nodes inside.
Now I will start off with setting up my primary availability group on the SS2016 & SS2016P nodes.
CREATE AVAILABILITY GROUP [AG_Ception]
FOR
DATABASE [AGTest] REPLICA
ON
N’SS2016\SS2016′
WITH
(ENDPOINT_URL =N’TCP://SS2016.SQLSTIJN.COM:5022′,
FAILOVER_MODE = AUTOMATIC,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE(ALLOW_CONNECTIONS=NO),
Seeding_Mode = AUTOMATIC),
N’SS2016P\SS2016P’
WITH (
ENDPOINT_URL = N’TCP://SS2016P.SQLSTIJN.COM:5022′,FAILOVER_MODE=AUTOMATIC,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE(ALLOW_CONNECTIONS=NO),
Seeding_Mode = AUTOMATIC);
Because we are using the Seeding mode automatic, we still have to join the database and grant the availability group rights to create a database on the secondary replica. In my case I connect to the SS2016P node and execute following command
ALTER AVAILABILITY GROUP [AG_Ception] JOIN
ALTER AVAILABILITY GROUP [AG_Ception] GRANT CREATE ANY DATABASE
GO
Now our the database should be created on the secondary node and start synchronizing using automatic seeding (so easy without the backup restore … J ) (In case of error, check your errorlog à make sure your folders are the same and existing f.e. if you have on the primary d:\data en you have named the folder on the secondary s:\dataa it will not work. So make sure all folders and drives exist and have the same name J )
After you executed all the commands you should see something like this. This has been our normal setup so far, so this is apart from the automatic seeding has no difference with the SQL Server 2014 version.
Now we are going to create a listener for the first availability group.
USE [master]
GO
ALTER AVAILABILITY GROUP [AG_Ception]
ADD LISTENER N’AGCeption1′
(WITH IP((N’33.0.0.90′,N’255.255.255.0′)), PORT=65000);
GO
After this we are going to create a single node secondary availability group for the SS2016DR node. We are not going to specify a database this time because we will again use the automatic seeding functionality of SQL Server 2016. We will however create it with the seeding_mode automatic to ensure the ability to seed after we join it through a distributed AG. IMPORTANT: Use the same mirroring endpoint otherwise after a local failover you will not be able to replicate.
USE [master]
GO
CREATE AVAILABILITY GROUP [AG_Ception2]
FOR REPLICA ON N’SS2016DR\SS2016DR’
WITH
(ENDPOINT_URL = N’TCP://SS2016DR.SQLSTIJN.COM:5022′,
FAILOVER_MODE=MANUAL,
AVAILABILITY_MODE=SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY= 50,
PRIMARY_ROLE(ALLOW_CONNECTIONS=ALL),
SECONDARY_ROLE(ALLOW_CONNECTIONS=NO),
SEEDING_MODE =AUTOMATIC);
GO
Now we create a listener for the second AG_Ception2
USE [master]
GO
ALTER AVAILABILITY GROUP [AG_Ception2]
ADD LISTENER N’AGCeption2′
(WITH IP((N’33.0.0.91′,N’255.255.255.0′)), PORT=65000);
GO
When we have configured both availability groups it is finally time to add our Distributed Availability group. We will execute this on our current primary node SS2016\SS2016
CREATE AVAILABILITY GROUP [DISTRICEPTION]
WITH (DISTRIBUTED)
AVAILABILITY GROUP ON ‘AG_CEPTION’
WITH (
LISTENER_URL =‘TCP://AGCeption1:5022’,
AVAILABILITY_MODE=ASYNCHRONOUS_COMMIT,
FAILOVER_MODE=MANUAL,
SEEDING_MODE =AUTOMATIC
),
‘AG_CEPTION2’
WITH
(
LISTENER_URL =‘TCP://AGCeption2:5022’,
AVAILABILITY_MODE=ASYNCHRONOUS_COMMIT,
FAILOVER_MODE=MANUAL,
SEEDING_MODE =AUTOMATIC
);
GO
Notice that I use the endpoint port and not the listener port. This is needed otherwise you will get errors J
Now I’m going to join the secondary availability group, I will execute this on the SS2016DR\SS2016DR (Secondary AG)
Before doing this, check your endpoints, apparently mine was not created. Execute this on the secondary node if you don’t have an endpoint.
USE [master]
GO
CREATE ENDPOINT [Hadr_endpoint]
STATE=STARTED
AS
TCP (LISTENER_PORT= 5022,LISTENER_IP=ALL)
FOR DATA_MIRRORING (ROLE=ALL,AUTHENTICATION=WINDOWS NEGOTIATE,ENCRYPTION=REQUIREDALGORITHM AES)
GO
After this you can join your secondary availability group to the Distributed AG
ALTER AVAILABILITY GROUP [Distriception]
JOIN
AVAILABILITY GROUP ON ‘AG_CEPTION’
WITH
(
LISTENER_URL = ‘TCP://AGCeption1:5022’,
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE=MANUAL,
SEEDING_MODE = AUTOMATIC
),
‘AG_CEPTION2’
WITH
(
LISTENER_URL = ‘TCP://AGCeption2:5022’,
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE=MANUAL,
SEEDING_MODE =AUTOMATIC);
GO
After this you can check your error log and databases from the secondary AG and see that the automatic seeding has started and you now have a database in sync on the secondary AG.
If you see this, then you have successfully set up your Distributed Availability group.
One major downside I find in the current version is that you cannot create a listener on the distributed availability group & there is no synchronous mode. Which means that the whole failover process is manual and will need some editing to DNS.
This is however a very promising feature and hope to see evolve this!
Thanks for reading and stay tuned!