Upgrade and App Compat Sessions

2. Upgrade and App Compat Sessions • 13:30-14:45 Tools and Planning • 14:45-15:00 Break • 15:00-15:45 HA Upgrades • 15:45-16:45 DTS to SSSIS • Q+A

3. SQL Academy 2008 01 – Upgrade Basics Tools and Planning Bob Duffy Senior Consultant MCA Database| SQL Ranger

4. Agenda • Review Landscape • Upgrade methodology • Upgrade tools • Planning the upgrade • Pre-upgrade tasks • The upgrade • Post upgrade tasks

5. Operational Thoughts*? • Review How O/S is deployed • WDS in Windows 2008 • SSCM as part of System Centre • Hyper-V (SSVM) or xcopy • Junior with a old bit of paper and a cd and a few hours training • Review Configuration Management • Do we know what SP level and settings are in prod v test v dev • Should we use SSCM, SCOM, SQLH2 or 3rd party. • Review Operational Management • Can we Track SQL Availability and Health via tool? • Do we need regular health and performance reviews? • Do we subscribed to engineering excellence ? • SQL Deployment • DBA running around with CD and some paper notes • Central install point with command line • Imaging/sysprep (watch out for cloning issues) * If these are mature they really help upgrade process

6. SQL 2008 Installation Tips • Slipstream Pre-reqs into OS Build Plan (sql 2008 only!) • Dot.net 3.5 Sp1 • Windows Installer 4.1 • Note new command line changes • Publishing Strategy: Can we publish Client Tools? • Do we need 2005 Client As Well ? • Can we use New Configuration Files (next slide) • Avoid sysprep for Production SQL Server

8. Agenda • Ops/Deployment Recap • Upgrade Planning • Upgrade tools • Pre-upgrade tasks • The upgrade • Post upgrade tasks

9. Upgrade Blockers • Hardware • Third Party Applications • Performance Point Server Needs SP2 • Non Microsoft vendors may be slower • Testing Effort • SQL 2005 meets needs

10. Upgrade planning considerations • It’s a Project not a Task – repeat three times… • Stakeholders • Application • Database • Hardware • Infrastructure • Users • Business sponsor • Motivators • Support (application & database) • Regulations / Policies • Extended euphoria after attending SQL Academy • New capabilities

11. Upgrae Plan

12. Upgrade Planning Tips • Provide platform for testing early in project • Iterate, script, document • 3x Flawless dry-run executions • Minimize Scope / Variables • May require Production changes • Database Mirroring • Recovery model • Backup type and schedule • Run CHECKDB with DATA_PURITY • Take a baseline.

13. Deciding on an upgrade strategyIn-place or side-by-side • Business criticality and application complexity • Highly critical applications are not always complex but will have strict limitations on testing, down time windows, etc… • Large complex application are not always mission critical but will require extensive testing and rollback strategy may be complex • Practical considerations • Hardware capacity / age • Tolerance for downtime • Back-out time & effort • Note parallel operation requirements • Number of databases • Consolidation / other guiding principles • Budget/Effort • Database size / SAN present • DBA Skills • System dependencies & other applications • Local maintenance jobs

14. Upgrade strategy considerations Complexity Advanced Upgrade Complex Upgrade Advisor Post Upgrade Optimization BasicUpgrade App Compat Testing Simple Strategic Importance Low High

15. Agenda • Ops/Deployment Recap • Upgrade Planning • Upgrade tools • Pre-upgrade tasks • The upgrade • Post upgrade tasks

16. Upgrade tools: analysisUpgrade Advisor • Analyzes SQL Server database, trace files and script files • Read-only operation, can be CPU intensive • Disk I/O intensive • Supports remote execution • Supports default and named instance • Use alias for non-standard ports • 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.

17. Upgrade Advisor

18. demo Upgrade Advisor 2008

19. Upgrade tools: ACTUpgrade Assistant • Used in addition to Upgrade Advisor forApplication Compatibility Testing (ACT) • Allows testing of actual TSQL execution against SQL Server not just syntactical checks • Can detect changes in execution method, path and results – Upgrade Advisor does not • Leverages Profiler/Trace capability • Requires SQL Server 2008 Client Tools • Free download from www.scalabilityexperts.com/ssua

20. Application Compatibility TestingProcess • Capture realistic workload from test/production environment • Compatibility test, not stress/scalability/performance test • Establish functional and relative performance 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 • Determine functional and performance of same application on SQL Server 2008 64-bit (x64 or IA64) • Restore SQL Server 2005 database to 64-bit system • Replay capture trace and record functional and performance data • Submit reports/feedback to Microsoft via http://connect.microsoft.com/sqlserver

