4

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.

2 Answers 2

1

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

8
0

If your talking about doing a DC1 controlled shutdown, as hinted in one of your comments, AKA do a fail over to DC2 without data loss.

While both DC1 and DC2 are online:

You need to change the 2ndarys in DC2 to synchronous mode.

make sure all the ownership settings of the objects in the Windows failover cluster are set so DC2 can take ownership of the cluster and the AOAG.

Perform a normal fail over to DC2

Change to 2ndarys in DC1 to a-sync mode.

Now you can shutoff DC1.

If you are talking about a DR event, AKA unexpected shutdown of DC1,

This command will force a failover to an a-sync replica:

USE master
ALTER AVAILABILITY GROUP [distributedag] FORCE_FAILOVER_ALLOW_DATA_LOSS

once DC1 comes back up you will have to reseed the Databases on the DC1 nodes. basically

stop backups in DC2, 

Take a full backup and take a Log backup.

Restore the Full and Log to the DC1 secondaries with NORECOVERY

Then you can rejoin the Nodes and databases to the availability group.

You have to do this because the log chains are different on the two sides, and you have to undo the lost transactions on the DC1 side and redo all the ones that happened on the DC2 side while DC1 was offline.

2
  • Thank you Jason. When DC1 is down(unexpected shutdown), I will execute command on DC2: ALTER AVAILABILITY GROUP [distributedag] FORCE_FAILOVER_ALLOW_DATA_LOSS Once DC1 is up, I need to execute same command on DC1 and start backup restoration, join DB, is this my undesratnding is correct?
    – Sridhar G
    Nov 21, 2019 at 14:01
  • You should not run that command again on DC1, you would loss all the data processed in DC2 while DC1 was offline. You want to Backup DC2 and restore to DC1 then re-join DC1 to DC2. Then you can follow the planed shutdown steps above but from DC2 to DC1. Nov 21, 2019 at 21:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.