SQL SERVER DBA, Linux and Azure: SQL Server Database migration checklist

SQL Server Database migration checklist

Pre Migration Checklist 

  • Analyze the disk space of the target server for the new database.
  • Confirm the data and log file location for the target server.
  • Collect the information about the Database properties (Auto Stats, DB Owner, Recovery Model, Compatibility level,etc).
  • Collect the information of dependent applications, make sure application services will be stopped during the database migration.
  • Collect the information of database logins, users and their permissions.
  • Check the database for the Orphan users if any.
  • Check the SQL Server for any dependent objects (SQL Agent Jobs and Linked Servers).
  • Check, if the database is part of any maintenance plan.

Database Migration Checklist

  • Stop the application services.
  • Change the database to single user mode.
  • Take the latest backup of all the databases involved in migration.
  • Stop the SQL Services on live server.
  • Copy the backup from live to destination server.
  • Restore the databases on the target server on the appropriate drives.
  • Cross check the database properties as per pre-migration checklist output.
  • Execute the output of Login transfer script on the target server, to create logins on the target server.
  • Check for Orphan Users and Fix Orphan Users. 
  • Execute DBCC UPDATEUSAGE on the restored database.
  • Rebuild Indexes ,As per the requirement.
  • Update statistics.
  • Recompile procedures.
  • Configure Full backup, Log backup, integrity check, rebuild index jobs.

Post Migration Checklist

  • Check the integrity of database.
  • Start the application services, check the application functionality.
  • Check the SQL Server Error Log for login failures and other errors

No comments:

Post a Comment