The Migration Checklist

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.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.7MB) podcast or subscribe to the feed at iTunes and Libsyn.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Editorial and tagged , . Bookmark the permalink.

4 Responses to The Migration Checklist

  1. pianorayk says:

    Steve: I’m working on a presentation about checklists. You okay if I borrow this as a reference?

  2. Jason Squires says:

    For Start-SqlMigrations, if used, verify that the SQL Server Agent Properties for Alert System is populated after the migration.

  3. pianorayk says:

    Reblogged this on Welcome to Ray Kim's 'blog and commented:
    I am working on a future presentation regarding checklists. I saw this post from my friend, Steve Jones, and I figured it was worth a reblog (not to mention that I can refer to this for my reference).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s