Steve Jones posted a good editorial a month or so ago about why he typically prefers side-by-side migrations over in-place upgrades. If you are running virtualized and want to upgrade in-place then it is not horrendous if you need to revert back to a previous snapshot. However, you would then have to start the upgrade process all over again from the snapshot. A side-by-side migration you can leave the existing VM up until you are confident in your testing of the new instance and then perform the final backup / restore / migration steps before switching VMs.

Overall he provided a good list of things to plan for in a SQL Upgrade / Migration:

“This could be just moving the existing SQL Server instance to new hardware. It could be a version upgrade, consolidation, or any other reason to move. In any case, I’ll start with a general list of things to check. Let me know what I’ve missed:

Instance Level

  • Check new hardware/software meets requirements for SQL Server
  • Verify patches levels are the same (with items needed for installs)
  • Map paths from old to new drives, verifying space
  • Ensure all logins, server roles, credentials, and permissions are migrated.
  • Migrate all sp_configure items
  • Migrate startup stored procedures
  • Migrate all linked servers
  • Migrate all XE sessions
  • Migrate Audits
  • Migrate any server level cryptographic objects
  • Migrate all jobs and agent settings (operators, alerts)
  • Migrate SSIS stuff
  • Migrate Resource Governor data
  • Migrate Database Mail settings
  • migrate replication settings at the instance.

Database Level

  • Backup all databases
  • Backup and certificates needed for TDE
  • Restore databases with new paths
  • Verify database ownership
  • Ensure backups are running on the new instance

As a side note, dbatools will perform much, or maybe all (still trying to determine that), of what you need. There is a Start-SqlMigration that is very impressive. While I would still want a checklist to ensure the new system works as needed, I think I’d use the PoSh tools and then add anything else I need to them. ”

When you are planning on performing a migration of any sort it is good to have a plan. We recently put together a plan to migrate a Client Dashboard Solution from a custom onsite web service to a more familiar SQL batch solution. We approached it with a general plan /checklist for what we expected if things went right and we also had a plan to keep the solution working if we ran into unexpected issues and had to revert. This allowed a much easier conversation with local IT and removed a lot of the worry during the transition.

How do you like to approach planning?

Let us know in the comments below.