Upgrading to SQL Server 2008 Graeme Scott Technology

Upgrading to SQL Server 2008 Graeme Scott Technology Solution Professional Microsoft Corporation

CONTENT Why Upgrade? Upgrade Options Upgrade Tools Upgrade Planning

Why Upgrade? Upgrade Options Upgrade Tools Upgrade Planning WHY UPGRADE?

• • • • • Transparent Data Encryption Extensible Key Management Data Auditing Pluggable CPU Transparent Failover for Database Mirroring Policy Management Server Group Management Streamlined Installation Enterprise System Management Performance Data Collection System Analysis Data Compression Query Optimization Modes Resource Governor Entity Data Model LINQ Visual Entity Designer Entity Aware Adapters SQL Server Change Tracking Synchronized Programming Model Visual Studio Support FILESTREAM data storage SQL Server Conflict Detection Integrated Full Text Search Sparse Columns Large User Defined Types Date/Time Data Type SPATIAL data types Virtual Earth Integration Partitioned Table Parallelism Query Optimizations Persistent Lookups Change Data Capture Backup Compression MERGE SQL Statement Data Profiling Star Join Enterprise Reporting Engine Internet Report Deployment Block Computations Scale out Analysis BI Platform Management Export to Word and Excel Author reports in Word and Excel Report Builder Enhancements TABLIX Rich Formatted Data Personalized Perspectives … and many more

• • • • • Transparent Data Encryption Extensible Key Management Data Auditing Pluggable CPU Database Mirroring Enhancements Policy Management Single Server Administration Streamlined Installation Enterprise System Management Performance Data Collection System Analysis Data Compression Query Optimization Modes Resource Governor Entity Data Model LINQ Visual Entity Designer Entity Aware Adapters SQL Server Change Tracking Synchronized Programming Model Visual Studio Support FILESTREAM data storage SQL Server Conflict Detection Integrated Full Text Search Sparse Columns Large User Defined Types Date/Time Data Type SPATIAL data types Virtual Earth Integration Partitioned Table Parallelism Query Optimizations Persistent Lookups Change Data Capture Backup Compression MERGE SQL Statement Data Profiling Star Join Enterprise Reporting Engine Internet Report Deployment Block Computations Scale out Analysis BI Platform Management Export to Word and Excel Author reports in Word and Excel Report Builder Enhancements TABLIX Rich Formatted Data Personalized Perspectives … and many more

Why Upgrade? Upgrade Options Upgrade Tools Upgrade Planning UPGRADE OPTIONS

Upgrading from SQL Server 2000 – 2005 - 2008 ü A possible option if ISV applications are not initially supported on SQL 2008 ü The upgrade delta to SQL 2008 is smaller from SQL 2005 compared to SQL 2000 û 2 upgrades needed 2000 - 2008 ü Only 1 upgrade needed ü Latest capabilities and security with SQL 2008 ûMajor Changes for Dev/DBA ûDeprecated Features now removed

Upgrade Paths Components • Database Engine • Reporting Services • Notification Services not in 2008 • Analysis Services • Data Transformation Services/ Integration Services Editions • • • Workgroup Express Standard Developer Enterprise Platforms • 32 -bit • 64 -bit • (IA 64 and x 64)

In Place Upgrade • • Updates an existing installation while preserving user data Instance name remains the same Automated process Similar to 2000 2005 upgrade

In Place Upgrade Pros Cons • Easier, mostly automated • Generally fast overall process • Requires no additional hardware • Applications remain pointing to same server/database name • Instance name can remain unchanged • Less granular control over upgrade process • All databases on the instance are upgraded in one pass • Instance remains offline during part of upgrade • Not best practice for all components • Complex rollback strategy

Side by Side Upgrade • • • Similar to 2000 2005 migrate (side-byside) Install new instance of SQL Server 2008 Database objects are copied between instances Can be same server (named instance) or different server Mostly Manual process

Side by Side Upgrade Pros • More granular control over upgrade process • Can be used to perform test migration • Ability to run systems sideby-side for testing and verification • Relatively straightforward rollback Strategy • Can leverage failover/switchover to reduce downtime Cons • Usually require additional hardware • Server/database name changes • Not practical for VLDB unless utilizing SAN

Upgrading Business Intelligence 2000 – 2005 – 2008 2000 - 2008 Analysis Services Different architecture and designer Small refinements Different architecture and designer Reporting Services Issues with custom IIS/ SSRS settings No dependency on IIS Issues with custom IIS/ SSRS settings Data Transformation Services (DTS) Runs natively Integration Services (SSIS) DTS to SSIS Small refinements Everything has changed

