Upgrading to Microsoft SQL Server 2008 R2 from Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000

(1)

Upgrading to Microsoft SQL Server 2008 R2 from

Microsoft SQL Server 2008, SQL Server 2005, and SQL

Server 2000

Your Data, Any Place, Any Time

Executive Summary:

More than ever, organizations rely on data storage and analysis for business operations. Companies need the ability to deploy data‐driven solutions quickly. Microsoft SQL Server 2008 R2 data

management software provides a trusted, productive, and intelligent data platform that makes it possible for you to run your most demanding mission‐critical applications, reduce time and cost of application deployment and maintenance, and deliver actionable insights to your entire organization.

Microsoft SQL Server 2008 R2 provides a number of new features and enhancements that make it an attractive upgrade from previous versions of SQL Server:

 Data analysis features, such as PowerPivot, Report Builder, Master Data Services, and

StreamInsight, give you powerful tools for standardizing and analyzing large volumes of data, enhanced ad hoc reporting, managing enterprise data centrally, and generating insight from real‐time data streams.

 Scalability and processing enhancements found in Microsoft SQL Server 2008 R2 Datacenter and SQL Server 2008 R2 Parallel Data Warehouse editions make it possible for you to scale your mission‐critical environments for the most demanding database applications.

 Management and availability features, such as Hyper‐V with Live Migration, the SQL Server Utility Control Point Dashboard, and data‐tier applications, reduce management overhead and increase database application availability while lowering total cost of ownership.

This white paper discusses detailed benefits of upgrading to SQL Server 2008 R2 from Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000, as well as ways to minimize upgrade risks.

(2)

Disclaimer

This document is provided “as‐is.” Information and views expressed in this document, including URL and other Internet Web site references, may change without notice. You bear the risk of using it.

This document does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this document for your internal, reference purposes.

(3)

Introduction

Upgrading to Microsoft SQL Server 2008 R2 provides organizations with next‐generation database capabilities built on the trusted fundamentals of Microsoft SQL Server. At the most fundamental level, an enterprise‐class database must protect the data stored within it while making the data continuously available to a wide audience. Microsoft has taken significant care to make the database more productive and reduce the cost of managing your data infrastructure while streamlining database application development. Following the Microsoft tradition of continuously improving SQL Server with higher performance, easier management, and better security, SQL Server 2008 R2 equips organizations with a highly secure, reliable, and scalable platform on which to run their mission‐critical applications.

Why Upgrade from Microsoft SQL Server 2008?

Microsoft SQL Server 2008 R2 contains a number of new features, enhancements, and tools that make it an attractive upgrade from previous versions of SQL Server.

New Data Analysis Features Enhance Productivity

Data analysis is one of the core functions of any database system. Users want to be able to access and analyze data to gain actionable insights. SQL Server 2008 R2 provides a number of new solutions that provide powerful data analysis and reporting to both developers and end users.

Analyze Massive Amounts of Data with PowerPivot for Microsoft Excel and Microsoft SharePoint

PowerPivot lets users do more by giving them the tools to quickly analyze mountains of data to extract business insights. Using SQL Server 2008 R2 as a foundation, PowerPivot provides the means to create sophisticated self‐service business intelligence solutions in Excel that users can easily manage. Users can create Excel models that contain large, multidimensional datasets without the assistance of IT staff. This capability improves organizations by providing business insights to all employees, leading to better, faster, and more relevant decisions.

PowerPivot consists of two components: PowerPivot for SharePoint 2010 and PowerPivot for Excel 2010. PowerPivot for SharePoint extends Microsoft SharePoint 2010 and Excel Services, adding server‐

side processing, collaboration, and document management support. PowerPivot for Microsoft Excel 2010 removes the one million row limit for worksheets and takes advantage of multiple CPU cores to provide rapid calculations and extend Excel so that workbooks can contain large amounts of data. Using Excel 2010 and the PowerPivot 2010 add‐in, users create data sources inside worksheets that integrate data from multiple sources, such as SQL Server databases and data stored locally on their computers.

