Chapter 1: SQL Server 2008 R2 Editions and Enhancements

This chapter is taken from book "Introducing Microsoft SQL Server 2008 R2" by Ross Mistry and Stacia Misner published for Microsoft Press.

Microsoft SQL Server 2008 R2 is the most advanced, trusted, and scalable data platform released to date. Building on the success of the original SQL Server 2008 release, SQL Server 2008 R2 has made an impact on organizations worldwide with its groundbreaking capabilities, empowering end users through self-service business intelligence (BI), bolstering efficiency and collaboration between database administrators (DBAs) and application developers, and scaling to accommodate the most demanding data workloads.

This chapter introduces the new SQL Server 2008 R2 features, capabilities, and editions from a DBA's perspective. It also discusses why Windows Server 2008 R2 is recommended as the underlying operating system for deploying SQL Server 2008 R2. Last, SQL Server 2008 R2 hardware and software requirements and installation strategies are also identified.

SQL Server 2008 R2 Enhancements for DBAs

Now more than ever, organizations require a trusted, cost-effective, and scalable database platform that offers efficiency and managed self-service BI. These organizations face ever-changing business conditions in the global economy, IT budget constraints, and the need to stay competitive by obtaining and utilizing the right information at the right time.

With SQL Server 2008 R2, they can meet the pressures head on to achieve these demanding goals. This release delivers an award-winning enterprise-class database platform with robust capabilities that improve efficiency through better resource utilization, end-user empowerment, and scaling out at lower costs. Enhancements to scalability and performance, high availability, enterprise security, enterprise manageability, data warehousing, reporting, self-service BI, collaboration, and tight integration with Microsoft Visual Studio 2010, Microsoft SharePoint 2010, and SQL Server PowerPivot for SharePoint make it the best database platform available.

SQL Server 2008 R2 is considered to be a minor version upgrade of SQL Server 2008. However, for a minor upgrade it offers a tremendous amount of new, breakthrough capabilities that DBAs can take advantage of. Microsoft has made major investments in the SQL Server product as a whole; however, the new features and breakthrough capabilities that should interest DBAs the most are the advancements in application and multi-server administration. This section introduces some of the new features and capabilities.

Application and Multi-Server Administration Enhancements

The SQL Server product group has made sizeable investments in improving application and multi-server management capabilities. Some of the main application and multi-server administration enhancements that allow organizations to better manage their SQL Server environments include

• The SQL Server Utility This is a new manageability feature used to centrally monitor and manage database applications and SQL Server instances from a singlemanagement interface known as a Utility Control Point (UCP). Instances of SQL Server, data-tier applications, database files, and volumes are managed and viewed within the SQL Server Utility.
• The Utility Control Point (UCP) As the central reasoning point for the SQL Server Utility, the Utility Control Point collects configuration and performance informationfrom managed instances of SQL Server every 15 minutes. After data has been collected from the managed instances, the SQL Server Utility dashboard and viewpoints in SQL Server Management Studio (SSMS) provide DBAs with a health summary of SQL Server resources through policy evaluation and historical analysis. For more information on the SQL Server Utility, Utility Control Points, and managing instances of SQL Server, see Chapter 2, "Multi-Server Administration."
• Data-tier applications A data-tier application (DAC) is a single unit of deployment containing all of the database's schema, dependant objects, and deployment requirements used by an application. A DAC can be deployed in one of two ways: it can be authored by using the SQL Server data-tier application project in Visual Studio 2010, or it can be created by extracting a DAC definition from an existing database with the Extract Data-Tier Application Wizard in SSMS. Through the use of DACs, the deployment of data applications and the collaboration between data-tier developers and DBAs is significantly improved. For more information on authoring, deploying, and managing data-tier applications, see Chapter 3, "Data-Tier Applications."
• Utility Explorer dashboards The dashboards in the SQL Server Utility offer DBAs tremendous insight into resource utilization and health state for managed instances of SQL Server and deployed data-tier applications across the enterprise. Before the introduction of the SQL Server Utility, DBAs did not have a powerful tool included with SQL Server to assist them in monitoring resource utilization and health state. Most organizations purchased third-party tools, which resulted in additional costs associated with the total cost of ownership of their database environment. The new SQL Server Utility dashboards also assist with consolidation efforts. Figure 1-1 illustrates SQL Server Utility dashboard and viewpoints for providing superior insight into resource utilization and policy violations.
  
  
  
  FIGURE 1-1 Monitoring resource utilization with the SQL Server Utility dashboard and viewpoints
  
  
