2

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.

0

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/

  • agree. yes another option is log shipping but the downside is you can't move back to lower version of SQL Server (if you need to rollback). Rollback is much tougher. so testing is needed. – user37701 Jan 3 '18 at 6:55
  • Same is true for any upgrade option – Greg Jan 3 '18 at 10:23
  • Thanks for the advice. I will look into Log shipping as a way to reduce the 4 hour upload time as downtime. – S Chase Jan 3 '18 at 12:59
  • 1
    There's no need to actually set up log shipping for a single cutover. Do your Full backup, restore it WITH NORECOVERY; occassionally move a differential backup over, restore WITH NORECOVERY, and then you're ready to move the rest, do one more DIFF before your window, restore it, and then put your DB in read only, do a T-log backup, move it over, and restore it. – Anti-weakpasswords Jan 8 '18 at 6:57
  • yep, you could do it without log shipping. Again it comes down to how much data changes, how quickly you can get the data up to the other data centre, and how big an outage you can take. Differential databases contain all changes since the last full backup, so depending on the use of the database they could be significantly bigger than shipping logs. – Greg Jan 8 '18 at 22:00
2

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.

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.