Sql-server – SQL Server 2017 Distributed Availability Groups

availability-groupsclusteringsql serversql-server-2016sql-server-2017

My Environment is SQL Server 2017 Distributed Availability Groups, details as follows:

Configured SQL Distributed AG from DC1 to DC2.

  • DC1 (Production): TEMPDB1 AND TEMPDB2 (HOSTED ON WSFC1)
  • DC2 (DR): TEMPDB3 AND TEMPDB4 (HOSTED ON WSFC2)
  • DC1 and DC2 located in two different Geo location
  • DC1 is unavailable (shutdown).

Please suggest on following scenarios:

  • What are the steps/commands need to execute on DC2 to bring as a Production?

  • Once DC1 is up/running, what are the steps/commands need to execute in DC2 and DC1 to bring DC1 as a Production?

Here are the screenshots of status from 2 DC's

DC1-AG Status before DC1 shutdown:
enter image description here

DC2-AG Status before DC1 shutdown:
enter image description here

DC2-AG Status After DC1 shutdown:
enter image description here

After DC1 shutdown, tried to access DC2-DB1, got blow error.
enter image description here

Changed DC2-AG2 properties as mentioned below (Allow R/W Connections and Readable Secondary=Yes)
enter image description here

After changing properties, I am able to access DB1 but not able to perform any DML operations in DC2-DB1.
enter image description here

This DB should allow for all operations because app is pointing DC2-DB during DC1 shutdown.

Please suggest/advise on this.

Best Answer

After DC 1 is down, since your secondary availability group [distributedag] is asychronous and is in manual failover mode, it is still in secondary role, so you will need to connect to [distributedag] listener and execute command:

USE master
ALTER AVAILABILITY GROUP [distributedag] FORCE_FAILOVER_ALLOW_DATA_LOSS

This will force your secondary AG at DC2 to become primary, and bring it into a read-write state

Related Question