• Consolidation management Organizations can maximize their investments by consolidating SQL Server resources onto fewer systems. DBAs, in turn, can bolster theirconsolidation efforts through their use of SQL Server Utility dashboards and viewpoints, which easily identify underutilized and overutilized SQL Server resources across the SQL Server Utility. As illustrated in Figure 1-2, dashboards and viewpoints make it simple for DBAs to realize consolidation opportunities, start the process toward eliminating underutilization, and resolve overutilization issues to create healthier, pristine environments.
  
  
  

  FIGURE 1-2 Identifying consolidation opportunities with the SQL Server Utility dashboard and viewpoints

• Customization of utilization thresholds and policies DBAs can customize the utilization threshold and policies for managed instances of SQL Server and deployed data-tier applications to suit the needs of their environments. For example, DBAs can specify the CPU utilization policies, file space utilization policies, computer CPU utilization policies, and storage volume utilization policies for all managed instances of SQL Server. Furthermore, they can customize the global utilization policies for data-tier applications. For example, a DBA can specify the CPU utilization policies and file space utilization policies for all data-tier applications. The default policy setting for over utilization is 70 percent, whereas underutilization is set to 0 percent. By customizing the utilization threshold policies, DBAs can maintain higher service levels for their SQL Server environments.

Figure 1-3 illustrates the SQL Server Utility. In this figure, a Utility Control Point has been deployed and is collecting health state and resource utilization data from managed instances of SQL Server and deployed data-tier applications. A DBA is making use of the SQL Server Utility dashboards and viewpoints included in SSMS to proactively and efficiently manage the database environment. This can be done at scale, with information on resource utilization throughout the managed database environment, as a result of centralized visibility. In addition, a data-tier developer is building a data-tier application with Visual Studio 2010; the newly created DAC package will be deployed to a managed instance of SQL Server through the Utility Control Point.

FIGURE 1-3 The SQL Server Utility, including a UPC, managed instances, and a DAC

In the example in Figure 1-4, a DBA has optimized hardware resources within the environment by modifying the global utilization policies to meet the needs of the organization. For example, the global CPU over utilization policies of a managed instance of SQL Server and computer have been configured to be over utilized when the utilization is greater than 85 percent. In addition, the global file space and storage volume over utilization policies for all managed instances of SQL Server have been changed to 65 percent.

FIGURE 1-4 Configuring over utilization and underutilization global policies for managed instances

For more information on consolidation, monitoring, using the SQL Server Utility dashboards, and modifying policies, see Chapter 5, "Consolidation and Monitoring."

Additional SQL Server 2008 R2 Enhancements for DBAs

This section focuses on the SQL Server 2008 R2 enhancements that go above and beyond application and multi-server administration. DBAs should be aware of the following new capabilities:

• Parallel Data Warehouse Parallel Data Warehouse is a highly scalable appliance for enterprise data warehousing. It consists of both software and hardware designed to meet the needs of the largest data warehouses. This solution has the ability to massively scale to hundreds of terabytes with the use of new technology, referred to as massively parallel processing (MPP), and through inexpensive hardware configured in a hub-and spoke (control node and compute nodes) architecture. Performance improvements can be attained with Parallel Data Warehouse's design approach because it partitions large tables over several physical nodes, resulting in each node having its own CPU, memory, storage, and SQL Server instance. This design directly eliminates issues with speed and provides scale because a control node evenly distributes data to all compute nodes. The control node is also responsible for gathering data from all compute nodes when returning queries to applications. There isn't much a DBA needs to do from an implementation perspective-the deployment and maintenance is simplified because the solution comes preassembled from certified hardware vendors.
• Integration with Microsoft SQL Azure The client tools included with SQL Server 2008 R2 allow DBAs to connect to SQL Azure, a cloud-based service. SQL Azure is part of the Windows Azure platform and offers a flexible and fully relational database solution in the cloud. The hosted database is built on SQL Server technologies and is completely managed. Therefore, organizations do not have to install, configure, or deal with the day-to-day operations of managing a SQL Server infrastructure to support their database needs. Other key benefits offered by SQL Azure include simplification
  of the provisioning process, support for Transact-SQL, and transparent failover. Yet another enhancement affiliated with SQL Azure is the Generate And Publish Scripts Wizard, which now includes SQL Azure as both a source and a destination for publishing scripts. SQL Azure has something for businesses of all sizes. For example, startups and medium-sized businesses can use this service to create scalable, custom applications, and larger businesses can use SQL Azure to build corporate departmental applications.
