Understanding SQL Server Migration & Upgradation

Database Migration / Upgrade in SQL Server:


Microsoft has performed extensive enhancements on SQL Server 2005 product suite. The product is introduced in market not only as a RDBMS that meets new generation database needs but also as an advanced BI technology product.  Although DTS and SSIS are both ETL tools, their architectures diverge greatly. The advanced features that are introduced in the new product suites are:

  • Enterprise Performance
  • High Availability
  • Manageability
  • Security
  • Developer Productivity
  • Advanced Business Intelligence
  • Competitive features

This article explores the migration/upgrade path available for companies wanting to migrate to SQL Server 2005 from SQL Server 2000 or SQL Server 7.0. It also discusses the new SQL Server 2005 Upgrade Advisor tool that Microsoft has developed for the upgradation.

Upgrade Mechanism


The basic difference between upgrade and migration is that, an upgrade is an automated process in which a set-up program moves an old instance of the database to a new instance, keeping the metadata (with some exception) and data same. Migration is a manual process, where the old and the new instances resides at the same time and are helpful to verify correctness of the migration. Upgrading the database engine from older version of SQL Server to SQL Server 2005 is done easily using a set-up wizard. Moving the data from Data Transformation Service (DTS) to SSIS requires a migration. Following table summarizes the upgrade path for each component of SQL Server 2005.


SQL Server 2005 Component Upgrade/Migration Path 
Database engine Upgrade Tool: Setup
Migration Method: Side-by-side installation, then database backup/restore, detach/attach
Analysis Service Upgrade Tool: Setup
Migration Tool: Migration Wizard migrates objects, requires optimization and client provider upgrades
Integration Service Upgrade Tool: None
Migration Tool: DTS Migration Wizard
Migration Method: Migration Wizard converts 50-70 percent of tasks, requires some manual migration; runtime DTS DLLs available in SSIS; package re-architecture is recommended
Reporting Service Upgrade Tool: Setup
Migration Method: Side-by-side installation and deployment of reports on new instance
Notification Service Upgrade Tool: None
Migration Tool: Upgrade of Notification Services instances during install



2 responses

  1. Nice job! Really love your site! Full of information. Thanks so much!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: