It's funny. Most organizations I work with would prefer to have all their infrastructure and platform components on a single software release: one version of Windows for domain controllers or one version of Exchange Server for messaging, for example. When it comes to Microsoft SQL Server, however, "one version to rule them all" isn't a rallying cry in most companies.
I have clients whose SQL Server software looks as if they're trying to build a collection, with SQL Server 2000, 2005, 2008 and 2008 R2 all running in the same data center. Sometimes that's because of financial concerns: Why pay to upgrade software that's working fine? Other times compatibility is the problem, when the applications working with and storing data in SQL Server don't support a different version.
Eventually, you'll need a SQL Server upgrade. Running SQL Server 2000, for example -- a version that is years out of its support lifecycle -- is probably a bit risky, since there's nobody around to help if problems arise. When the time does come to upgrade, here are three tips for making the process smoother:
Test it. It's incredibly easy to stand up a new SQL Server instance (even using free trial software) on a new server (or virtual machine). Use SQL Server's own management tools to copy databases from the old production server to the new test machine, and then check if everything works OK. With virtualization, it's easy to re-create your production environment in a self-contained lab. You can even use SQL Profiler to capture production traffic and "replay it" against the test server to see if things work or break when running on a new version of SQL Server.
Use compatibility options. There are always a few minor changes from version to version in SQL Server, and these often involve tweaks to the query language. Databases all support a "compatibility version" setting that can make the database emulate the behavior of an older version of SQL Server. You'll still get the performance and manageability improvements that come with a new version of SQL Server, but in many cases you'll be able to bypass any "breaking changes" introduced in the query language by that new version. But don't assume that such a setting will be needed; test applications on the latest compatibility level and go with an older version only if necessary. Doing so will help ensure that you get the best performance and security.
Upgrade in place. I'm not normally a fan of in-place upgrades; I like to stand up a whole new server and copy databases to it. But in the case of SQL Server, I don't mind so much. Provided the existing server hardware can run the new version of SQL Server, in-place upgrades (once you've tested the new version in a lab, of course) offer an easy and fairly risk-free option (after making backups of your data, of course).
That said, SQL Server tends to want to run on a version of Windows that's more or less contemporaneous, so an in-place upgrade to SQL Server 2008 from SQL Server 2000 running on Windows 2000 Server isn't possible. If your proposed new version of SQL Server can't run on the old server's version of Windows, it's time for a whole new server (or virtual machine).
With some testing and common sense, a SQL Server upgrade is often an easy, straightforward task. Dive in there, test it out and make it happen.
About the author:
Don Jones is a senior partner and principal technologist at strategic consulting firm Concentrated Technology. He can be reached through the website www.ConcentratedTech.com.