How can I use a Mirrored database to restore DB's at a remote facility?

Question

How can I use a Mirrored database to restore DB's at a remote facility?

Greg Roberts-134214

SSCommitted

Points: 1686
More actions
October 1, 2010 at 1:02 pm

#142959

Scenario:
Facility 1 located in Texas, USA hosts the production database and is the primary data center.
Facility 2 located in North Carolina, USA is the Disaster Recovery datacenter and hosts development databases.
The production database is mirrored from Facility 1 to Facility 2 in High-Performance (asynchronis) mode. (this is due to limitations in network bandwidth between the facilities and some transactions being over 1MB. (BLOBs))
The database servers are SQL Sever 2005 Enterprise - SP3.
The database is over 700 GB

To establish the mirror we copied the full backup to an external disk, and shipped it to Facility 2. We then coppied hourly transaction log backups accross the network to Facility 2. Once the external drive arrived, we connected it to the server, restored all of the backups, and established the mirror. The mirror has been running flawlessly for months now.

I can take a snapshot of the mirrored DB and write an ETL process to extract the data, but that will only get the data and not the objects.

I cannot failover the mirror because the application is not yet mirror aware. (part of what the developers are working on) If I failover the mirror, and backup at the remote facility it will take a minimum of 5 hours to complete the process. That's 5 hours of downtime to the business. Not acceptable. 🙁

So, any suggestions? How can I use the mirror the replace the development databases?

Greg Roberts

Derrick Smith SSChampion Points: 10833 More actions · Answer 1

If you create a snapshot of the databases, I'm pretty sure you could just do:

Restore Database [dbname] FROM database_snapshot = [snap_name]

edit: Snapshots are only available in Enterprise, so this wouldn't work if you're running STD for your DR. Also..I have restored a snapshot to the source database it was from, but not neccessarily another copy of it. I'm not sure if this is possible

Greg Roberts-134214 SSCommitted Points: 1686 More actions · Answer 2

We've tried that, but unfortunately the snapshot can only be used to revert / restore the database it was created by.

We also tried breaking the mirror, restoring the tail of the log, then backing up the remote DB. But to restore the mirror we had to completely reinitialize it. When the backup was taken, the LSN of the transaction changed, andthey were no longer in sync.

Greg Roberts

Derrick Smith SSChampion Points: 10833 More actions · Answer 3

Yeah, restoring the mirror is out of the question since you can't go back without starting all over, which defeats the whole purpose of this. Doing a full schema creation + data copy on a 700gb database (or snapshot) would take a horribly long time and I'm not sure is a good option either.

You can't back up a mirrored database or a snapshot, so neither of those are options.

You _might_ be able to shut down the server, copy the data files of the mirrored db, bring it back up, and then try to attach the new set of files..but even if that works, that's not a reliable method at all.

Greg Roberts-134214 SSCommitted Points: 1686 More actions · Answer 4

Derrick Smith (10/1/2010)
You _might_ be able to shut down the server, copy the data files of the mirrored db, bring it back up, and then try to attach the new set of files..but even if that works, that's not a reliable method at all.

Thank You, that worked on my PoC!!!!!! :w00t:

Now to attempt it with a larger, and active, database. I am concerned that the mirror will be disconnected for however long it takes to copy the data files, and how long it will take to resync. But if i do it on a weekend the impact should not be too significant.

Greg Roberts