Few people planning to upgrade to SQL Server 2005 will start from absolute scratch. Most will migrate to 2005 either from another database product entirely or from an earlier version of SQL Server. In this article I'll look at issues that may arise specifically when upgrading an earlier version of SQL Server to 2005.
The first thing that needs to be touched on is whether the upgrade is going to be performed by taking an existing SQL Server installation and upgrading it or by installing SQL Server 2005 on a new machine and migrating databases to it. A clean install can avoid some issues that would go with upgrading an existing installation, and you can install it in parallel with an existing version; you don't have to take the existing SQL Server offline. But if you don't have the funds or the provisioning for a new server, then upgrading the existing installation may be your only choice.
In some ways Microsoft has done a good deal of the heavy lifting when it comes to figuring out what you're in for when upgrading. The Microsoft SQL Server Upgrade Advisor (last updated April 2006) examines installed instances of SQL Server 7.0 and 2000 to determine if blatant issues exist that need to be dealt with immediately. The utility is non-destructive and doesn't require you to take anything offline when you use it, so it can be run at any time.
During the installation itself, a component called the System Configuration Checker will scan your current SQL Server version to determine if there are any problems that would prevent the installation. Microsoft has documented parameters that may cause blocking issues, so you can pre-emptively check the system against any such problems if you want to (and it's probably a good idea).
When upgrading an existing installation, consider if the default settings in an earlier version of SQL Server will successfully convert to SQL Server 2005. Some of those modified defaults -- which may have been changed to solve specific issues now properly addressed in 2005 -- may have adverse side effects.
For instance, SQL Server 2005 will have memory buffer pool problems if the max server memory setting is not set to its default 2147483647 (i.e., all available memory). You can always fine-tune this value later if you need to. Another changed default, which can cause things to behave unexpectedly, is the query governor cost limit. SQL Server 2005 uses a different cost-modeling algorithm for queries. Set this to 0 before upgrading whenever possible.
As a side note, if the AUTO_UPDATE_STATISTICS option is turned off in any database to be migrated, re-enable it. Without it SQL Server 2005 can't generate optimal query plans.
On the other hand, before an upgrade you should disable the trace flags feature; some SQL Server 2000 trace flags simply do not exist in 2005. You should also disable duplicate security identifiers (SIDs), as they are unsupported in 2005.
Extended stored procedures that were previously registered without the full path for the DLL name may not work after you upgrade to SQL Server 2005. Run the sp_dropextendedproc and sp_addextendedproc stored procedures to drop and add back extended stored procedures if needed.
Another possible upgrade issue that is more related to databases in general than SQL Server itself deserves mention here: SQL Server 2005 uses slightly more data per column for some data types; text, ntext and image data types require 40 more bytes per column. For that reason, any migrated databases that use these data types should be allowed to grow automatically if they aren't already allowed to do so. The same goes for the tempdb database: Set it up to grow automatically during the course of the upgrade. Its own settings may be preserved during the upgrade, which is why it's worth looking into before starting.
About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
