SQL Server Migration Plan

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

premigration

migration

post

If you like the post then hit like !!!

suggestions  are welcome

Advertisements

3 thoughts on “SQL Server Migration Plan

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s