• Installation of SQL Server with Sysprep Organizations have been using the System Preparation tool (Sysprep) for many years now to automate the deployment of operating systems. SQL Server 2008 R2 introduces this technology to SQL Server. Installing SQL Server with Sysprep involves a two-step procedure that is typically conducted by using wizards on the Advanced page of the Installation Center. In the first step, a stand-alone instance of SQL Server is prepared. This step prepares the image; however, it stops the installation process after the binaries of SQL Server are installed. To initiate this step, select the Image Preparation Of A Stand-Alone Instance For Sys-Prep Deployment option on the Advanced page of the Installation Center. The second step completes the configuration of a prepared instance of SQL Server by providing the machine, network, and account-specific information for the SQL Server instance. This task can be carried out by selecting the Image Completion Of A Prepared Stand-Alone Instance step on the Advanced page of the Installation Center. SQL Server 2008 R2 Sysprep is recommended for DBAs seeking to automate the deployment of SQL Server while investing the least amount of their time.
• Analysis Services integration with SharePoint SQL Server 2008 R2 introduces a new option to individually select which feature components to install. SQL Server PowerPivot for SharePoint is a new role-based installation option in which PowerPivot for SharePoint will be installed on a new or existing SharePoint 2010 server to support PowerPivot data access in the farm. This new approach promises better integration with SharePoint while also enhancing SharePoint's support of PowerPivot workbooks published to SharePoint. Chapter 10, "Self-Service Analysis with PowerPivot," discusses PowerPivot for SharePoint.
  NOTE In order to use this new installation feature option, SharePoint 2010 must be installed but not configured prior to installing SQL Server 2008 R2.
• Premium Editions SQL Server 2008 R2 introduces two new premium editions to meet the needs of large-scale data centers and data warehouses. The new editions, Datacenter and Parallel Data Warehouse, will be discussed in the "SQL Server 2008 R2 Editions" section later in this chapter.
• Unicode Compression SQL Server 2008 R2 supports compression for Unicode data types. The data types that support compression are the unicode compression and the fixed-length nchar(n) and nvarchar(n) data types. Unfortunately, values stored off row or in nvarchar(max) columns are not compressed. Compression rates of up to 50 percent in storage space can be achieved.
• Extended Protection SQL Server 2008 R2 introduces support for connecting to the Database Engine by using Extended Protection for Authentication. Authentication is achieved by using channel binding and service binding for operating systems that support Extended Protection.

Advantages of Using Windows Server 2008 R2

The database platform is intimately related to the operating system. Because of this relationship, Microsoft has designed Windows Server 2008 R2 to provide a solid IT foundation for business-critical applications such as SQL Server 2008 R2. The combination of the two products produces an impressive package. With these two products, an organization can achieve maximum performance, scalability, reliability, and availability, while at the same time reducing the total cost of ownership associated with its database platform.

It is a best practice to leverage Windows Server 2008 R2 as the underlying operating system when deploying SQL Server 2008 R2 because the new and enhanced capabilities of Windows Server 2008 R2 can enrich an organization's experience with SQL Server 2008 R2.

The new capabilities that have direct impact on SQL Server 2008 R2 include