SelfService Ad Hoc Reporting with Report Builder 3.0

Report Builder 3.0 saves time by letting users create professional looking reports without the need for IT staff assistance. It includes support for rich geospatial data and provides a report authoring environment

(6)

that lets users create powerful reports within a Microsoft Office environment. Users specify various data sources to draw from and then design custom reports around that data. When users run the report, the SQL Server report processor retrieves the data and combines it with the report layout to generate the report. Users can also share their reports using SharePoint 2010. Report Builder 3.0 makes it possible for users and workgroups to create complex reports using multiple data sources in an environment the users are familiar with.

Centrally Managed Data Assets with Master Data Services

Master Data Services helps organizations reduce complexity by standardizing the data users rely on to make critical business decisions. With Master Data Services, IT departments can centrally manage critical data assets and the integrity of data across the organization while making it possible for more users to securely manage master data. Master Data Hub, a component of Master Data Services, provides a foundation for centrally defining, deploying, managing, and reporting master data that is consistent across systems in an organization.

Powerful Complex Event Processing with StreamInsight

StreamInsight lets users extract actionable insights from their data in real time, making it possible for users to quickly make critical business decisions. It is a powerful platform that lets developers create and deploy complex event processing (CEP) applications. The high‐throughput stream processing

architecture found in StreamInsight, combined with the Microsoft .NET Framework–based development platform, makes it possible for developers to quickly implement powerful and highly efficient event processing applications.

Event stream sources can include data from such varied sources as financial trading applications, manufacturing applications, Web analytics, and operational systems. Using StreamInsight, developers can create CEP applications that derive immediate business value from this raw data by reducing the cost of extracting, analyzing, and correlating the data. Users can then monitor, manage, and mine the data for specific conditions that can define, for example, opportunities or defects.

By using StreamInsight to create CEP applications, developers can achieve the following tactical and strategic business goals:

 Monitor data from multiple sources for meaningful patterns, trends, exceptions, and opportunities.

 Analyze and correlate data incrementally while the data is in flight without first storing the data, yielding very low latency.

 Aggregate seemingly unrelated events from multiple sources, and perform highly complex analyses over time.

 Manage business by performing low‐latency analytics on the events and triggering response actions that are defined on key performance indicators (KPIs).

 Respond quickly to areas of opportunity or threat by incorporating KPI definitions into the logic of the CEP application, improving operational efficiency and the ability to respond quickly to changing opportunities.

(7)

 Mine events for new business KPIs.

 Move toward a predictive business model by mining historical data to continuously refine and improve KPI definitions.

Scalability

New features and editions of SQL Server 2008 R2 give you the ability to scale from small business databases to petabyte‐sized data warehouses.

Increased Scalability and Manageability with SQL Server 2008 R2 Datacenter Edition SQL Server 2008 R2 Datacenter makes it possible for you to cost effectively scale your mission‐critical environments by helping you achieve the highest levels of scalability and manageability for large application workloads. Key features include:

 The ability to manage hundreds of instances from a management server

 The highest level of virtualization support for maximum return on investment on consolidation and virtualization

 High‐scale complex event processing with StreamInsight

 Support for more than 8 physical processors and up to 256 logical processors Massively Parallel Processing with SQL Server 2008 R2 Parallel Data Warehouse SQL Server 2008 R2 Parallel Data Warehouse helps you reduce costs by providing a highly scalable appliance‐based data warehouse solution. Parallel Data Warehouse delivers performance at a lower cost through a massively parallel processing (MPP) architecture and compatibility with hardware partners, making it possible for you to scale your data warehouse from tens and hundreds of terabytes to the petabyte range. Key features include:

 Advanced data warehousing capabilities such as Star Join Queries and Change Data Capture

 Integration with Microsoft SQL Server Integration Services (SSIS), Microsoft SQL Server Reporting Services (SSRS), and Microsoft SQL Server Analysis Services (SSAS)

 Industry‐standard data warehousing hub‐and‐spoke architecture support

