none
DR Drill Test - SQL Always-ON RRS feed

  • Question

  • Hi All,

    My current DB setup and Distributed Always-On setup posted on below link. With the help of SQL exprts I learned on manual failover from DC1 to Dc2 in Distribued Always-On Env.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/63ac7553-a3a4-46a2-9b06-bb937545576f/dr-drill-test-sql-alwayson?forum=sqldisasterrecovery

    Please suggest, what are the SQL commands and steps i have to follow in below scenerios.

    Scenerio: 
    - Lets assume DC1 shutdown automatically. What are the SQL commands I have to execute bring DC2 as Production.
    - Once DC1 is up, what command need to execute to bring back DC1 as production.
    Saturday, November 16, 2019 9:29 AM

All replies

  • Hi PraveenKumar_1999,

    Did your environment in this situation or this is only your assume? Could you share us the AG state in DC2?

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, November 18, 2019 8:28 AM
  • Hi Cathy, It's my assumption.

    DC1 AG Stats:

    DDC2 AG Stats:

    Monday, November 18, 2019 9:20 AM
  • Any advise? please...
    Tuesday, November 19, 2019 4:23 AM
  • Hi PraveenKumar_1999,

    You can try to make a failover in DC2 manually to bring AG online.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, November 20, 2019 6:07 AM
  • Hi Cathy, Thanks again for your reply and suggestion.

    In next couple of days, we have plan on to shutdown DC1 and point App to use DC2 DB servers. I'm not sure what steps need to follow in DR (DC2) to make DC2 as production.

    I have created another Env for test simillar to existing DC1-Production and DC2-DR setup. Here is my new env details.

    DC1:
    TEMPDB1
    TEMPDB2
    DC2:
    TEMPDB3
    TEMPDB4

    DC1-AG Status before DC1 shutdown:

    DC2-AG Status before DC1 shutdown:

    DC2-AG Status After DC1 shutdown:

    1. After DC1 shutdown, tried to access DC2-DB1, got blow error.

    2. Changed DC2-AG2 properties as mentioned below (Allow R/W Connectionas and Readable Secondary=Yes)

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

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

    Please suggest/advise on this.



    Wednesday, November 20, 2019 7:06 AM
  • Hi Cathy,

    Any suggestions? please ...

    Thursday, November 21, 2019 6:27 AM