• Maximum scalability Windows Server 2008 R2 is capable of achieving unprecedented workload size, dynamic scalability, and across-the-board availability and reliability. For instance, Windows Server 2008 R2 supports up to 256 logical processors and 2 terabytes of memory in a single operating system instance. When SQL Server 2008 R2 runs on Windows Server 2008 R2, the two products together can support more intensive database and BI workloads than ever before.
• Hyper-V improvements Building on the approval and success of the original Hyper-V release, Windows Server 2008 R2 delivers several new capabilities to the Hyper-V platform to further improve the SQL Server virtualization experience. First, availability can be stepped up with the introduction of Live Migration, which makes it possible to move SQL Server virtual machines (VMs) between Hyper-V hosts without service interruption. Second, Hyper-V can make use of up to 64 logical processors in the host processor pool, which allows for consolidation of a greater number of SQL Server VMs on a single Hyper-V host. Third, Dynamic Virtual Machine Storage, a new feature, allows for the addition of virtual or physical disks to an existing VM without requiring the VM to be restarted.
• Windows Server 2008 R2 Server Manager Server Manager has been optimized in Windows Server 2008 R2. It is usually used to centrally manage and secure multiple server roles across SQL Server instances running Windows Server 2008 R2. Remote management of connections to remote computers is achievable with Server Manager. Server Manager also includes a new Best Practices Analyzer tool to report best practice violations.
• Best Practices Analyzer (BPA) Although there are only a few roles on Windows Server 2008 R2 that the BPA can collect data for, this tool is still a good investment because it helps reduce best practice violations, which ultimately helps fix and prevent deterioration in performance, scalability, and downtime.
• Windows PowerShell 2.0 Windows Server 2008 R2 ships with Windows Power-Shell 2.0. In addition to allowing DBAs to run Windows PowerShell commands against remote computers and run commands as asynchronous background jobs, Windows PowerShell 2.0 features include new and improved Windows Management Instrumentation (WMI) cmdlets, a script debugging feature, and a graphical environment for creating scripts. DBAs can improve their productivity with Windows PowerShell by simplifying, automating, and consolidating repetitive tasks and server management processes across a distributed SQL Server environment.

SQL Server 2008 R2 Editions

SQL Server 2008 R2 is available in nine different editions. The editions were designed to meet the needs of almost any customer and are broken down into the following three categories:

• Premium editions
• Core editions
• Specialized editions

Premium Editions

The premium editions of SQL Server 2008 R2 are meant to meet the highest demands of large-scale datacenters and data warehouse solutions. The two editions are

• Datacenter For the first time in the history of SQL Server, a datacenter edition is offered. SQL Server 2008 R2 Datacenter provides the highest levels of security, reliability, and scalability when compared to any other edition. SQL Server 2008 R2 Datacenter delivers an enterprise-class data platform that provides maximum levels of scalability for organizations looking to run very large database workloads. In addition, this edition offers the best platform for the most demanding virtualization and consolidation efforts. It offers the same features and functionality as the Enterprise edition; however, it differs by supporting up to 256 logical processors, more than 25 managed instances of SQL Server enrolled into a single Utility Control Point, unlimited virtualization, multi-instance dashboard views and drilldowns, policy-based resource utilization evaluation, high-scale complex event processing with Microsoft SQL Server StreamInsight, and the potential to sustain up to the maximum amount of memory the operating system will support.
• Parallel Data Warehouse New to the family of SQL Server editions is SQL Server 2008 R2 Parallel Data Warehouse. It is a highly scalable appliance for enterprise data warehousing. SQL Server 2008 R2 Parallel Data Warehouse uses massively parallel processing (MPP) technology and hub-and-spoke architecture to support the largest data warehouse and BI workloads, from tens or hundreds of terabytes to more than 1 petabyte, in a single solution. SQL Server 2008 R2 Parallel Data Warehouse appliances are pre-built from leading hardware venders and include both the SQL Server software and appropriate licenses.

Core Editions

The traditional Enterprise and Standard editions of SQL Server are considered to be core edition offerings in SQL Server 2008 R2. The following section outlines the features associated with both SQL Server 2008 R2 Enterprise and Standard:

• Enterprise SQL Server 2008 R2 Enterprise delivers a comprehensive, trusted data platform for demanding, mission-critical applications, BI solutions, and reporting. Some of the new features included in this edition include support for up to eight processors, enrollment of up to 25 managed instances of SQL Server into a single Utility Control Point, PowerPivot for SharePoint, data compression support for UCS-2 Unicode, Master Data Services, support for up to four virtual machines, and the potential to sustain up to 2 terabytes of RAM. It still provides high levels of availability, scalability, and security, and includes classic SQL Server 2008 features such as data and backup compression, Resource Governor, Transparent Data Encryption (TDE), advanced data mining algorithms, mirrored backups, and Oracle publishing.
• Standard SQL Server 2008 R2 Standard is a complete data management and BI platform that provides medium-class solutions for smaller organizations. It does not include all the bells and whistles included in Datacenter and Enterprise; however, it continues to offer best-in-class ease of use and manageability. Backup compression, which was an enterprise feature with SQL Server 2008, is now a feature included with the SQL Server 2008 R2 Standard. Compared to Datacenter and Enterprise, Standard supports only up to four processors, up to 64 GB of RAM, one virtual machine, and two failover clustering nodes.

Specialized Editions

SQL Server 2008 R2 continues to deliver specialized editions for organizations that have unique sets of requirements.

• Developer Developer includes all of the features and functionality found in Datacenter; however, it is strictly meant to be used for development, testing, and demonstration purposes only. It is worth noting that it is possible to transition a SQL Server Developer installation that is used for testing or development purposes directly into production by upgrading it to SQL Server 2008 Enterprise without reinstallation.
• Web At a much more affordable price compared to Datacenter, Enterprise, and Standard, SQL Server 2008 R2 Web is focused on service providers hosting Internet-facing Web serving environments. Unlike Workgroup and Express, this edition doesn't have a small database size restriction, and it supports four processors and up to 64 GB of memory. SQL Server 2008 R2 Web does not offer the same premium features found in Datacenter, Enterprise, and Standard; however, it is still the ideal platform for hosting Web sites and Web applications.
• Workgroup Workgroup is the next SQL Server 2008 R2 edition and is one step below the Web edition in price and functionality. It is a cost-effective, secure, and reliable database and reporting platform meant for running smaller workloads than Standard. For example, this edition is ideal for branch office solutions such as branch data storage, branch reporting, and remote synchronization. Similar to Web, it supports a maximum database size of 524 terabytes; however, it supports only two processors and up to 4 GB of RAM. It is worth noting that it is possible to upgrade Workgroup to Standard or Enterprise.
• Express This free edition is the best entry-level alternative for independent software vendors, nonprofessional developers, and hobbyists building client applications. This edition is integrated with Visual Studio and is great for individuals learning about databases and how to build client applications. Express is limited to one processor, 1 GB of memory, and a maximum database size of 10 GB.
• Compact SQL Server 2008 R2 Compact is typically used to develop mobile and small desktop applications. It is free to use and is commonly redistributed with embedded and mobile independent software vendor (ISV) applications.

NOTE Review "Features Supported by the Editions of SQL Server 2008 R2" at http://msdn.microsoft.com/en-us/library/cc645993(SQL.105).aspx for a complete comparison of the key capabilities of the different editions of SQL Server 2008 R2.

Hardware and Software Requirements

The recommended hardware and software requirements for SQL Server 2008 R2 vary depending on the component you want to install, the load anticipated on the servers, and the type of processor class that you will use. Tables 1-1 and 1-2 describe the hardware and software requirements for SQL Server 2008 R2.

Because SQL Server 2008 R2 supports many processor types and operating systems, Table 1-1 strictly covers the hardware requirements for a typical SQL Server 2008 R2 installation. Typical installations include SQL Server 2008 R2 Standard and Enterprise running on Windows Server operating systems. If you need information for Itanium-based systems or compatible desktop operating systems, see "Hardware and Software Requirements for Installing SQL Server 2008 R2" at http://msdn.microsoft.com/en-us/library/ms143506(SQL.105).aspx.

TABLE 1-1 Hardware Requirements

