SQL Server日志传送

嘉为教育 2021-05-11

1363

日志传送是SQL Server的一种规划高可用性的解决方案，主要由主服务器、备用服务器和监视服务器组成。主服务器用于生产服务器，备用服务器是主服务器的副本，但并不是实时副本，监视服务器用于跟踪日志传送的所有细节。

日志传送解决方案主要应用于：

1.作为主服务器的副本存放数据；

2.备用数据库可以做只读查询，实现数据库的读写分离的效果，用于分担主服务器的负载；

3. 因为辅助数据库并不是实时的，所以可以用于一些报表服务，和其他非实时的只读应用。

实验环境

注：SQL Server 版本为2016

服务器名称	服务器角色	IP地址
DC1	域控制器	192.168.10.10
SQL12NA	主服务器，监视服务器	192.168.10.11
SQL12NB	备用服务器	192.168.10.12

本实验在VM Workstation上搭建3台虚拟机，分别为DC1、SQL12NA和SQL12NB，均在域环境中，2台虚拟机的名称、角色以及IP可查看上表。DC1是本实验中的域控制器，SQL12NA和SQL12NB是本实验的数据库服务器。

实验原理及前提条件

实验原理

主服务器用于生产服务器，备用服务器是主服务器的副本，但并不是实时副本，监视服务器用于跟踪日志传送的所有细节。具体原理如下：1.备份主服务器上的事务日志到共享文件夹；2.备用服务器的SQLAgent从共享文件夹中拷贝事务日志到本地文件夹；3.在备用服务器上还原事务日志。

日志传送的实现前提

1. 主服务器和备用服务器SQL实例大版本一致；

2. 数据库必须使用Full或bulk-logged的恢复模式；（数据库的恢复模式必须是完整或大容量的恢复模式，不能是简单恢复模式。）

3. SQL Server Agent服务自动启动。

实验步骤

1. 登陆SQL 12NA，然后连接三个需要使用的实验实例：

2. 必须让所有实例满足“五”中提到的日志传送的前提条件：大版本一致；恢复模式不能是简单；代理服务自动启动。目前HR实例的代理服务不是自动启动：

配置成自动启动，并且进行代理服务启动。·

因为主体服务器和副本服务器之间通过共享文件夹进行数据的交互。主体服务器将数据放入共享文件夹中，副本服务器通过代理服务将共享文件夹中的备份文件拷贝到副本服务器的本地文件夹中。因此，主服务器的代理服务必须对共享文件夹具备写入权限，而副本服务器的代理服务必须对共享文件夹具有读取权限。

由于目前NA和NB两个实例的代理服务的用户账号都是使用域用户sqlsvc，因此共享文件夹只需要分配给sqlsvc一个读写权限即可。

3. 在NA本地创建一个名为backup的共享文件夹并完成权限的分配：

4. 在NB的本地创建一个本地文件夹，不需要共享。NB的代理服务的用户需要将共享文件夹中的备份拷贝到本地文件夹，然后从本地文件夹进行备份还原。因此，sqlsvc也需要对NB的本地文件夹进行数据的读写。这里直接给NB中的NB本地文件夹一个完全控制权限：

5. 对着Northwind数据库右键-属性-数据库属性页面-事务日志传送：

6. 按照以下操作，进行配置项目的输入：

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

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

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

7. 其他选项可以选择配置，此处默认。

备份的作业，可以通过“计划”，进行配置。目前是每个15分钟进行一次。

点击确定，主服务器上的配置就完成了。

8.回到数据库属性页面，在辅助数据库处点击“添加”：

9. 按照以下配置，配置辅助服务器。

辅助服务器连接SQL12NB；

在初始化辅助数据库处，有三个选项：

因为目前NB中还没有备份还原过Northwind这个数据库，因此在初始化这个地方，选择在本地文件夹中生成主数据库的完整备份，并将它还原到辅助数据库中。

注意：用于连接的帐户必须是辅助服务器实例上 sysadmin 固定服务器角色的成员。

--是，生成主数据库的完整备份并将其还原到辅助数据库

通过备份主数据库并在辅助服务器上还原该数据库，让 SQL Server Management Studio 配置辅助数据库。如果在“辅助数据库”框中输入新的数据库名称，数据库将作为还原操作的一部分进行创建。

