Moving SQL to a hosted server

Posted on

Question :

I’m in the process of moving a large (7GB-9GB) database to a hosted server. the old server uses SQL 2008 R2 and the new one is using SQL 2016. I’m trying to find the best way to reduce the downtime for this migration. I’ve looked into mirroring and replications and don’t think either really fits the situation. I’m hoping I’m wrong and there’s something I can use that I’m just not looking at properly.

Is it possible to set up a mirror or something in this manner and then once I drop the primary just disable it?

I have full access to both servers but may need to have firewall ports opened if needed.

Answer :

I agree with @dco that it’d be easiest to just do a backup/restore on a 10 GB database, however if that is too much downtime for you (e.g. it’s going to take hours to ship between data centers), then I’d look at using Transaction Log Shipping. There’s plenty of info on the net, but basically you do a full backup of your database, restore it on the new server, and then do regular transaction log backups on the primary, shipping them to the new server and restoring them. When you do your cut over, you just need to do a final transaction log backup/restore, bring the database out of “Restoring” mode and then point everything to your new server.

Plenty of info out there: https://www.mssqltips.com/sqlservertip/2301/step-by-step-sql-server-log-shipping/

https://www.brentozar.com/archive/2017/06/introducing-sp_allnightlog-log-shipping-scale-open-source/

For 7GB to 9GB of database, I would advise you to just use backup and restore strategy. This is the most simplest and effective way of migrating small database. I highly recommend you look at dbatools to help you automate your migration (it also migrate your objects like logins). The most important part of your migration is you practice your rollback and making sure application/users can still connect to your database after migration. Always test before you go live.

Leave a Reply

Your email address will not be published. Required fields are marked *