SQL 2012 Always on DR test approach

Question

question

Sharma asked Nov 24, '15

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.**

sql-server-2012 always-on availability-groups disaster-recovery

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ commented · Nov 24, 2015 at 11:14 PM

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?

1 ·

Sharma commented · Nov 26, 2015 at 05:02 PM

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?

0 ·

Answer 1 · 2015-11-29T11:53:07Z

Wilfred van Dijk answered Nov 29, '15

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!

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2016-07-19T11:16:00Z

vijaygan answered Jul 19, '16

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

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2016-07-26T05:00:13Z

Sagar Bhargava answered Jul 26, '16

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. 2. Make sure Availability Group is on Primary Replica. 3. Upgrade all remote and Secondary Replica. Perform reboot if required. 4. Failover to secondary Replica. 5. Upgrade former Primary replica. 6. 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. 7. 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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question