Our application writes to multiple Sql Server databases within a distributed transaction. The Ops guys are saying that this messes up their disaster recovery plan because while the transactions on the live tables may commit at the same time, the log shipping on the separate databases happen at slightly different times. So in in a disaster recovery situation, there will be a few partial transactions.

Is there a method for maintaining separate but synced databases in DR? Or do we have to re-design to relatively independent databases (or a single database)?

migrated from stackoverflow.com Mar 18 '10 at 3:40

This question came from our site for professional and enthusiast programmers.

  • Perhaps this would be more suited to serverfault.com? – zneak Mar 18 '10 at 1:55
  • Indeed it probably is. – DevDelivery Mar 18 '10 at 2:48

I'm not sure what your operations disaster recovery plan is but you you can recover to a specific transaction that ensures that all databases are in a consistent state. To do this you will use Marked Transactions.

One limitation of this approach is that you can recover only to a marked transaction and not to a specific point in time. I'm not sure if that messes up your strategy or not.

From the article above, the basic approach is:

  1. Create a full or differential database backup of each of the related databases.
  2. Mark a transaction block in all the databases.
  3. Back up the transaction log for all the databases.
  4. Restore database backups WITH NORECOVERY.
  5. Restore logs WITH STOPATMARK.

Note that during the two phase commit for the transaction mark transaction other transactions will be blocked which can affect performance.

  • Yes, looks like there can be a delay in the restore of the shipped logs, so that in a recovery the last logs can be restored to a common point. – DevDelivery Mar 18 '10 at 2:49

Using Marked Transactions is, IMO, the best way to maintain logical application integrity, over separate physical boundaries, in your case databases.

Another option, would be to use SQL Mirroring (http://technet.microsoft.com/en-gb/library/cc917680.aspx). In short it is a method of ensuring transactions are completed on two databases (one at main site, one at DR site) before it is marked as complete. There are obvious performance impacts and you will need to determine if this is a problem for your application.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy