• Home
  • About
  • Blog
  • Speaking
  • Contact

Distributed Availability Groups: Availability groupception!

4 July 2016SQL StijnSQL 2016No Comments

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!

Tags: AVAILABILITY GROUP, DISTRIBUTED AVAILABILITY GROUPS, HIGH AVAILABILITY, SQL SERVER, SQL SERVER 2016

Related Articles

Migration Gone Wrong! The Fallback Scenario

23 February 2016SQL Stijn

THE SQL SERVER MISCONFIGURATION CHRONICLES: PART 3 Database Files

24 December 2015SQL Stijn

Index maintenance on a VLDB: How to tame the beast!

5 July 2016SQL Stijn

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Recent Posts

  • Building a Modern Lakehouse Data Warehouse with Azure Synapse Analytics: Moving your Database to the lake
  • Azure Synapse Dedicated SQL Pools: Workload Management
  • Synapse Spark – Playing with Data Frames with Python
  • Synapse Spark – Reading CSV files from Azure Data Lake Storage Gen 2 with Synapse Spark using Python
  • Azure Synapse Analytics – Serverless SQL Pools: Data Elimination

Recent Comments

    Archives

    • June 2022
    • December 2021
    • May 2021
    • April 2021
    • March 2021
    • February 2021
    • January 2021
    • December 2020
    • August 2016
    • July 2016
    • March 2016
    • February 2016
    • December 2015
    • October 2015
    • August 2015
    • March 2015
    • December 2014
    • November 2014
    • July 2014
    • February 2014
    • January 2014
    • December 2013

    Categories

    • Azure Synapse Analytics
    • Building a Modern Lakehouse Data Warehouse
    • Geen categorie
    • MS PDW
    • SQL 2016
    • SQL Maintenance
    • SQL Server Migration
    • SQL Server Misconfiguration Chronicles
    • SQL2014
    • Troubleshooting

    Contact Stijn now


    © SQL Stijn 2017 - Privacy Policy - Cookie Policy
    Manage Cookie Consent
    To provide the best experiences, we use technologies like cookies to store and/or access device information.
    Functional Always active
    The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
    Preferences
    The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
    Statistics
    The technical storage or access that is used exclusively for statistical purposes. The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
    Marketing
    The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
    Manage options Manage services Manage vendors Read more about these purposes
    View preferences
    {title} {title} {title}