1 / 23

Microsoft SQL Server 2005 Upgrade Planning

Microsoft SQL Server 2005 Upgrade Planning. Kevin Ashby Microsoft EMEA kashby@microsoft.com +44 79 68 83 9696. Why Upgrade?. Replication Seamless DDL replication Merge Web Sync Oracle Publication Peer to Peer Transactional replication Merge replication perf and scalability

john-herrera
Download Presentation

Microsoft SQL Server 2005 Upgrade Planning

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. Microsoft SQL Server 2005UpgradePlanning Kevin Ashby Microsoft EMEA kashby@microsoft.com +44 79 68 83 9696

  2. Why Upgrade? Replication • Seamless DDL replication • Merge Web Sync • Oracle Publication • Peer to Peer Transactional replication • Merge replication perf and scalability • New monitor and improved UI Analysis Services and Data Mining • Analysis Management Objects • Windows Integrated Backup and Restore • Web Service/XML for Analysis • Integration Services and DM Integration • Eight new Data Mining algorithms • Auto Packaging and Deployment • Migration Wizard Integration Services • New high performance architecture • Visual design and debugging environment • Extensible with custom code and scripts • XML task and data source • SAP connectivity • Integrated data cleansing & text mining • Slowly changing dimension wizard • Improved flow control • Integration with other BI products Reporting Services • Report Builder • Analysis Services Query Designer • Enhanced Expression Editor • Multi-valued Parameters • Date Picker • Sharepoint Web Parts • Floating Headers • Custom Report Items • XML Data Provider Database Maintenance • Backup and Restore Enhancements • Checksum Integrity Checks • Dedicated Administrator Connection • Dynamic Configuration AWE • Highly-available Upgrade • Online Index Operations • Online Restore Management Tools • MDX & XML/A Query Editor • Maintenance Plan Designer • Source Control Support • Profiler access to non-sa • SQLCMD Command Line Tool • Database Mail Performance Tuning • Profiling Analysis Services • Exportable Showplan & Deadlocks • Profiler Enhancements • New Trace Events Full-text Search • Backup/Restore includes FT catalogs • Multi-instance service SQL Client .NET Data Provider • Server Cursor Support • Multiple Active Result Sets Security • Catalog and meta-data security • Password policy enforcement • Fine Grain Administration Rights • Separation of Users and Schema • Surface Area Configuration Notification Services • Embed NS in existing application • User-defined match logic • Analysis Services Event Provider Database Engine • Service Broker • HTTP Access • Database Tuning Advisor • Enhanced Read ahead & scan • Indexes with Included Columns • Multiple Active Result Sets • Persisted Computed Columns • Try/Catch in T-SQL statements • Common Table Expressions • Server Events • Snapshot Isolation Level • Partitioning • Synonyms • Dynamic Management Views .NET Framework • Common Language Runtime Integration • CLR-based Types, Functions, & Triggers • SQL Server .NET Data Provider Data Types • CLR-based Data Types • VARCHAR(MAX), VARBINARY(MAX) • XML Datatype Database Failure and Redundancy • Fail-over Clustering (up to 8 node) • Database Snapshots • Enhanced Multi-instance Support XML • New XML data type • XML Indexes • XQUERY Support • XML Schema (XSD) support • FOR XML PATH • XML Data Manipulation Language • SQLXML 4.0

  3. Agenda • Definitions • What can be upgraded • How to upgrade • Upgrade Plan • Pre-upgrade • Upgrade Execution • Post-upgrade • In-place Upgrade vs. Migration • Upgrading Log Shipping • Upgrade Tips and Best Practices

  4. Definitions • Upgrade (or in-place upgrade): • Updates an existing installation while preserving user data • Instance name remains the same after upgrade • Automated process • Migration (or side-by-side migration): • Starts with a new installation • New & old instance reside side-by-side • Objects are copied from the old to new instance • Mostly a manual process

  5. In-place Upgrade SQL Server 7.0/2000 Instance: Foo SQL Server 2005 Instance: Foo Upgrade • Take users off the system • Run Upgrade Advisor • Identify/resolve problem/compatibility issues • Run setup.exe and start the upgrade

  6. Side-by-side Migration Compare and Verify SQL Server 7.0/2000 Instance: Foo SQL Server 2005 Instance: Bar Verified! Remember to run the Upgrade Advisor

  7. What can be upgraded or migrated? • Versions • SQL Server 7.0 (latest service pack) • SQL Server 2000 (latest service pack) • Components • Database Engine • Includes sub-components like SQL Agent, Full-text, Tools, etc. • Analysis Services • Reporting Services • Notification Services • Migration of Data Transformation Services to Integration Services • Editions • Desktop, Workgroup, Personal, Standard, Developer, Enterprise • Platforms • 32-bit & 64-bit (IA64 and x64) • Languages • All SQL Server 7.0 and SQL Server 2000 released languages

  8. Upgrade Plan • Smooth upgrade requires a good plan • Devise an upgrade plan by grouping upgrade tasks into: • Pre-upgrade tasks • Upgrade execution tasks • Post-upgrade tasks • 0 day, 30 day, 90+ day • Exercise upgrade plan • Test it more than once!

  9. Pre-upgrade:Prepare Your Environment • Study SQL Server 2005 minimum hardware & software requirements • Get an inventory of your applications & legacy systems • Releases, Components, SKU’s, Platforms • Opt for the same or a compatible edition • Check features in each SQL Server 2005 SKU • Beware of cross-SKU upgrade matrix • Record benchmarks • Functional, Performance, Stress • Run Upgrade Advisor • Examine issues reported by Upgrade Advisor • Fix or work around backward compatibility issues

  10. Pre-upgrade: Backward Compatibility • Some features are discontinued: • They do not appear in SQL Server 2005 • Example: Undocumented system stored procedures, Virtual cube, Virtual dimension… • Some are being deprecated: • They won’t be supported in the release following SQL Server 2005 • Example: SQL Mail, Calculated Cell, Cell evaluation list… • Some features have a different behavior • Example: Database Engine catalog security • Some editions have a different feature set • Example: Express does not have SQL Server Agent • Check Books Online for a full list as well as replacements and techniques • Run Upgrade Advisor before any migration or upgrade

  11. Pre-upgrade: Upgrade Advisor Tool • Improves the SQL Server 2005 upgrade experience • Avoids surprises during or after upgrade • Analyzes SQL Server 2000 and SQL 7.0 instances • Analyzes objects on server; script and trace files • Performs read-only operation • Provides a report for detected issues • Presents guidance on when detected issues need to be fixed • Describes how to fix or work around issues • Has links to documentation for additional content • Download from web http://www.microsoft.com/downloads/details.aspx?FamilyID=cf28daf9-182e-4ac2-8e88-f2e936558bf2&DisplayLang=en

  12. Pre-upgrade Upgrade Advisor Upgrade Advisor Report Ready for upgrade Post-Upgrade Upgrade

  13. Pros & Cons of Migration • Pros • Migration provides more granular control over the upgrade process • Having new and old instances side-by-side helps with testing & verification • Legacy instance remains online during migration • Flexibility to implement migration with failover • Cons • May require new or additional hardware resources • Applications need to be directed to new instance

  14. Pros & Cons of Upgrade • Pros • Easier, faster, less headache for small systems • Requires no additional hardware • Applications remain pointing to old instance • Cons • Less granular control over upgrade process • Instance remains offline during part of upgrade • Not best practice for all components • Analysis Services cubes are recommended to be migrated

  15. Upgrade & Migration Tools • Setup is in-place upgrade tool • Database Engine • Analysis Services • Reporting Services • Wizards for side-by-side migration • Analysis Services • DTS to Integration Services • Specific configuration tools for migration • Reporting Services • Notification Services • Upgrade Advisor is upgrade analysis tool for all components

  16. Upgrading Database Engine • Setup.exe performs the in-place upgrade • Several migration techniques for Database Engine: • Detach/Attach • Backup/Restore • Copy Database Wizard, DTS • Manual scripts, BCP

  17. Upgrading Replication Do Distributor First! Updatable Subscriber to a SQL Server 2005 transactional publication can be any version equal to or greater than SQL Server 2000 SP3 Distributor can be any version as long as it is greater than or equal to the Publisher version Distributor Distributor Publisher can be any version as long as it less than or equal to the Distributor version Subscriber Publisher Read-only Subscriber to a transactional publication can be any version within two versions of the Publisher version Publisher Subscriber Subscriber to a merge publication can be any version less than or equal to the Publisher version

  18. Upgrading Legacy SQL Server Tools • SQL Server Agent has a new security model (proxy account) • Registered Servers can be upgraded to Management Studio • Database Diagram are upgraded into the new SQL Server 2005 format • Database maintenance plans can be migrates to new Maintenance Plan packages

  19. Post-upgrade • Perform post-upgrade tasks on Upgrade Advisor report • Examples: Update statistics, build cubes • Change database COMPATIBLITY level • Reconfigure log shipping • Execute upgrade testing • Verify Agent jobs and maintenance tasks • Bring system online • Monitor system activity

  20. Upgrade Tips and Best Practices • You can reduce upgrade down time • Pre-install Setup pre-requisites • Microsoft .NET Framework 2.0 • Microsoft SQL Native Client • Setup support files • If you are planning a migration using Copy Database Wizard • Before upgrade, place the database in single user mode • make sure that no applications or services are trying to access the database • Do not use read-only mode -- this will result in an error • Cannot rename database during operation • Be aware of SQL Server 2005 reduced surface area • Some services & features are disabled on new installations • Use Surface Area Configuration tools to enable or disable features & services

  21. Upgrading to SQL Server 2005 the biggest findings • SS2K statistics invalid after upgrade • Update statistic will kick in automatically upon first execution of queries using sample data • Default update statistics sampling could be very small for very large tables, possibly <1% • Recommendation: manually update statistics after upgrade. Full if possible, suggest: 10% for Very Large tables. • Example: Update statistics sales.salesorder with sample 10 percent; • Recommendation: Ensure you remember to run surface area configuration manager • May not recognize an important component is off by default: named pipes, Service Broker, CLR, FTS, Dedicated Administration Connection, etc.

  22. Upgrading to SQL Server 2005 the biggest findings • Recommendation: Specify the WITH keyword when using table hints • Optional in SS2K / Mandatory with SS2005 and therefore an error will occur • Close to 25% of the problems we have seen • Example: UPDATE Production.Product WITH (TABLOCK) SET ListPrice = ListPrice * 1.10 WHERE ProductNumber LIKE 'BK-%' • Recommendation: Remove references to undocumented system tables • Use of keyword “sys”, now a reserved word

  23. Summary • Upgrade has been heavily tested • Please try out Upgrade Advisory and use it before upgrading • Your experience should be good

More Related