Mirroring between two Ms-sql servers for Migrating server from one Datacenter to other Datacenter

Question

I have a query about microsoft sql server. I'm looking for migrating ms sql server from one vendor to another vendor(Datacenter). I'm looking to have a mirror configuration between servers since my database is close to 40GB and would take days for copying from old to new, So once the database is synchronized, i would be taking down the old server and connect my app with new server.

My concern is if i take backup and restore it in the new server and start mirroring, Would the data will be mirrored where transactions happened between taking backup of the server and starting mirroring?

Thanks, Swaroop.

Answer 1

This is perfectly viable (and is how I moved several databases before). The outline of the process is this:

1. Make sure the source database is in full recovery mode
2. Configure the source to no NO_TRUNCATE transaction log backup (so the log is preserved)
3. Full Backup the source. Preferably use backup compression
4. Take your full backup (on a USB stick if it takes to long to copy over the network) to the target
5. Restore full backup on target
6. Take a log backup of the source
7. Move (using your fastest method as in 4) the log backup to the target an apply it there
8. Configure mirroring in async mode (source and target can now mirror)
9. Start regular transaction log backups again on the primary
10. Wait for mirror to catch up (use the mirror admin tools to monitor)
11. Set mirror to sync mode
12. Move the app pointer and manually fail over the mirror

A thing you have to be careful about is that you have enough space to hold the transaction log while the move is happening. But a 40GB database is tiny, so I assume you wont have a lot of tlog traffic on it.

Another thing to watch out for is that you have the same security set up on the target as on the source. If not, you may end up with orphaned users when you swing over the server. This is less of an issue if you only use Windows logins.

Getting this right takes a bit of practise. I would advise to test it out on a development system first to make sure you have all the steps scripted nicely.

Answer 2

Setting up database mirroring will be the same amount of time as just copying the database since copying the database is just one of the steps in a mirroring setup. Even if a transfer of the full backup file takes 40 hours like you claim (have you looked into backup compression?), you can then take a differential backup at the source and apply it at the destination. The differential should be much smaller than the full and so the transfer time should be much smaller. If that's still too long, look into taking and applying log backups. Hopefully, there's a slow time of day where the log backups will be small and thus will transfer quickly. Best of luck.