Upgrade – Prep and Methodology

1. Upgrade – Prep and Methodology

2. Agenda • Upgrade methodology overview • Upgrade options and considerations • Upgrade tools

3. Upgrade Paths Database Generations SQL Server 2008 SQL Server 2005 SQL Server 2000 SQL Server 7.0 SQL Server 6.5 SQL Server 6.0

4. Upgrade Methodology Overview • Identify Upgrade Requirements • SQL Server edition • Hardware considerations • Run Upgrade Advisor • Resolve identified issues • Perform Application Compatibility Testing • Ensure thorough testing of all RDBMS queries • Pre-Upgrade Considerations • Document existing system • Develop validation tests / performance benchmarks and capture baseline data • Formulate and test rollback plan • May required 3rd party applications • Determine Appropriate Upgrade Strategy • In-place or side-by-side (migrate) • Upgrade Process • Pre-upgrade tasks • Post Upgrade Considerations • Immediate / short-term / medium-term / long-term

5. Agenda • Upgrade methodology overview • Upgrade options and considerations • Upgrade tools

6. Upgrade Path Options • Versions • SQL Server 2000 SP4 or later • SQL Server 2005 SP2 or later • Components • Database Engine • Includes sub-components like SQL Agent, Full-text, Tools, etc. • Reporting Services • IIS to http.sys? • Notification Services 2005 only (deprecated in 2008) • Analysis Services (it is recommended that cubes are migrated) • Data Transformation Services • Integration Services • Editions • Desktop, Workgroup, Personal, Standard, Developer, Enterprise • Platforms • 32-bit & 64-bit (IA64 and x64) • Languages • All SQL Server 2000 and SQL Server 2005 released languages

7. Upgrade Options and Considerations: In-Place • Similar to 20002005 upgrade • Updates an existing installation while preserving user data • Instance name remains the same after upgrade • Existing instance overwritten post-upgrade • Automated process

8. Upgrade Options and Considerations: In-Place SQL Server 2000/2005 Instance SQL Server 2008 Instance Upgrade

9. Upgrade Options and Considerations: In-Place Point of no return Instance is still available Attach resource database Redirect services to new binaries Install SQL Server 2008 binaries Restart service Execute various upgrade scripts Install prerequisites Upgrade Complete Upgrade Starts Start service in single-user mode Stop service Stop service Start updating all databases Check for upgrade blockers Remove old binaries Instance is no longer available Partial availability starts here Instance becomes available

10. Upgrade Options and Considerations: In-Place • Pros • Easier, mostly automated • Generally fast overall process • Requires no additional hardware • Applications remain pointing to same server/database name • Cons • Less granular control over upgrade process • Instance remains offline during part of upgrade • Not best practice for all components • Complex rollback strategy

11. Upgrade Options and Considerations: Side-by-side • Similar to 20002005 migrate (side-by-side) • Install new instance of SQL Server • Database objects are copied between instances • Copy Database Wizard • Does not copy extended procs, alerts, DTS packages, linked servers. • Detach -> Attach • Backup -> Restore • T-SQL Scripts, BCP New and Old instance reside side-by-side • Can be same server (named instance) or different server • Mostly Manual process

12. Compare and Verify Upgrade Options and Considerations: Side-by-side SQL Server 2000/2005 Instance SQL Server 2008 Instance Verified!

13. Upgrade Options and Considerations: Side-by-side • Pros • More granular control over upgrade process • Database level • Can be used to perform test migration • Document process & gather metrics • Ability to run systems side-by-side for testing and verification • May require 3rd party application to keep both in-sync • Relatively straightforward rollback Strategy • Can leverage failover/switchover to reduce downtime • Cons • Usually require additional hardware • Additional resources required if on same server • Server/database name changes • Not practical for VLDB unless utilizing SAN • Beware of “loss of quick roll-back”

14. Agenda • Before you run setup.exe • Upgrade methodology overview • Upgrade options and considerations • Upgrade tools

15. Upgrade Tools • 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 • Generates a report of • Detected issues • Blocking Issues • Pre-Upgrade Issues • Post-Upgrade / Migration Issues • Suggestions on how to fix / work around • Requires .NET framework 2.0 sp1 or later • Download latest version from web!

16. Upgrade Advisor Overview • Analyzes • Configuration and objects within installed components • SQL Server • Analysis Services • Notification Services • Reporting Services • Data Transformation Services • Integration Services • Trace files (captured using Profiler/SQL Trace) • T-SQL scripts (any script file) • Generates report of issues that must be addressed • Before – potential showstoppers, must resolve before upgrading • After – should be resolved after completing upgrade • Anytime • Advisory – notices and/or warnings

17. Requirements • Windows XP SP2, Windows Server 2003 SP1, Windows Vista, Windows Server 2008 Beta 3 • Windows Installer 3.1 • .NET Framework 2.0 • Supports • SQL Server 2000 • Requires decision support objects (DSO) if scanning Analysis Services 2000 • Requires client components if scanning SQL Server 2000 packages • SQL Server 2005 • Requires backward compatibility components if scanning SQL Server 2005 DTS packages that were upgraded from SQL Server 2000

18. Upgrade Tools • Upgrade Assistant / Database Upgrade Testing Toolkit (DUTT) • Used in addition to Upgrade Advisor • Allows testing of actual TSQL execution against SQL Server • Can detect changes in execution method, path and results • Upgrade Advisor does not • Allows testing of dynamic/embedded SQL • Leverages Profiler/Trace capability • Requires SQL Server 2008 Client Tools • Collaborative development between SQL Server Development Team and Scalability Experts • Free download from www.scalabilityexperts.com

19. Some Known Compatibility Issues • Applications work fine on 8.0/9.0 compatibility mode but fails in 10.0 mode • Additional ANSI SQL standards enforcements in 2005 (e.g. left and right outer joins using *= and =* no longer supported) • Modify SQL statements to be standards compliant (will still work if in 8.0 compatibility mode) • References to system and/or undocumented objects • Accessing system tables/stored procedures or undocumented features may/will fail or behave differently • Modify application to utilize Dynamic Management Views and Functions (e.g. syslocks  sys.dm_tran_locks) • Poorer performance after upgrade • Queries take longer to run after upgrading to SQL Server 2008 • Several possible reasons and solutions • Review queries to ensure hints are still valid (or just remove them) • Do full updated statistics (use different sampling size for large tables) • Review TempDB utilization and optimize via storage isolation • Run DB Tuning Advisor • Contact PSS if performance difference is significant • Cannot Access SQL Server after upgrade • Clients can no longer connect to the database server or related components • Check settings in Surface Area Configuration – “off-by-default” is in place for features and access methods • Cannot Upgrade Log Shipping 2000 • Log Shipping in 2008 does not use DB Maintenance Plan Wizard • Migrating with failover – switchover to standby, upgrade primary, switchback, upgrade standby, re-establish Log Shipping or other HA technology • Migrating without failover – upgrade primary, upgrade secondary, re-establish Log Shipping or other HA technology • Check http://support.microsoft.com/, msdn, technet and Books Online for latest updates.