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.

1. Pre-migration checks
2. Actual DB migration and setup (Isolation of the DB, migration of jobs & logins)
3. Post-migration consistency and connectivity checks.

Pre-Migration phase

Pre-Migration Checklist (Source Server)

The following constraints / features shall be checked / noted down:

1. Database sizes.
2. Data and Log file location.
3. Server and Database properties (Collation, Auto Stats, DB Owner, Recovery Model, Compatibility level,     Trustworthy option etc)
4. Collect the information of dependent applications, make sure application services will be stopped during     the database migration
5. Database logins, users and their permissions.
6. Dependent objects (SQL Agent Jobs and Linked Servers)
7. Maintenance plans.

Pre-Migration Checklist (Destination Server)

Analogous to the above checklist, we shall check / create the following:

1. Adequate Disk space on the server.
2. Correct destination folders are created.
3. SQL Server is correctly installed and configured as per requirement.
4. Connectivity to the application servers and linked servers.

Migration Phase

 Steps to be performed on the Source Server

1. Isolate Source server from all application and linked servers.
2. The Database(s) from the source server are backed up with password to ensure secure movement of         the data.
3. Script out all Jobs, Linked Servers, Logins and Users.
4. The Databases may now be put into Read-Only mode if required.

Steps to be performed on the Destination Server

1. Transfer the backup to the desired location.
2. Restore the database ensuring that the data and log files are placed in the correct location.
3. Recreate the Logins and User. Resolve Orphan User issues.
4. Re-establish Linked Servers and check any FTP Locations that are to be accessed.
5. Recreate the Jobs and Maintenance plans
6. Perform consistency checks and update index stats.

 Post Migration Phase

1. Point the application to the new DB server IP (Connection string etc to altered by the application                 support team)
2. Restart Network connections between all stake holding servers (Network Team)
3. Check the SQL Server Error Log and Windows Error logs for any failures.
4. Confirm application functionality with end users.

Schematic Diagram

Pre – Migration Phase

If you like the post then hit like !!!

suggestions  are welcome