1 / 47

Upgrading to SQL Server 2005 Data platform and BI

Upgrading to SQL Server 2005 Data platform and BI. Yair Rozilio Manager, Microsoft Databases Team SRL Group yairr@srl.co.il. SRL Group. Employs over 150 professionals. Hundreds of customers of all sectors. Top technologies adapters. Excellence driven Professional Services Division

bill
Download Presentation

Upgrading to SQL Server 2005 Data platform and BI

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. Upgrading to SQL Server 2005Data platform and BI Yair Rozilio Manager, Microsoft Databases Team SRL Groupyairr@srl.co.il

  2. SRL Group • Employs over 150 professionals. • Hundreds of customers of all sectors. • Top technologies adapters. • Excellence driven Professional Services Division Consultants expertise along with project management E2E at some of the leading technologies: • Team Foundation Server • SQL Server • .NET • BizTalk Server

  3. What is the first thing that you think when you need to upgrade?

  4. All you need is… Find your way Motivation Plan ahead

  5. Agenda • Why Upgrade? • What can be upgraded or migrated? • Upgrade or Migrate ? • Upgrade & Migration Tools • Components Upgrade • Upgrade Plan • Q&A

  6. 10 good reasons to upgrade • Service Pack 1 is here ! • 5 years of Development ! • New Management and Development Tools • New Development Features • New High Availability Features • New Security Enhancements • New ETL platform - Integration Services • New Business Intelligence Features • Upgrade brings immediate improvements • Easy to upgrade

  7. Agenda • Why Upgrade? • What can be upgraded or migrated? • Upgrade or Migrate ? • Upgrade & Migration Tools • Components Upgrade • Upgrade Plan • Q&A

  8. What can be upgraded or migrated? Editions Versions Platforms Components DTS Enterprise SQL Server 2000 Service Pack 3a+ 32bit Notification Services Developer Standard Reporting Services Analysis Services Workgroup 64bit SQL Server 7.0 Service Pack 4 Personal Database Engine Desktop

  9. Agenda • Why Upgrade? • What can be upgraded or migrated? • Upgrade or Migrate ? • Upgrade & Migration Tools • Components Upgrade • Upgrade Plan • Q&A

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

  11. SQL Server 2005 Instance: SQL1 SQL Server 7.0/2000 Instance : SQL1 In-place Upgrade Upgrade

  12. Side-by-Side Migration SQL Server 2005 Instance: SQL2 SQL Server 7.0/2000 Instance: SQL1 Migration Compare& Verify Verified!

  13. In-place Upgrade vs. Migration Before After SQL Server 2005 SQL Server 7.0/2000 In-place Upgrade Upgrade SQL Server 7.0/2000 SQL Server 7.0/2000 Side-by-side Migration Migration SQL Server 2005

  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

  15. 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 • Cons • Applications need to be directed to new instance • May require additional hardware resources

  16. Agenda • Why Upgrade? • What can be upgraded or migrated? • Upgrade or Migrate ? • Upgrade & Migration Tools • Components Upgrade • Upgrade Plan • Q&A

  17. Upgrade & Migration Tools • Setup.exe • Migration Wizards • Upgrade Advisor

  18. Upgrade Using Setup.exe • Setup.exe is the in-place upgrade tool • Database Engine • Analysis Services • Reporting Services • Setup will recognize the installed instance exists and initiate the upgrade

  19. Migration Wizards • Wizards for side-by-side migration • Analysis Services migration Wizard • Package migration Wizard • Copy Database Wizard • Preserve prior version structures • Wizards are invoked through SQL Server 2005 Tools or Migration executables

  20. Upgrade Advisor Tool • Improves the upgrade experience • Analyzes live servers, scripts and trace files • Describes how to fix or work around issues • Provides a report for detected issues need to be fixed . • Describes how to fix or work around issues • Links to documentation for additional content • Performs read-only operation

  21. Upgrade Advisor

  22. Agenda • Why Upgrade? • What can be upgraded or migrated? • Upgrade or Migrate ? • Upgrade & Migration Tools • Components Upgrade • Upgrade Plan • Q&A

  23. Upgrade & Migration Databases • Setup.exe performs the in-place upgrade for database engine • Several migration techniques : • Detach/Attach • Backup/Restore • Copy Database Wizard, DTS • Manual scripts, BCP

  24. Databases Migration SQL 2000 SQL 2005 Create new Database Detach Backup Restore Attach __ SSIS/DTS/BCP Upgrade Complexity: Low

  25. Copy Database Wizard

  26. Upgrading Analysis Services • Choose one of the upgrade techniques • Setup.exe for in-place upgrade • Migration • Redesign from scratch Upgrade Complexity: Medium

  27. Upgrading Analysis Services Analysis Services 2000 Default Instance SSAS 2005 Default Instance Instance Rename tool (asinstancerename.exe) SSAS 2005 New Instance InstallAnalysis Services 2005 Process Uninstall Migration Wizard Verify

  28. Upgrading Reporting Services • Reporting Services 2005 supports both upgrade and migration from Reporting Services 2000 • Migration tool • Reporting Services Configuration tool Upgrade Complexity: Low

  29. Upgrading DTS to SSIS • DTS Packages will continue to run alongside with Integration Services (SSIS) • Packages can be migrated to SSIS via DTS Migration Wizard • Migration wizard copies DTS packages and then re-creates them in SSIS • Migration is notperfect -- “Best Effort” migration • Redesign is a better option! Upgrade Complexity: Medium

  30. Upgrading Log Shipping • Do not upgrade log shipping configuration to SQL Server 2005 • Database Maintenance Plan Wizard is not used in SQL Server 2005 Log Shipping • Migrating with failover • Migrating without failover Upgrade Complexity: Medium

  31. Upgrading Log Shipping Migrating with failover Server A Server B Fail over Configure log shipping on Server B Upgrade Upgrade SQL Server 2000 SQL Server 2000 SQL Server 2005 SQL Server 2005

  32. …More Upgrade issues • Upgrading Replication • Upgrading Failover Cluster • Upgrading maintenance plans

  33. Agenda • Why Upgrade? • What can be upgraded or migrated? • Upgrade or Migrate ? • Upgrade & Migration Tools • Components Upgrade • Upgrade Plan • Q&A

  34. Upgrade Plan Devise an upgrade plan by breaking down upgrade tasks into : • Pre-Upgrade tasks • Upgrade execution tasks • Post-upgrade tasks

  35. Pre-Upgrade tasks • Review hardware and software requirements • Review SQL Server backward compatibility • Get an inventory of your applications • SQL Server 2005 Education for developers and DBA • Learn about upgrading to SQL Server 2005 Choose your upgrade path

  36. Pre-Upgrade tasksTesting, Planning, and Validation • Prepare test environment • Set a pre-upgrade baseline • Develop a test plan • Develop a recovery plan • Create application-modification procedures • Run upgrade advisor • Perform an upgrade test run

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

  38. Upgrade Execution tasks • Check database consistency with DBCC • Back up old instance and Verify the backup • Perform pre-upgrade changes • Perform SQL Server component upgrade • Make primary application and client changes where necessary • Execute your upgrade testing • Be prepared with recovery plan

  39. Pre-upgrade SQL Server 2005 Setup In-place Upgrade Execution Post-upgrade Upgrade Advisor

  40. Post-upgrade • Perform post-upgrade tasks on Upgrade Advisor report • Example: Update statistics, build cubes • DTS to SSIS migration • Change database COMPATIBLITY level • Reconfigure log shipping • Run upgrade tests • Verify Agent jobs and maintenance tasks • Bring system online • Monitor system activity

  41. Agenda • Why Upgrade? • What can be upgraded or migrated? • Upgrade or Migrate ? • Upgrade & Migration Tools • Components Upgrade • Upgrade Plan • Q&A

  42. Resources • SQL Server 2005 Upgrade Handbook http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx • Advantages of a 64-bit Environment http://www.microsoft.com/sql/techinfo/whitepapers/advantages-64bit-environment.mspx • Microsoft SQL Server 2005 Upgrade Advisor http://www.microsoft.com/downloads/details.aspx?familyid=451FBF81-AB07-4CCB-A18B-DA38F6BCF484&displaylang=en • Upgrade SQL Server 2005 Failover Cluster http://msdn2.microsoft.com/en-US/library/ms191295.aspx

  43. איך ממלאים משוב? ב - email בסוף כל יום ב -Beat Center מה מקבלים? חולצת Feel The Beat השתתפות בהגרלת כרטיסי טיסה מכשירי i-mate ועוד...(לממלאים משוב לכל יום) New York! New York!

  44. rainbow & stars אחד הקברטים היוקרתיים האלגנטיים והיקרים בעיר קומה 65 ברוקפלר פלאזה.

  45. Summary “It is not the strongest species that survive, nor the most intelligent ,but the ones most responsive to change.“ Charles Darwin

  46. Yair Rozilio SRL Group yairr@srl.co.il

More Related