We're installing new data center and we'll set that location as primary location. What do you think, how should we migrate SQL Server data? System is normally active 24x7x365, but we're prepared for scheduled downtime. Our idea was this: at certain moment shut down the network interface, backup the database, transfer it to another location, restore it and start the network interfaces. The longest thing here is backup and restore. Database size is approx 50 GB. At primary location we're using SQL Server 2005 Standard and on new location we're using SQL Server 2008 R2 Standard. Primary location has synchronized mirroring inside it. New location will have SQL Cluster with 2 nodes.
What about this idea? We break current mirroring and set mirroring between primary and new location, that way we removed data migration because they will be synchronized – real time. The question is is this possible? Because on first location we have 2005 version and no cluster and on new location we have 2008 r2 with cluster. Also, network between those two locations is 10 mbps.
Best Answer
You'll probably want to use log shipping of a sort. Do a full backup and restore that. Setup log backups and copy them to the new server, restoring them.
At the time of the cut over, stop the user connections and backup the last of the log. Restore that on the new server, rolling the log forward and bringing the database online. You can then redirect the customer connections to the new data center.