SQL Server Log Shipping学习总结

 

SQL Server的日志传送（log shipping）技术一直比较鸡肋，尤其当SQL Server 推出了Always On技术以后，估计使用日志传送（log shipping）这种技术方案的企业越来越少，但是日志传送也有自己的一些优点，有些特殊场景或业务背景下也有其存在的价值。最近由于特殊业务场景可能需要用到这个技术，所以做了一些测试和验证，比对一些知识做了一下总结、归纳。下面有部分内容从官方文档摘抄。此篇是总结性内容。如有不足，敬请指点！

 

 

日志传送（Log Shipping）介绍

 

SQL Server使用日志传送，可以自动将“主服务器”实例上“主数据库”上的事务日志备份发送到“辅助服务器”实例上的一个或多个“辅助数据库”。 

事务日志备份分别应用于每个辅助数据库。 可选第三个服务器实例（称为“监视服务器 ”）记录备份和还原操作的历史记录及状态，还可以在无法按计划

执行这些操作时引发警报。

 

事务日志传送提供了数据库级别的高可用性保护。日志传送可用来维护相应生产数据库（称为“主数据库”）的一个或多个备用数据库（称为“辅助数据库”）。发生故障转移之前，必须通过手动应用全部未还原的日志备份来完全更新辅助数据库。日志传送具有支持多个备用数据库的灵活性。如果需要多个备用数据库，可以单独使用日志传送或将其作为数据库镜像的补充。当这些解决方案一起使用时，当前数据库镜像配置的主体数据库同时也是当前日志传送配置的主数据库。

 

日志传送的拓扑结构图如下所示：

 

 

 

 

优点：

 

         可以为单个主数据库配置一个或多个辅助数据库（每个数据库都位于单独的SQL Server实例上），提供灾难恢复解决方案。

       

         支持对辅助数据库的受限的只读访问权限（在还原作业之间的间隔期间）。可做简单的读写分离。

 

         允许用户将延迟时间定义为：从主服务器备份主数据库日志到辅助服务器必须还原（应用）日志备份之间的时间。 例如，如果主数据库上的数据被意外更改，则较长的延迟会很有用。 如果很快发现意外更改，则通过延迟，您可以在辅助数据库反映此更改之前从其中检索仍未更改的数据

 

缺点：

 

 

        容易出现异常，导致数据不一致。而且出现异常后基本无法补救，需要重新初始化。

 

 

        日志传送配置不会自动从主服务器故障转移到辅助服务器。 如果主数据库变为不可用，需要手动将辅助数据库联机。

 

     

        没有自我纠错、自我验证的处理机制。

 

       

        数据同步有延迟。

 

 

 

相关术语（摘自官方文档）

 

主服务器 (primary server)

  

   位于生产服务器上的SQL Server实例。

 

主数据库 (primary database)

  

   希望备份到其他服务器的主服务器上的数据库。 通过SQL Server Management Studio进行的所有日志传送配置管理都是在主数据库中执行的。

 

辅助服务器 (secondary server)

 

   想要在其上保留主数据库的热备副本的SQL Server实例。

 

辅助数据库 (secondary database)

 

   主数据库的热备用副本。 辅助数据库可以处于 RECOVERING 状态或 STANDBY 状态，这将使数据库可用于受限的只读访问。

 

监视服务器 (monitor server)

 

   跟踪日志传送的所有详细信息的SQL Server的可选实例，包括：

 

       主数据库中事务日志最近一次备份的时间。

 

       辅助服务器最近一次复制和还原备份文件的时间。

 

       有关任何备份失败警报的信息。

 

备份作业

 

一种SQL Server代理作业，它执行备份操作，将历史记录信息记录到本地服务器和监视服务器上，并删除旧的备份文件和历史记录信息。 

启用日志传送后，将在主服务器实例上创建作业类别“日志传送备份”。

 

复制作业

 

一种SQL Server代理作业，它将备份文件从主服务器复制到辅助服务器中的可配置目标，并在辅助服务器和监视服务器中记录历史记录。 

在数据库上启用日志传送后，将在日志传送配置中在各辅助服务器上创建作业类别“日志传送复制”。

 

