Keeping five databases in sync
I'm running SQL Server 2000 and using log shipping to create a standby server with five databases as a DR failover. The five databases need to remain in sync in order for the application to open and operate properly.
It seems that with log shipping (set to one minute intervals) I could still have, say, three out of the five databases ship their 2:05 log before a failure, which would leave the other two databases sitting at 2:04. This would basically leave the system out of sync. Am I missing something in the setup?
Since the log backups and shipping are run independently, there is no way to keep all five databases totally in sync all of the time. Part of your recovery process should be running one last transaction log backup from all of your databases and then applying this to your standby server. The standby server would not be a totally automated failover, so you would need to have some of these manual processes in place. If you are looking for automated failover and to keep a server in sync, you should look at some of the third-party applications that are built for automated failover.
Do you have comments on this Ask the Expert Q&A? Let us know.
This was first published in July 2005