使用SQL Server日志传送将SQL数据库移动到其他服务器

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:

作为SQL Server DBA，我们负责将客户SQL数据库移至其他服务器。 最近，我被分配了相同的项目。 其中一位客户希望将其SQL数据库移至单独的服务器。 以下是挑战：

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
   该数据库非常大； 因此，我们无法分离数据库并将其复制到另一台服务器并重新附加它。 这是一个坏主意，因为它大约需要花费时间。 5个小时，它也可能会阻塞网络
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
   备份的大小也很大。 我们尝试在测试服务器上还原它，整个数据库还原过程大约花费了3个小时

So, we decided to use the following approach:

因此，我们决定使用以下方法：

1. Script out all the SQL Jobs and recreate them on the new server
   编写所有SQL作业的脚本，然后在新服务器上重新创建它们
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:

在本文中，我将解释如何使用SQL日志传送将SQL数据库移动到其他服务器。 为了演示整个过程，我创建了一个演示设置，详细信息如下：

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.

我们将移动AdventureWorks2017数据库，因此我已在SQL01（旧服务器）上将其还原。 我在SQL01上创建了两个名为nisargupadhyay和niraliupadhyay的 SQL登录名。

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.

我已经使用Ola Hallengren的数据库维护解决方案创建了SQL Jobs来进行数据库维护。 维护解决方案使用的存储过程和表是在名为DBA的单独数据库上创建的，因此在编写维护任务脚本之前，必须将其还原到新服务器上。

First, we will script out the SQL Jobs.

首先，我们将编写SQL作业脚本。

编写SQL Job脚本并在新服务器上重新创建它们 (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:

要生成数据库维护作业的脚本， 请使用远程桌面连接->启动SQL Server Management Studio连接到SQL01.DC.Local并连接到SQL Server数据库引擎 。 连接后，展开SQL Server代理 ->展开作业 ->右键单击DatabaseBackup – USER_DATABASES – FULL- > 将鼠标悬停在脚本作业上 ->将鼠标悬停在创建到 ->将鼠标悬停在新查询编辑器窗口上 。 见下图：

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.

复制T-SQL脚本以重新创建SQL作业并在新服务器上执行它。 同样，在新服务器上重新创建其他SQL作业。 创建SQL作业后，让我们在新服务器上创建所需的登录名。

在新服务器上重新创建所有登录名 (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.

首先，我们必须编写所有登录的脚本。 为此，我们可以使用sp_help_revlogin存储过程。 我写了一篇文章，解释了我们如何使用它在新服务器上传输SQL登录名。 您可以查看本文使用sp_help_revlogin将SQL登录名传输到辅助副本 。

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:

如前所述，我在SQL01 （旧服务器）上创建了两个登录名。 sp_help_revlogin存储过程将为其生成CREATE LOGIN脚本。 脚本如下：

-- 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).

复制整个脚本并在SQL02 （新服务器）上执行它。

设置旧服务器和新服务器之间的日志传送 (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.

SQL Server日志传送是一种广泛使用的技术。 关于它的文章很多。 因此，我将不解释设置日志传送的过程。 您可以阅读以下文章，以了解SQL Server日志传送的部署和监视。

1. How to configure SQL Server Log Shipping 如何配置SQL Server日志传送
2. Monitor Transaction Log shipping using T-SQL and SSMS 使用T-SQL和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:

我已经在SQL01（旧服务器）和SQL02（新服务器）之间建立了日志传送。 要查看配置，请连接到SQL01- >展开数据库 ->右键单击AdventureWorks2017- >选择属性 。 见下图：

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:

在辅助数据库设置屏幕上，您可以查看辅助数据库的状态，用于在辅助数据库上还原事务日志SQL Job的名称及其计划。 见下图：

禁用日志传送作业并切换数据库服务器 (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:

在维护时间内，我们可以在SQL01（旧服务器）和SQL02（新服务器）上禁用日志传送作业。 SQL作业的详细信息如下：

To disable the job, execute the following queries:

要禁用该作业，请执行以下查询：

On SQL01:

在SQL01上：

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:

在SQL02上：

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:

现在，在SQL01（旧服务器）上，通过执行以下查询，生成带有NORECOVERY选项的AdventureWorks2017数据库的尾日志备份：

BACKUP LOG AdventureWorks2017 TO DISK = '\SQL01BackupAdventureWorks2017_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.

生成备份后，使用恢复选项在SQL02（新服务器）上恢复 AdventureWorks2017的备份。

RESTORE LOG [AdventureWorks2017] FROM  DISK = N'\SQL01BackupAdventureWorks2017_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:

成功还原备份后，AdventureWorks2017数据库在SQL02（新服务器）上处于联机状态。 见下图：

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.

作为数据库管理员，有时我们必须开箱即用以解决问题。 在本文中，我已经解释了如何使用SQL Server日志传送技术将SQL数据库移动到另一台服务器，而停机时间最少。

翻译自: https://www.sqlshack.com/move-sql-databases-to-a-different-server-using-sql-server-log-shipping/