还原作业

 

一种SQL Server代理作业，它将复制的备份文件还原到辅助数据库。它将历史记录信息记录在本地服务器和监视服务器上，并删除旧文件和旧历史记录信息。 在数据库上启用日志传送后，在辅助服务器实例上会创建作业类别“日志传送还原”。

 

警报作业

一种 SQL Server 代理作业，它在备份或还原操作在指定阈值内未成功完成时为主数据库和辅助数据库引发警报。 在数据库上启用日志传送后，在监视服务器实例上会创建作业类别“日志传送警报”。

 

 

 

配置日志传送（Log Shipping）的先决条件

 

·         主数据库必须使用完整恢复模式或大容量日志恢复模式，将数据库切换为简单恢复模式会导致日志传送停止工作。

 

·         在配置日志传送之前，您必须创建共享，以便辅助服务器可以访问事务日志备份。 这是对生成事务日志备份的目录的共享。 

    例如，如果将事务日志备份到目录 C:\data\tlogs\，则可以对该目录创建 \\primaryserver\tlogs 共享

 

 

 

 

配置日志传送（Log Shipping）的权限要求

 

日志传送的相关存储过程要求有sysadmin服务器角色的权限。

 

 

 

配置日志传送（Log Shipping）的步骤操作

 

注意：日志传送配置比较简单，下面很多简单介绍，不做详细介绍。

 

 

1: 在主服务器（primary server）上，选择要配置日志传送的主数据库（ primary database），然后右键单击数据库，在选项”事务日志传送“

   里面选择勾选下图选项

 

 

 

 

2：配置事务日志备份

 

这里的选项都很简单，但是必须根据实际情况配置修改。需要注意的是共享路径的配置。注意事项，请注意认证读一遍。

 

思考：如果勾选“禁用这个作业”是否可行？ 是否可以用其它的备份呢？

 

 

 

2：配置辅助数据库

 

配置辅助数据库，在辅助数据库选项中点击添加来添加辅助数据库，可以添加多个辅助数据库。

 

 

 

注意：有时候可能会遇到权限问题“ Operating system error 5(Access is denied.)”，遇到这个问题的原因五花八门，例如这个案例当中，就是因为SQL Server实例是用内置账号Network Service启动的，可以使用其它账号，例如域账号或者Local System账号就不会遇到这个问题。

 

Cannot open backup device '\\192.168.27.108\DB_BACKUP\LOG_BACKUP\MyDB......'. Operating system error 5(Access is denied.).

RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3201)

 

 

 

 

 

 

 

 

配置日志传送（Log Shipping）的注意事项

 

1: 如果数据库是“备用模式”（Standby/Read-Only），那么数据库就是只读模式。

 

 

   关于辅助数据库（secondary database）能否执行存储过程。可以执行部分存储过程，但是有限制。

  如果“辅助数据库”是Standby模式，那么如果存储过程里面有DML操作的话，那么存储过程执行报错。所以做为读写分离的时候，还是有一些限制的！这个需要特别注意！

 

 

 

 

 

2: 主服务器上的完整备份是否影响日志传送（Log Shipping）呢？

 

  

   实验测试是不会。

 

 

3：Log Shipping的备份作业之外的其它事务日志备份是否影响、干扰日志传送（Log Shipping）呢？

 

  

   实验测试这个是会导致log shipping中断。

 

  

   是否可以不用Log Shipping的事务日志备份作业（禁用这个作业），使用其它事务日志备份作业。

 

 

  实验测试是不行。

 

 

 

   如果对应目录有主数据库的事务日志备份，是否会被全部Copy到辅助服务器？如果辅助服务器有之前的旧事务日志备份，是否会被全部应用还原？

 

  

      

 

4：主服务器与辅助服务器的SQL Server版本是否可以不一致呢？

 

  

   答案是主服务器与辅助服务器的SQL Server版本可以不一致，但是在不一致的情况下，辅助数据库只能选择无恢复模式（No Recovery Module）而不能使用

 

  备用模式。否则会报类似下面的错误：

 

   Msg 3180, Level 16, State 1, Line 2

