Problem definition
Our database server needs to be transferred to an other datacenter. It runs on Microsoft SQL Server 2012 Enterprise (64-bit) and contains two databases of about 2TB and 1TB.
Having little to no downtime for this would be ideal.
Workload
Those databases are used for a .NET website and are constantly getting updated.
Having it not available over the weekend would be acceptable though. The currently in use DB would remain the only one in use until the switch over to the new one.
That switch would ideally be made just by changing DNS entries to point to the new DB server while making sure the DB is not being updated.
Also, time taken by this operation does not really matter as long as the switch from one server to the other (downtime) is kept low.
Approaches considered
-
Backup and restore
This has been done in the past but involved a high downtime even though it was done through an internal network, so more efficiently than through Internet
-
Log shipping
As far as I understand, this approach would minimize downtime by configuring a master/slave and transferring an exact copy of the master DB to its slave being read only. As mentioned above, no access to the slave would be necessary and we just need a way to have a replica of the master DB without data corruption.
It also seems to be quite efficient in terms of resources utilization and wouldn't impact to much the master performance.
I might be wrong about this approach so feel free to correct me.
-
Database mirroring
I'm not too aware of that approach but it seems like a valid option. No need to have real time sync and performance of the master is quite important so asynchronous would be the way to go if this approach were to be chosen.
-
Other options?
That server runs directly on bare metal hardware so lower level solutions are unfortunately not an option. Maybe there is a better way to get this done?
Constraints
As described, those databases are quite big to the point they are hard to maintain but that's an other problem.
The versions of SQL Server will be the same (Microsoft SQL Server 2012 Enterprise 64-bit).
It will have to be transferred over network between two datacenters so most probably over Internet. Having disks sent from one site to the other for an initial sync is unfortunately not an option. Having some sort of security for the transfer would be ideal but we will do the best of this situation.
That should give a quite good overview of our needs for this task and hopefully some of you had to face that situation before.
Best Answer
Straight backup and restore is obviously out. I also wouldn't consider replication of any kind.
Database mirroring is relatively simple to set up, but requires real-time connectivity between the two servers, setting up of partners and endpoints, etc. Availability Groups could be an option, but on top of the networking complications you also have to have both servers as members of the same WSFC - which means they must both be in the same domain. This is not a typical setup (or could even be made to work temporarily) for a data center move.
My vote would be for log shipping. The nice thing about this is that you can use the backups and log backups you're already taking (right?) and don't necessarily have to have real-time connectivity between the two databases - they don't need to know about each other, you don't need to set up endpoints for mirroring, partners, security, etc. You just need a way to get files from the old server to a place where they can be restored on the new server. You can take a full backup well in advance, get it over to the new server, restore it, then apply (possibly diffs and) incremental log backups from that point right up to the moment of cut-over. The process is actually quite simple, and there are many tutorials on log shipping available online if you come across any difficulties.
If the web app is moving with the database, since DNS can take a while to propagate, you might want to make a switch in the connection strings of the old app to make them point at the IP of the new database server once it is writable, since - even after the switch, and even if your TTL settings are tight - clients may continue to hit the old web servers. It all depends on how much respect their providers give your TTLs.