Farewell Old Friend Saying goodbye to SQL Server

Farewell Old Friend Saying goodbye to SQL Server 2000 Andrew Fryer Evangelist Microsoft UK afryer@microsoft. com http: //blogs. technet. com/andrew

AGENDA Why Upgrade? Upgrade Options Upgrade Tools Upgrade Planning

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

SQL Server 2005 • Microsoft®. NET Framework • Common Language Runtime integration • User-defined Aggregates • User-defined Data Types • User-defined Functions • SQL Server. NET Data Provider • Extended Triggers • Data Types • Managed SQL Types • New XML Datatype • Varchar (MAX) Varbinary (MAX) • SQL Server Engine • New Message Service Broker • HTTP Support (Native HTTP) • Database Tuning Advisor • Multiple Active Result Sets • Persisted Computed Columns • Snapshot Isolation Level • Scale Up Partitioning • VIA support • NUMA support • Database Failure and Redundancy • Failover Clustering (up to 8 node) • Enhanced Multi-instance Support • Database Mirroring • Database Snapshots • XML • XQUERY Support • XML Data Manipulation Language • FOR XML Enhancements • XML Schema (XSD) Support • MSXML 6. 0 (Native) • . NET XML Framework • Full-text Search • Indexing of XML Datatype • • • Database Maintenance • Backup and Restore Enhancements • Checksum Integrity Checks • Dedicated Administrator Connection • Dynamic AWE • Fast Recovery • Highly-available Upgrade • Online Index Operations • Online Restore • Parallel DBCC • Parallel Index Operations Management Tools • New Management Studio • MDX Query Editor • Version Control Support • XML/A • SQLCMD Command Line Tool Performance Tuning • Profiler Enhancements • Profiling Analysis Services • Exportable Showplan • Exportable Deadlock Traces Replication • Auto-tuning replication agents • Oracle publication • Improved BLOB change tracking • Enhanced replication monitor SQL Client. NET Data Provider • Server Cursor Support • Asynch Security • All Permissions Grantable • Fine Grain Administration Rights • Separation of Users and Schema • Data encryption primitives • MDAC • SNAC • Microsoft installer based setup • Support for Active Directory® deployment • OLAP and Data Mining • Analysis Management Objects • Windows Integrated Backup and Restore • Web Service/XML for Analysis • DTS and DM Integration • New Data Mining Algorithms • Auto Packaging and Deployment • Data Transformation Services • New Architecture (DTR + DTP) • Complex Control Flows • Control Flow Debugging • For Each Enumerations • Property Mappings • Full Data Flow Designer • Full DTS Control Flow Designer • Graphical Presentation of Pkg Execution • Immediate Mode and Project Mode • Package (Advanced) Deployment Tools • Custom Tasks and Transformations • Reporting Services • Multiple Output Formats • Parameters (Static, Dynamic, Hierarchical) • Bulk Delivery of Personalized Content • Support Multiple Data Sources • STS (Web Parts, Doc Libraries) • Visual Design Tool • Charting, Sorting, Filtering, Drill-Through • Scheduling, Caching • Complete Scripting Engine • Scale Out architecture • Open XML Report Definition • Notification Services and SQL Server Mobile

Transparent Data Encryption External 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 SQL Server Conflict Detection FILESTREAM data type Integrated Full Text Search Sparse Columns Large User Defined Types Date/Time Data Type LOCATION data type SPATIAL data type 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

Security SQL Server 2005 • • • Database Snapshots Database Mirroring Administrative Port Snapshot Isolation Encrypted fields 3 D security (by Design, Default & Deployment) SQL Server 2008 • • Transparent Data Encryption Audit and Change Capture Bad Page Restore in DB Mirroring Backup Compression

Management SQL Server 2005 • SQL Server Management Studio • Dynamic Management Views SQL Server 2008 • • • Data Compression Resource Governor Performance Studio Policy Management Powershell

Why Upgrade? Upgrade Options Upgrade Tools Upgrade Planning UPGRADE OPTIONS

Upgrade Paths Components • Database Engine • Reporting Services • Notification Services • Analysis Services • Data Transformation Services • Full. Text Editions • • • Workgroup Express Standard Developer Enterprise • 32 -bit • 64 -bit • (IA 64 and x 64) Platforms

In Place Upgrade Updates an existing installation while preserving user data Instance name remains the same Automated process

In Place Upgrade Pros • Easier & Automatic • Generally fast overall • No extra hardware • Applications remain pointing to same server/database name Cons • Less granular control • Instance remains offline during part of upgrade • Not best practice for all components • Complex rollback strategy

Side by Side Upgrade Install new instance of SQL Server 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

High Availability Upgrade Clustering Log Shipping Replication

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

Why Upgrade? Upgrade Options Upgrade Tools Upgrade Planning UPGRADE TOOLS

Upgrade Advisor Overview Analyzes Configuration and objects within installed components 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 Advisory – notices and/or warnings

Upgrade Advisor Analyzes SQL Server 2000/2005 database, trace files and script files 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 Output

Upgrade Advisor has limitations UA does not detect everything: 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 UA does not have rules for all known issues or any “yet to be known” issues

Upgrade Assistant Developed in conjunction with Scalability Experts Available for upgrades to SQL Server 2005 and SQL Server 2008

Upgrade Assistant Process Capture realistic workload from test/production Establish a baseline in ACT environment: Restore SQL Server 2000/2005 database environment Replay captured trace and record baseline data Determine functional and performance measures of same application on SQL Server 2008 32 -bit Upgrade database to SQL Server 2008 Replay captured trace and record functional and performance data Repeat for x 64 if required or just test against x 64

Upgrade Assistant Output

Compatibility Mode Support for SQL 2000 Set by default after upgrade Trace flags for deprecated events

Upgrading DTS Replaced by Integration Services Deprecated in SQL Server 2008 But you can still use a DTS packages as is Basic migration tool as part of setup 3 rd Party tool DTSXchange.

Why Upgrade? Upgrade Options Upgrade Tools Upgrade Planning UPGRADE PLANNING

Outline Upgrade Plan Treat Upgrade like any IT Project Identify Upgrade Requirements Agree Acceptance Criteria Complete the Upgrade Checklist Run the latest Upgrade Advisor Pre-Upgrade Considerations Determine Appropriate Upgrade Strategy Upgrade Process 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 Check database consistency Monitor upgrade progress Check timing and compare to test upgrade timing

Immediate Post Upgrade Tasks Review all logs Change the 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 A tested rollback plan is critical Don’t jump to conclusions if performance issues are encountered There are lots of resources to help you and your partners/customers…

Call to Action Download the Upgrade Technical Reference Guide: http: //www. microsoft. com/downloads/details. aspx? Family. ID=66 D 3 E 6 F 5 -69024 FDD-AF 75 -9975 AEA 5 BEA 7&displaylang=en Start Using SQL Server 2008 for management http: //www. microsoft. com/sql/2008/default. mspx Be aware of deprecated features in SQL Server 2008 http: //technet. microsoft. com/en-us/library/cc 707789. aspx Get the latest SQL Server 2008 Upgrade Advisor http: //www. microsoft. com/downloads/details. aspx? Family. ID=c 6 c 3 e 9 ef-ba 29 -4 a 43 -8 d 69 a 2 bed 18 fe 73 c&Display. Lang=en If you need the Upgrade Assistant http: //www. scalabilityexperts. com/default. asp? action=article&ID=43 If you need a better DTS Migration Tool http: //dtsxchange. com/ If you really haven’t got a Tech. Net+ subscription or just needs some help afryer@microsoft. com and http: //blogs. technet. com/andrew

© 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.