Why Upgrade? Upgrade Options Upgrade Tools Upgrade Planning UPGRADE TOOLS

Upgrade Advisor Overview • Analyzes − Configurations, databases, objects and components on a SQL instance − Trace files (captured using Profiler / SQL Trace) − T-SQL scripts (any script file) • Generates Detailed Issue Report: − Before – potential showstoppers, must resolve before upgrading − After – should be resolved after completing upgrade − Anytime, will prevent the upgrade process − Advisory – notices and/or warnings

Upgrade Advisor − Analyzes: − SQL Server 2000/2005 databases − Analysis Services databases − − − Read-only operation, can be CPU intensive Supports remote execution Supports default and named instance Requires. NET framework 2. 0 sp 1 or later Download latest version from web!

Upgrade Advisor DEMO

Running the Upgrade Advisor

Running the Upgrade Advisor

Running the Upgrade Advisor

Running the Upgrade Advisor

Running the Upgrade Advisor

Running the Upgrade Advisor

Running the Upgrade Advisor

Running the Upgrade Advisor

Upgrade Advisor has limitations • Upgrade Advisor does not detect issues with: − Dynamic SQL − Meta-data driven applications (both during application setup and regular use) − Changes in system objects (stored procedures, functions, tables, etc…) but uses the same name • Upgrade Advisor does not have rules for all known issues − or any “yet to be known” issues

Application Compatibility Testing (ACT) • Tool developed by the Scalability Experts with the SQL Server product team • Two separate tools available for SQL 2005 / 2008 • Focuses on workload performance on SQL 2008

ACT Process • Capture a profiler trace of a realistic workload from a production / pre-production environment • Use the ACT tool to replay the trace in a preproduction environment to form a performance baseline • Either side-by-side or on separate machines, use the ACT tool to replay the SQL trace file on a SQL 2008 instance to measure performance •

ACT Process • View any performance differences in the ACT tool Playback Results Analyzer (PRA) • Can also be used to verify performance differences when moving from 32 Bit to 64 Bit infrastructure.

ACT Output - (PRA)

Why Upgrade? Upgrade Options Upgrade Tools Upgrade Planning UPGRADE PLANNING

Outline Upgrade Plan • Identify the SQL components to be upgraded • Use the Upgrade Advisor / ACT tools • Form an overall upgrade strategy • Decide on the upgrade process for SQL Server − in place / side by side • Identify post upgrade considerations

Pre Upgrade Preparation • • • Document existing solution Capture performance baseline data Develop criteria and unit / verification tests • • Optional: capture query plans for complex queries Ensure Clean Environment Backup old instance / database and Verify it! Loop in Windows Administrators, SAN Administrators and Network Operations Optional: Access to Support •

Performing the Upgrade • Document EVERY step to the last detail and follow the document • Monitor upgrade progress − Check upgrade timing and compare to tested timing • Check database consistency

Immediate Post Upgrade Tasks • • • Review all logs Change the database compatibility level Change the PAGE_VERIFY option Revisit Upgrade Advisor recommendations Update statistics to ensure performance Reconfigure Log Shipping Re-populate Full-Text catalogs Verify Agent jobs and maintenance tasks Verify security settings, especially cross server and/or cross-domain access privileges

Longer Term Post Upgrade Tasks • • • Re-evaluate statistics Check Optimizer Hints Database Tuning Advisor Leverage New ‘Low Cost’ Features − Backup compression − Transparent Data Encryption Make use of schemas Plan DTS Migration

Summary Upgrades are not rocket science but… • The smallest detail missed can break the entire upgrade • Use the Upgrade Advisor / Application Compatibility Testing tools • A tested rollback plan is essential • Don’t jump to conclusions if performance issues are encountered

Call to Action • • • SQL Server 2008 http: //www. microsoft. com/sql/2008/default. mspx SQL Server 2005 Upgrade Advisor http: //www. microsoft. com/downloads/details. aspx? familyid=1470 e 86 b 7 e 05 -4322 -a 677 -95 ab 44 f 12 d 75&displaylang=en SQL Server 2008 Upgrade Advisor http: //www. microsoft. com/downloads/details. aspx? Family. ID=f 5 a 6 c 5 e 94 cd 9 -4 e 42 -a 21 c-7291 e 7 f 0 f 852&Display. Lang=en Application Compatibility Testing tool http: //ssua. scalabilityexperts. com/ DTS Migration Tool http: //dtsxchange. com/

© 2008 Microsoft Corporation. All rights reserved. Microsoft, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U. S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.