HARDWARE COMPONENT	REQUIREMENTS
Processor	Processor type: (64-bit) x64 Minimum: AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV with EM64T support Processor speed: minimum 1.4 GHz; 2.0 GHz or faster recommended Processor type: (32-bit) Intel Pentium III-compatible processor or faster Processor speed: minimum 1.0 GHz; 2.0 GHz or faster recommended
Memory (RAM)	Minimum: 1 GB Recommended: 4 GB or more Maximum: Operating system maximum
Disk Space	Database Engine: 280 MB Analysis Services: 90 MB Reporting Services: 120 MB Integration Services: 120 MB Client components: 850 MB SQL Server Books Online: 240 MB

TABLE 1-2 Software Requirements

SOFTWARE COMPONENT	REQUIREMENTS
Operating system	Windows Server 2003 SP2 x64 Datacenter, Enterprise, or Standard edition or The 64-bit editions of Windows Server 2008 SP2 Datacenter, Datacenter without Hyper-V, Enterprise, Enterprise without Hyper-V, Standard, Standard without Hyper-V, or Windows Web Server 2008 or Windows Server 2008 R2 Datacenter, Enterprise, Standard, or Windows Web Server
.NET Framework	Minimum: Microsoft .NET Framework 3.5 SP1
SQL Server support tools and software	SQL Server 2008 R2 - SQL Server Native Client SQL Server 2008 R2 - SQL Server Setup Support Files Minimum: Windows Installer 4.5
Internet Explorer	Minimum: Windows Internet Explorer 6 SP1
Virtualization	Windows Server 2008 R2 or Windows Server 2008 or Microsoft Hyper-V Server 2008 or Microsoft Hyper-V Server 2008 R2

NOTE Server hardware has offered both 32-bit and 64-bit processors for several years, however, Windows Server 2008 R2 is 64-bit only. Please take this into consideration when planning SQL Server 2008 R2 deployments on Windows Server 2008 R2.

Installation, Upgrade, and Migration Strategies

Like its predecessors, SQL Server 2008 R2 is available in both 32-bit and 64-bit editions, both of which can be installed either with the SQL Server Installation Wizard or through a command prompt. As was briefly mentioned earlier in this chapter, it is now also possible to use  Sysprep in conjunction with SQL Server for automated deployments with minimal administrator intervention.

Last, DBAs also have the option to upgrade an existing installation of SQL Server or conduct a side-by-side migration when installing SQL Server 2008 R2. The following sections elaborate on the different strategies.

The In-Place Upgrade

An in-place upgrade is the upgrade of an existing SQL Server installation to SQL Server 2008 R2. When an in-place upgrade is conducted, the SQL Server 2008 R2 setup program replaces the previous SQL Server binaries with the new SQL Server 2008 R2 binaries on the same machine. SQL Server data is automatically converted from the previous version to SQL Server 2008 R2. This means that data does not have to be copied or migrated. In the example in Figure 1-5, a DBA is conducting an in-place upgrade on a SQL Server 2005 instance running on Server 1. When the upgrade is complete, Server 1 still exists, but the SQL Server 2005 instance, including all of its data, is now upgraded to SQL Server 2008 R2.

FIGURE 1-5 An in-place upgrade from SQL Server 2005 to SQL Server 2008 R2

NOTE SQL Server 2000, SQL Server 2005, and SQL Server 2008 are all supported for an in-place upgrade to SQL Server 2008 R2. Unfortunately, earlier editions, such as SQL Server 7.0 and SQL Server 6.5, cannot be upgraded to SQL Server 2008 R2.

In-Place Upgrade Pros and Cons

The in-place upgrade strategy is usually easier and considered less risky compared to the side-by-side migration strategy. Upgrading is also fairly quick, and additional hardware is not required. Because the names of the server and instances do not change during the upgrade process, applications still point to the old instances. As a result, this strategy is less time consuming, because there is no need to make changes to application connection strings.

The disadvantage is that there is less granular control over the upgrade process. For example, when running multiple databases or components, a DBA does not have the flexibility to choose individual items for upgrade. Instead, all databases and components are upgraded to SQL Server 2008 R2 at the same time. Note also that the instance remains offline during the in-place upgrade. This means that if a mission-critical database, an application, or an important line-of-business application is running, a planned outage is required. Furthermore, if a disaster transpires during the upgrade, the rollback strategy can be a complex and time consuming affair. A DBA might have to install the operating system from scratch, and then install SQL Server and restore all of the SQL Server data.

