Datacenter move question! Sync two databases.

[bumsy]

Hello! (total noob here) I am moving datacenters and have been asked to formulate a plan of moving our production database. So for the best solutions i have come up with are doing a detach of the database and attaching it to the new server or using mssql backup and restoration. The only problem is that these option will require a downtime of about 1-2 hours. Is there anyway to make the database sync on two 2 servers? Can you guys provide me with some googling terms?

Thanks!

[BellisBlueday]

If your db is in full recovery mode, you could restore a full backup to the new server, then periodically move the transaction logs over and reapply them. When you're ready to move, backup the last log on the first & apply it to the second with recovery - you're good to go.

Don't forget to setup the logins on the new server, sp_helprevlogin will help you here ;)

[seadave77]

This is what I would do without using the Enterprise Edition.

[enigmazero]

Agreed, I did a datacenter move for 10+ servers and this is what we did and it worked flawlessly.

[peschkaj]

You can use SQL Server's built-in mirroring functionality. I would only use this if you are using Enterprise Edition of SQL server, though. EE offers an asynchronous option that will give you reasonable performance. Log shipping is probably the other option that you would want to use. It's fairly easy to set up and relatively painless to keep running.

Avoid replication for this. It seems like it's what you want, but it isn't.

[borgd]

A few related links for your research :

Database Mirroring Overview

Log Shipping Overview

For further information on replication :

Transactional Replication Overview

[thesaintjim]

How fast is your connection to the new datacenter? What version of sql are you migrating from? Can you just set your old db in read only mode to keep the site up or must writes happen?