Getting Ready to Install

If you look again at the opening screen for SQL Server 2005 setup, you'll see that you have two other pre-installation choices in addition to the hardware and software requirements document. First, you can look at release notes that contain late-breaking or other important information to supplement the Books Online documentation. You should read this entire document before installing SQL Server 2005. In addition to late-breaking information, the release notes might contain information about bugs in the product or the documentation that may be fixed in a subsequent release. For example, the release notes for one of the CTP releases reported that the documentation for the dynamic management view sys.dm_db_session_space_usage was incorrect and reported the wrong names for the last two columns of output. They also report a product bug: the partition_number column in the sys.dm_db_index_operational_stats dynamic management function returns incorrect values.

SQL Server 2005 Upgrade Advisor

The final pre-installation option on the opening setup screen is useful only if you have existing SQL Server 7.0 or SQL Server 2000 databases that you will be upgrading to SQL Server 2005. The Upgrade Advisor is a tool for database developers and administrators to analyze SQL Server 7.0 and SQL Server 2000 servers before upgrading to SQL Server 2005. The Upgrade Advisor allows you to analyze your existing database components. It then provides reports that identify changes you must make due to deprecated features and new behaviors in SQL Server 2005.

The link on the opening screen installs the Upgrade Advisor, which you can then run as often as you need it. (An alternative way to install it is by launching SQLUASetup.msi in the \Servers\Redist\Upgrade Advisor subdirectory on the installation CD or from the downloaded setup files.) Each time you run the Upgrade Advisor, you can analyze one or more databases in a single instance of SQL Server 7.0 or SQL Server 2000. The initial dialog box of the Upgrade Advisor is shown in Figure 1-5. You indicate the name of the server you want to analyze and select which components to analyze: database server, analysis services, notification services, reporting services, or data transformation services.

Figure 1-5. Choosing which components to analyze prior to upgrading

The Upgrade Advisor contains rules for items that must be fixed before the upgrade, rules for items that must be fixed after the upgrade, and rules to detect and report items that can be fixed at any time. Items that must be fixed before the upgrade are deemed "upgrade blockers"they will prevent the setup process from successfully installing SQL Server 2005. There are actually only five upgrade blockers for the SQL Server engine:

• There is a database with an ID value of 32767. SQL Server 2005 reserves this database ID value for a special hidden database called the resource database, which I'll cover in Chapter 4. A database with this ID must be detached (or dropped) before upgrading to SQL Server 2005. You can reattach the database either before or after doing the upgrade, but if you do it before, you'll need to verify that it didn't get the same database ID value.

• Duplicate Security Identifiers (SIDs) exist. If your SQL Server 7.0 or SQL Server 2000 database has two login IDs with the same SID, an upgrade to SQL Server 2005 will fail. You must remove all but one of the duplicates.

• Login names match a fixed server role. If a SQL Server 7.0 or SQL Server 2000 login name is the same as one of the fixed server roles, an upgrade to SQL Server 2005 will fail. You must change the login name.

• A database has a user name "sys". All SQL Server 2005 databases include a schema and user "sys" that is used for special purposes. If your database already has a regular user "sys", you must change the name before upgrading. If the user is not renamed, the database will be in a suspect state after the upgrade process and will be unavailable until the database is brought online.

• A duplicate index name for a table or view exists. If two indexes with the same name exist on the same object (table or view), the upgrade will fail. You must use sp_rename to rename one of the indexes.