SQL Server 2008 R2 High-Level In-Place Strategy

The high-level in-place upgrade strategy for upgrading to SQL Server 2008 R2 consists of the following steps:

1. Ensure that the instance of SQL Server you plan to upgrade meets the hardware and software requirements for SQL Server 2008 R2.
2. Review the deprecated and discontinued features in SQL Server 2008 R2. Refer to "SQL Server Backward Compatibility" at http://msdn.microsoft.com/en-us/library/cc707787(SQL.105).aspx for more information.
3. Ensure that the version and edition of SQL Server that will be upgraded is supported. To review all the upgrade scenarios supported for SQL Server 2008 R2, see "Version and Edition Upgrades" athttp://msdn.microsoft.com/en-us/library/ms143393(SQL.105).aspx.
4. Run the SQL Server Upgrade Advisor for SQL Server 2008 R2. The Upgrade Advisor is a tool included with SQL Server 2008 R2 or downloaded directly from the Microsoft Web site. It analyzes the installed components on the SQL Server instance you plan to upgrade to ensure that the system supports SQL Server 2008 R2. The Upgrade Advisor generates a report identifying anomalies that require fixing or attention before the upgrade can begin.
5. Install the SQL Server 2008 R2 prerequisites.
6. Begin the upgrade to SQL Server 2008 R2 by running Setup.

Side-by-Side Migration

The term side-by-side migration describes the deployment of a brand-new SQL Server 2008 R2 instance alongside a legacy SQL Server instance. When the SQL Server 2008 R2 installation is complete, a DBA migrates data from the legacy SQL Server database platform to the new SQL Server 2008 R2 database platform. Side-by-side migration is depicted in Figure 1-6.

NOTE It is possible to conduct a side-by-side migration to SQL Server 2008 R2 by using the same server. You can also use the side-by-side method to upgrade to SQL Server 2008 on a single server.

FIGURE 1-6 Side-by-side migration from SQL Server 2005 to SQL Server 2008 R2

Side-by-Side Migration Pros and Cons

The biggest benefit of a side-by-side migration over an in-place upgrade is the opportunity to build out a new database infrastructure on SQL Server 2008 R2 and avoid potential migration issues with an in-place upgrade. The side-by-side migration also provides more granular control over the upgrade process because it is possible to migrate databases and components independent of one another. The legacy instance remains online during the migration process. All of these advantages result in a more powerful server. Moreover, when two instances are running in parallel, additional testing and verification can be conducted, and rollback is easy if a problem arises during the migration.

However, there are disadvantages to the side-by-side strategy. Additional hardware might need to be purchased. Applications might also need to be directed to the new SQL Server 2008 R2 instance, and it might not be a best practice for very large databases because of the duplicate amount of storage that is required during the migration process.

SQL Server 2008 R2 High-Level Side-by-Side Strategy

The high-level side-by-side migration strategy for upgrading to SQL Server 2008 R2 consists of the following steps:

1. Ensure that the instance of SQL Server you plan to migrate to meets the hardware and software requirements for SQL Server 2008 R2.
2. Review the deprecated and discontinued features in SQL Server 2008 R2 by referring to "SQL Server Backward Compatibility" athttp://msdn.microsoft.com/en-us/library/cc707787(SQL.105).aspx.
3. Although you will not upgrade a legacy instance to SQL Server 2008 R2, it is still beneficial to run the SQL Server 2008 R2 Upgrade Advisor to ensure that the data being migrated to the new SQL Server 2008 R2 is supported and that there is nothing suggesting that a break will occur after migration.
4. Procure hardware and install the operating system of your choice. Windows Server 2008 R2 is recommended.
5. Install the SQL Server 2008 R2 prerequisites and desired components.
6. Migrate objects from the legacy SQL Server to the new SQL Server 2008 R2 database platform.
7. Point applications to the new SQL Server 2008 R2 database platform.
8. Decommission legacy servers after the migration is complete.