SqlServer 2019 事务日志传送

最近在微软官网上看了一下sql server的高可用的文档，最简单的应该是事务日志传送，那就做个实验。

先决条件：

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

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

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

 

配置日志传送

使用 SQL Server Management Studio
1. 右键单击要在日志传送配置中用作主数据库的数据库，然后单击 “属性” ；在 “选择页” 下，单击 “事务日志传送” ；

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

 2、 在 “事务日志备份” 下，单击 “备份设置” 。在 “备份文件夹的网络路径” 框中，键入为事务日志备份文件夹创建的共享的网络路径。 

 “如果备份文件夹位于主服务器上，则在备份文件夹中键入本地路径”框。 （如果备份文件夹不在主服务器上，此框可以保留为空。） 

如果主服务器上的 SQL Server 服务帐户运行在本地系统帐户下，则必须在主服务器上创建备份文件夹，并指定该文件夹的本地路径。

3、配置 “删除文件，如果其保留时间超过” 和 “在以下时间内没有执行备份时报警” 参数。

4、在 “辅助服务器实例和数据库” 下，单击 “添加” 。
5、单击 “连接” ，连接到要用作辅助服务器的 SQL Server 实例。
6、在 “辅助数据库” 框中，从列表中选择一个数据库或键入想要创建的数据库的名称。
7、在 “初始化辅助数据库” 选项卡上，选择要用于初始化辅助数据库的选项。

如果选择让 Management Studio 从数据库备份中初始化辅助数据库，则辅助数据库的数据文件和日志文件将与
master 数据库的数据文件和日志文件放置在同一位置。 此位置可能不同于主数据库的数据文件和日志文件所在的
位置。

 

 

 8、 在 “复制文件” 选项卡上的 “复制文件的目标文件夹” 框中，键入应该将事务日志备份复制到其中的文件夹的路径。 该文件夹通常位于辅助服务器上。

 

9、 在 “还原” 选项卡上的 “还原备份时的数据库状态” 下，选择 “无恢复模式” 或 “备用模式” 选项。

当主服务器和辅助服务器的版本相同时，仅选择“备用模式”。 当辅助服务器的主版本高于主服务器时，仅允许“无恢复模式。 

如果选择了 “备用模式” 选项，请选择是否要在进行还原操作时从辅助数据库断开用户连接。

如果希望延迟辅助服务器上的还原进程，请在 “延迟还原备份操作至少” 下选择延迟时间。

在 “在以下时间内没有执行还原时报警” 下选择警报阈值。单击“确定”。

 

10、在 “监视服务器实例” 下，选中 “使用监视服务器实例” 复选框，然后单击 “设置” 。
单击 “连接” 连接到想要用作监视服务器的 SQL Server 实例。
在 “监视器连接” 下，选择备份、副本以及还原作业所使用的连接方法来连接到监视器服务器。
在 “历史记录保持期” 下，选择想要保留日志传送历史记录的时间长度。
单击“确定”。

 

 在 “数据库属性” 对话框中，单击 “确定” 开始配置进程。 

 

 

 点确定后开始执行配置，遇到以下错误。

sqlserver 服务账号权限的问题，不能访问新建的目录。

 授予权限后配置成功。

 

 

 当成功后，可在辅助服务器上看到已创建辅助数据库，状态为只读。

 

另外还可以配置监视服务器实例，对日志传送进行监控。本次实验没有配置，在主、备库已提供了各种查询存储过程和表。详情可查阅以下链接：

 https://docs.microsoft.com/zh-cn/sql/database-engine/log-shipping/log-shipping-tables-and-stored-procedures?view=sql-server-ver15

总结一下，

日志传送由三项操作组成：
1. 在主服务器实例中备份事务日志。
2. 将事务日志文件复制到辅助服务器实例。
3. 在辅助服务器实例中还原日志备份。

其实就是 备份---->复制---->恢复

日志可传送到多个辅助服务器实例。 在这些情况下，将针对每个辅助服务器实例重复执行操作 2 和操作 3。
日志传送配置不会自动从主服务器故障转移到辅助服务器。 如果主数据库变为不可用，可手动使任意辅助数据
库联机。

日志传送缺点比较明显：

1、主备库不是实时同步的，延迟比较大，要等待日志传送和恢复，如果restore时没有可用的日志备份，刚又要等待一个周期。

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

3、主库出现故障不能自动转移到备库，必须手动处理。

 

在删除过期日志备份文件时报错，给NT SERVICE\SQLSERVERAGENT授权。

 

 

补充：

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

主服务器表

表	描述
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 表中返回指定的辅助数据库的监视记录。

 

删除日志传送

只要登录主库实例，右键数据库-->Properties-->Transaction Log Shipping-->取消勾选Enable this as a primary database in a log shipping configuration，删除后，主库和从库的job都自动删除了，就算job被disable禁用了，也会被自动删除。

删除logshipping后恢复从库为读写状态

RESTORE DATABASE dbname with recovery

如果从库是只读并且有用户连接执行以下命令

ALTER DATABASE dbname  SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE dbname  WITH RECOVERY

ALTER DATABASE dbname  SET MULTI_USER

 

故障转移 

在出现故障时转移到辅助数据库：

1. 将所有未复制的备份文件从备份共享复制到每台辅助服务器的复制目标文件夹中。

2. 将所有未应用的事务日志备份按顺序应用到每个辅助数据库中。 有关详细信息，请参阅应用事务日志备 份 (SQL Server)。

如果可以访问主数据库，则请备份活动的事务日志，并将日志备份应用到辅助数据库。 可能需要在发出restore 命令之前将数据库设置为单用户模式以获得独占访问权限，

然后在还原完成后将其切换回多用户模式。如果原始主服务器实例没有损坏，则请使用 WITH NORECOVERY 备份主数据库的事务日志尾部。 这将使数据库处于还原状态，

因此用户无法使用。 最终，您将能够通过应用替换主数据库中的事务日志备份前滚此数据库。 

3、恢复辅助数据库之后，可以将其重新配置为其他辅助数据库的主数据库。