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.
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.
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.
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.
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.
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.
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.
Rate this Tip
To rate tips, you must be a member of SearchSystemsChannel.com. Register now
to start rating these tips. Log in if you are already a member.
DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
TechTarget provides enterprise IT professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective IT purchase decisions and managing their organizations' IT projects - with its network of technology-specific Web sites, events and magazines.