SQL 2012 Always on DR test approach

How to test DR procedure on actual environment? Is it acceptable to test this on actual environment or peoples use different technique to test this?

SQL 2012 AlwasysOn WSFC Availability Group (No FCI configured)

All nodes are standalone instance.

Primary Site- 2 Node running in synchronized mode with auto fail-over.

DR Site- 1 Node running in A-synchronized mode with manual fail-over.

more ▼

asked Nov 24, 2015 at 02:33 PM in Default

avatar image

1.4k 90 93 96

Are you planning to test DR for just the SQL Server, or do you have an enterprise DR plan where you are going to test the DR plan for the entire environment including SQL, Active Directory and any applications that need to connect to SQL?

Nov 24, 2015 at 11:14 PM KenJ

How usually people do that in there environment or they just wait for emergency and configure DR without any test?

What is your suggestion if we want to do this for database and corresponding application?

Nov 26, 2015 at 05:02 PM Sharma
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

If you've never done this before, start with small steps. First check if your environment is AlwaysOn aware. Some things to check: listener process running? Logins and Jobs on all nodes? Jobs on secondary nodes are AlwaysOn aware?

The next step is to do a failover outside office hours to the other node on the primary site to make you comfortable with this action. Make sure you have some people working with the application during the failover, so you can collect the behavior of the application during a failover. Also check if you SQLjobs are working properly and logins are

The next step is to know how to restore a database in an AlwaysOn environment. Remember: AlwaysOn doesn't prevent you from user errors like deleting too much data.

Hope this helps you starting working with AlwaysOn!

more ▼

answered Nov 29, 2015 at 11:53 AM

avatar image

Wilfred van Dijk
3.6k 28 39 49

(comments are locked)
10|1200 characters needed characters left

Hi Sharma, Did you complete the DR test of alwayson.if yes please let me know the list of steps you carried out. and things to keep in mind.

Regards, Vijay

more ▼

answered Jul 19, 2016 at 11:16 AM

avatar image


(comments are locked)
10|1200 characters needed characters left

Here are some notes that should help:

Patching Availability Group with One or more Secondary Replicas

  1.  Change failover mode from Automatic to Manual on all synchronous-commit replicas. 

Also change Availability mode from Synchronous-commit to Asynchronous-commit. Failover mode needs to be changed first to Manual before the Availability mode is changed to ASYNCHRONOUS_COMMIT.

  1.  Make sure Availability Group is on Primary Replica.
  2.  Upgrade all remote and Secondary Replica. Perform reboot if required.
  3.  Failover to secondary Replica.
  4.  Upgrade former Primary replica.
  5.  Failback Availability Group to Primary replica (secondary replica currently). To manually fail over an availability group, connect to the server instance that hosts the target secondary replica.
  6.  Set Synchronous commit and Automatic failover as it was before upgrade.

While changing back the settings, Availability mode is changed to SYNCHRONOUS_COMMIT, followed by Failover mode to AUTOMATIC.

more ▼

answered Jul 26, 2016 at 05:00 AM

avatar image

Sagar Bhargava
638 25 30 34

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 24, 2015 at 02:33 PM

Seen: 817 times

Last Updated: Jul 26, 2016 at 05:00 AM

Copyright 2018 Redgate Software. Privacy Policy