Upgrading to SQL Server 2008 R2 and SQL Server codename “Denali”

1. Upgrading to SQL Server 2008 R2and SQL Server codename “Denali” Dandy Weyn

2. State of the art Advanced Feature set

3. Technological EvolutionCustomer Demand Industry Specs Growing Needs ScalabilityAccessibility

7. What is an upgrade? Existing version of SQL Server to a higher version

8. Upgrade versus Migration SQL Server Migration Assistant • Assess the migration process complexity • Transform schema and objects • Transforms stored procedures, functions, UDFs,… • Transforms data • Test Transformation • Runs validation

9. What people try to accomplish… VIRTUALIZATION HAPPY UPGRADE CONSOLIDATION

10. Upgrade Methods • Replace an existing instance by one/one upgrade • Less granular control over upgrade process • Instance remains offline during part of upgrade • Not best practice for all components • Considerations for Rollback Strategy • IN PLACE UPGRADE • Easier, mostly automated, generally fast overall process • System data upgraded • May require no additional hardware • Applications remain pointing to same server/DB • Install an instance next to the existing one • More granular control over upgrade process at DB level • Can be used to perform test migration • Ability to run systems side-by-side for parallel runs • Relatively straightforward rollback strategy • Usually require additional hardware • Server/database name changes • Not practical for VLDB unless utilizing SAN • SIDE BY SIDE • Install a new server and instances • More granular control over upgrade process at DB level • Can be used to perform test migration • Ability to run systems side-by-side for parallel runs • Relatively straightforward rollback strategy • Goes frequently together with OS upgrade • Goes frequently together with platform upgrade (x86->x64) • Requires additional Hardware • Server/Database name changes • Not practical for VLDB unless utilizing SAN • NEW INSTALLATION

11. In Place Upgrade Process Point of no return Instance is still available Attach resource database Redirect services to new binaries Install SQL Server 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 Remove old binaries Check for upgrade blockers Instance is no longer available Partial availability starts here Instance becomes available

12. Important steps for a happy upgrade experience PRE UPGRADE Plan and Assess Understand Upgrade Strategies Clean your environmentHave a backup THE UPGRADE Document Upgrade Determine Success POST UPGRADE Decommission Document Get Ready for More

13. Your Key to Success … • Understanding Upgrade Strategies • Plan for Upgrade • Perform the Upgrade • Post-Upgrade

14. Tools • SQL Server Upgrade Advisor • SQL Server Best Practices Analyzer

15. In Place Upgrade • Copy Database Wizard • Backup / Restore • SSIS Object Transfer • Attach / Detach

16. Upgrade Path Considerations

17. Pre-Upgrade Considerations • Document existing SQL Server solution • sp_configure • SQLDIAG.EXE • sp_dboption • Configurations: SAN, networks, and security • Capture performance baseline data • System level (WMI, Counters, etc.) • Application level (query response, concurrent users, etc.) • Develop criteria and unit / verification tests • Optional: capture plans for complex queries

18. Deciding on Upgrade Strategy • Upgrade In-Place or Side-by-Side • Database size and number of objects • Local maintenance jobs • Tolerance for downtime • Hardware capacity / age • System dependencies & other applications • Back-out time

19. Pre-Upgrade Tasks: Backward Compatibility • Some features are discontinued:They do not appear in SQL Server 2008 R2 / Denali • Examples • Undocumented system stored procedures • Virtual cube • Virtual dimension • DUMP / LOAD database • Backup with TRUNCATE ONLY • Some are deprecated: • They won’t be supported in a future SQL Server release (e.g. Notification Services)

20. Some Known Compatibility Issues • Applications work fine on 8.0/9.0 compatibility mode but fails in 10.0 mode or higher

21. Some Known Compatibility Issues • References to system and/or undocumented objects

22. My queries run longer – what now?

23. Some Known Compatibility Issues • Cannot access SQL Server after upgrade

24. Pre-Upgrade Tasks: Outside Forces • Database Solutions • COM Components • Extended Stored Procedures • sp_OA% • CLR assemblies • Linked Servers • Cross-database dependencies • DTS packages

