|Upgrading to SQL Server 2005
||[Feb. 3rd, 2006|01:58 pm]
The Upgrade Process
Before we look at the upgrade considerations for each major SQL Server 2005 component, let’s review a general road map that can help your upgrade team from planning to production. You can break the upgrade process into four phases: planning and research, testing and process validation, the production upgrade, and post-upgrade considerations.
Planning and Research
The upgrade process begins now. Your developers, DBAs, and application architects have plenty of motivation to start the educational and review process. Their training, experience, and research will drive much of the planning process. Because they intimately know your applications’ profiles, they’ll be able to provide valuable insight into the upgrade details.
The planning phase should move from identifying the databases targeted for the upgrade to determining the changes and processes the upgrade will require. The Upgrade Advisor will help your team determine where to focus its efforts and what to expect. A major decision in the preliminary work is to decide whether to perform an in-place upgrade or a side-by-side migration. You’ll base this decision on a combination of factors, including the platform upgrade path available, enhancements you want to implement during the upgrade, your application architecture, and hardware requirements.
Generally, you should conduct the following planning activities:
• Learn about SQL Server 2005 upgrade tools. Understand the platform’s highlights, drill into the functionality, and test the upgrade and migration tools.
• Assess your application features. Evaluate and determine which applications, servers, and databases will benefit most from the upgrade.
• Select your upgrade path. Use the Upgrade Advisor to help you decide which upgrade path, in-place upgrade or side-by-side migration, will work best for your environment.
• Identify the prerequisites for the upgrade process. Work with your team to research compatibility and functionality changes you’ll need to make to ensure a successful upgrade and to take advantage of the release’s new and enhanced features. The Upgrade Advisor will provide valuable help.
• Set specific planning and research milestones. Determine your upgrade path and steps, set up an initial test plan, and make sure you have a risk mitigation and recovery plan in place.
Testing and Process Validation
Nothing can replace testing. Even if you plan to upgrade only the SQL Server database engine without changing your application, testing will help identify any backward-compatibility problems and behavioral changes from previous SQL Server releases that the Upgrade Advisor didn't detect. Furthermore, testing will help validate data and organize the upgrade process. This phase entails establishing a test environment and composing validation scripts and application functions to confirm a successful upgrade.
Your final plan should include a backup of your SQL Server 2000 or 7.0 databases and a tested recovery strategy. Also be sure to identify all application references (such as connection strings, package references, and reports) to the upgraded SQL Server components. Here is where an in-place upgrade has advantages: When you upgrade an earlier SQL Server release in-place through the install upgrade process, all existing application connections remain the same because the server and server instance do not change.
Consider these tasks in the testing and validation phase:
• Prepare your test environment. Side-by-side migrations require a separate test SQL Server 2005 installation. In-place upgrades require a test machine running SQL Server 2000 or 7.0 and target database copies. Hardware comparable to your production setup will allow production volume testing.
• Set a pre-upgrade baseline. This baseline will help you evaluate your system post-upgrade and determine any behavioral changes, letting you simulate a typical workload after your upgrade. The baseline will also help you confirm functionality and document performance improvements or changes. To set up the baseline, you can use familiar tools such as SQL Server Profiler, application load testing tools, Performance Monitor counters, and Showplan statistics.
• Develop a test plan. Set up a generalized testing script or test procedures for the following areas: data validation, data processing, stress and workload, client/server performance, and application functionality.
• Develop a recovery plan. Develop upgrade rollback procedures in case of an upgrade interruption. The recovery plan should include running a DBCC consistency check on the pre-upgrade databases before backup as well as a full restore of the database to validate the backup reliability. After the upgrade, you should also perform a consistency check and a backup with validation. Make sure you test your rollback procedures.
• Create application-modification procedures. Your test environment should include the full application tier so that you can confirm application changes work as expected. These application-modification procedures should include a catalog of effected users. Such procedures also allow complete documentation of your application changes so that they can be applied successfully during the production cutover.
• Perform an upgrade test run. A final test run of the upgrade will confirm that the process and procedures work as you expect. You can use the Upgrade Advisor in this step. Run the tool after you apply your pre-upgrade changes to validate that you’ve addressed all the problem areas the tool identified earlier.
The Production Upgrade
The Upgrade Advisor and Setup wizard should help you confidently through the planning and testing steps, positioning you for a successful production upgrade. You can use some of the testing steps you developed for pre-upgrade use (such as record counts and validation scripts) in validating the upgrade upon completion. You'll generally follow the subsequent steps for your production upgrade, depending on the SQL Server component you’re upgrading:
Back up your systems (applications and databases). Perform a consistency check if applicable, back up database and related systems, then validate the backup.
Perform pre-upgrade tasks. Notify your users, then disable user interface components, pausing all data processing, data entry, and data changes. Make the necessary pre-upgrade changes you identified in testing. Re-execute the Upgrade Advisor to validate the pre-upgrade state, and perform an optional secondary backup of your systems before the upgrade.
Perform primary SQL Server back-end platform upgrade tasks. Run SQL Server 2005 for side-by-side migration. Install the .NET Framework and SQL Native Client. In the Setup wizard, specify the same instance as your legacy installation. Then, specify the same components as your legacy instance (e.g., Database Services, Analysis Services, Reporting Services). Once setup is complete, perform tasks for special upgrade considerations (e.g., repopulation of full-text indexes, special handling of clusters and log shipping). Next, make any post-upgrade platform changes, such as scripts or tasks required to support the back-end functionality on the new SQL Server 2005 platform. And last, run platform data and functionality validation testing scripts to confirm the success of your SQL Server 2005 upgrade.
Make primary application changes. Make application functionality changes to support new back-end structures, and make any required database reference changes in application connection strings and other connection references. Test application functionality, including data processing, front-end and report usage, and other application components based on the test procedures you created in the planning phase.
Perform post-upgrade steps. For the database engine, the upgrade automatically sets the compatibility mode to 8.0; but you might want to set the compatibility mode to 9.0 to take advantage of the release's new features. For side-by-side migrations, stop the former platform services (or set the database to read-only) to prevent unknown data changes. For the relational data, run DBCC consistency checks to validate the data. Back up SQL Server 2005 structures and data with backup validation, and back up application systems and files. Then, re-enable processing and the application user interface, notifying your users that the upgrade is complete.
Following your successful upgrade, you need to address just a few areas to finalize the process and prepare for short-term changes that will let you leverage the new SQL Server 2005 features and functionality. First, you should perform some DBA tasks involving high availability, disaster-recovery planning, and recurring maintenance. These tasks include re-evaluating weekly and monthly procedures for the new platform and testing disaster recovery. If you perform a side-by-side installation, certain SQL Server 2005 features are switched off by default to reduce the surface area. You can switch on the features by using sp_configure or the Surface Area Configuration (SAC) utility.
Another valuable phase in the post-migration process is identifying new SQL Server 2005 features that your applications can leverage by asking the following questions:
• Will table partitioning give your database expanded performance and table management?
• Will the XML features solidify your .NET application architecture, or will the new database mirroring feature give you the high availability that your applications demand?
• For Analysis Services, can you use the many-to-many (M:N) relationships that your BI requirements specify, or will your users require the Reporting Services ad hoc query tool, Report Builder?
• Are your data processing needs strapped by bottlenecks and volume, and do they need the performance benefits that SSIS provides?
• Would you like to integrate notifications into your line of business applications and leverage the ability of Notification Services to natively communicate with phones, pagers, PDAs, and email?
• Does your development team need a better way to develop, test, and deploy applications by using the new Business Intelligence Studio and its ability to integrate with source control?
These features are only a few of the compelling new functions that SQL Server 2005 offers for increasing your database systems’ productivity, reliability, and capability.
Top of page
Component Upgrade Considerations
Given the breadth of the SQL Server 2005 platform, writing an upgrade guide that applies across the board is no easy task. So, let’s take a closer look at the upgrade considerations for the major SQL Server 2005 components. (For a comprehensive and detailed discussion of upgrade considerations, see SQL Server 2005 Books Online and the white papers, Webcasts, and other resources listed in “SQL Server 2005 Resources,”.
Upgrading to the SQL Server 2005 Database Engine
The database engine upgrade is the easiest upgrade and will result in immediate return on investment in the areas of management, performance, and high availability. Again, the two main options for the database engine upgrade are side-by-side migration (in which you install the SQL Server 2005 engine as a secondary instance on the same server as your SQL Server 2000 or 7.0 server or on a completely separate server) and an in-place upgrade (in which you upgrade an instance of SQL Server 2000 or 7.0 through the install process and databases and other objects are upgraded “in place”).
With a side-by-side migration, the most common upgrade path is a simple database detach and re-attach on the SQL Server 2005 instance or a database backup and restore from the older version to the new version. If you keep an up-to-date version of your metadata scripts, you can also create the objects on the SQL Server 2005 server and use BCP to export and import your data. The other option is an in-place upgrade, in which you upgrade and adapt the databases, settings, and extended features to the SQL Server 2005 engine during the install process. When you run the setup process on a server that has a SQL Server 2000 or 7.0 instance, you’ll see an option to upgrade the selected instance to SQL Server 2005.
Note that for the database engine upgrade, all your existing Microsoft Data Access Components (MDAC) and ADO.NET applications will continue to function as when they were running against SQL Server 2000 or 7.0. In fact, SQL Server 2005 doesn't come with a newer release of MDAC. But new to the platform is the SQL Native Client, combining an updated SQL ODBC driver and SQL OLEDB provider with network libraries in a single DLL. The SQL Native Client lets you leverage SQL Server 2005's new client-access features, such as Multiple Active Result Sets (MARS), the XML data type, and user-defined types (UDTs). SQL Server comes with tight integration with the .NET Framework 2.0, which includes the latest ADO.NET version.
The in-place server upgrade will be the easiest. Although this approach requires a more thorough fallback plan and testing, you reap the rewards through seamless connectivity. By performing an in-place upgrade, logins and users remain in-sync, database connections remain the same for applications, and SQL Agent jobs and other functionality is concurrently upgraded during the installation. Note that several features, such as log shipping, replication, and cluster environments, have special upgrade considerations.
As noted earlier, for the database engine, the upgrade sets the compatibility mode to 8.0. You might benefit from leaving this setting at 8.0 under certain circumstances, such as for T-SQL references that are no longer supported in SQL Server 2005. I’ve run across some query hints that require rewriting, for example, as well as some legacy ANSI join syntax that’s being phased out (the *= left outer join syntax in particular). The analysis phase of your upgrade will uncover situations where using a lower compatibility setting might apply. However, I recommend that you fix any syntax that requires a lower compatibility level than 9.0 (SQL Server 2005) during the upgrade process. By reworking the syntax, your developers will have immediate access to all the new programming enhancements and features in the release. One quick trick to isolating these type issues and other syntax that might cause upgrade trouble is to script out the objects and procedures from the earlier platform version and attempt to run the scripts within SQL Server 2005. A simple attach or restore might suppress these issues. Also remember that some SQL logic can be embedded in your application. For data validation, running DBCC checkdb on your attached or restored database will confirm the integrity of the migrated data.
One caution: You probably have developers who are adept at leveraging system objects to make their lives easier. Microsoft has always told us to use Information_Schema views to get various metadata instead of querying the system tables directly because Microsoft can’t guarantee that the underlying object structure will persist in new platforms. With the new release, Microsoft has changed SQL Server’s underlying object structure. Also note that SQL Server 2005 catalog views and Dynamic Management Views (DMVs) have restricted permissions. PUBLIC users no longer have permissions to view catalog views, and users with GUEST/PUBLIC permissions can't select from DMVs.
Migrating to SQL Server 2005 Integration Services
There’s a reason that Microsoft didn’t use the name of Integration Services’ predecessor, Data Transformation Services (DTS), for its new SQL Server 2005 extraction, transformation, and loading (ETL) component. SQL Server Integration Services (SSIS) was a complete code rewrite—Microsoft didn’t bring one line of code over from DTS. With industry demands for faster performance and hardware consolidation to handle ever-increasing data complexity and volume, DTS wasn’t positioned as the long-term solution. So while DTS and SSIS are both ETL tools, architecturally they diverge greatly. Because of this, migration will require some redesign and solution changes so that you can leverage the compelling new SSIS features.
Moving from DTS to SSIS is a migration, involving wizard-driven output along with some manual redesign to complete the process. Some DTS tasks have a straightforward upgrade path to SSIS and are accommodated by the wizard. You might be able to use the wizard to upgrade other tasks depending on their use and design, but some tasks might be more difficult to upgrade or not upgradeable. Here’s what you can expect in upgrading your DTS packages:
• Simple data pumps. Data pumps created by the DTS Wizard or that are simple copy column transformations will be upgradeable most of the time.
• Workflow packages. If your packages are mainly workflow based, with tasks such as the Execute SQL task, they will have a fairly straightforward upgrade path and should also be handled by the wizard in most cases.
• Data pumps with transformations. If your developers used the DTS Designer to build packages that use data pumps with transformations, the wizard will encapsulate the old data pump task. The newly migrated package will invoke the old DTS object model at runtime. Before you upgrade your SQL Server 2005 installation to the next version of SQL Server, you'll have to replace this encapsulated functionality with the new SSIS constructs. Also in this category are Data Driven Query Tasks and Parallel Data Pump Tasks.
• Self-modifying packages. You’ll need to redesign DTS packages that leverage the DTS API to manipulate DTS objects and properties. For example, a common scenario is a looping construct in DTS, designed with scripts that use the API to modify wait states on other package steps. You can redesign this construct by using the built-in For Loop and For-Each Loop Containers in SSIS.
• Scripts tasks. A migrated ActiveX Script Task will run unless it tries to access the DTS runtime package objects. Variable access should use the new SSIS variables without redesign. But in general, you should still reevaluate script tasks against the new SSIS functionality and possibly rewrite them using new tasks.
• OLAP processing tasks. As with Data Transformation Tasks, the wizard will encapsulate these tasks, and they will run in SSIS. After the target server has been upgraded, consider replacing encapsulated functionality with the new SSIS functionality.
• Custom tasks. The wizard will encapsulate these custom tasks, and in many cases, they will run in SSIS. However, the best approach is to make new SSIS custom tasks or transformations.
• Dynamic property tasks. You’ll need to redesign these tasks in SSIS, using Expressions and Configurations to replace the Dynamic Property functionality.
Note that you'll have time to incrementally migrate packages over to SSIS. When you install SQL Server 2005, you have the option to install the runtime files required for DTS packages to execute on SQL Server 2005—without SQL Server 2000 needing to be installed. This makes the side-by-side migration a compelling story, especially in an environment where the DTS packages contain many tasks that require manual migration. SSIS also contains an Execute DTS package object when the runtime files or SQL Server 2000 has been installed on the SSIS server. A side-by-side implementation of SQL Server 2005 SSIS and SQL Server 2000 DTS will give you the greatest flexibility as you approach package migration.
Upgrading Analysis Services
Dimensions, partitions, storage modes, aggregates, and measures—the strengths of Analysis Services 2000—have been preserved in the new release. However, SQL Server 2005 also brings many notable enhancements. The Unified Dimensional Model (UDM), for example, now goes beyond traditional OLAP sources to allow expanded relational and aggregate data in a unified view. Dimensions are another area with valuable changes. A shift from a hierarchy-based model to an attribute-based model, with related optimizations on the storage and aggregation side, allows Analysis Services 2005 to fully scale for enterprise performance and volume.
From an upgrade perspective, Microsoft provides direct in-place upgrade from Analysis Services 2000 to Analysis Services 2005—preserving cubes, partitions, dimension hierarchies, measures, calculations, and sets. Because Analysis Services objects are built on top of a Data Source View (DSV) referencing database engines, it’s important to create the DSV on the base tables that the Analysis Services 2000 objects are built on rather than on views referencing underlying tables. The Migration Wizard does a first-rate job of generating DSVs that are complete with relationships and attributes from source tables. This will allow developers to add attributes to the cube even though they were not present in Analysis Services 2000.
Note that the Migration Wizard won’t optimize the Analysis Services objects; it simply moves the objects in place to the new Analysis Services server. The goal of the wizard is to migrate the cube structures and architecture objects so that client applications relying on the Analysis Services 2000 structures won’t break after you’ve migrated the cube to Analysis Services 2005. Thus, the migrated cube design might not take advantage of SQL Server 2005 enhancements (you can take advantage of those features later). However, your cubes will have the immediate performance and scalability benefits of the new Analysis Services architecture. When the Migration Wizard finishes its processes, you can then reprocess your cube and test your data and reports.
For Analysis Services 2005, the biggest upgrade considerations revolve around the client access methods and structure impact to reports. Analysis Services 2005 takes advantage of the new Web service protocol for OLAP, XML for Analysis (XML/A), that Microsoft helped write. (Support for XML/A first came to Analysis Services 2000 as a Web release, letting an Analysis Services 2000 server listen and respond to XML/A requests.) With native support for XML/A, you need to update existing client components of OLEDB for OLAP (Pivot Table Services—PTS) to access SQL Server 2005. That means users will need the latest version of PTS that comes with SQL Server 2005. The new driver will be installed side-by-side with the earlier PTS version, letting users access both SQL Server 2005 and SQL Server 2000 Analysis Services.
The second client-access consideration is the OLAP structure and related MDX compatibility after the upgrade. MDX isn’t gracious to members and structures that have changed. Although the Upgrade Wizard does a good job at recreating the OLAP structure, with the dimension architecture change from hierarchy based to attribute based, you might find small anomalies, structural and data, that appear after the upgrade. Therefore, report and data testing is more critical here than on the database engine side. You might need to recreate some reports and underlying MDX for the structures in Analysis Services 2005.
Upgrading Reporting Services
Because Microsoft released Reporting Services 2000 for the first time early in 2004, you won’t see major architectural changes in the Reporting Services 2005 platform. However, you will see several new features such as multi-select parameters, built-in MDX support, and dynamic report generation. Microsoft provides a direct, in-place upgrade path for moving from Reporting Services 2000 to Reporting Services 2005. Furthermore, Reporting Services 2005 will run RDL report definitions created in Reporting Services 2000 without requiring you to upgrade the definition. However, when a developer opens a report in the BI Development Studio, the developer will be prompted to convert the RDL to the new Reporting Services 2005 standards.
Managing the upgrade to SQL Server 2005 will require plenty of planning and testing. But with appropriate forethought and preparation—and use of the SQL Server 2005 Upgrade Advisor and Setup wizard—you’ll be able to avoid surprises and identify the areas where you need to concentrate your efforts. With a smooth upgrade under your belt, you’ll be ready to fully leverage the power and functionality that SQL Server 2005 provides.
Top of page
Operating System and Hardware Upgrade Considerations
The basic operating system (OS) and hardware requirements for SQL Server 2005 are much the same as they are for SQL Server 2000. SQL Server 2005 runs on Microsoft Windows Server 2003, Windows XP, and Windows 2000 Server, with a recommended hardware configuration of a 1 gigahertz (GHz) processor and 1 gigabyte (GB) or more of RAM. Table 2 summarizes hardware requirements for each SQL Server 2005 edition (except for the Developer and Evaluation editions). Also note that you need Microsoft IIS 5.0 or higher to run SQL Server 2005 Reporting Services.
SQL Server Edition Max. Procs RAM Requirements 32-bit Support x64 Support Itanium Support
Minimum: 128 MB
Recommended: 512 MB or more
Maximum: 1 GB
As 32-bit application
Minimum: 512 MB
Recommended: 1 GB or more
Maximum: 3 GB
As 32-bit application
Minimum: 512 MB
Recommended: 1 GB or more
Maximum: 64 GB (32-bit) or 512 GB (64-bit)
32 (32-bit) or 128 (64-bit)
Minimum: 512 MB
Recommended: 1 GB or more
Maximum: 64 GB (32-bit) or 512 GB (64-bit)
Table 2: Hardware requirements for SQL Server 2005 editions
Although SQL Server 2005 will run on the same hardware configurations that support SQL Server 2000, advances in both hardware and OS technology give you new processing capabilities at lower prices than ever before, making it tougher to decide on the correct hardware for SQL Server 2005. New processor advancements from AMD and Intel, with their support of the x64 platform, along with Microsoft’s new native 64-bit Windows Server 2003 x64 Edition makes the 64-bit platform an important consideration for both new SQL Server 2005 implementations as well as upgrades from existing SQL Server systems. The key to determining the correct platform is matching your expected workload and budget to the available options.
The 64-bit Intel Itanium Platform
For workloads that need the highest levels of scalability, Intel Itanium 2-based systems are the clear platform choice. The Itanium 2 holds all the top TPC-C benchmark scores for Windows Server. Itanium 2 systems support a much higher level of addressable memory than the x64-based platform and provide greater SMP capabilities. Although Itanium 2-based systems have the highest cost of acquisition, they also offer the most scale-up capabilities. This level of system typically provides redundant hardware for fault tolerance and hardware-based system partitioning.
The x64 Platform
Clearly, not every organization needs the highest levels of scalability. Far more systems fall into the 2-way to 4-way server zone, which is exactly the segment today’s x64 systems address. A primary reason for moving to the x64 platform might be if your current workload is memory constrained and you’ve reached the 32-bit Windows 2GB barrier. Although you could move to Address Windowing Extensions (AWE), which lets you address more than 3GB of memory, AWE uses Non Paged Memory and doesn’t deliver the same level of performance as using system memory on an x64 system. Other workloads that can benefit from the 64-bit capabilities of the x64 platform include business intelligence (BI) solutions and server consolidation scenarios.
The move from 32-bit SQL Server to 64-bit SQL Server is seamless. Both editions of SQL Server use the same on-disk structures; you simply need to detach the databases for the 32-bit system, then attach them to the 64-bit system. You don’t need to change any client applications.
The 32-bit Platform
The 32-bit platform remains the price-performance choice. If your current SQL Server 2000 system has available headroom, you can leverage the investment your business has made in its 32-bit servers by continuing to run SQL Server 2005 on them. Although 32-bit systems might not have the same level of scalability as 64-bit platforms, the raw performance of 32-bit systems has increased and will continue to improve with enhancements in raw CPU speed and the availability of new dual-core systems in 2005.