Upgrading to SQL Server 2008

 

This session was pretty good – primarily just touched on the stability of the current 2008 version of SQL Server, encouraging people to play with prebuilt VPCs before upgrading and even testing upgrades as they can.  Below are highlights of the upgrade discussion.

 

In-Place Upgrade

• Instance name remains the same after upgrade
  • Existing instance is overwritten post-upgrade
• pros
  • easier, mostly automated
  • generally fast overall process
  • system data upgraded
  • may require no additional hardware
  • apps remain pointing to same server/database name
• cons
  • less granular control over upgrade process
  • instance remains offline during part of upgrade
  • not best practice for all components
  • complex rollback strategy (you’ve got to build a new box and restore backups, etc. … this will not be quick)

Side-by-side (migrate) Upgrade

• Install new instance of SQL Server
• Pros
  • more granular control over upgrade process (at the database level)
  • can be used to perform test migration (document process & gather metrics)
  • ability to run systems side-by-side for parallel runs
  • relatively straightforward rollback strategy
  • can leverage failover/switchover to reduce downtime
• Cons
  • usually require additional hardware
  • server/database name changes (other ways to solve this problem, but will just take extra work)
  • not practical for VLDB (multiple terabytes) unless utilizing SAN

Other Core Components

• Analysis Services
  • Simple in-place upgrade for 2005
  • Side-by-side upgrade for 2000
• Reporting Services
  • In-place upgrade supported for 2005 and 2000, sp2.
  • No upgrade support for:
    • Report Server that uses a remote 2000 database
    • 2000 report server web service – endpoint de-supported
    • earlier versions of WMI provider
• DTS
  • Simple in-place upgrade for 2000 and 2005
  • DTS packages are retained (need to run DTS Package Migration Wizard to migrate to SSIS format)
  • No 64-bit design/run time for DTS packages
  • No 32-bit design/run time support for DTS packages on Itanium based systems
  • Really, REALLY, consider re-writing or migrating DTS code to SSIS
    • 3rd party tools can be helpful (e.g. DTSXchange.com)