Server upgrades require significant planning and testing. Most of the time everything goes just fine, or only small issues are found, but there are still many things that can go wrong. Let's take a look at several mistakes companies make when upgrading to the next version, as well as how to mitigate risks.
Insufficient testing. One of the most common reasons SQL Server upgrades fail is insufficient testing. You need to exhaustively test not only each application that uses the database, but all other ways of executing database code -- scheduled jobs, SQL Server Integration Services packages, utility and helper stored procedures, replication. Pay special attention when upgrading from SQL Server 2000 to a later version.
There are several syntax-breaking changes related to the need to support SQL Server 2005 and later Transact-SQL (T-SQL) features. For example, you need to add the WITH keyword when using the NOLOCK hint combined with an index hint. One of the ways to test for broken syntax is to recompile all T-SQL code by running the ALTER command for all stored procedures. But doing that still does not catch breaking syntax in dynamic SQL, so there is no substitution for executing all database code.
Not paying attention to the upgrade white paper. In addition to the upgrade section in SQL Server Books Online, Microsoft releases an upgrade white paper with each SQL Server version. While the papers tend to be boring, and large sections of them may not apply to you, take the time to read them all and take notes about what's important for you.
Take note of discontinued and obsolete features and make sure you are not using them. If you notice a feature you are using is obsolete and might be removed in a future version, you're better off changing your code to stop using it. If you don't have the time before the upgrade, keep in mind that the feature might stop working in the version that follows your upgrade version; so be prepared to make the changes in the next two years or so.
Assuming the hardware can run the new version. Typically, each new version of SQL Server is accompanied by presentations and articles from Microsoft, which typically discuss how much better and more efficient the new version will be. Database administrators (DBAs) sometimes assume that the new version will run just fine on the existing hardware. In most cases this is true, but don't assume that it universally applies to all servers.
For example, SQL Server 2005 has a larger footprint than SQL Server 2000, and DBAs have reported servers running more slowly than in the old version. Microsoft has admitted that in very small number of cases, the performance in 2005 was much worse. The only way to find out is to perform benchmarks -- you could set up the same machine with the different server versions installed and compare the performance of your application.
Another alternative is to use virtualization to make sure you are comparing apples to apples. In some cases, a move to the new version might help you work the company's politics and get an approval for new hardware, especially if the old one has been running for a while.
Not changing the compatibility version. Setting the compatibility level allows you to run a database in a way in which the syntax is compatible with earlier version. When you restore a database from an earlier version to a later version of SQL Server, the database keeps its compatibility version, essentially allowing you to run syntax that is no longer valid. This is designed to help with your SQL Server upgrade if you do not have the time to make syntax modifications.
If you leave the database in the earlier-version compatibility mode, you might mistakenly think that your upgrade worked because everything runs just fine when you test it. But if you later change the setting to match the version, you might encounter T-SQL errors. So, remember to change the compatibility setting for each database (including the system databases) and only set it back if you need to run in the earlier mode.
Not having a rollback plan. No matter how big or small your upgrade is, make sure you can easily roll back SQL Server when something goes wrong. It's easy to say "make database backups before you start the upgrade," but restoring many databases and redoing all SQL Server setups may not be an option for everyone (especially if you use replication).
Shops that are 24/7 lose money and customer and user confidence when their system is down longer than expected. In these environments, the upgrade needs to happen very quickly, and you cannot afford an upgrade gone wrong. One way to mitigate risks is to plan on going live on new hardware and keep the old install and hardware in place. If your SQL Server upgrade fails or your post-upgrade testing shows that something major is not working, you can go back to the old setup and delay the upgrade until you resolve the issue.
About the author:
Roman Rehak is principal database architect at MyWebGrocer in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. He contributes to Visual Studio Magazine, SQL Server Magazine and other publications.