One of the things I always recommend is that when you upgrade a SQL Server instance, you perform a side by side migration to a new host instead of an in-place upgrade. My main concern is risk. While the upgrade process is fairly smooth, I still have hiccups installing SQL Server at times, and for a live server, the last thing I want to do is have to uninstall SQL Server and reinstall an old version.
Apart from the risk, I also think an upgrade is a great time to refresh hardware. If you're paying for the latest bits, I'd spend a little more for newer hardware if I can. The cost usually isn't much compared to SQL licenses, especially these days with hardware being very cheap and powerful. New hardware also gives me a staging place to test the migrations, without disturbing the existing system.
Planning the migration across hosts usually isn't too difficult, but that there can always be small issues that I need to fix after the job is done. In most cases, that's not a problem. In some, it can cause downtime (and plenty of embarassment), not to mention a loss of confidence in the DBA team. I find that I often end up building a checklist, working through the existing instance to ensure I don't forget any items, fixing my list as I run test migrations to new hardware and find issues.
That makes me think I should have a good checklist that I can start from, marking this up for my specific instances. BOL doesn't give much information. I did see a nice one in a forum post on SQLServerCentral, but I'd really like a good, solid checklist. An overall list from @spaghettidba might be a good start, and I'm hoping he'll write an article on each of these, with the list for someone to check. However, I'm hoping to give him, and others, a jump start.
What items do you need in a checklist for a SQL Server 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.