This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.

 

      注意：这里所说的SQL Server版本不一致，只能是辅助服务器的SQL Server版本比主服务器的SQL Server版本高。

 

 

 

 

 

日志传送删除

 

SSMS UI 界面操作（当然也可以使用脚本，不过这里UI界面操作比较简单）：

 

 

1.     连接到当前日志传送主服务器的SQL Server实例并展开该实例。

 

2.     展开“数据库”，右键单击日志传送主数据库，再单击“属性”。

 

3.     在“选择页”下，单击“事务日志传送”。

 

4.     取消“将此数据库启用为日志传送配置中的主数据库” 复选框。

 

5.     单击“确定”，从此主数据库中删除日志传送。

 

 

 

 

日志传送完成后，如果需要将辅助数据库联机，那么就必须使用下面SQL操作：

 

错误做法：

 

 

USE [master]

GO

ALTER DATABASE [TEST] SET  READ_WRITE WITH NO_WAIT

GO

 

Msg 5063, Level 16, State 1, Line 3

Database 'TEST' is in warm standby. A warm-standby database is read-only.

Msg 5069, Level 16, State 1, Line 3

ALTER DATABASE statement failed.

 

 

 

正确做法：

 

 

USE master;

GO

ALTER DATABASE [TEST] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

RESTORE DATABASE [TEST] WITH RECOVERY

GO

ALTER DATABASE [TEST] SET MULTI_USER

 

 

 

 

 

日志传送相关表和存储过程

 

 

主服务器表

 

表	描述
log_shipping_monitor_alert	存储警报作业 ID。 仅当尚未配置远程监视服务器时，主服务器上才会使用此表。
log_shipping_monitor_error_detail	存储与此主服务器关联的日志传送作业的错误详细信息。
log_shipping_monitor_history_detail	存储与此主服务器关联的日志传送作业的历史记录详细信息。
log_shipping_monitor_primary	存储一条此主数据库的监视记录。
log_shipping_primary_databases	包含指定服务器上主数据库的配置信息。 每个主数据库存储一行。
log_shipping_primary_secondaries	将主数据库映射到辅助数据库。

 

主服务器存储过程

 

 

存储过程	描述
sp_add_log_shipping_primary_database	设置日志传送配置（包括备份作业、本地监视记录及远程监视记录）的主数据库。
sp_add_log_shipping_primary_secondary	向现有的主数据库添加辅助数据库名称。
sp_change_log_shipping_primary_database	更改主数据库设置，包括本地和远程监视记录。
sp_cleanup_log_shipping_history	根据保持期清除本地历史记录及监视器上的历史记录。
sp_delete_log_shipping_primary_database	删除主数据库的日志传送，包括备份作业以及本地和远程历史记录。
sp_delete_log_shipping_primary_secondary	从主数据库中删除辅助数据库名称。
sp_help_log_shipping_primary_database	检索主数据库设置并显示 log_shipping_primary_databases 和 log_shipping_monitor_primary 表中的值。
sp_help_log_shipping_primary_secondary	检索主数据库的辅助数据库名称。
sp_refresh_log_shipping_monitor	利用指定的日志传送代理的最新信息刷新监视器。

 

 

辅助服务器表

 

 

表	描述
log_shipping_monitor_alert	存储警报作业 ID。 仅当尚未配置远程监视服务器时，辅助服务器上才会使用此表。
log_shipping_monitor_error_detail	存储与此辅助服务器关联的日志传送作业的错误详细信息。
log_shipping_monitor_history_detail	存储与此辅助服务器关联的日志传送作业的历史记录详细信息。
log_shipping_monitor_secondary	存储与此辅助服务器关联的辅助数据库监视记录。每个辅助数据库存储一条监视记录。
log_shipping_secondary	包含指定服务器上辅助数据库的配置信息。 每个辅助 ID 存储一行。
log_shipping_secondary_databases	存储指定辅助数据库的配置信息。 每个辅助数据库存储一行。

 

 

备注

与指定主数据库位于同一个辅助服务器上的辅助数据库共享 log_shipping_secondary 表中的设置。 如果一个辅助数据库更改了共享设置，所有辅助数据库的设置都将更改。

 

 

