As a DBA , migration of database is a part of day to day activities. sometime its a quite hectic task if we don’t have a proper guidelines or checklist.
here is something useful.
SQL Server Migration Plan
The migration plan would be executed in 3 phases.
- Pre-migration checks
- Actual DB migration and setup (Isolation of the DB, migration of jobs & logins)
- Post-migration consistency and connectivity checks.
Pre-Migration Checklist (Source Server)
The following constraints / features shall be checked / noted down:
- Database sizes.
- Data and Log file location.
- Server and Database properties (Collation, Auto Stats, DB Owner, Recovery Model, Compatibility level, Trustworthy option etc)
- Collect the information of dependent applications, make sure application services will be stopped during the database migration
- Database logins, users and their permissions.
- Dependent objects (SQL Agent Jobs and Linked Servers)
- Maintenance plans.
Pre-Migration Checklist (Destination Server)
Analogous to the above checklist, we shall check / create the following:
- Adequate Disk space on the server.
- Correct destination folders are created.
- SQL Server is correctly installed and configured as per requirement.
- Connectivity to the application servers and linked servers.
Steps to be performed on the Source Server
- Isolate Source server from all application and linked servers.
- The Database(s) from the source server are backed up with password to ensure secure movement of the data.
- Script out all Jobs, Linked Servers, Logins and Users.
- The Databases may now be put into Read-Only mode if required.
Steps to be performed on the Destination Server
- Transfer the backup to the desired location.
- Restore the database ensuring that the data and log files are placed in the correct location.
- Recreate the Logins and User. Resolve Orphan User issues.
- Re-establish Linked Servers and check any FTP Locations that are to be accessed.
- Recreate the Jobs and Maintenance plans
- Perform consistency checks and update index stats.
Post Migration Phase
- Point the application to the new DB server IP (Connection string etc to altered by the application support team)
- Restart Network connections between all stake holding servers (Network Team)
- Check the SQL Server Error Log and Windows Error logs for any failures.
- Confirm application functionality with end users.
Pre – Migration Phase
If you like the post then hit like !!!
suggestions are welcome