Sql-server – Syncing two databases in a redundant network architecture


I've been reading posts on here about database mirroring and it's an almost overwhelming amount of options and information, but as I'm still new to most of the SQL database scene, I was hoping to find a more definitive answer to my situation:

My company has two 2008 R2 SQL Server databases, which exist on the same network and contain the same information. They are both updated simultaneously as new system data comes in from the automated processes they record, so all the records in both databases match perfectly. The concern in this scenario is that when one database goes down, the other is meant to kick in as the backup, and then when the problematic database is back up, it will be missing the records that were created during down time.

The question:

What would be the best method for retroactively syncing data between the two after one of them has crashed and been brought back online?

As many of the other posts have asked more questions back about what the company will / will not allow per the DBA or other permissions that might exist, I'd like to just state that it is safe to work off the assumption that all other concerns or conflicts have been taken care of, and we purely need to just migrate data to keep each database up to date with the other after a crash. In terms of a master / slave relationship, I don't believe one currently exists, as neither database is getting its data from the other (so long as they are both running); rather, it's coming from a tag-based program that monitors the afore-mentioned automated system


Did some looking into using the Publication / Subscription method through the REPLICATION folder you can find in SSMS and this seems plausible. The options appear as if it allows for immediate and continuous data sharing – would this be a viable option?

Best Answer

That's tough. Even 5 minutes of downtime for a reboot would put you in a situation with out of sync data. I really think you should look into other options.

I'd definitely recommend mirroring here instead of what the app is doing. With Synchronous Database Mirroring with a witness, the two databases will be in sync and you'll even get automatic failovers. It's not hard to setup either.

You could use Transaction Replication as it can handle a server being down and pickup where it left off. But I don't consider it to be an HA or DR technology. Mirroring, Log Shipping and Availability Groups are the HADR technologies. Specifically Sync mirroring and Sync AG are HA, async mirroring, LS and async AG would be DR.