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