When upgrading SQL Server to SQL Server 2005, you must first determine whether your server upgrade will entail taking an existing SQL Server installation, upgrading it by installing SQL Server 2005 on a new machine and then migrating databases to it.
A clean version can be installed with an existing version so you don't have to take the existing SQL Server offline. But that requires funds and provisioning. Your only choice may be upgrading the existing installation.
The Microsoft SQL Server Upgrade Advisor 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 doesn't require you to take anything offline when you use it, so it can be run at any time.
During the installation 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 such problems.
When upgrading an existing installation consider if default settings in an earlier version of SQL Server will successfully convert to SQL Server 2005. Some of those modified defaults may have adverse side effects now that they've changed. 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.
You should also disable the trace flags feature, some of which do not exist in SQL Server 2005, and disable the 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: 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.
This tip originally appeared on SearchSQLServer.com.
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!
