SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Migration Checklist

By Steve Jones,

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.

 
Total article views: 167 | Views in the last 30 days: 16
 
Related Articles
BLOG

SQL Server Hardware Checklist

I’m in the process of putting together a SQL Server hardware inventory/audit checklist for an upcomi...

FORUM

Checklist Needed

Checklist For Migration

FORUM

What is the easiest way to migrate to new hardware?

What is the easiest and quickest way to migrate a publisher instance to new hardware?

BLOG

SQL Server Instance Checklist

This is my third in a series of checklists that I am putting together for a new book designed for......

ARTICLE

SQL Server Migration

Some SQL migrations require keeping the server name, instance name, IP, and port number. Geoff Ruble...

Tags
editorial    
 
Contribute