Chapter 3: Upgrading SQL Server 2005 Best Practices

Chapter 2 covers performing a new installation of SQL Server 2005. In this chapter, we discuss upgrading SQL Server from a previous edition. First, we cover reasons for upgrading to SQL Server 2005. We then discuss the pros and cons of various upgrade strategies. You learn about the various tools available to help mitigate risk during the upgrade process. We then explain how to use the new configuration tool. To wrap up, we discuss SQL Server 2005 behavior changes and discontinued features that you need to know about before upgrading. By the end of the chapter, you will have everything you need to perform a successful upgrade to SQL Server 2005.

Why Upgrade to SQL Server 2005

With the release of SQL Server 2005, Microsoft introduced numerous advancements in the areas of scalability, reliability, availability, and security.

As you'll see throughout this book, significant enhancements have been introduced throughout the product. During the development cycle, there were four pillars of focus for the product: developer productivity, security, business intelligence, and enterprise data management. The enterprise data management pillar had the following goals:

• Availability and security advancements. New and enhanced high-availability options include database mirroring, server clustering, peer-to-peer replication, fine-grained online repairs, dynamic management views, backup and restore, and many other improvements in the areas of online operations. These features ensure that SQL Server 2005 is available for your enterprise. Also, the SQL Server 2005 security initiative brought about security enhancements such as support for data encryption, password-policy enforcement, fine-grain security control, user-schema separation, and improved auditing capabilities.

• Continued focus on manageability. SQL Server Management Studio is the new integrated management toolset for relational and business intelligence technologies. This tool set is based on Visual Studio 2005 Integrated Development Environment (IDE), which brings about enhanced collaboration capabilities through the use of projects for storing code such as stored procedures. SQL Server 2005 also enhanced its ability to self-optimize and to load balance its workload across processors. The introduction of dynamic management view (DMV) along with support for dedicated admin connection (DAC) will ensure that administrators can get the information that they need to manage their environment under any conditions.

• Performance and scalability. Performance and scalability enhancements have been introduced through technologies such as data partitioning, snapshot isolation, and support for dynamic AWE memory. Enhancements in the area of buffer-pool management will also ensure that the right data is in the data cache. SQLOS is the improved abstraction layer that provides SQL Server 2005 the ability to scale on any technology platform. This capability empowers you with the technical and business flexibility to solve unique problems in a way that will best meet your needs.

Note

In Appendix A, you'll find the lab report from Raymond James Financial, a client of Jean-Claude Armand, and their experiences with upgrading to SQL Server 2005. Raymond James Financial maintains a 3TB SQL 2000 Data Warehouse deployed on HP Itanium 2-based Superdome running on Windows Server 2003 Datacenter. During the fall of 2005, Raymond James wanted to evaluate what benefits SQL Server 2005 could bring to their enterprise.

Risk Mitigation - The Microsoft Contribution

As with all previous versions of SQL Server, the SQL team took extraordinary steps to ensure that the quality of SQL Server 2005 was as high grade as possible. The specific steps of the software engineering cycle are beyond the scope of this book, but we will highlight a few points considered public knowledge about the daily build process. We will also touch on a few points about how Trustworthy Computing improves this process. Ever since the January 2002 memo from Bill Gates on Trustworthy Computing, security procedures have been incorporated into every software development process.

Today, a daily process produces a 32-bit, X64, and Itanium versions of SQL Server 2005 code (called a build) that have gone through a battery of tests. This process is utilized for both the development of new releases and the development of service packs for SQL Server 2005. These tests are a convergence of in-house built tests, customer-captured workloads, and Trustworthy Computing processes. Microsoft Research is a group of 700 computer-science researchers who work on bringing innovations to Microsoft's products. For example, on the SQL Server 2005 release, these innovations were in the areas of data-mining algorithms and indexed-views performance enhancements. In the areas of software development, the MS Research team is an essential contributor to the software engineering and testing processes. They improve the test harness with enhancements in several areas, including threat modeling, testing efficiencies, and penetration analysis.

In addition, there are approximately 275 customer-captured workloads that are also part of the software testing harness. These workloads are acquired through an assortment of programs such as the Customer Playback program and various lab engagements including Yukon-compatibility labs.

The daily builds are tested against this gathered information, and out of this process come performance metrics, security metrics, and bugs. Bugs are subsequently filed, assigned, prioritized, and tracked until resolution. Once a bug has been fixed, its code goes through security testing as part of the software-engineering process. This happens before the code gets checked back into the software tree for the next testing cycle.

Independent Software Vendors (ISV) & SQL Community Contributions

Starting with SQL Server 2005, the concept of community technology preview (CTP) was adopted. The December 2004 CTP (2004) was the first of seven such releases. The decision to adopt this snapshot in time of code (or build) resulted in over 326,000 CTP downloads, providing unprecedented access to updated code to both ISV and SQL community testing. This type of access to beta code was leveraged as a means of identifying additional bugs, conducting additional testing of software fixes, and driving additional improvements based on community feedback. Paul Flessner was quoted by Microsoft Watch as saying that this will be his process of choice as part of his SQL Server Reengineering Initiative. This initiative is aiming for a 24 to 36 month release cycle going forward, putting the next release of SQL Server in the 2008 timeframe.

In addition, Microsoft migrated over 90 internal systems, including their own financial SAP (July 15, 2004), and 1000 customer databases were also upgraded to SQL Server 2005.