25. Pre-Upgrade Tasks: Outside Forces • Some editions have a different feature set • Example: Express does not have SQL Server Agent • No substitute for knowing your application! • Especially if supporting international versions

26. Post-Upgrade Tasks • Immediate Tasks • Decision whether to roll back • Ensure performance • Resource Planning • Short-Term • Medium-Term • Long-Term

27. Immediate Post-Upgrade Tasks • Review all logs • Revisit Upgrade Advisor recommendations • Update statistics to ensure performance • Full if possible • Sample for very large tables • Reconfigure Log Shipping (if upgraded from 2000) No direct upgrade path to Denali from SQL Server 2000

28. Immediate Post-Upgrade Tasks • Re-populate Full-Text catalogs • Verify Agent jobs and maintenance tasks • Verify security settings • Especially cross server and/or cross-domain access privileges • Check database consistency • DBCC CHECKDB • DBCC UPDATEUSAGE (for 2000 upgrades)

29. Immediate Post Upgrade Tasks • Configuration Manager • Critical for Side-By-Side to reset environment, protocols, etc… • Upgrade process “tries” to preserve functionality for In-Place • Size and configure tempdb correctly • DBCC CHECKDB, CTEs, Cursors, MARS, Row Versioning, Service Broker, Triggers • Verify MODEL database • Verify MASTER database • Perform unit / verification tests • Commit upgrade or rollback!

30. Short-Term Post Upgrade Tasks • Data Types • Take advantage of new data types • From 2000: NVARCHAR(MAX), VARCHAR(MAX), VARBINARY(MAX), XML • From 2000/2005: Filestream, spatial, date, time, hierarchyID • Even more new datatypes in SQL Server Codename “Denali” • Persisted columns • Index persisted columns • Database options • DB_CHAINING, EXECUTE AS • PAGE_VERIFY CHECKSUM • Evaluate AUTO_UPDATE_STATISTICS_ASYNC • Evaluate PARAMETERIZATION

31. Short-Term Post Upgrade Tasks • Partitioned Tables • Enterprise Edition • Partitioned Views -> Partitioned Tables • Appropriate lock escalation setting • Database Files / Filegroups • Performance Benefits • sp_configure ‘affinity mask’ • sp_configure ‘affinity I/O mask’ • Management Benefits • Reports, DMVs (including new and changed), etc. • Database Tuning Advisor

32. Medium-Term Post-Upgrade Tasks • Re-evaluate Optimizer Hints • Remove or document reasons to retain • Online operations • Enterprise Edition • Row Versioning • Evaluate Read Committed Snapshot Isolation (RCSI) • ALLOW_SNAPSHOT_ISOLATION • READ_COMMITTED_SNAPSHOT

33. Medium-Term Post-Upgrade Tasks • Re-evaluate Indexed Views • Definition of “determinism” has changed • Custom aggregations • Re-evaluate indexes • Optimizer has changed • Database Engine Tuning Advisor • Re-evaluate statistics • Sampling • Automatic • Manual

34. Longer-Term Post-Upgrade Tasks • Re-write “COM components” as CLR • XPs to C# or VB in CLR • Custom CLR types in 2005 • E.g. date, time, spatial • Deprecated Features • xp_sendmail / SQL Mail • Data types

35. Long-Term Post-Upgrade Tasks • DTS Packages • Especially if “upgrade” from 2000 to 2005 then 2008 without re-write • Security • Schemas • Different encryption options • New audit capabilities • There are many more! • Especially for 2000 upgrades – see Books Online

36. More on upgrade …

