SQL Server migration using a replica

Question

I want to migrate a fairly big SQL Server instance (100 GB+) to a cloud machine. I don't think the attach/detach method will be a good idea. Because sending big files over the internet would take a long time (probably more than 1 night) I thought about configuring snapshot/transactional replication from the source to the cloud instance.

The idea is to let the replication take its course over a few days and then after the databases are up to date simply stop the replication and direct the application to the subscriber database.

Is it possible? Do you have any other ideas? Maybe AlwaysOn (I guess it requires Enterprise and not Standard edition)?

Answer 1

What version of SQL Server are you using? From SQL Server 2008 onwards, Backup Compression can offer superb rates of compression. I've seen backups pack down to 10% of their original size, so your 100GB+ could be more like 10GB. Of course this really varies depending on the type of data you are storing; XML doesn't pack down too well for example.

You also have options like breaking the backup into multiple files so you could copy multiple files in parallel rather than one big backup file. This won't help much if your "pipe" to the cloud is narrow though.

Have a look at your compressed backup size, then work out how many Megabytes per second you can transfer through your existing pipe ( ftp, WAN, etc ? ) eg by transferring a sample 1GB file to get a rough idea.

A final option, and something I'm looking at right now is SQL Server 2014 Clustered Columnstore with full Archival Compression ( DATA_COMPRESSION = COLUMNSTORE_ARCHIVE ). Intended for 'cold', rarely accessed data, again this really varies but I've seen up to 30x compression. YMMV. I'm therefore considering an option of 1) drop all indexes, 2) add clustered columnstore with archive compression, 3) backup and move to cloud 4) recreate earlier indexes or leave as it. I'm currently investigating this and the relationship between clustered columnstore compression and backup size.

Good luck!

Answer 2

It seems I rarely ever see this recommendation, but Log Shipping is a great method to migrate large databases. If you combine that with turning compression on for the instance (it sounds like you have 2012 since you mentioned Always On), it should be a piece of cake. I've used log shipping to migrate a 2 TB database and several smaller ones. A few caveats: your database must be in full recovery in order to take the log backups and you'll need to create a share on the host machine or another system that both systems can access to copy the backup files. It might be a good idea to test this with a small test database first to make sure you know how it works, but it is pretty straight forward.

Answer 3

if you do not have an enterprise edition, an availability group will not be an option. I would suggest you start up Database mirroring between the two servers and simply failover when the databases are synchronized(Take a backup first) (Don't forget to change your application to connect to the new cloud server at that point because the original database will become unavailable"restoring").

If you have an enterprise edition(2012) you can set up an availability group which you can failover to when synchronized and back should it fail (this is not an option with mirroring).

Other options are to backup and restore your database to the cloud. You take a full backup and then keep taking log backups until the moment you want to switch over. Then you stop the application and take a last transaction log backup(the tail log) then you restore your full backup and all your transaction log backups on the cloud machine. If you use backup compression your file will be aproximatly 1/10 of your original database size, and your transaction logs will be depending on the amount of traffic on your server. To save time you can transfer your full and following transactional backup to the cloud machine before you bring the application down. and then restore them when you actually have transferred the last transaction log file.