Nisarg Upadhyay
Script out the SQL job of SQL Database

Move SQL databases to a different server using SQL Server log shipping

July 14, 2020 by

As a SQL Server DBA, we are responsible for moving the customer SQL databases to other servers. Recently, I was assigned the same project. One of the customers wanted to move their SQL database to a separate server. Following were the challenges:

  1. The database is very large; therefore, we cannot detach the database and copy it to another server and reattach it. It was a bad idea because it takes approx. 5 hours and it might block the network as well
  2. The size of the backup was large too. We tried to restore it on the test server, and the entire database restore process took approximately 3 hours

So, we decided to use the following approach:

  1. Script out all the SQL Jobs and recreate them on the new server
  2. Recreate all the logins on the new server
  3. Set up log shipping between the old server and the new server
  4. During maintenance hours, perform a switch over from the old server to the new server and disable the log shipping

In this article, I am going to explain how we can move the SQL Database to a different server using SQL Log shipping. To demonstrate the entire process, I have created a demo setup, and the details are as following:

Source database server

Destination database server

Host Name and IP Address

SQL01.DC.Local (192.168.0.131)

SQL02.DC.Local (192.168.0.132)

SQL Server version

SQL Server 2016

SQL Server 2016

We are going to move the AdventureWorks2017 database, so I have restored it on SQL01 (Old server). I have created two SQL logins named nisargupadhyay and niraliupadhyay on SQL01.

I have created SQL Jobs for database maintenance using the Ola Hallengren’s database maintenance solution. The stored procedures and tables used by the maintenance solutions are created on a separate database named DBA, so it must be restored on a new server before we script out the maintenance jobs.

First, we will script out the SQL Jobs.

Script out SQL Jobs and recreate them on the new server

To generate the scripts of the database maintenance Job, connect to the SQL01.DC.Local using the remote desktop connection -> Launch SQL Server Management Studio and connect to the SQL Server database engine. Once connected, expand SQL Server Agent -> Expand Jobs -> Right-click on DatabaseBackup – USER_DATABASES – FULL -> Hover on Script Job as -> hover on Create To -> Hover on New Query Editor Window. See the following image:

Script out the SQL job of SQL Database

Copy the T-SQL script to recreate the SQL job and execute it on the new server. Similarly, recreate other SQL jobs on a new server. Once the SQL Jobs are created, Let us create the required logins on the new server.

Recreate all the logins on the new server

First, we must script out all the logins. To do that, we can use sp_help_revlogin stored procedure. I have written an article that explains how we can use it to transfer the SQL Logins on the new server. You can check out this article, Transferring SQL Logins to the secondary replica using sp_help_revlogin.

As mentioned, I have created two logins on SQL01 (Old server). The sp_help_revlogin stored procedure generates the CREATE LOGIN script for them. Script is below:

Copy the entire script and execute it on the SQL02 (New Server).

Set up log shipping between the old server and the new server

The SQL Server log shipping is a widely used technology. Many articles have been written on it. So, I am not going to explain the process to set up the Log shipping. You can read the following articles to understand the deployment and monitoring of the SQL Server log shipping.

  1. How to configure SQL Server Log Shipping
  2. Monitor Transaction Log shipping using T-SQL and SSMS

I have already established a log shipping between the SQL01 (Old server) and the SQL02 (New server). To view the configuration, connect to SQL01 -> Expand Database -> Right-click on AdventureWorks2017 -> Select Properties. See the following image:

SQL Database properties

In Properties, click on Log Shipping. In the right pane, you can view the schedule of the transactional log backups, the list of secondary instances, and the secondary database. To view the configuration of the secondary server, click on the ellipsis (…) in the secondary server and instances grid view. See the following image:

Log shipping properties on Primary server

On the secondary database setting screen, you can view the state of the secondary database, name of the SQL Job that is used to restore the transactional logs on the secondary database, and its schedule. See the following image:

Log shipping properties on secondary server.

Disabling log shipping jobs and switch over the database server

During maintenance hours, we can disable the log shipping jobs on SQL01 (Old Server) and SQL02 (New Server). Details of the SQL Jobs are as follows:

SQL Server instance

Name of the SQL Job

SQL01 (Old Server)

  1. LSBackup_AdventureWorks2017
  2. LSAlert_SQL01

SQL02 (New Server)

  1. LSAlert_SQL02
  2. LSCopy_SQL01_AdventureWorks2017
  3. LSRestore_SQL01_AdventureWorks2017

To disable the job, execute the following queries:

On SQL01:

On SQL02:

Now, on SQL01 (Old Server) generate a tail log backup of the AdventureWorks2017 database WITH NORECOVERY option by executing the following query:

Once the backup is generated, restore the backup of AdventureWorks2017 on SQL02 (New server) with the Recovery option.

Once the backup is restored successfully, the AdventureWorks2017 database is online on SQL02 (New server). See the following image:

SQL Database have been migrated on secondary server.

If required, update the connection strings of the application, and we are good to go.

Summary

As a database administrator, sometimes we must think out of the box to fix the issues. In this article, I have explained how we can use SQL Server Log shipping technology to move a SQL Database to another server with minimum downtime.

Nisarg Upadhyay
Migration, Transaction log

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

329 Views