辅助服务器存储过程

 

 

存储过程	描述
sp_add_log_shipping_secondary_database	设置用于日志传送的辅助数据库。
sp_add_log_shipping_secondary_primary	为指定的主数据库设置主服务器信息，添加本地和远程监视器链接，并在辅助服务器上创建复制作业和还原作业。
sp_change_log_shipping_secondary_database	更改辅助数据库设置，包括本地和远程监视记录。
sp_change_log_shipping_secondary_primary	更改辅助数据库设置，例如源目录、目标目录和文件保持期。
sp_cleanup_log_shipping_history	根据保持期清除本地历史记录及监视器上的历史记录。
sp_delete_log_shipping_secondary_database	删除辅助数据库、本地历史记录和远程历史记录。
sp_delete_log_shipping_secondary_primary	从辅助服务器上删除有关指定的主服务器的信息。
sp_help_log_shipping_secondary_database	从 log_shipping_secondary、 log_shipping_secondary_databases和 log_shipping_monitor_secondary 表中检索辅助数据库设置。
sp_help_log_shipping_secondary_primary	此存储过程将在辅助服务器上检索给定的主数据库的设置。
sp_refresh_log_shipping_monitor	利用指定的日志传送代理的最新信息刷新监视器。

 

 

监视服务器表

 

 

 

表	描述
log_shipping_monitor_alert	存储警报作业 ID。
log_shipping_monitor_error_detail	存储日志传送作业的错误详细信息。
log_shipping_monitor_history_detail	存储日志传送作业的历史记录详细信息。
log_shipping_monitor_primary	存储与此监视服务器关联的主数据库的监视记录。每个主数据库存储一条监视记录。
log_shipping_monitor_secondary	存储与此监视服务器关联的辅助数据库的监视记录。每个辅助数据库存储一条监视记录。

 

监视服务器存储过程

 

 

存储过程	描述
sp_add_log_shipping_alert_job	如果尚未创建日志传送警报作业，则创建它。
sp_delete_log_shipping_alert_job	如果没有关联的主数据库，则删除日志传送警报作业。
sp_help_log_shipping_alert_job	返回警报作业的作业 ID。
sp_help_log_shipping_monitor_primary	从 log_shipping_monitor_primary 表中返回指定的主数据库的监视记录。
sp_help_log_shipping_monitor_secondary	从 log_shipping_monitor_secondary 表中返回指定的辅助数据库的监视记录。

 

 

 

 

日志传送问题

 

   日志传送可能会遇到一些问题，例如，Skipped log backup file

 

Message

2018-11-08 12:45:01.51 Skipped log backup file. Secondary DB: 'TEST', File: '\\XXXXXXXX\DB_BACKUP\TEST_20181108041515.trn'

2018-11-08 12:45:01.53 Could not find a log backup file that could be applied to secondary database 'TEST'.

 

 

Message

Executed as user: NT AUTHORITY\SYSTEM. The log shipping secondary database xxxxx.TEST has restore threshold of 45 minutes and is out of sync. No restore was performed for 75 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14421).  The step failed.

 

 

 

如果遇到这个问题，则需要分析主数据库的事务日志备份，看看是否因为某些原因导致事务日志链断开引起。另外，关于错误14421， 可以参考官方文档"Description of error message 14420 and error message 14421 that occur when you use log shipping in SQL Server", 下面摘录在此（微软有些文档，经常会失效）。

 

 

Summary

 

This article discusses the reasons for "out of sync" error messages when you have log shipping configured for SQL Server 2000.


One of the following error messages may be logged in the SQL Server error log:

Error message 14420

Error: 14420, Severity: 16, State: 1
The log shipping destination %s.%s is out of sync by %s minutes.

Error message 14421

Error: 14421, Severity: 16, State: 1
The log shipping destination %s.%s is out of sync by %s minutes.

If you are using SQL Server 2005, the description for these error messages are different:

Error message 14420

Error: 14420, Severity: 16, State: 1
The log shipping primary database %s.%s has backup threshold of %d minutes and has not performed a backup log operation for %d minutes. Check agent log and logshipping monitor information.

Error message 14421

