4

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)?

6

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!

  • Another vote for backup compression here. Bear in mind it's only in 2008 ENTERPRISE, but in 2008r2 onward it's in Standard Edition. If you can't use it, HyperBac is an option. I routinely do file copies for this (of a 100 GB database compressed to like 20 GB) to an Amazon AWS instance, and it only takes a couple of hours normally. – JNK Dec 2 '14 at 14:08
  • Yet another vote here for compression. Theres a blog post by brentozar with some steps you could take: brentozar.com/archive/2010/02/… – Reaces Dec 2 '14 at 14:31
  • @JNK isn't HyperBac discontinued? According to RedGate, support ends next year. Having said that, a trial of any of the 3rd party backup tools (redgate, litespeed, idera, etc) would work to maximize compression (they offer higher compression than the native engine in most cases) and presumably meet the stated needs for this one database. – Your_comment_is_not_funny Dec 2 '14 at 14:56
  • HyperBac is discontinued as I tried to get hold of it a few years ago. Never knew why as its 'mount db from backup' ability honestly looked cool. I have in the past resorted to manually zipping uncompressed SQL Server backup files for transfer, which actually has quite a good effect. Zipping already compressed files has no effect and is a waste of time! : ) – wBob Dec 2 '14 at 15:54
3

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.

0

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.

  • @ShawnMelton It is possible. blogs.technet.com/b/bshastri/archive/2014/08/20/… . However I'm not sure if it's feasible for a running production server. Or how severe the impact on the production system's performance would be. EDIT: Shawn's comment seems to have dissapeared, I'll leave mine here for the link. – Reaces Dec 2 '14 at 14:32
  • All that is way more complicated than it needs to be for just getting data moved up to the cloud. Especially if that is the only reason the OP will need Enterprise Edition. – Shawn Melton Dec 2 '14 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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