Migration of SQL Server from one Server to another server with less downtime:-

Migration of SQL Server from one Server to another server with less downtime:-

As newer versions of SQL Server are released, we will plan implement to upgrade to higher versions and during this process, we may also upgrade to new  servers with better hardware and latest Operating System. Also, as part of data center relocation, we migrate the SQL Server instance from one server to another server in new data center. The process of migration involves lot of steps and need to plan and implement the steps so that, at end of migration, we have SQL Server instance running without any problems.

This article covers a scenario where we want to migrate SQL Server instance from one server to another server. This is applicable for scenarios where we are migrating between servers which have same versions of SQL Server installed or new servers having higher version of SQL Server. Goal of the migration is that there should be  minimal downtime when we switch the applications from old server to new server.

Below are the high level steps involved in migration.

1)  we need to perform these steps on test server, so that we can find the issues that can arise during the migration before on production itself and also to test if the applications work without any problems after migration.

2) If we are migrating to a higher version of SQL Server, then need to document the issues that can arise with the migration by running Upgrade Adviser and prepare documentation with remediation steps to fix those issues as part of the migration. If we are migrating to new server with same version of SQL Server, then no requirement of upgrade adviser.

3) Install the required version of SQL Server instance on the new server.
4) Apply latest Service Pack or Cumulative Updates.
5) Script logins from existing server by using revlogin script  and Create those logins on the new server.
6)  Script all the jobs on existing server and create them on the new server, keep them disabled. Create maintenance plans on new server similar to existing server.
7) Configure Database Mail if used.
8) Create any linked servers if required.
9) Configure SQL Server settings like Max Server Memory, MaxDrop, backup compression settings, etc on the new server.
10) Configure OS or Network settings on new server like Lock Pages In Memory, Perform Volume Maintenance Tasks, etc.
11)  Backup all User databases on existing server, copy the backups to new server and restore them. Document the time it took for performing backups, copying backups to new server and for restoring of databases.
12) Fix Orphan Users for all user databases.
13) Enable backup and maintenance jobs like rebuild index, Update stats, etc on new server.
14) Change the compatibility level of user databases to latest version(If migrating to higher version.
15) Perform Application testing to make sure the application is working without any problems and performance is acceptable.
16)Once, everything looks fine and got permission to move ahead, plan for the Go-Live of new server.

Go-Live Planning
– Our goal here is to have minimum downtime during the Go-Live process, so we need to either setup Logshipping or Database Mirroring between existing and new servers.

– Configure Logshipping from existing server to new server for all user databases and make sure backup, copy and restore jobs are running without any issue.
– Before Go-Live, make sure Logins and jobs are same on both the existing and new servers.
Start of Down Time
– Disable all jobs on existing server.
– Run logshipping backup jobs on existing server, once it completes successfully, disabled the job.
– Run logshipping copy jobs on new server, once it completes successfully, disabled the job.
– Run logshipping restore jobs on new server, once it completes successfully, disabled the job.
– Remove the logshipping configuration between existing and new server.
– Bring the all user databases online on new server by running “restore database databasename with recovery”.
– Enable all required SQL Server Agent jobs on new server and disable the same on old server
– Fix the orphan users.
– Change the compatibility level of all user databases to latest version(If migrating to higher version).
– Point the application to use the new server.
End of Down Time
– Test the Application to make sure it is working as expected.
– Later run rebuild indexes and Update Statistics on all user databases.
– If there are any issues, try possibilities of fixing those issue, if it is taking time and decided to rollback, refer below RollBack plan.
– If no rollabck required, take SQL Services offline on old server and decommission the old server.

RollBack Plan
– Verify all the user Databases are online on old server.
– Point the application back to old server.
– Enable all required jobs on old server and disable the same on new server.
– Test the application.

You can also use database mirroring instead of logshipping, just replace the steps related to logshipping with database mirroring related steps.

Hope this was helpful.