21. Application Compatibility TestingOptional but recommended • Test with Compatibility Level 10 (2008 level) • Restore database from SQL Server 2000/2005 on 32 or 64-bit • Set compatibility level to 10 and repeat replay sequence • Compare results and note number of successful/failed events • Test actual application against SQL Server 2008 • Can leverage VPCs or test directly from dev/test workstation • If necessary, make changes and re-test • Test setup of application against SQL Server 2008 • Most applications have specific checks/requirements for setup/install; make sure these are updated also (including security) • Basic optimization review • Run Database Tuning Advisor against the database and trace file to identify potential “low effort” optimizations • Explore new/enhanced features with applications • Resource Governor, Performance Data Collector, etc… • Not replacement for solid unit test framework

22. demo SQL Server 2008 Upgrade Assistant

23. Agenda • Ops/Deployment Recap • Upgrade Planning • Upgrade tools • Pre-upgrade tasks • The upgrade • Post upgrade tasks

24. Pre-upgrade tasksCompatibility • Some features are discontinued: • They do not appear in SQL Server 2008 • Example: Undocumented system stored procedures, virtual cube, DUMP database, backup with TRUNCATE ONLY, sp_addgroup, SAC • Some are being deprecated: • Will not be supported in the immediate release following SQL Server 2008: SET ROWCOUNT on updates, FASTFIRSTROW, 80 compatibility • Some features have a different behavior • Example: Unusable plan guides creates new plan, REPLACE keeps trailing space • Check Books Online for a full list and all relevant details • Perform Application Compatibility Testing to identify issues that Upgrade Advisor and/or documentations may have missed • Not all databases/applications require ACT; necessity is dependent on business criticality and complexity of application • Note that business critical != complex and vice-versa

25. Pre-upgrade tasksPrepare environment • Ensure clean environment • Database consistency checks • Consider shrinking databases (one of very few times it’s ok) • Faster file copy for side-by-side • Consider rebuilding indexes • Faster manual copy/loading for side-by-side • Backup old instance / databases • Internal communications • Ensure no collisions with other activities • NOC does not re-start servers, SAN admin pager doesn’t go off, security team does not block port or network segment • Access to SME • Emergency support contacts

26. Pre-upgrade tasksDependencies • Assuming Upgrade Advisor and Application Compatibility Testing are complete and issues addressed • Database Solution • “COM Components”, Extended Stored Procedures, sp_OA%, CLR assemblies • Linked servers security and data providers • Cross-database dependencies • DTS packages • Third Party Dependencies • Software – backup, management agents, clusters, MPIO, SAN mirroring • Components – data encryption & keys, mail, etc… • Anti-virus support/impact • Usually good idea to shut down before upgrade but may not be allowed • Some editions have a different feature set • Example: Express does not have SQL Server Agent (SQL Server 2000 upgrades may be unhappy) • No substitute for knowing your application! • Especially if supporting international versions

27. Pre-upgrade tasksBaseline data • Document existing SQL Server solution • sp_configure, SQLDIAG, sp_dboption • Storage, network & security configuration • Capture/update performance baseline data • System level (e.g. perfmon, waitstats, etc…) • Application level (e.g. query response, concurrent users, etc…) • Develop criteria and unit / verification tests • Can leverage existing user acceptance test harness but be sure to update relevant components • Optional (but recommended): capture query plans for complex and/or critical queries

28. Sample Performance Baseline

29. Sample Report from SCOM

30. Agenda • Ops/Deployment Recap • Upgrade Planning • Upgrade tools • Pre-upgrade tasks • The upgrade • Post upgrade tasks

31. The upgrade • Document EVERY step clearly, precisely • Do not “just wing it” or “figure it out along the way” • Check server, storage and network health and alerts • Check your emergency contact info • If cannot have SME present, verify phone works • Back up old instance and VERIFY • Disable startup procedures • Set max worker thread back to zero (0) • Perform upgrade • Monitor upgrade progress • Check timing and compare to test upgrade timing • Execute your post upgrade tasks • If side By Side double check: • Connection Strings, logins, jobs, master procs, ext procs, maintenance plans, other bits hanging out of server

32. demo Some Upgrades

33. Agenda • Ops/Deployment Recap • Upgrade Planning • Upgrade tools • Pre-upgrade tasks • The upgrade • Post upgrade tasks

34. Post-upgrade tasksImmediate • Review all logs • Revisit Upgrade Advisor/BPA recommendations • Update statistics to ensure performance • Full if possible • Sample for very large tables • Reconfigure/re-establish high availability functions if not part of upgrade process (e.g. Log Shipping) • Verify Agent settings, jobs and maintenance tasks • Verify security settings • Especially cross server and/or cross-domain access privileges • Check database consistency

