Sql-server – Minimise time for migration

migrationsql server

I want to migrate production DBs from one server to a different one.

This is my plan:

  1. Shut down the application
  2. Change the DB to single user mode
  3. Take a full backup for all databases
  4. Restore the backup to the New server and remap logins
  5. Create replication
  6. Point the application to the new server
  7. Start the application

This may take 4 hours to complete. Is there a way I can perform this in less time? It is kind of hard to request 4 hours down time.

Best Answer

You can significantly lower the amount of downtime by running both current and new versions in parallel. Use something like Log Shipping, Always On Availability Groups or even replication to generate a copy of your database on a new instance that is running whatever newer version you want to upgrade to. I'd go with Log Shipping as it's the simplest, technically, and is available on any edition of SQL Server. Always On AG gets you closest in sync but take a bit more work to setup. Repl is kinda in between.

It may take a while for both databases to be "almost" in sync depending on how large the database is and your infrastructure but that is not as important since your system is online the whole time. Once you get to a steady state where all existing data from the original DB has moved to the new DB and you're left with only new transactions to move, then stop transactions on the original, let all data get moved to the new DB. Actual method/command depends on what approach you take but all are well documented on MSDN.

Verify all pending transactions have been pushed to the new DB then make the necessary changes so applications point to the new DB. Finally, run validation tests against the new DB to make sure everything works as it's supposed to (even if you've tested in your pre-prod/QA environment) and re-enable applications/transactions if all tested clear.

This puts the bulk of your downtime on whatever tests you need to run and you really should not skip that part. Cutting over from Log Shipping, AlwaysOn AG or replication is a matter of minutes, assuming no server, storage or network bottlenecks.