none
DR Drill Test - SQL Always-ON RRS feed

  • Question

  • Hi All,

    We have plan to shutdown DC1 and make DC2 as Production. Please advise what are the steps I have to follow to move DC1-DB to DC2-DB? 

     Step 1: Shut down DC1-DB1, DB2 servers

    Step 2: Change application connection string to use DC2  DB server listener 

    Is there any other steps i have to follow?

    DC1:

    SQLDB1 - Read/Write mode

    SQLDB2: Read mode

    DC2:

    SQLDB1: Read mode

    SQLDB2: Read mode

    Configured Distributed SQL Always-ON from DC1 to DC2 as shown below

    Monday, November 11, 2019 6:48 AM

Answers

  • Hi praveenkumar_1999,

    If you need to restart your actual primary DC you will have to follow these steps:

    1) change the synchronization mode to synchronous (wait some seconds so the DAG can apply all of the remaining changes on the other DC)

    2) Perform the manual failover,

    3) Switch again to asynchronous

    4) Change the connection string to use the new listener

    5) Test your application access (assuming that your application is working on the new DC as well..)

    6) Restart Servers on the DC1 

    hope this helps!

    regards!


    Monday, November 11, 2019 2:44 PM
  • Hi PraveenKumar_1999,

    >> Step 1: Execute below command on DC1-DB1 and DC2-DB3. Is this correct?

    Yes, you are right.

    >> Step 2: Execute below command on DC1-DB1. Is this correct?

    Yes, you are right.

    >> Fail over from the primary availability group to the secondary availability group. Execute below command on DC1-DB1. Is this correct?

    No, execute this command on DC2-DB3.

    >> Step 4: Change back to ASYNCHRONOUS_COMMIT. Execute below command on DC2-DB1. Is this correct?

    Running this command on new global primary and the forwarder.

    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

    Tuesday, November 12, 2019 8:35 AM

