Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Migrating a SQL Server Instance

MSSQLTips author Jeremy Kadlec By:   |   Read Comments   |   Related Tips: More > 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.
  • 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.
  • 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.


Last Update: 5/17/2007


About the author
MSSQLTips author Jeremy Kadlec
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.