--是，将主数据库的现有备份还原到辅助数据库

让 Management Studio 使用主数据库的现有备份初始化辅助数据库。在“备份文件”框中键入该备份的位置。如果在“辅助数据库”框中输入新的数据库名称，数据库将作为还原操作的一部分进行创建。

--如果选择“是，将主数据库的现有备份还原到辅助数据库”选项，请键入要用于初始化辅助数据库的完整数据库备份的路径和文件名。

--否，辅助数据库已初始化

指定辅助数据库已初始化并准备接受主数据库的事务日志备份。如果在“辅助数据库”框中键入新的数据库名称，则此选项不可用。

10. 在复制文件页面，输入本地的文件夹，这个地方是每个15分钟，将共享文件夹中的日志备份文件复制到本地的C:\NB中：

键入事务日志备份应复制到的路径以还原到辅助数据库。通常，此路径为辅助服务器上文件夹的本地路径。但是，如果该文件夹位于其他服务器，则必须指定该文件夹的 UNC 路径。辅助服务器实例的 SQL Server 服务帐户必须具有此文件夹的读取权限。此外，还必须向代理帐户授予此网络共享的读写权限。通过代理帐户，复制作业和还原作业将在辅助服务器实例上的该帐户下运行。默认情况下，这是辅助服务器实例的 SQL Server 代理服务帐户，但是 sysadmin 可以为该作业选择其他代理帐户。

11. 在还原事务日志页面，选择“备用模式”。

无恢复模式：在辅助服务器上的数据库进行日志还原的时候不可被访问；

备用模式：进行日志恢复的时候，也能够进行只读访问。

下面还原作业的计划也是默认每15分钟进行一次还原操作。

默认设置时，数据库处于 NORECOVERY（无恢复）模式，则不能运行查询。可以手动选择，使辅助数据库处于 STANDBY （备作）模式，此时可以通过使用辅助服务器进行只读查询处理，可以减少主服务器的负荷。

配置使辅助数据库处于备用模式时，“在还原备份时断开数据库中用户的连接”选项指定了两种方式：

（1）还原事务日志备份时，使数据库用户断开连接。

　　如果选中此选项，则日志传送还原作业每次尝试将事务日志还原到辅助数据库时，用户都将与数据库断开连接。断开连接将按为还原作业设置的计划发生。

（2）不与用户断开连接。

　　在这种情况下，如果用户连接到辅助数据库，则还原作业无法将事务日志备份还原到辅助数据库。事务日志备份将一直累积到没有用户连接到该数据库为止。

重要提示：如果更改现有辅助数据库的恢复模式（例如，从“无恢复”模式到“备用”模式），则更改仅在下一次日志备份还原到数据库后才会生效。

说明：加速复制数据库

如果数据量较大，建议：先通过移动介质，将主机做的完全备份拿到客户机上恢复，然后再做日志传送。

12. 辅助数据库设置完成后，可以继续添加辅助数据库。

选择监视服务器实例为NA上的HR实例：

13. 配置完成后，点击确定。等待操作配置执行完成：

14. 在NB中可以看到相应的数据库副本：

但是该副本并不是实时同步的，每15min主数据库进行一次日志备份，每15min辅助数据库进行数据的拷贝；每15min进行日志备份文件的还原。因此，两个数据库的数据是异步的。

15. 打开监视服务器即HR实例：右键-报表-标准报表-事务日志传送状态：

16. 这里就会记录每次数据库备份、复制、还原的情况：

测试：

17. 为了得到测试效果，可以将备份计划、复制文件的时间间隔和还原的时间间隔设置为十秒。

备份计划时间：

复制计划时间：

还原备份的时间间隔：

用同样的方式设置还原的计划也是10s，并勾选“在还原备份的时候断开数据库中的用户连接”：

还原备用服务器的时候，断开目前的用户连接。因为还原后数据库肯定不一样，所以断开了再还原。现在设置的时间为10s，只是为了试验效果，如果勾选了此处，每十秒就断一次，肯定是不能接受的。微软默认推荐时间是15min，实际可以按照自己的需求进行时间间隔的配置。不过配置得太久的话，两个库的信息同步就相差越大。