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)?
Best Answer
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!