By: Jeremy Kadlec | Updated: 2007-05-17 | Comments (3) | Related: > Upgrades and Migrations
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.
- Test Backup - Backup a subset of the user defined databases to test on another machine (development, your desktop, etc.).
- Test Restore - Restore the user defined database on another machine (development, your desktop, etc.).
- Shrink Databases - Shrink the user defined databases before starting the backup process.
- New SQL Server - Setup the new SQL Server ahead of time to minimize the amount of steps that need to be taken during the downtime.
- Preliminary Steps - Depending on the situation, execute as many of the steps as possible on the new SQL Server ahead of time to minimize the downtime needed.
Migration Steps
- Review - Since the original issue, review the Windows, SQL Server, Application, etc. error logs to ensure no unexpected errors have occurred.
- Read-only Mode - Set the user defined databases to read-only mode.
- Online Database Backups - Perform SQL Server online backups and verify the backups.
- Simple script to backup all databases
- **** YOU WILL NEED TO MODIFY THIS SCRIPT TO INCLUDE THE SYSTEM DATABASES (MASTER, MODEL, MSDB) ***
- Simple script to backup all databases
- Verifying Backups - Verifying Backups with the RESTORE VERIFYONLY Statement.
- Shut down SQL Server - Shut down the SQL Server Services.
- File system backup - Perform a Windows file system backup.
- Windows Configurations - Make the needed machine name or DNS changes.
- Old SQL Server - May need to put the original machine in a work group, change the machine name, IP address and reboot.
- New SQL Server - Enter the machine name, domain, IP address and reboot.
- DNS - Validate the entries are correct.
- Copy Process - Move the backups to the new SQL Server.
- Database Restore - Restore the user defined databases to the new SQL Server.
- If you need to move the databases to another volume, reference this tip - System Database (Master, Model, MSDB) Restore
- User Defined Database Restore
- Validate your databases are not in read-only mode - http://msdn2.microsoft.com/en-us/library/aa933268(SQL.80).aspx
- 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.
Next Steps
- 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.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2007-05-17