37. Upgrading Mirrored Databasesto SQL Server 2008 R2 • Rolling upgrades supported to minimize impact • Mirror version has to be equal or newer than Principal • At least one manual failover required; failback is usually performed but not required (dependent on setup) • Ensure system can do both without issues prior to upgrade especially if using any SAN replication technologies (e.g. SRDF, Business Copy, etc…) • Consider temporarily disabling SAN replication but be aware of risks • Typical upgrade flow • Remove Witness (for HA configuration only) • Witness can be upgraded anytime after removal • Switch to High Safety mode (if current mode is different) • Upgrade MIRROR instance, allow to rejoin Database Mirroring pair and synchronize • Failover to MIRROR and upgrade original PRINCIPAL, allow to rejoin as the new MIRROR and synchronize • Failback if appropriate • Re-establish witness and/or High Performance mode where applicable

38. Upgrading Replicated Databases • Mixed versions are supported but • Distributor version must always be equal or newer than Publisher • Subscribers for transactional replication can be within 2 versions newer/older of Publisher • Subscribers for merge replication must be equal or older than Publisher • Verify features supported by editions before upgrade, especially for 2000 MSDE  2005 Express • E.g. Merge in Express 2008 is subscriber only • Process is similar to 20002005 upgrade • 20002008R2 is supported • Update agent security settings post upgrade to reflect new security model (2008 and 2005 have same security model) • Typical upgrade flow: Distributor, Publisher, Subscriber

39. Upgrading Log Shipped Databases • Upgrade from 2000 to 2008R2 is not supported; migrate using same method as 20002005 • Monitor server instance can be updated anytime but monitoring is stopped until completed – Log Shipping itself is not affected • Secondary server must be upgraded first • Instance is upgraded first but DB remains 2000/2005 because it is offline; upon failover/recovery it automatically upgrades • Logs are accumulated during upgrade and applied when done • Primary can be upgraded with • Less downtime – requires failover and re-establish Log Shipping after upgrade • Less effort – no failover but system is unavailable during upgrade of primary • Typical upgrade flow: • Less downtime – upgrade secondary, failover, upgrade primary, establish Log Shipping, failback, establish Log Shipping • Less effort – upgrade secondary, allow catch-up, upgrade primary

40. Upgrading Analysis Services • 20052008 R2 typically upgraded via 2008R2 setup • 20002008 R2 upgrade via setup supported but side-by-side upgrade recommended using AS Migration Wizard • Migration Wizard requires DSO backwards compatibility components which are not installed by default – remember to select option during setup or add component before migration • Typical upgrade flow • setup: run 2008 R2 setup, select upgrade workflow, select 2000/2005 AS instance to upgrade • migrate: install 2008 R2 AS, run AS Migration Wizard and follow workflow

41. Upgrading Reporting Services • Upgraded via 2008 R2 Setup • Different patch level requirements from RDBMS • RS2000: Service Pack 2 or later • RS2005: RTM or later • Manual upgrade for RS server using a remote SQL Server 2000 database • RS2000 report server endpoint is no longer supported • Reporting Services 2008 R2 uses native http.sys • Typical upgrade workflow • Run 2008R2 setup, select upgrade workflow for RS, select report server instance to upgrade

42. Upgrading Integration Services (and DTS) • DTS upgrade supported as side-by-side installation – DTS has no dependency on SSIS • Upgrade does not automatically migrate DTS packages to SSIS format – run migration wizard • Consider converting DTS source packages to native SSIS • Conversion tool available from Microsoft partners such as www.dtsxchange.com

43. Upgrading Log Shipping Cannot upgrade Log Shipping 2000

44. Stay up to date with TechNet Belux Register for our newsletters and stay up to date:http://www.technet-newsletters.be • Technical updates • Event announcements and registration • Top downloads Join us on Facebook http://www.facebook.com/technetbehttp://www.facebook.com/technetbelux LinkedIn: http://linkd.in/technetbelux/ Twitter: @technetbelux DownloadMSDN/TechNet Desktop Gadgethttp://bit.ly/msdntngadget

45. TechDays 2011 On-Demand • Watchthis session on-demand via TechNet Edge http://technet.microsoft.com/fr-be/edge/http://technet.microsoft.com/nl-be/edge/ • Download to your favorite MP3 or video player • Get access to slides and recommended resources by the speakers

46. THANK YOU