One of the most difficult projects to undertake as a DBA is upgrading your SQL Server from one version to another.  The biggest reason that it’s so difficult is the lack of planning and testing before during and after the migration.  With proper planning, everyone involved with the migration from the DBA, to the programmers, to the business users can understand what is involved in the migration and what part they will play in this migration.

I’m going to walk you through the basic steps of the migration.  If you’re a DBA, you can use these steps to help educate your coworkers on what it will take to make the migration happen successfully.  If you’re a business user, this article will help you learn how much can be involved in a migration.  If you’d like help moving from one version of SQL Server to another, please feel free to contact me through my new consulting company, HiveMind.

Run the Best Practices Analyzer

For SQL Server 2000 through 2012, there are programs written by Microsoft to help determine if your SQL Server instance is configured according to the certified best practices.  It will help determine if there are any problems with your current server.  If there are, it can recommend fixes you may want to take care of before, or during the migration process. It can also help you determine if it will be safe to proceed with your migration.

If there are any issue preventing the migration, address those at this time, and then re-run the analyzer until it suggests it’s safe to proceed.

Check for any queries that will break

When Microsoft updates the engine, they make changes that make SQL more efficient and robust.  As a result, some features of earlier versions of SQL Server might not work in later versions.  You need to check for this code before you try to upgrade, otherwise, you might find your system no longer works after the migration.

I suggest running a server side trace to determine if anything will break during the migration.  This trace will need to run long enough to cover a representative sample of your work load.  If your business cycle is weekly, you’ll have to run the trace for a week.  If your cycle is monthly, then run the trace for a month, if your cycle is yearly, you might want to develop an alternative test plan to handle that.  Again, contact me if you need help with this testing!

If you determine something will break during the migration, you can either address it now, or you can address it during the test migration.  Some changes will need to be made before hand, others will only be possible after the upgrade.

Build a test environment

This is the step I see most migrations skip, and as a result, this is where most of the pain comes from.  You need to set up an environment where you can safely test your migration, and any changes you have to make in order to make the migration successful.  Without this testing, you simple upgrade to the new version and deal with problems in a live environment.  That usually leads to down time and upset users in the end.

By setting up an environment that mirrors the backend (SQL), the middleware (any code that touches the database), and the client side programs, you can see exactly how the migration will affect all your moving parts.  During this test period, you can address any problems that come up from the migration.

Make sure you test everything, trust me.  Anything you miss will turn up to give you headaches later!

Spin up the new servers and test again

I do not recommend in-line upgrades for SQL Server, rolling back to a previous version in-line is fraught with peril (Yeah, I’ve been reading too much Game of Thrones.)  If you’ve spun up your test environment with sufficient hardware, you can often migrate your test environment into production, and go live!

By keeping your existing systems online, you keep the option available to rollback, should any issues come up you can’t immediately address.  After you’re running your new servers in production, you need to repeat your tests again, verify everything is truly up and running.  If all your test cases pass, keep your previous servers for some period (usually 30-90 days) as a fallback solution.  But you will need to address how to migrate data from your newer versions to your older versions of SQL.

With proper planning, you’ll address this before rolling to the new servers.

Success!

If you’ve followed all these steps, you’re finished.  You’ve had a successful migration.  Now it’s time to consider what to do with your old production servers.  I’d suggest considering them for Disaster Recovery Servers, or perhaps an additional test environment for your developers.  No matter what, I’m sure you’ll find a good use for the older machines.

As always if you have any questions, let me know.  I’m here to help!