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:
- 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
- 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:
- Script out all the SQL Jobs and recreate them on the new server
- Recreate all the logins on the new server
- Set up log shipping between the old server and the new server
- 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:
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:
|
1 2 3 4 5 6 7 |
-- Login: NisargUpadhyay CREATE LOGIN [NisargUpadhyay] WITH PASSWORD = 0x020014224E06368CBCDA16C4722BFFF476B1D244CC0ABE02CBAA906F143C331560B0E6A39BAB0CC8BDFEF75EA4A65E052CB7276BAD3827F0B2FC211CBAD8516548AAC38A7D3F HASHED, SID = 0x2F7DBDA6F4C58B4EB469294EFCAEAE72, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF -- Login: NiraliUpadhyay CREATE LOGIN [NiraliUpadhyay] WITH PASSWORD = 0x0200412CDCDB555FF142871BEBF50C6CF5069FF0713C55505CD9CB0EF603593745E2250CEECCC9E6B696DB8791C34184C3DA8AFD01AA2C6D42EC50FFE21926E7DB8EEE09FF87 HASHED, SID = 0xAFD93F8F801E0A43AFD34ABA01F89574, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF |
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.
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:
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:
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:
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) |
|
SQL02 (New Server) |
|
To disable the job, execute the following queries:
On SQL01:
|
1 2 3 4 |
EXEC msdb.dbo.sp_update_job @job_name=' LSBackup_AdventureWorks2017',@enabled = 0 Go EXEC msdb.dbo.sp_update_job @job_name=' LSAlert_SQL01',@enabled = 0 Go |
On SQL02:
|
1 2 3 4 5 6 7 |
EXEC msdb.dbo.sp_update_job @job_name='LSCopy_SQL01_AdventureWorks2017',@enabled = 0 Go EXEC msdb.dbo.sp_update_job @job_name='LSRestore_SQL01_AdventureWorks2017',@enabled = 0 Go EXEC msdb.dbo.sp_update_job @job_name=' LSAlert_SQL02',@enabled = 0 Go |
Now, on SQL01 (Old Server) generate a tail log backup of the AdventureWorks2017 database WITH NORECOVERY option by executing the following query:
|
1 |
BACKUP LOG AdventureWorks2017 TO DISK = '\\SQL01\Backup\AdventureWorks2017_NORECOVERY.TRN' WITH NORECOVERY, COMPRESSION, CHECKSUM, STATS = 25 |
Once the backup is generated, restore the backup of AdventureWorks2017 on SQL02 (New server) with the Recovery option.
|
1 2 |
RESTORE LOG [AdventureWorks2017] FROM DISK = N'\\SQL01\Backup\AdventureWorks2017_NORECOVERY.TRN' WITH FILE = 1, NOUNLOAD, STATS = 10 GO |
Once the backup is restored successfully, the AdventureWorks2017 database is online on SQL02 (New server). See the following image:
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.
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
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022
- Manage SQL Databases in CentOS: Manage filegroups of user databases - October 5, 2022