Monday, September 14, 2009

Upgrading from SQL 2000 to SQL 2008

A Practical Approach

  • Greg Low
  • Managing Director and Mentor – SolidQ Australia
  • MVP SQL
  • DAT 305
Expectations – Goals
  • 2008 Upgrade Options
  • Methodology tools and planning
  • Technical and non-technical considerations
  • Common issues, myths and mistakes
What have you already got?
  • Hardware and infrastructure review
  • Application and database domain
  • Features and editions
Upgrade Advisor
  • Analyses: Databases, Trace Files, Scripts
  • Reports on: Blocking issues, pre-upgrade issues; post-upgrade issues
  • Suggests how to fix/work around
Reporting Services
  • In-place upgrade supported for 2005 and 2000 p2
  • No upgrade support for
    • Report Server that users a remote 2000 database
    • 2000 Report Server Web Service – endpoint de-supported
    • Earlier versions of WMI provider
    • Anonymous authentication
Notification Services
  • Basically gone
  • SQL Server 2008 Feature Pack migrated
Data Transformation Services
  • Simple in-place upgrade for 2000 and 2005
  • DTS packages are retained – Need to run DTS Package Migration Wizard
  • No 64-bit design/run time for DTS packages
  • No 32-bit design/run time support for DTS packages on Itanium based systems
  • Consider 64 bit driver issues

Considerations

  • Upgrade Motivations
  • Stakeholder Considerations
  • Prepare the Environment
  • Locate Dependencies
  • Third-party dependencies
  • Verification Tests
  • Mismatched SIDs (Demo)

The Upgrade

  • Prepare
  • Execute
  • Post-Upgrade tasks

Some common upgrade issues

  • *=
  • System table access
  • Service master key not backed up
  • Forgetting to re-enable DAC
  • Poorer performance after upgrade
  • Changes to log shipping from 2000
  • TOP 100 PERCENT
  • tempdb sizing
  • Avoid staying in order dbcompat levels!

Summary

  • Upgrades are conceptually simple
  • Easy to make human errors
  • No one right way
  • Application Compatibility Testing highly recommended
  • A tested rollback plan is critical
  • Lots of resources
  • Feedback to Microsoft via Connect will drive improvements in product and upgrade process/tools

No comments:

Post a Comment