What all you need to consider for SQL Server Hardware Migration?

Leave a comment (0) Go to comments

I was being contacted by one of my existing customer to move his existing dedicated SQL Server to upgraded hardware. Just to heads up, he asked me,

What all you need to consider for SQL Server Hardware Migration?

Here is a quick checklist for your SQL Server Migration.

  • Build your New Server, Install SQL Server and required updates and keep the server ready for migration. Do check what are current configuration and what is optimized for new Server.
  • Stop Application Server / Service and make sure that the no application is connected to the Databases during the migration process.
  • Disable all the jobs.
  • Change database(s) from multi user mode to ‘SINGLE_USER’ mode
  • Take a FULL backup of all the User databases.
  • Move the backups to the destination server or a Shared location, then restore them to the appropriate drives on the destination.
  • Change the compatibility level of the databases (Optional) Do this if the applications connecting to these databases are independent of the database compatibility level.
  • Transfer logins using SSIS (Transfer Logins Task) or using "sp_help_revlogin"
  • Check for Orphaned Users in the databases and Fix them (if Any)
  • Update Usage on user restored databases
  • Update Stats on user restored databases
  • Re-Index or Re-Organize Indexes on user restored databases
  • Transfer Jobs using SSIS or manually create them
  • Build Maintenance plans (if Any) or create new
  • Recompile database objects (Optional)
  • Move or rebuild SSIS or DTS packages (if Any)
  • Create Alerts and Operators (if Any)
  • Setup High Availability Options (if Any Like Replication, Log Shipping, Mirroring) (this migration assumes that these solution are not implemented)
  • Change Application connection string to point to new Server and start the Application Service(s)
  • If the application uses any of the user defined error messages, you may need to copy them to the new server (sys messages)
  • Test the Application and mar this project as completed.

Note : If the backup and restore taken more time which is not accepted to business, look for more options like log shipping / mirroring etc.

EOF - What all you need to consider for SQL Server Hardware Migration?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.