| Obviously, the Upgrade Advisor is useful only if you have existing SQL Server databases. But if you have existing databases, you should first consider whether it is better to upgrade or to install a new instance for SQL Server 2005 and then migrate the existing data to the new SQL Server. Each choice has its advantages. With either choice, you'll run the same Setup.exe application; during the setup process, you will indicate whether you want to install a new SQL Server or upgrade an existing SQL Server. Migrating To migrate a database from an existing SQL Server instance (called the source) to SQL Server 2005 (the destination), you'll need to choose the option to install a new SQL Server, and as a follow-up, you will move your objects and data to the new server. All the following methods migrate one database at a time from the source instance to the destination instance, so in general, I'll be talking about migrating a single database rather than migrating an instance. To migrate an instance, each database must be migrated. The benefits of migrating existing databases to SQL Server 2005 include the following: The migration option provides more granular control over the upgrade process. Having the new and old instances side by side helps with testing and verification. The source instance of SQL Server stays online during the entire setup process. You have greater flexibility in case of a failure during the setup operation.
The drawbacks of the migrate method include the following: Extra hardware will probably be required to support two side-by-side installations. You might have to modify your application code to point to the destination installation because it will have a new instance name. You can't migrate from a default instance to a default instance on the same machine because only one instance on any machine is considered the default. Even if the source database is no longer needed and is removed, there is no way to convert a named instance to a default instance.
For moving or copying your data to the second SQL Server instance, several tools are available. Detach and attach SQL Server allows you to detach a database from one SQL Server instance and attach it to another instance. If you want to keep the data available on the source instance, you can copy the database files after detaching the database and then attach the files to both the source and the destination SQL Server 2005 instances. Detach and attach are available through the graphical tools in SQL Server 2000 and later. Backup and restore The source databases can be backed up from the original SQL Server instance and restored to the new instance. No file copy operation is needed. Copy Database Wizard This wizard for copying databases is available through SQL Server Integration Services (SSIS), which is the successor to Data Transformation Services (DTS). The wizard allows you to choose which objects from the source database to copy, so you can migrate only a subset of your database if you want. Manual migration If you want the destination database to be based on the original version of the source database, perhaps before you make some schema changes, you can use a script that creates all the objects on the destination SQL Server 2005 database. You can copy data using bcp from files containing the original data (assuming you have these available).
Upgrading Upgrading your source databases means that the setup program converts your database to SQL Server 2005 in place. The benefits of this direct upgrade include the following: For small systems, upgrading is faster and easier than migrating. No additional hardware is required to upgrade, as long at the existing hardware meets the requirements for SQL Server 2005. Your applications do not need any modifications regarding connections and can continue pointing to the same server name.
The drawbacks include the following: You have less control over what objects and data are included in the SQL Server database and which are not. The SQL Server instance will be offline during part of the upgrade process. In-place upgrade is not the recommended approach for all SQL Server components. For example, if you have Analysis Services cubes, it is recommended that they be migrated instead of upgraded to take best advantage of all the new SQL Server capabilities.
Upgrade Internals If you choose to upgrade instead of migrate, keep in mind that it is an in-place operation, and one SQL Server instance is converted to a new version. The bits of the executable are replaced, and the data files are modified. During much of this process, the SQL Server engine will be unavailable, and once the process passes a certain point, the decision to upgrade is irrevocable and you cannot go back to the previous version. The upgrade process goes through the following steps: - Verify prerequisites. Your original instance is completely available during this step. If you have run the Upgrade Advisor and followed its recommendations, you shouldn't have any missing prerequisites.
- Check for upgrade blockers. Again, your original instance is completely available during this step. If you have any upgrade blockers, the install process will fail at this point, but if you followed all the recommendations of the Upgrade Advisor, you shouldn't have any at this point.
- Install SQL Server 2005 binaries in a new directory location. The default location for the server-side executables is C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL. The default location for the client-side executables is C:\Program Files\Microsoft SQL Server\90.
- Stop the execution of the previous version. Obviously, the previous instance is not available at this point.
- Point the service to the new executable. This is an in-place upgrade, so the service name for the SQL server will stay the same. The registry is updated to map the location of the new executables to the existing service name.
- Start the new SQL Server service in single-user mode. Once the SQL Server service starts under the new name, it is considered to be at the point of no return. Even if the installation is canceled at this point, you cannot cleanly revert to your old version under the original service name.
- Attach the resource database. After the new service starts running, a new hidden system database is attached to the instance. I'll explain more about this special resource database in Chapter 4.
- Stop and restart SQL Server service. Attaching the resource database is the only thing that needs to be done while in single-user mode. Once that's done, the SQL Server service can be stopped and restarted in multi-user mode.
- Start updating all databases. The new SQL server is partially available at this point. Each database becomes available as it is upgraded. If the upgrade is from SQL Server 2000 to SQL Server 2005, this database update is a relatively fast operation. The primary change made to each database is the introduction of new metadata structures (as discussed in chapters 4 and 6). If you're upgrading from SQL Server 7.0 to SQL Server 2005, the changes are a little more invasive because they entail changes to the physical database files. SQL Server 2000 introduced two new kinds of allocation structures that are stored in the first few pages of each file (as well as every 512-K page thereafter at fixed locations), and these special pages are needed in SQL Server 2005 data files. If these specific pages are used in your SQL Server 7.0 data files for other purposes, such as storing table or index data, that data will have to be moved. I'll talk about these special allocation structures in Chapter 4.
- Execute replication and SQL Agent upgrade scripts. Depending on the tasks you defined in your original SQL server, additional scripts might have to be run to prepare these tasks to run under SQL Server 2005. In particular, execution and SQL Agent tasks will need a bit of upgrading.
- Uninstall old binaries. The original executable and supporting files are no longer needed, so they are removed as a final step in the upgrade process. Your new instance of SQL Server 2005 is now fully available.
Post-Upgrade Operations As mentioned earlier, if you choose to upgrade rather than migrate, you will not be able to test the new version while still running the previous version. Upgrading is basically an all-or-nothing operation. Once you upgrade, all your data management applications and operations will run on SQL Server 2005 and you will not be able to revert to the previous version unless you completely reinstall SQL Server 2000 and restore all your old data from backups. There are several tasks you might need to perform immediately after upgrading to SQL Server 2005. Some tasks suggested by the Upgrade Advisor might be peripheral to the relational database enginetherefore, I will not cover them here. (These tasks might include migrating DTS packages to SSIS, reconfiguring log shipping, and rebuilding Analysis Services cubes.) The final screen you'll see when installing SQL Server 2005 (Figure 1-6) suggests that you run the Surface Area Configuration Tool, which gives you the choice of configuring your services and connections or your SQL Server 2005 features. When using the Surface Area Configuration Tool to configure your services and connections, you can control which SQL Serverrelated services will start automatically when your machine starts and which will have to be started manually. You should also look at the configuration for features; you might be surprised which features are disabled by default. For security and safety reasons, some of the most widely advertised features of SQL Server 2005 will not be available unless you open the Surface Area Configuration Tool and enable them. These include CLR integration and Native XML Web Services. Figure 1-7 shows the screen from the Surface Area Configuration Tool that lists all the features that must be enabled before they can be used. Figure 1-6. The final setup screen showing the option to open the Surface Area Configuration Tool 
Figure 1-7. Using the Surface Area Configuration Tool to disable unused or unneeded features 
The most important post-installation tasks related to the core database engine include the following: Update statistics. The optimizer is constantly being improved with each SQL Server version and each service pack, and so are the techniques by which SQL Server accumulates and maintains the statistics that the optimizer bases its decisions on. Although the SQL Server 2005 optimizer can use the statistics gathered by earlier versions, it comes up with the best query plans when the statistics have been gathered using the most up-to-date algorithms. I'll discuss statistics in Inside Microsoft SQL Server 2005: Query Tuning and Optimization. Change database COMPATIBLITY level. The database's compatibility level controls whether new reserved words will be recognized in unquoted identifier names and whether certain syntax constructs will be recognized. In an upgraded database, the database compatibility level is set to the level of the source server so that object names will continue to be recognized and the previously valid syntax will continue to work. However, if a database stays in an earlier compatibility level, some of the new functionality of the new SQL Server version will be unavailable. The new reserved words for SQL Server 2005 are PIVOT, UNPIVOT, REVERT, and TABLESAMPLE. If you have any objects using these words as object names, you should stay in your older compatibility level (70 or 80) until you have changed the names and modified the code that refers to these names. However, until you change the compatibility level to 90 (for SQL Server 2005), you will not be able to use any of these new reserved words for the purpose they were intended for in SQL Server 2005. Among the most drastic behavior changes when you move from compatibility level 70 or 80 to 90 are that you can no longer use the operators *= and =* to specify outer joins and that you must use the OUTER JOIN keywords. (Inner and outer joins will be covered in detail in another volume in this series, Inside Microsoft SQL Server 2005: Query Tuning and Optimization.) Another major change, if you have used optimizer hints in any of your code, is that the keyword WITH is now required in most cases with the use of table hints. In previous versions of SQL Server, WITH was optional. Hints will be discussed in several places in this series of books, including Chapter 8 of this volume, where I'll discuss the hints that control locking and isolation. More than two dozen other new behaviors are introduced with compatibility level 90. See the entry for sp_dbcmptlevel in Books Online for the full list.
After upgrading to SQL Server 2005, you'll want to make sure that the new SQL Server service starts with no errors and start testing your applications. You'll want to monitor your system activity and compare the resource usage under the new version with baselines you recorded when running your applications under the previous version. |
