Home / server / Questions / 5349
Accepted
Matt Rogish
Asked: 2009-05-07 07:18:22 +0800 CST

SQL Server 2005 Mirrored DB Recovery

  • 772

Scenario: We want to use SQL Server 2005 Standard's version of DB mirroring along with a witness server in an Active Directory domain environment. The database is fed from a 3rd party app server that cannot be modified apart from the DB connection string.

Two questions related to this:

  1. How can we make the failure somewhat invisible (or at least, require less manual work) to our 3rd party app server spending as little money as possible? My thinking is set up DNS "sqlserver.ourdomain" that points to the primary and use that in the connection string. Then should it fail, we change DNS to point to the mirror and re-start the app server. Not invisible or automatic but is cheap and seems like it ought to work.
  2. How do we push transactions from the secondary back to the primary after failure and re-promote the primary? This process must take into account "undoing" the failover mode in #1. Obviously if we go with the DNS option above we'd re-point DNS to the primary but how do I get the system back as before -- with the primary up to date from the mirror and the mirror demoted back to mirror?
sql-server sql-server-2005 disaster-recovery mirror

2 Answers

  1. Best Answer

    Question 1: You're overthinking it. SQL 2005's database mirroring specifications are built into the connection string. You specify both the primary server and the mirror right in the connection string, and the driver tries the servers in order. That's one of the beauties of database mirroring - no client app changes are required.

    More info here: http://msdn.microsoft.com/en-us/library/5h52hef8.aspx http://msdn.microsoft.com/en-us/library/ms366348.aspx

    Question 2: You said you're using a witness server, so I assume you're using automatic failover. SQL 2005's database mirroring does this stuff automatically - more info here:

    http://msdn.microsoft.com/en-us/library/ms189590.aspx

    If you're not using automatic failover, then it's still pretty easy: you just right-click on the database, go into Database Mirroring, and you can fail the databases back and forth from primary to secondary and vice versa. They automatically get back in sync after outages like reboots.

    • 8

  2. One other thing to bear in mind (unrelated to your questions, but something people don't think about sometimes) - if the original principal is going to be down for a while, you should remove mirroring to prevent the transaction log on the new principal growing out of control. Otherwise, the transaction log all must be kept around because mirroring assumes the old principal will come back online at some point.

    Thanks

    • 2