::: Welcome To OCS Infotech

Sitemap

Mail

Why OCS

.Net Competency Center

Business Intelligence Services

Hospitality Practice

Enterprise Infrastructure Solutions

Case Studies

Resource Centre

Home > Resource Centre > SQL Server 2005 Migration- Our Understanding:

SQL Server 2005 Migration- Our Understanding:

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

Upgrade Advisor

This is a tool developed by Microsoft for upgrading the Microsoft SQL Server 2000/7.0 to SQL Server 2005. This tool analyzes the configuration of the existing database server, services, and applications and provides reports that identify changes within the SQL Server product that can affect the upgrade. These changes include security enhancements, closer adherence to the SQL standard compared to previous SQL Server versions, and architectural changes. The Upgrade Advisor also provides links to documentation that describe these changes and necessary steps to complete the upgrade process. The Upgrade Advisor can help administrators manage the changes between releases, improve upgrade planning, and minimize any problems after the upgrade has completed.

Whether enterprises are running Analysis Services for business intelligence, DTS for data processing, Notification Services for alerting, Reporting Services for enterprise reporting, or a combination of components, the Upgrade Advisor can help all.

Upgrade Process

Likewise any migration project, OCS treats this activity also as a project. The migration project is mainly divided into three phases

Planning

Here we identify the impact of the migration process, on the data, on the existing application (application connection strings and other connection references), on the hardware. Based on the gathered information we derive an estimate required to execute the project. The estimation will be prepared based on the WBS prepared for the project (starting from the broad level task to the smallest task). The estimates will be shared with the Client. Based on the client approval a detailed project plan will be arrived at keeping in mind the timelines expectations of the customer. The findings will be documented in the form of a gap document.

Execution

This phase consists of executing the migration/upgrade process in a test bed environment. The application using the underlying database needs to be tested to ensure that it continues to work as designed. The test scripts (either existing one or created new) will be executed to confirm that they are working as per design (including data processing, front-end and report usage, and other application components). Similarly, the user acceptance testing will be conducted on the

Production Rollout

In this phase the production database is affected. The users will be notified, the user interface components will be disabled, pausing all data processing, data entry, and data changes. Make the necessary pre-upgrade changes identified during testing. The systems (applications and databases) will be backed-up on permanent media. A consistency check will be performed (if applicable); database and related systems will be backed up and validated.

The database will be migrated as per the process identified during planning phase and modified during Execute phase. The application will be tested as per the test scripts.

Migration- Our Conclusion

Managing the upgrade to Microsoft SQL Server 2005 requires significant planning and testing. With appropriate forethought and preparation-and use of the SQL Server 2005 Upgrade Advisor and Setup wizard- we can avoid problems and identify the areas where they need to concentrate our efforts. After performing a smooth upgrade, the client can be ready to fully leverage the power and functionality that SQL Server 2005 is designed to provide.