SQL Server 2005 migration methods

Consider the following common SQL Server 2005 migration methods before determining which path you'll take to upgrade a customer's SQL Server environment.

SQL Server 2005 Migration Options
ID Component Description Additional Information
1a Database Engine and all objects In Place Upgrade: Upgrading from SQL Server 2000 to 2005 can be performed on the same server during the installation process. How to: Upgrade to SQL Server 2005 (Setup)

Upgrading the Database Engine
1b Database(s) and all objects Copy Database Wizard: With the database in single-user mode, a database can be moved from one SQL Server to the next. How to: Upgrade to SQL Server 2005 with the Copy Database Wizard
1c Database(s) and all objects Detach and Attach Database: Ability to issue the systems stored procedures to detach the database from a SQL Server 7.0 or 2000 machine, copy the database and log files and the attached database in the SQL Server 2005 environment. How to: Upgrade a Database Using Detach and Attach (Transact-SQL)
1d Database(s) and all objects Backup and Restore: Backing up from SQL Server 2000 and restoring to SQL Server 2005 is a reliable and trusted way to perform the upgrade and let the relational engine upgrade during the restore process. Backup Command

Restore Command
1e Database(s) and all objects DDL and DML T-SQL scripts: Database objects can be scripted directly from SQL Server and then executed in the destination system to perform the upgrade process....

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Database Deployment and Upgrade Projects The changing database consolidation landscape Maintaining performance after a database consolidation Refining plans for database consolidation Cleaning up with database consolidation services Oracle Database 11g innovations: Get ahead of the game Remote DBA services: Overcoming sales hurdles Computer information systems for small and midsized businesses (SMBs) Web-based database support DBA services can make VARs money Architectural solutions to Web-based applications with large datasets for VARs SQL Server Upgrade Microsoft SQL Server migration to SQL Server 2005 SQL Server 2005: Server upgrade requirements SQL Server upgrade: Moving to SQL Server 2005 Database Management Services SQL Server 2008 Reporting Services for high-availability deployment SQL Server 2008 Reporting Services for Internet deployment Key features in SQL Server 2008 Reporting Services editions SQL Server 2008 hardware and software requirements Optimizing SQL Server 2008 performance SQL Server 2008 features study guide How to automate database integration Using continuous database integration and a database 'sandbox' Troubleshooting a failed SQL Server 2008 installation Microsoft SQL Server 2008 guide

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

How to: Generate a Script (SQL Server Management Studio)
1f Data SQL Server Integration Services (SSIS) Import and Export Wizard: SSIS' wizard offers an easy means to point and click to move data. Creating Packages Using the SQL Server Import and Export Wizard
2 Analysis Services Analysis Services Migration Wizard: This wizard walks you through the instance and database-level upgrade process. Upgrading or Migrating Analysis Services

How to: Upgrade an Instance of Analysis Services

How to: Migrate Analysis Services Databases
3 Reporting Services Run the setup process to upgrade each Reporting Services component. Upgrading a Default Installation of Reporting Services
4 ETL Code SQL Server Integration Services: Upgrade during the SQL Server Engine installation process.

Options are available to execute the DTS Packages natively in a backward compatibility mode, build SSIS Packages to call the DTS Packages or migrate the functionality from DTS to SSIS. Upgrading or Migrating Data Transformation Services

How to: Upgrade from Data Transformation Services to Integration Services

5 Notification Services nscontrol: Leverage nscontrol at the command prompt in tandem with the SQL Server 2005 setup application to complete the upgrade. How to: Migrate Notification Services 2.0 Instances to SQL Server 2005 (SQL Server Management Studio)

6 Replication Based on the types and amount of customization, the configurations can be maintained with the same server and database names or customized based on the environment. How to: Upgrade Replication Scripts (Replication Transact-SQL Programming)

Upgrading Replicated Databases

7 External scripts Review and execute in the test environment to validate the functionality. N/A
8 Middle-tier and front-end applications Review and execute in the test environment to validate the functionality. N/A
9 Third-party tools Contact the vendor for the product upgrade path. N/A
10 New application development Build a new application to support the application needs rather than upgrade the current application. N/A

About the author: Jeremy Kadlec is the Principal Database Engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server Users Groups and nationally at SQL PASS. Jeremy is also SearchSQLServer.com's Performance Tuning expert. Ask him a question.