SQL Server 2008 R2 Parallel Data Warehouse offers hardware choices with industry‐standard hardware from HP, Dell, Bull, IBM, and EMC.

Support for Up to 256 Logical Processors on Windows Server 2008 R2

SQL Server 2008 R2 provides unprecedented scalability for the most demanding database applications.

When running on the Windows Server 2008 R2 operating system, SQL Server 2008 R2 can take

advantage of up to 256 logical processors; previous versions of SQL Server supported a maximum of 64 processors. Support for additional processors lets more users run database applications simultaneously than was possible with earlier versions of SQL Server.

Reduced Storage Costs with UCS2 Unicode Data Compression

UCS‐2 Unicode data compression helps organizations expand globally while keeping data storage costs in check. As organizations expand internationally, they require database applications that can use

(8)

Unicode‐based data types to support multiple language character sets. SQL Server 2008 R2 uses the Unicode Transformation Format 2 (UCS‐2) encoding standard, which uses two bytes to represent each character. Applications written to use Unicode data types can significantly increase storage

requirements.

SQL Server 2008 R2 includes row‐level and page‐level Unicode compression, which can significantly reduce an application’s storage requirements. SQL Server 2008 R2 data compression features do not only reduce hardware, space, and cooling costs, but the data compression features can also reduce the operating footprint and enhance processing speeds as a result of smaller amounts of data being retrieved and saved to the database.

Management and Availability

SQL Server 2008 R2 contains new features and enhancements that reduce management overhead and increase availability, which helps lower the total cost of ownership.

High Availability Using HyperV with Live Migration

Hyper‐V with Live Migration helps you minimize downtime and service disruption. Live Migration is a new Hyper‐V feature in Windows Server 2008 R2 that lets you move running virtual machines from one Hyper‐V host to another. When combined with SQL Server 2008 R2, administrators can use Live

Migration to reduce maintenance windows by scheduling maintenance during regular business hours.

Live Migration makes it possible to keep SQL Server 2008 R2 virtual machines online during these maintenance windows, increasing productivity for both users and database administrators. Running SQL Server 2008 R2 in Hyper‐V virtual machines also makes greater consolidation ratios possible, resulting in power and cooling cost savings.

Simplified Management of LargeScale Deployments with SQL Server Utility Control Point Dashboard

SQL Server Utility control point dashboard lowers administration costs by simplifying management of large‐scale SQL Server 2008 deployments. The SQL Server Utility helps database administrators manage their multi‐server and multi‐application SQL Server deployment as a unified whole. It models an

organization’s SQL Server environment, providing a holistic view of SQL Server resources through an instance of SQL Server that serves as a utility control point (UCP). Administrators can view the following entities and resources through the UCP:

 SQL Server instances

 Storage volumes

 Database files

 Data‐tier applications

 CPU utilization

 Storage utilization

Database administrators use the Utility Explorer in the Microsoft SQL Server Management Studio (SSMS) to manage the SQL Server Utility.

(9)

With SQL Server 2008 R2, database administrators can reduce the time and costs of managing the enterprise information infrastructure when they use Management Studio to troubleshoot, tune, and monitor instances across the enterprise; define configuration policies for the enterprise; and use pre‐

defined policies to manage configuration and apply best practices.

Simplified Deployments and Upgrades with DataTier Applications

Data‐tier applications ease deployment headaches by consolidating database objects and application code. Database administrators spend a significant amount of time preparing for database upgrades and deployments by locating all of the objects associated with individual database applications, such as tables, runtimes, and stored procedures. Moving existing data‐tier applications and deploying new ones is a very time‐consuming process, filled with trial and error.

New to SQL Server 2008 R2 are data‐tier applications (DACs), which contain all of the database and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data‐tier objects instead of having to manage them separately. This approach provides tighter integration of data‐tier development with its associated application code.

DACs help database administrators with deployments and upgrades by reducing deployment trial and error. With a single unit of deployment, administrators and developers can significantly increase efficiencies around data‐tier deployments and upgrades, which can result in dramatically streamlined consolidation and management.

Additional Reasons to Upgrade from SQL Server 2005 and SQL Server

2000

If your organization is using either Microsoft SQL Server 2005 or Microsoft SQL Server 2000, SQL Server 2008 R2 provides a number of additional benefits.

HighAvailability Enhancements Ensure Maximum Uptime

SQL Server 2008 R2 high‐availability technologies help minimize downtime and maintain high levels of application availability while taking advantage of existing hardware resources. These features include:

 Database mirroring: Increases database application availability by providing database redundancy, which is useful during planned upgrades or in the event of a disaster.

 Log shipping: Provides database‐level redundancy between two or more instances of SQL Server by automatically backing up, copying, and restoring transaction logs onto multiple standby servers.

 Failover clustering: Makes seamless failover of SQL Server possible in the event of CPU,

memory, or other non‐storage failures. This is accomplished by sharing disk access between SQL Server nodes and restarting SQL Server on a working node in the event of a failure.

Geographically dispersed failover clustering also removes the single point of failure in a typical failover cluster by using a certified Microsoft Geographically Dispersed Cluster Services

configuration with storage area network (SAN) replication and a virtual local area network (VLAN).

(10)

 Peer‐to‐peer replication: Replicates databases changes to peer database servers at near‐real‐

time speeds while the databases manage higher priority transactions.

 Enhanced backup and restore: Protects data by performing concurrent backups of a database to multiple backup devices using mirrored backups, increasing protection in the event that backup media fails. SQL Server also creates checksums on backup media, ensuring valid restore

operations.

 Data page checksums: Ensures database data integrity by quickly detecting and restoring damaged database pages.

Efficient Centralized Management of Multiple Database Instances

Powerful manageability enhancements in SQL Server 2008 R2 let database administrators efficiently manage multiple database instances, automate execution of daily administrative tasks, and monitor server performance. These enhancements include:

 Central management servers: Simplifies management by letting administrators manage groups of SQL Server instances using dedicated management servers.

 Windows PowerShell integration: Reduces management costs by providing command‐line navigation of SQL Server objects, making it possible for database administrators to build custom scripts for automating common administration tasks.

 Database snapshots: Increases data integrity by facilitating the investigation and repair of errors by comparing data in a snapshot with the current database data. Snapshots also facilitate point‐

in‐time reporting capabilities.

New Health and Performance Monitoring Tools Ensure Optimal Database

Performance

SQL Server 2008 R2 provides tools and features to help optimize the performance of very large databases. These tools and features include:

 Resource Governor: Optimizes workloads by controlling CPU and memory resources to ensure that mission‐critical database workloads maintain higher priority over non‐critical database activity.

 Performance Data Collector: Reduces complexity and increases IT efficiency by letting database administrators troubleshoot, tune, and monitor all of an organization’s SQL Server 2008 R2 instances from a central data collection point.

(11)

Improved Data Storage Facilitates Storage of Special Data Types

SQL Server 2008 R2 features a number of enhancements for storing special data types, including:

 FILESTREAM: When integrated with an NTFS file system, FILESTREAM makes it possible for application developers to efficiently manage unstructured data, such as Microsoft Office documents. FILESTREAM stores these documents as large binary objects on the NTFS file system.

 Spatial data: Lets developers store and manipulate geodetic and planar spatial data within the database, making it possible for organizations to seamlessly access location‐based data.

 Hierarchical data: Manages hierarchical types of data within the database, where data items are related through hierarchical relationships. Hierarchical relationships exist when one item of data is the parent to another item of data. Examples of hierarchical data include organizational structures and project tasks.

