Problem I need to migrate an instance of SQL Server 2000 due to a hardware issue that I am having. What are the steps that I should take before, during and after the process? What pit falls should I watch out for?
Solution Let's walk through the general steps to migrate a SQL Server 2000 instance. Based on your environment and the specific circumstances, a different set of steps or additional steps may be needed, but below outlines the general steps that are needed:
Preliminary Migration Steps
Plan - Pull together a plan for all of the steps that you need to take, who is responsible for individual tasks, general time frames, start times, end times, etc.
Notification - Notify the users of the issue and the general amount of downtime that will be needed directly and via your change management process.
Team - Determine the team members to perform the migration or determine if you are going to fly solo. One item that should not be overlooked is application testing. If you cannot perform this task, see who on the team would be best suited for this critical piece of the process.
Hardware - Determine what hardware is needed or available for the migration. Depending on the uptime requirements for your application, consider doing some research to determine if the server will be able to support the load until the next downtime when the server can be replaced.
Review - Since the original issue, review the Windows, SQL Server, Application, etc. error logs to ensure no unexpected errors have occurred.
Installation Media - Find your Windows, SQL Server, third party and service pack media (CD's and DVD's).
Versions - Capture the Windows, SQL Server, application, etc. versions to be sure to install the same versions on the new machine.
SQL Server Validation - Validate the following items:
Restores - Databases are restored properly and no error messages are recorded in the SQL Server Error Log.
Security - Logins and users are mapped appropriately for both Windows and SQL Server Standard logins.
SQL Server Services - SQL Server services are using the correct domain account and the services are configured to auto start.
SQL Server Jobs - Validate that the Jobs are migrated properly and are referencing the correct SQL Server.
SQL Server DTS Packages - Validate that the DTS Packages have been migrated.
Connection String Changes - If the machine name is changed, then change the application connection strings.
Testing - Test your applications to validate they are working properly.
Production Promotion - Promote the SQL Server to the production responsibilities.
Notification - Notify the users that the SQL Server is back up and running.
Post Migration Steps
Monitor the SQL Server - Validate that operational and business needs are being met by checking backups, performance, SQL Server Error Logs, feedback from users, etc.
Lessons Learned - Inevitably you will run into a snag, record those items and learn from them for the next time you have to perform this sort of process.
If you have the luxury to test the migration process prior to performing the migration in production, take advantage of it and practice the migration. Note any special steps you need to take for your environment.
One potential opportunity to test the migration process is to use a virtual machine. This may be a simple and easy way to test the process and validate all of the steps taken are correct and nothing is missing.
The value in testing the process is to work out the kinks, get a practice migration under your belt before the real thing and to have a good understanding of the time needed to perform the migration.
In most cases, these sorts of migrations are executed under duress. Just keep your cool and 'check twice and click once' during all of the steps that you take.
If you also have the luxury to move to a new piece of hardware take advantage of it and try to keep the original server around for a week to 3 months to make sure you have not missed any code for month and quarter end processes.
Last Update: 5/17/2007
About the author
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.