2

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)?

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

2 Answers 2

1

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.

1
  • 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, 2010 at 2:49
0

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.

You must log in to answer this question.