22

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.

20

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.

16

I recently migrated 15tb across 6 databases using mirroring. Very simple and worked perfectly with just a couple seconds of failover time.

Edits:

I had two new virtualized SQL Servers. The databases were coming from 3 servers that they had just plain outgrown, and were impacting performance on the smaller databases hosted on them.

The process was very simple.

  1. Wait for weekend full backups to complete
  2. Restore with no recovery to new servers
  3. When those restores complete, pause backups
  4. Run one additional restore up to the latest log backup from originals, leaving in no recovery
  5. Start mirroring across all six
  6. Resume backups

I chose to leave them in asynchronous mode until we were ready to fail them over to reduce load on the network, etc. Mirroring has some reputation for causing latency during maintenance (index/statistics) and other high volume activities, but I didn't find that to be true. They do have to be switched to synchronous mode before manual failover.

During the next maintenance window, I manually failed over each database, and after some smoke testing, turned mirroring off, and eventually removed the old data files from the originating servers. One quirk in this process is that failing a mirrored database over leaves the former primary in a recovering state, so unless you're comfortable just dropping them, you need to bring them back online and then detach, or whatever your preferred method of removal is. Also, I did not configure a witness for any of this because I did not want automatic failover. This was a controlled event.

Please let me know if you'd like any further specifics. I've left out server and network specs but can provide if you'd like.

Thanks

protected by Community Nov 16 '15 at 16:00

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.