Why Upgrading to SQL Server 2008 R2 Is Not a Given

You might be running a version of SQL Server that is perfectly suited to your company. Here's a quick look at the pros and cons of upgrading now.

• By Joshua Jones
• 03/15/2010

With SQL Server 2008 R2, Microsoft has upped the ante -- sort of. While there are a few great new features (and improvements to existing ones), upgrading to this latest release isn't a no-brainer, much like many felt the upgrade to SQL Server 2005 was.

Let's cut through the noise and talk about the real reasons to upgrade, as well as the look at reasons why it might not make sense. To make it easy, let's assume you're running one of these versions:

From SQL Server 2000: If you're still running SQL Server 2000, you better be at least running SP4. There were a ton of bug fixes over the life of SQL Server 2000. For stability, make sure you have the latest service pack. And if you haven't seriously considered upgrading yet, you might as well go straight for SQL Server 2008 R2. Of course, this means doing a thorough evaluation of both your hardware platform (including storage) as well as your applications that rely on SQL Server.

While many DBAs feel that "progressing" through the SQL Server versions (upgrading 2000 to 2005, etc.) is the safer route, SQL Server 2008 has been through quite a few paces at this point. The R2 release doesn't feature too many "rewrites" as much as it has quite a few new features that you can choose to use (or not). Either way, you will get a faster database engine, a more robust set of management tools, and quite a few development options that don't exist in SQL Server 2000.

While for the most part any SQL Server 2000 based application will run easily on a SQL Server 2008 server, there are a few "gotchas" in terms of deprecated features. For example, the old style outer joins "*=" or "=*" won't work. Plus -- and this is a biggie -- Data Transformation Service packages will need some very special attention. 

Make sure to use the Upgrade Advisor to find as many problems before you actually upgrade; I highly recommend testing your upgrade process in an isolated environment thoroughly. There isn't really a rollback process, other than having a recent backup on hand, so don't take this procedure lightly.

Finally, SQL Server 2000 is out of support; if you find any new major bugs in the product, don't expect much in the way of help from Microsoft.

Running SQL Server 2005: If you're already running SQL Server 2005, you've got a pretty good thing going. The product is solid, and features quite a few of the better features we've come to expect from an RDBMS. However, this product is nearing end of life as well. Those shops that prefer to stay on the front edge of technology will want to already be evaluating SQL Server 2008 R2 (and you might as well skip SQL Server 2008 at this point).

Not to sound like a broken record, but if you are considering SQL Server 2008 R2, make sure to run the Upgrade Advisor, and as always, test your upgrade process. If you're comfortable with SQL Server 2005, it probably only makes sense to upgrade now if you need functionality in SQL Server 2008 R2. Again, at this point, skipping SQL Server 2008 is a pretty reasonable option, since there isn't that much of a difference in the base functionality, and SQL Server 2008 R2 will be supported longer.

Running SQL Server 2008: If you're already running the original release (that is, not R2), you really are pretty much set. You should only consider upgrading to R2 if there is a specific functionality that you really need. R2 does have better management tools for larger environments, as well as the new editions for heavy-hitting systems. Plus new interoperability, such as the PowerPivot and StreamInsight features, will make some shops look to R2 sooner than later. If you do upgrade, follow the same process you would for any major RDBMS change; use the Upgrade Advisor, and test the process.

Overall, whether or not you upgrade to SQL Server 2008 R2 will be based on budget, features and support. If you're current environment is humming along nicely on any version, take your time and thoroughly evaluate features, cost and the total impact an upgrade would have on your business.