All replies

  • Hi All,

    Please share the steps to switch DC1 (Production) to DC2 (DR). We have configured Distributed SQL Always-On from DC1 to DC2 on WSFC.

    Currently Application is using DC1 availability group listener to connect DC1-DB. After switch from DC1 to DC2, application need to connect DC2-DB availability group listener.

    DC1:  Hosted VM_DB1 and VM-DB2

    DC2: Hosted VM_DB3 and VM_DB4

    Current setup:

    • Merged by Olaf HelperMVP Monday, November 11, 2019 11:10 AM Same question from same OP
    Monday, November 11, 2019 10:51 AM
  • Hi praveenkumar_1999,

    If you need to restart your actual primary DC you will have to follow these steps:

    1) change the synchronization mode to synchronous (wait some seconds so the DAG can apply all of the remaining changes on the other DC)

    2) Perform the manual failover,

    3) Switch again to asynchronous

    4) Change the connection string to use the new listener

    5) Test your application access (assuming that your application is working on the new DC as well..)

    6) Restart Servers on the DC1 

    hope this helps!

    regards!


    Monday, November 11, 2019 2:44 PM
  • Thanks.

    During drill test we're shut downing DC1.

    Below image contains current SYNC state and other configurations of SQL-DAG. Please review below steps and correct me If I missed any steps.

    1) change the synchronization mode to synchronous (wait some seconds so the DAG can apply all of the remaining changes on the other DC)

    <Praveen>: change sync mode on DC2-DB1 server using GUI/TSQL command. Is this correct???

    2) Perform the manual failover,

    <Praveen>: Will this failover from DC1 to DC2? or it's just failover within DC1 from DB1 to DB2?? As per my knowledge, it's not possible to failover  DAG from DC1 to DC2(manual/automatic). We can failover DB's within DC's, it's not possible to failover DB's from DC1-DBs to DC2-DB's or vice-versa. Please correct me.

    3) Switch again to asynchronous

    <Praveen>: change sync mode on DC1-DB1 server using GUI/TSQL command. Is this correc

    4) Change the connection string to use the new listener

    <Praveen>OK

    5) Test your application access (assuming that your application is working on the new DC as well..)

    <Praveen> OK

    6) Restart Servers on the DC1 

    <Praveen> OK

    Current DC1  mode:

    Current DC2 mode:


    Monday, November 11, 2019 3:45 PM
  • To perform the failover you can follow this link -> 

    https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-distributed-availability-groups?view=sql-server-ver15#failover

    Fail over to a secondary availability group -> Section.

    Just test the scenario in a test environment so you feel comfortable with these steps.

    Hope this helps!

    Monday, November 11, 2019 5:34 PM
  • Thanks a lot Gonzalo Bissio for your help. We don't have test env to verify below steps :)

    Can you please review below steps and share your comments on my questions (for step 3 & 4)?

    My Env:
    DC1: DB1, DB2 
    DB1 is global primary

    DC2: DB3, DB4
    DB3 is forwarder

    Step 1: Execute below command on DC1-DB1 and DC2-DB3. Is this correct?
     
    ALTER AVAILABILITY GROUP [distributedag] 
     MODIFY 
     AVAILABILITY GROUP ON
     'ag1' WITH 
      ( 
      AVAILABILITY_MODE = SYNCHRONOUS_COMMIT 
      ), 
      'ag2' WITH  
      ( 
      AVAILABILITY_MODE = SYNCHRONOUS_COMMIT 
      );

    Proceed to step 2 after the availability group synchronization_state_desc is SYNCHRONIZED. 

    Step 2: Execute below command on DC1-DB1. Is this correct?

    ALTER AVAILABILITY GROUP distributedag SET (ROLE = SECONDARY);

    Test the failover readiness and proceed with Step 3

    Step 3: Fail over from the primary availability group to the secondary availability group. Execute below command on DC1-DB1. Is this correct?

    ALTER AVAILABILITY GROUP distributedag FORCE_FAILOVER_ALLOW_DATA_LOSS;

    Step 4: Change back to ASYNCHRONOUS_COMMIT. Execute below command on DC2-DB1. Is this correct?

     ALTER AVAILABILITY GROUP [distributedag] 
     MODIFY 
     AVAILABILITY GROUP ON
     'ag1' WITH 
      ( 
      AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT 
      ), 
      'ag2' WITH  
      ( 
      AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT 
      );
    Tuesday, November 12, 2019 4:23 AM
  • Hi PraveenKumar_1999,

    >> Step 1: Execute below command on DC1-DB1 and DC2-DB3. Is this correct?

    Yes, you are right.

    >> Step 2: Execute below command on DC1-DB1. Is this correct?

    Yes, you are right.

    >> Fail over from the primary availability group to the secondary availability group. Execute below command on DC1-DB1. Is this correct?

    No, execute this command on DC2-DB3.

    >> Step 4: Change back to ASYNCHRONOUS_COMMIT. Execute below command on DC2-DB1. Is this correct?

    Running this command on new global primary and the forwarder.

    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

    Tuesday, November 12, 2019 8:35 AM
  • Thanks Cathy and Gonzalo Bissio.

    Y'day I have rebooted both DC1-DB1 and DC1-DB2 servers. After reboot I am not able to connect DC1-DB1 via SSMS. As per below image, DC1-DB2 is global primary. In above steps, no command need to be executed on DC1-DB1. Shall I proceed even if DC1-DB1 is not connecting via SSMS?

    I have already posted DC1-DB1 connectivity issue in below link? Can you please help me to fix this?

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2a701430-9c38-431e-a865-96dfd9f185e2/cannot-connect-to-sql-server-instance?forum=sqldatabaseengine

    Please share your comments/advise  on above two my queries

    AG status on DC1-DB2 after reboot:

     


    Wednesday, November 13, 2019 4:01 AM
  • Hi praveen, It seems that on agsql1 on the server db1 you are getting a connection issue... did you try restarting the ag endpoint on that server? What dis you see on the sql server error log on that replica?
    Wednesday, November 13, 2019 11:37 AM
  • Hi Gonzalo: I have resolved issue myself with the help of Sr. DBA's.

    Quick question: 

    1. Created new AG and Listener (Listener name: SQLList).
    2. Added SQLList in DNS/AD and granted full control.
    3. Due to some AG configuration mistakes, I have removed/drooped AG, during AG remove/drop, listener also removed automatically. 

    I tried to use same listener name during new listener creation, i got error saying name already exist in DNS/AD..conflict....So I asked to Windows Admin to remove/delete from DNS/AD. After removal from DNS/AD, I am able to create new listener with the same name.

    My question: Is it possible to use same listener name for new AG instead of asking windows admin to delete from DNS/AD? 

    Lets' assume that, the Listener IP/Name is not used in any AG.
    Wednesday, November 13, 2019 11:49 AM
  • Hi Praveen!

    Great news that you were able to resolve your issue! Regarding your question:

    My question: Is it possible to use same listener name for new AG instead of asking windows admin to delete from DNS/AD? 

    Well the answer is no and yes.

    Yes if the object dissapear from the AD and also the DNS removed automatically (this will depend on the number of DC that you have today... Because the replication take time).

    No

    If the DNS and AD object already exist on the domain and they were not removed automatically.

    Hope this helps!

    regards!

    Wednesday, November 13, 2019 3:48 PM