35. Post-upgrade tasksImmediate • Configuration Manager • Critical for Side-By-Side to reset environment, protocols, … • Upgrade process “tries” to preserve functionality for In-Place • Make sure tempdb was sized correctly • DBCC CHECKDB, CTEs, Cursors, MARS, Row Versioning, Service Broker, Triggers • Verify MODEL database • Verify MASTER database • Re-populate Full-Text Search catalogs • Perform unit / verification tests • Commit upgrade or rollback!

36. Post-upgrade tasksShort-term • Data Types • Review precision levels and storage sizes • Sparse column usage • Persisted columns • Index computed columns • New statistics (particularly if upgraded from 2000) • Database options • DB_CHAINING, EXECUTE AS • PAGE_VERIFY CHECKSUM • Evaluate AUTO_UPDATE_STATISTICS_ASYNC • Evaluate PARAMETERIZATION • SLA for critical queries • Review query plans saved earlier and baseline data

37. Post-upgrade tasksShort-term • Partitioned Tables • Enterprise Edition • Partitioned Views -> Partitioned Tables (not always!) • Appropriate lock escalation setting • Beware of deadlock possibility with partition lock escalation • Review resource allocation settings • sp_configure ‘affinity mask’, sp_configure ‘affinity I/O mask’ • Management benefits/changes • Data Collector, reports, DMVs (including new and changed) • Backup Compression • Database Tuning Advisor! • You already have a workload trace anyway

38. Post-upgrade tasksMedium-term • Re-evaluate Indexes and Indexed Views • Re-evaluate statistics management • Sampling, automatic/manual management • Re-evaluate Optimizer Hints • Remove or document reasons to retain • Consider using plan guides instead of hints • Online operations (mostly Enterprise Edition) • Consider impact on TEMPDB • Policy Based Management • Review current standards enforcements • Re-create standards/rules using PBM • Resource Governor • Start with minimal groups, pools and rules (in classifier function)

39. Post-upgrade tasksLong-term • Data type changes • More granular data types – date, time • Custom CLR types to new native types in 2008 • Re-write “COM components” as CLR • XPs to C# or VB in CLR • Security • Schemas (for 2000 upgrades), new encryption option (TDE), new audit capabilities • Update/remove deprecated features • AS COM assemblies, DTS, ActiveX Script task, SQL Mail, sp_repladdcolumn, updateable subscriptions, sp_dbcmptlevel, …… • Many more if upgrading from SQL Server 2000 – See BOL

40. Agenda • Ops/Deployment Recap • Upgrade Planning • Upgrade tools • Pre-upgrade tasks • The upgrade • Post upgrade tasks • Common Issues

41. Some upgrade common issues1 • Applications work fine on 8.0 compatibility mode but fails in 9.0/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) – often ignored when upgraded from 2000-2005 • 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) • Undocumented objects are not supported, ever • Can’t access database and/or data after upgrade • Change in default settings and/or security requirements • Enable “remote network access” and appropriate protocol (e.g. TCP/IP) • Did you backup and restore Service Master Key used for encryption? • Don’t forget remote Dedicated Admin Connection

42. Some upgrade common issues2 • 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 CSS if performance difference is significant • Cannot Upgrade Log Shipping 2000 • Log Shipping in 2005 does not use DB Maintenance Plan Wizard • Migrating with HA technology – see next session… • Check http://support.microsoft.com/, http://forums.microsoft.com/, msdn, TechNet and Books Online for latest updates.

43. Summary • Upgrades are simple – fixing is simple - testing is hard. • There are no one plan– consider factors discussed in your system/environment. • ACT is highly recommended for complex/business critical systems • A rollback plan is critical for critical systems • There are lots of tools, docs and help

44. References • SQL Server 2008 Upgrade Technical Reference Guidehttp://www.microsoft.com/downloads/details.aspx?familyid=66D3E6F5-6902-4FDD-AF75-9975AEA5BEA7&displaylang=en • Discontinued Features in SQL 2008http://msdn.microsoft.com/en-us/library/cc707782.aspx • SQL 2008 Upgrade Advisorhttp://www.microsoft.com/sql • Need some Help ? • Microsoft Consultancy Services Upgrade Services • Contact your MS Account Manager • MS Partner SQL Server Upgrade Planning Services (SUPS)https://partner.microsoft.com/40053800

45. © 2008 Microsoft Corporation. All rights reserved. Microsoft, Windows, 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.

46. Upgrading BI