Error: 14421, Severity: 16, State: 1
The log shipping secondary database %s.%s has restore threshold of %d minutes and is out of sync. No restore was performed for %d minutes. Restored latency is %d minutes. Check agent log and logshipping monitor information.

More Information

 

Log shipping uses Sqlmaint.exe to back up and to restore databases. When SQL Server creates a transaction log backup as part of a log shipping setup, Sqlmaint.exe connects to the monitor server and updates the log_shipping_primaries table with the last_backup_filename information. Similarly, when you run a Copy or a Restore job on a secondary server, Sqlmaint.exe connects to the monitor server and updates the log_shipping_secondaries table.

As part of log shipping, alert messages 14220 and 14221 are generated to track backup and restoration activity. The alert messages are generated depending on the value of Backup Alert threshold and Out of Sync Alert threshold respectively.

The alert message 14220 indicates that the difference between current time and the time indicated by the last_backup_filename value in the log_shipping_primaries table on the monitor server is greater than value that is set for the Backup Alert threshold.

The alert message 14221 indicates that the difference between the time indicated by the last_backup_filename in the log_shipping_primaries table and the last_loaded_filename in the log_shipping_secondaries table is greater than the value set for the Out of Sync Alert threshold.

Troubleshooting Error Message 14420

By definition, message 14420 does not necessarily indicate a problem with log shipping. The message indicates that the difference between the last backed up file and current time on the monitor server is greater than the time that is set for the Backup Alert threshold.

There are serveral reasons why the alert message is generated. The following list includes some of these reasons:

1.      The date or time (or both) on the monitor server is different from the date or time on the primary server. It is also possible that the system date or time was modified on the monitor or the primary server. This may also generate alert messages.

2.      When the monitor server is offline and then back online, the fields in the log_shipping_primaries table are not updated with the current values before the alert message job runs.

3.      The log shipping Copy job that is run on the primary server might not connect to the monitor server msdb database to update the fields in the log_shipping_primaries table. This may be the result of an authentication problem between the monitor server and the primary server.

4.      You may have set an incorrect value for the Backup Alert threshold. Ideally, you must set this value to at least three times the frequency of the backup job. If you change the frequency of the backup job after log shipping is configured and functional, you must update the value of theBackup Alertthreshold accordingly.

5.      The backup job on the primary server is failing. In this case, check the job history for the backup job to see a reason for the failure.

Troubleshooting Error Message 14421

By definition, message 14421 does not necessarily indicate a problem with Log Shipping. This message indicates that the difference between the last backed up file and last restored file is greater than the time selected for the Out of Sync Alert threshold.

There are serveral reasons why the alert message is raised. The following list includes some of these reasons:

1.      The date or time (or both) on the primary server is modified such that the date or time on the primary server is significantly ahead between consecutive transaction log backups.

2.      The log shipping Restore job that is running on the secondary server cannot connect to the monitor server msdb database to update the log_shipping_secondaries table with the correct value. This may be the result of an authentication problem between the secondary server and the monitor server.

3.      You may have set an incorrect value for the Out of Sync Alert threshold. Ideally, you must set this value to at least three times the frequency of the slower of the Copy and Restore jobs. If the frequency of the Copy or Restore jobs is modified after log shipping is set up and functional, you must modify the value of the Out of Sync Alert threshold accordingly.

4.      Problems either with the Backup job or Copy job are most likely to result in "out of sync" alert messages. If "out of sync" alert messages are raised and if there are no problems with the Backup or the Restore job, check the Copy job for potential problems. Additionally, network connectivity may cause the Copy job to fail.

5.      It is also possible that the Restore job on the secondary server is failing. In this case, check the job history for the Restore job because it may indicate a reason for the failure.

 

 

 

 

参考资料：

 

https://docs.microsoft.com/zh-cn/sql/database-engine/log-shipping/about-log-shipping-sql-server?view=sql-server-2017

https://www.mssqltips.com/sqlservertip/2301/step-by-step-sql-server-log-shipping/

https://blogs.technet.microsoft.com/mdegre/2009/08/08/logshipping-secondary-server-is-out-of-sync-and-lsrestore-job-failing/