1 / 60

SQL Server Upgrade Technical Value Proposition

SQL Server Upgrade Technical Value Proposition. Khaled Chebat Developer & Platform Evangelism Lead Microsoft NEPA. Provide high-level view of technical benefits from upgrading to SQL Server 2008 Map technical / product capabilities to users’ goals & challenges.

lucine
Download Presentation

SQL Server Upgrade Technical Value Proposition

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server Upgrade Technical Value Proposition KhaledChebat Developer & Platform Evangelism Lead Microsoft NEPA

  2. Provide high-level view of technical benefits from upgrading to SQL Server 2008 Map technical / product capabilities to users’ goals & challenges Upgrade Technical Value Proposition GOALS In-depth drill down on SQL Server 2008 features End-to-end coverage of SQL Server 2008 Discuss every bullet/word on every slide NON-GOALS

  3. Upgrade Resource/Benefit Comparison BENEFIT Resources Required • Spatial Support • Filestream Support • Hierarchy Id Support • CDC* & Change Tracking • LINQ Support • Entity Framework Support • ADO.NET Data Services Support Deprecated Features • Policy Based Management (DMF) • Performance Data Collection • Enhanced date and time support • Transact-SQL enhancements • Sparse column support • Service Broker enhancements • SSIS / SSRS / SSAS enhancements* Compat Level • Data/Backup Compression* • Transparent Data Encryption* • Resource Governor* • Filtered Indexes/Statistics • Query Optimizer / Storage Engine enhancements* • Enhanced SQL Server Audit* • SSRS/SSAS scalability improvements* * Requires Enterprise Edition

  4. Delivering on the Vision • Enterprise Data Platform • Secure, trusted platform for your data • Optimized and predictable system performance • Productive policy-based management of your infrastructure • Beyond Relational • Store and consume any type of data • Deliver Location Intelligence within your applications • Dynamic Development • Accelerate your development with entities • Synchronize your data from anywhere • Pervasive Insight • Integrate all your data in the Enterprise Data Warehouse • Reach all your users with scalable BI platform • Empower every user with actionable insights

  5. Industry trends and customer requirements Upgrade Technical Value Proposition Important Trends Affecting SQL Server 2008 Usage Expand solutions abilities/offerings New markets: industry and geography Globalize Reduce support costs Important Trends Affecting SQL Server 2008 Usage Improved fundamental abilities

  6. Improved Fundamental Abilities • Performance data collection • Backup compression • Partitioning improvements • Performance(A little early for specifics) • Minimally logged inserts • Star join • Plan guides

  7. Improved Fundamental Abilities • Scale-up to 64 CPUs and 1TB RAM • More with Windows Server in the future • Scale-out with • Data Dependent Routing • Service Broker • Peer-to-Peer replication with conflict detection • Scale-out Analysis Services • Lock escalation table setting • Filestream • Data compression • Scalability

  8. Improved Fundamental Abilities • Hot-add CPUs • Automatic page repair in DB Mirroring • Improved Failover Clustering • Improved Online Operations • Availability • Security • Transparent Data Encryption • Auditing • External key management

  9. Improved Fundamental Abilities Policy-Based Management Centralized policies management Server group management Extended events • Manageability

  10. Improved Fundamental Abilities • Performance • Runaway queries • Mixed-workload environments • Query response SLAs • Availability • Runaway queries • Denial-of-service attacks • Security • Resource Governor

  11. DEMO Backup Compression Transparent Data Encryption Auditing Resource Governor demo

  12. Reduce Support Costs • Data collector • Performance and diagnostic monitoring Policy-Based Management • Pro-active, flexible administration • Configuration Servers Data Collection • Used beyond performance monitoring/tuning; e.g. workload profiling, capacity planning Plan Guides and validation • Predictable query behavior • Data collection sets • User interface • Historical baseline comparisons • Policy-based management • Trouble shooting • Management data warehouse

  13. Reduce Support Costs Service Broker diagnostics • Runtime diagnostics • New Perfmon counters Reporting Services without IIS • Simplifying deployment and configuration mgmt • Uses http.sys and SQL Server networking components

  14. DEMO Policy-Based Management Plan Guides demo

  15. Expand Solutions Abilities/Offerings • ADO.NET Entity Framework • Intellisense • Extended data type support • Spatial • Date and Time • Location • User-Defined Table type • Filestream • LINQ • ADO.NET Data Services

  16. MOLAP-enabled writeback • Removes need for ROLAP partitions • Reporting Services & Office 12 integration • Improved Excel rendering • Word 2007 rendering Expand Solutions Abilities/Offerings • Transact-SQL Enhancements • MERGE statement (aka UPSERT) • Table-Valued Parameters • Row Constructors • Change Data Capture

  17. Peer-to-Peer replication topology Expand Solutions Abilities/Offerings • Change Data Capture • Log-based functionality to capture updates in tables • Streamlines the ETL Incremental Load process • Makes building SSIS data flows easier Extensible key management

  18. Report Designer Client • Authoring reports without BIDS Expand Solutions Abilities/Offerings • DM drill-through to structure cases/columns, enhanced time series algorithm • TABLIX • Easy creation of reports that combine fixed and dynamic rows (Table + Matrix) • Enhanced visualizations using Dundas technology

  19. Geometric / Geographicdata type New Markets/Industries • Disconnected Solutions Build location-aware applications • Capture, integrate, and visualize spatial intelligence • Integrate with 3rd parties Build Occasionally-Connected Systems • Change Tracking • Microsoft Synchronization Framework Expand content management solutions • Store objects > 2GB • Typical RDBM benefits • Filestream storage attribute

  20. Important Trends Affecting SQL Server 2008 Usage Consolidation • Greater opportunities with tools like Resource Governor and Extended Key Management • Providing flexible security in shared instances and shared servers Virtualization • Leverages Windows Server 2008 feature • Easier to support since technologies are from single vendor • Not just about dev/test and training Data in the Cloud • SQL Server Data Services \\\\\\

  21. Planning An Upgrade

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

  23. Upgrade Path Considerations

  24. Upgrade Methodology Overview • Pre-Upgrade Considerations • Document and Measure • Determine Appropriate Upgrade Strategy • Upgrade Process • Post-Upgrade Considerations

  25. 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 complex queries

  26. Upgrade Testing Options Complexity Complex Upgrade Advisor Post Upgrade Optimization Upgrade In Place AppCompat Testing Simple Strategic Importance Low High

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

  28. Pre-Upgrade Tasks: Backward Compatibility • Some features are discontinued:They do not appear in SQL Server 2008 • 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)

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

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

  31. Pre-Upgrade Tasks: Backward Compatibility • Some features have a different behavior • Example: Database Engine catalog security, Report Builder Client • Check Books Online for a full list as well as replacements and techniques • Perform Application Compatibility Testing to identify issues that Upgrade Advisor and/or documentation 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

  32. Pre-Upgrade Tasks: Prepare Environment • Ensure Clean Environment • Database Consistency • Consider rebuilding indexes and shrinking databases • For faster manual copy if taking side-by-side approach • Backup current instance / databases • Internal Department Communications • Ensure no collisions with other activities • Net-Ops does not re-start servers • SAN admin pager doesn’t go off • Access to SME • Emergency support contacts

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

  34. Pre-Upgrade Tasks: Outside Forces • Third Party Dependencies • Software • SQL Litespeed, SQL Spotlight, etc. • Components • Data encryption and keys, mail, etc. • Anti-virus support/impact • Usually good idea to shut down before

  35. Pre-Upgrade Tasks: Outside Forces • 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

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

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

  38. 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 • Verify database integrity with other DBCC commands

  39. Immediate Post-Upgrade Tasks • Configuration Manager • Critical for Side-By-Side to reset environment, protocols, etc… • In-Place Upgrade process “tries” to preserve all current settings • Size and configure tempdb correctly • Sorts and work tables • Cursors • MARS – Multiple Active Results Sets • Row Versioning • Service Broker • Triggers

  40. Immediate Post-Upgrade Tasks • Verify MODEL database • Verify MASTER database • Perform unit / verification tests • Commit upgrade or rollback!

  41. Short-Term Post-Upgrade Tasks • Data Types • New types in 2005 • NVARCHAR(MAX) • VARCHAR(MAX) • VARBINARY(MAX) • XML • New types in 2008 • Filestream • Spatial • Date • Time • Persisted computed columns • Consider indexing and statistics

  42. Short-Term Post-Upgrade Tasks • Database options • DB_CHAINING • EXECUTE AS • PAGE_VERIFY CHECKSUM • Evaluate AUTO_UPDATE_STATISTICS_ASYNC • Evaluate PARAMETERIZATION FORCED • Partitioned Tables • Enterprise Edition • Partitioned Views -> Partitioned Tables • Appropriate lock escalation setting

  43. Short-Term Post-Upgrade Tasks • 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!

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

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

  46. Long-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

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

  48. The Upgrade • Document EVERY step • Never “wing it” • Check database consistency • Check server and network health and alerts • Check your emergency contact info • Engage SME to be present • Back up current instance • Verify the backup • Perform upgrade • Monitor upgrade progress • Check timing and compare to test upgrade timing • Execute your post upgrade tasks

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

More Related