Improved Development Tools and Features Increase Developer Productivity

SQL Server 2008 R2 includes powerful tools that simplify application creation and increase developer productivity. These tools include:

 LINQ: Increases development efficiency by letting developers build queries directly from within any Microsoft .NET–based programming language. Developers can take advantage of the language they know well instead of having to learn various SQL dialects.

 Full‐text search architecture: Saves time by letting developers issue full‐text queries against character‐based data in SQL Server tables. By moving the full‐text search capabilities from the file system to the database engine, SQL Server 2008 R2 lets developers write a single query to access both full‐text and regular data types. The full‐text search architecture also simplifies administration by letting database administrators perform backups of just the database instead of both the database and file system.

Security Enhancements and a Robust Audit Framework Safeguard Data

More and more organizations have to comply with strict governmental and corporate regulations. SQL Server 2008 R2 features several enhancements to secure data and audit processes, including:

 Transparent Data Encryption (TDE): Protects data by performing real‐time encryption and decryption of data within the database and log files. Data is encrypted so that even if the physical media that contain the database and log files were stolen, the data would not be accessible without the proper encryption keys.

 SQL Server Audit: Makes fine‐grained auditing of actions by specific users and processes against database objects possible. This capability helps organizations comply with governmental and corporate compliance regulations by tracking what data is accessed and changed by specific users and processes.

(12)

Minimizing Risk when Upgrading from Previous Versions of SQL Server

Upgrading to SQL Server 2008 R2 from previous versions of SQL Server involves several important considerations. You must decide what route to take in upgrading your existing SQL Server instances, and you must evaluate how the upgrade will affect the functionality of your database applications and other SQL Server services on which your database workloads rely.

An upgrade presents challenges to you because most organizations require 24/7 access to their production SQL Server databases and applications. However, with proper planning and execution, you can minimize downtime.

Choosing an Upgrade Strategy

You primarily have two paths for upgrading previous versions of SQL Server to SQL Server 2008 R2.

These are an in‐place upgrade and a side‐by‐side upgrade.

InPlace Upgrade

An in‐place upgrade modifies the existing database, the end result being that the new version replaces the previous version. Using an in‐place upgrade strategy, the SQL Server 2008 R2 Setup program directly replaces a SQL Server 2000, SQL Server 2005, or SQL Server 2008 instance with a new SQL Server 2008 R2 instance on the same 32‐bit or 64‐bit platform. There is no need to copy database‐related data from the older instance to SQL Server 2008 because the old data files are automatically converted to the new format. When the process is complete, you remove the old SQL Server instance from the server. You can only restore the database instance to its previous state from backups.

SidebySide Upgrade

In a direct side‐by‐side upgrade, you transfer database structure and component data from the previous SQL Server instance to a new, distinct SQL Server 2008 R2 instance. The new SQL Server 2008 R2

instance operates alongside the existing SQL Server instance by using either two servers or a single server.

In a side‐by‐side upgrade, several object types are not automatically transferred and must manually be transferred using other methods.

This method offers the most flexibility and control: You can take advantage of a new and potentially more powerful server and platform, but the existing server remains as a fallback if you encounter compatibility issues. This method lets you rigorously test the new database before transitioning it into the production environment.

The downside of a side‐by‐side upgrade is that increased manual interventions are required, so it might take more up‐front preparation and planning. In most cases, the benefits of this degree of control merit the extra effort.

(13)

Comparing InPlace and SidebySide Upgrade Methods

Table 1 summarizes the differences between the two upgrade strategies:

Table 1: Comparison of the in‐place and side‐by‐side upgrade methods.

Process In‐Place Upgrade Side‐by‐Side Upgrade Number of resulting

instances

One Two

Number of physical servers

One One or more

Data file transfer Automatic Manual

SQL Server instance configuration

Automatic Manual

Required upgrade tool(s) SQL Server Setup Multiple tools from Microsoft and third parties

