Upgrading to SQL Server 2008 in C#.NET

Draw Denso QR Bar Code in C#.NET Upgrading to SQL Server 2008

Figure 4.10 The Upgrade Advisor wizard lets you inspect a SQL Server 2000 or 2005 instance before upgrading to detect issues requiring attention.

Running the Upgrade Advisor produces a list of tasks that fall into two categories: those that must be completed before an upgrade can be performed, and those for attention once the upgrade is complete. Rather than list all of the possible postupgrade tasks, here are the recommended ones for the core database engine: Compatibility level check Post-upgrade, the compatibility level of the database is left at the pre-upgrade version. Although this is advantageous in minimizing the possibility of applications breaking due to the use of older language syntax that s no longer compatible with SQL Server 2008, some new functionality and performance optimizations won t be available until the new compatibility level is applied. Leaving an upgraded database at a previous compatibility level should be seen as an interim migration aid, with application code updated as soon as possible after installation. You can change the compatibility level of an upgraded database using the sp_dbcmptlevel stored procedure, or via the database properties in Management Studio, as shown in figure 4.11. Max Worker Threads If you re upgrading from SQL Server 2000, the Max Worker Threads setting, covered in chapter 7, is kept at the default value of 255. After the upgrade, change this value to 0, which allows SQL Server to determine the appropriate value based on the number and type of CPUs available to the instance. Statistics update Although SQL Server 2008 can work with statistics generated from earlier versions, I recommend you perform a full statistics update to take advantage of the query optimizer improvements in SQL Server 2008. 13 will discuss statistics in more detail.

Figure 4.11 After you upgrade, the compatibility level of a database should be set to SQL Server 2008, once testing has confirmed the absence of any unexpected problems.

Configuration check Like 2005, SQL Server 2008 complies with the secure by default installation mode, whereby certain features are disabled upon installation. We ll cover these issues in chapter 6. Depending on the installation, certain required features may need to be manually enabled postinstallation. DBCC UPDATEUSAGE Databases upgraded from SQL Server 2000 may report incorrect results when using the sp_spaceused procedure. Running the UPDATEUSAGE command will update the catalog views used by this procedure, thus fixing this inaccuracy. Before looking at the upgrade methods, note that regardless of the method you choose, performing a test upgrade is crucial in identifying possible issues. Test upgrades provide an opportunity to Determine the length of the upgrade process, crucial in planning downtime for the real upgrade. Determine application behavior after the upgrade. This will allow you to set the database compatibility level to SQL Server 2008 and ensure applications work as expected. Performance-test applications on the upgraded database. If performance is poor, a test upgrade provides a chance of investigating the reasons prior to the production upgrade. Last but not least, complex upgrades involving components such as replication are certainly not something you want to be doing the first time in production! So, with these points in mind, let s take a look at the two upgrade methods, starting with the in-place method.

The in-place upgrade method upgrades an instance, and all of its databases, in a single irreversible action. For simple, small database instances, it provides the easiest and quickest upgrade method and has the following additional benefits: Applications connecting to any of the databases don t need modification. The instance name remains the same (if upgrading from a named instance), so no connection strings need to be changed. No additional hardware is required. The instance executables and data are changed in place. Despite these benefits, there are some significant downsides to this method, making it unsuitable for a range of scenarios: All of the instance s databases have to be upgraded at once. There s no option to upgrade only some. If one of the databases (or its applications) needs modification before upgrading, then all of the other instance databases will have to wait before the upgrade can proceed. A failed upgrade, or one that produces unexpected results, can t be rolled back, short of running up new hardware and restoring old backup files, or using a virtualization snapshot rollback process.