SQL Server Database Migration Checklist

Are you migrating your SQL Servers?

At some point of time we have to migrate old version of database server to new version of database server because of technology and feature enhancements, business requirements changes or hardware upgradation etc.

Migrating a database is very critical process, At the time of migrating databases, there are chances that we can miss some important steps, for example trustworthy property, dependent jobs, linked server, logins etc. If something goes wrong then it’s very tough job to rollback things and restore existing environment again.

SQL Server Database Migration Checklist

Here is a quick checklist for your SQL Server Migration.

Pre-Migration Checklist

1. Run upgrade advisor on existing database to check the compatibility and deprecated features with respect to database and applications and make the required changes accordingly.
2. Note down data file and log file locations and size and make sure new server has enough disk space available.
3. Note down database recovery model, collation type and database facets.
4. Note down compatibility level, database owner, linked server, full text catalogs and trustworthy settings details.
5. Always keep more than one copy of latest database backup if it is feasible and you have enough storage available.
6. Note down information regarding database logins, users and orphan users and permissions.
7. Take a copy of all SSIS packages and config files and note down disk locations for files to move.
8. Generate scripts for all SQL Server agent jobs.
9. Note down all existing database maintenance plan and its properties.
10. Generate all the SQL Server logins and keep it safe to deploy on new server.
11. Note down windows logins and groups and permissions if any.
12. Check if any Disaster recovery or high availability settings are available and make a note of that.

You can start migration once you are ready with pre-migration check points.

Migration Checklist

1. Make sure you stop all applications services connected with database.
2. Set database to read only mode if required.
3. Take the latest backup of databases.
4. Restore latest copy of database on new server.
5. Check and change the database compatibility level after restore.
6. Migrate all the user logins and windows logins to new server.
7. Check the database properties and alter it accordingly if required.
8. Enable trustworthy database setting if required or keep it as default.
9. Verify the orphan users and fix the same.
10. Execute DBCC UPDATEUSAGE command to correct pages and row counts on migrated database.
11. Execute DBCC CHECKDB on new migrated database to check the integrity of the objects.
12. It is very important to rebuild all indexes on newly migrated database else you will face performance degradation while running the applications.
13. Make required changes at application pointer to database and other connectivity settings.
14. Update statistics on migrated database tables.
15. Recompile all stored procedures, functions and triggers with sp_recompile.
16. Refresh all the views available in the migrated database with sp_refershview.
17. Deploy your high availability or disaster recovery plans if any on new database.
18. Now test the application and correct the errors if any else celebrate success.

Click here to see: The DBA Daily Checklist

<<Click here to see all posts>>

If you found any of the information on this page helpful in anyway then please consider sharing this content with your favorite social network or by leaving your thoughts in the comment section. Thanks!

Leave a Reply

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

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.