When evaluating which upgrade strategy to use, you should take into account the possibility that an in‐

place upgrade or side‐by‐side upgrade might need to be rolled back. The complexity and effort required to roll back is an important factor in choosing which method to use.

Functionality Considerations when Upgrading

Regardless of the upgrade strategy you choose, you should consider the functionality changes between existing versions of SQL Server and SQL Server 2008 R2 because previous features on which your applications rely may be discontinued or behave differently in SQL Server 2008 R2.

Application Backward Compatibility

When planning an upgrade to SQL Server 2008 R2, you need to understand which features have been deprecated, discontinued, or changed in the new version. Being aware of these changes can help prevent both performance problems and compatibility issues.

The majority of SQL Server 2008 R2 functionality and behavior is backward compatible with previous versions of SQL Server. However, you should examine feature changes during the planning process. The most serious backward‐compatibility issues are those that can block an in‐place upgrade and prevent an installation of SQL Server 2008 R2.

If the SQL Server 2008 R2 Setup program detects these issues while performing an in‐place upgrade, it will abort the install, leaving the existing instance unchanged. The SQL Server 2008 R2 Upgrade Advisor is the best tool for finding these types of blocking issues ahead of time. You can find comprehensive information about changes in the SQL Server 2008 R2 online book topic, “SQL Server Backward Compatibility” at http://msdn.microsoft.com/en‐us/library/cc707787.aspx.

(14)

Discontinued Features

While SQL Server 2008 R2 retains the majority of backwards compatibility, certain components of previous SQL Server versions may be discontinued. Although some references to these features might not block an in‐place upgrade, you should remove these references anyway. If you do not remove the references, your application might not behave correctly.

You can use the SQL Server Upgrade Advisor to detect whether your application is using discontinued features. For more information about such features, see “Discontinued SQL Server Features in SQL Server 2008 R2” at http://msdn.microsoft.com/en‐us/library/cc707782.aspx.

Deprecated Features

Features that are deprecated in SQL Server 2008 R2 still operate the same as in older versions of SQL Server, but they may be removed in a future version of SQL Server. Access to these features does not necessarily need to be removed to complete an upgrade, but you should eventually address deprecated features because they might cause problems with upgrades in future versions of SQL Server. For details, see the SQL Server 2008 online book topic, “Deprecated SQL Server Features in SQL Server 2008 R2” at http://msdn.microsoft.com/en‐us/library/cc707789.aspx.

Breaking Changes

Breaking changes to SQL Server 2008 R2 are those that might require changes to applications because the features in question now have a different behavior.

The best tool for discovering this type of issue is the SQL Server Upgrade Advisor, which analyzes an existing SQL Server system and reports on all potential breaking changes and how to resolve them. For more information about this type of change, see “Breaking Changes to SQL Server Features in SQL Server 2008 R2” at http://msdn.microsoft.com/en‐us/library/cc707784.aspx.

Behavior Changes

Behavior changes may not visibly affect your database code or applications, but administrators need to be aware of them. Database operations that depend on features that have modified behaviors may be adversely affected. For more information about behavior changes, see “Behavior Changes to SQL Server Features in SQL Server 2008 R2” at http://msdn.microsoft.com/en‐us/library/cc707785.aspx.

Other Affected SQL Server Services

For information on backward compatibility for SQL Server 2008 components, see “Backward Compatibility” at http://msdn.microsoft.com/en‐us/library/cc280407.aspx.

(15)

Conclusion

Upgrading from previous versions of SQL Server to SQL Server 2008 R2 can deliver important benefits to the scalability, manageability, security, and performance of database applications. SQL Server 2008 R2 provides a wide range of performance enhancements and new features that offer useful insights into your organization’s data. However, you must keep in mind numerous considerations as you plan and execute an upgrade. Among these considerations are not only the upgrade path and means but also features that have changed or will change in the future that will impact your organization’s database.

Upgrading to Microsoft SQL Server 2008 R2 from Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000