question

Sharma avatar image
Sharma asked

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-2012always-onavailability-groupsdisaster-recovery
2 comments
10 |1200 characters needed characters left characters exceeded

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

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 Like 1 ·
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 Likes 0 ·
Wilfred van Dijk avatar image
Wilfred van Dijk answered
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 characters needed characters left characters exceeded

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

vijaygan avatar image
vijaygan answered
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 characters needed characters left characters exceeded

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

Sagar Bhargava avatar image
Sagar Bhargava answered
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 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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