SqlServer事务日志传送（不停机不在主库加共享文件版本）

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了SqlServer事务日志传送（不停机不在主库加共享文件版本），小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8211字，纯文字阅读大概需要12分钟。

内容图文

SqlServer事务日志传送（不停机不在主库加共享文件版本）

                                    Auth：chaoqun.guo 2020-07-27

1. 需求

前提

（1）不能停机、重启服务、现在没有加入域

（2）现在已经有一个镜像副本了

现有业务，想做一个从库，有什么好办法？

 

我遇到的问题：

1. alwayon（需要加入域 所以要重启不行）
2. 镜像（因为有一个镜像副本了不能多个镜像副本），
3. 日志传送（延迟时间太长了且需要UNC路径权限似乎也需要修改启动账户需要重启服务）
4. 发布订阅（很多业务老表没有主键）

最终，我选择了日志传送，深入研究后，解决了UNC访问路径共享文件夹的问题，以及相关主库开共享文件夹不安全等问题。

 

【1】基本原理与前置要求

【1.1】基本原理

1. 以全备在从库初始化之后，主库备份事务日志（增量备份）到指定文件夹A
2. 从库从文件夹A复制事务日志备份到本地文件夹B
3. 从库从B文件夹对事务日志进行还原

测试环境我们的策略如下：

我们的测试数据操作到底要多久从库才能同步上去呢？

1. 每分钟复制一次，每分钟生成1个trn文件。
2. 每5分钟同步一次新的trn文件到从库上的文件夹上去。
3. 从库每分钟按生成顺序还原1个trn文件

从上面3个规律，我们可以发现，核心时间花费是在文件备份和文件传输上。

假设备份需要3秒，传输需要10秒，那么我们的平均时间是

5分+3秒+10秒+1分钟 =6分13秒

（这一分钟是可能文件传过来之前正好运行了一次还原操作，导致错过需要再等下一次周期，周期是一分钟）

 

【1.2】前置要求

1. 版本要求：sql server2005及以上，主数据库为完整恢复/大容量日志恢复模式
2. .netframework 3.5 环境
3. Sql server代理服务必须开启

【1.3】测试架构分布

主库：

IP/URL	角色	描述
10.20.50.23	主服务器	日志传送主服务器
test	主库	日志传送主库
D:\temp_bak	备份文件夹（本地）	主库日志文件备份
D:\dba_tools	DBA工具文件夹（本地）	复制事务备份到从库

从库：

IP/URL	角色	描述
10.20.50.42	从库	日志传送从库
test	同名从库	日志传送同名从库
d:\copy_file	备份文件夹（共享）	接收主库备份文件并还原

 

【1.4】软件包示例

 

【2】环境与前置工作准备

【2.1】主从库安装.netframework 3.5

（1）服务器管理器，添加角色与功能

    

（2）全部下一步下一步，直至功能选择，勾上.Net FrameWork3.5

    

 

然后直接下一步下一步完事，但有时候会报错，如下：

    

（3）这个时候我们重新来一次，指定备用源

    

 

然后找到我们传过来文件的这个目录：

        

输入进去，然后点击确定即可。

然后点击安装，等待安装完成

 

 

【2.2】主库：新建 D:\temp_bak备份文件夹

    

【2.3】主库：新建 D:\dba_tools文件夹

并把文件 copy_tran_bakfile.bat复制进去，文件见【1.4】软件包

    

【2.4】从库：新建 D:\copy_file 备份接收文件夹

    

（1）这个需要开共享，且需要给everyone 读取权限，或者给sql server代理启动账户以读取权限

（2）比如代理的 NT AUTHORITY\NetworkService 输入进去，然后点击添加，给只读权限即可。

（3）或者新建一个账户，然后用这个账户启动从库代理，然后共享文件夹只给这个账户访问即可（注意，修改代理启动账户需要重启代理）

     不过反正是新的从库，重启代理服务也是没有关系的。

 

    

如果共享成功请直接到【3】

 

如果共享失败请检查：

（1）共享功能

        

 

（2）共享程序

    

 

 

【3】sql server事务传送

【3.1】构造测试主库 test

--构造测试主库 test

create database test;

go

 

--在test库构造3个测试表 test1/test2/test3

select 1 as id into test..test1;

select 2 as id into test..test2;

select 3 as id into test..test3;

 

【3.2】利用 test 全备初始化从库

（1）全量备份

--全量备份

backup database test to disk='D:\temp_bak\test.bak' with stats=10

（2）复制备份文件到从库

--复制主库test.bak文件到从库，这里随便找个文件夹即可，比如我选的就是D:\temp\ 目录

--以norecovery 的方式还原到从库，为避免主从位置不同，必须以move参数来指定还原数据库的文件存放位置

restore database test from disk='D:\temp\test.bak' with norecovery,replace

,move 'test' to 'D:\Program Files\MSSQL1\MSSQL13.MSSQLSERVER\MSSQL\DATA\test.mdf'

,move 'test_log' to 'D:\Program Files\MSSQL1\MSSQL13.MSSQLSERVER\MSSQL\DATA\test_log.ldf'

（3）核验

右击数据库=》刷新，如果出现了test数据库，并且是正在还原的情况，那么就成功了

    

（4）删除bak文件

操作完之后，为了保证安全，删除主库、从库上的test.bak文件，

 

【3.3】（主库）配置事务日志传送

（1）在主库：右击test数据库=》属性=》事务日志传送

把将此数据库启用为事务日志传送配置中的主数据库

    

（2）事务日志备份设置

我们设置备份目录为实现预设创建好的 d:\temp_bak

 

 

（3）事务日志备份计划设置

这里因为是测试我们设置1分钟备份一次，到这里设置好之后，一路确定确定下去就好了。

    

确定，成功

（4）核验

核验一：作业

我们可以看到我们操作完成后，生成了2个作业

LSBackup_test 以及 LSAlert_WIN-MMFDQB29HJK，前者是备份作业，后者是报警作业，我们只需要查看备份作业是否存在就好了。

        

我们来看一下作业是否执行成功，是否是按我们设置的一分钟一次的频率在执行

看一下历史记录，真的是一分钟一次执行，且都执行成功了。

    

核验二：查看备份文件是否生成在我们指定的目录下

如下图，已经生成了，证明我们主库配置好了。

    

 

 

（5）暂时禁用 LSBackup_test 作业

为什么要暂时禁用这个作业呢，因为我们现在还没配置 复制备份到从库，也没有配置在从库还原，如果备份太多会导致从库落后比较多。

等我们全部都配置好了，再开启。

右击 LSBackup_test 作业=》禁用

    

 

【3.4】（从库）配置事务日志传送

（1）在主库：右击test库=》属性=》事务日志传送，点击添加

    

（2）复制数据库设置=》连接

点击连接=》然后登录连接从库验证

    

 

（3）辅助数据库设置=》初始化

我们之前已经手动初始化了，这里我们选择否，辅助数据库已经初始化

    

（4）辅助数据库设置=》复制文件

点击复制文件选项卡，然后复制文件的目标文件夹填写我们之前预设好的

    

 

点击计划=》我们设置和备份一样的频率，一分钟还原一次

    

（5）辅助数据库设置=》还原事务日志

我们设置备用模式，备用模式可以让我们在非还原时间点，从库可以查询使用，还原计划间隔我们设置成1分钟一次，和备份、拷贝一样。

    

 

 

一路点击确定=》保存配置信息成功

    

（6）核验

核验一：作业

我们可以看到，从库产生了三个作业，如下图，前面三个作业就是

 

    

LSAlert_WIN-6BA1LAPNHHK：告警作业，我们用不上，忽略

LSCopy_WIN-MMFDQB29HJK_test：复制作业，因为本文我们是不停服操作，这个暂时没用上，所以避免浪费资源禁用。

我们下个步骤会在主库使用windows计划任务复制备份

LSRestore_WIN-MMFDQB29HJK_test：恢复作业，对指定文件夹中的事务日志备份进行顺序恢复。

                                 如果有文件则它会还原，如果没有文件它有判定机制不做操作，但作业也会执行成功。

                                 只有当发生还原实际错误的时候，或权限等访问错误的时候作业才会失败。

核验二：数据核验

这个整个都没弄好，见后面整天弄完了才能看到

 

【3.5】（主库）配置windows计划任务复制数据库

（0）计划任务脚本内容

    echo %date%%time% 1>>output.log

replace /a d:\temp_bak\* \\10.20.50.42\copy_file >>d:\dba_tools\output.log 2>>d:\dba_tools\error.log

注意，如果目录、IP不同，脚本需要修改

（1）开始=》运行=》control schedtasks

打开任务计划程序

    

（2）创建任务

    

（3）创建任务=》常规

这里要使用管理员账户，由于我这里没有实际管理员密码，暂时先用这个管理组角色测试

正式服务器上，需要使用管理员账户+密码，并且选中下面的【不管用户是否登录都要运行】

    

 

（4）创建任务=》触发器

我们新建一个触发器，然后设置策略为每天执行一次，一次运行持续一天，且每5分钟执行一次该任务中的操作。

（5）创建任务=》操作

把我们的脚本路径输入进去。

    

 

（6）创建任务=》条件

这个无需怎么修改，注意一下电源那里的复选框即可

    

（7）创建任务=》设置

    

好，到现在都配置好了，我们点击确定。

 

（8）核验

右击=》运行

    

然后我们可以去我们的脚本所在目录看日志，如下图，没有错误信息且输出日志中告诉了我们添加了日志

    

这就算运行成功了，但还需实际核验文件到底有没有传输过去

登上从库10.20.50.42 ，我们发现，已经传到这个目录上来了。

    

【4】最终同步核验

【4.1】当前作业、计划、等情况

主库：

IP/URL/名称	角色	描述
10.20.50.23	主库服务器	日志传送主库
test	主库	日志传送主库
D:\temp_bak	备份文件夹（本地）	主库日志文件备份
D:\dba_tools	DBA工具文件夹（本地）	复制事务备份到从库
Copy_tranBak_file	Windows计划任务	调用bat复制备份文件到从库
LSBackup_test	数据库作业	test库事务日志备份/1分钟1次，备份到d:\temp_bak

 

从库：

IP/URL	角色	描述
10.20.50.42	从库	日志传送从库
test	同名从库	日志传送同名从库
d:\copy_file	备份文件夹（共享）	接收主库备份文件并还原
LSRestore_WIN-MMFDQB29HJK_test	数据库作业	用来还原trn备份到从库test库，并以备用模式存在，使得非还原操作时间，从库可以只读访问

 

【4.2】启动作业、计划任务

之前我们停了 主库的LSBackup_test，主库所在服务器的计划任务 copy_tranBak_file

我们现在都启动起来

    

【4.3】构造测试数据

在主库上执行：

    select getdate()

select 4 as id into test..test4

select 5 as id into test..test5

select 6 as id into test..test6

目前时间是：2020-07-27 15:23:56.253

【4.4】同步时间分析

我们的测试数据操作到底要多久从库才能同步上去呢？

1. 每分钟复制一次，每分钟生成1个trn文件。
2. 每5分钟同步一次新的trn文件到从库上的文件夹上去。
3. 从库每分钟按生成顺序还原1个trn文件

从上面3个规律，我们可以发现，核心时间花费是在文件备份和文件传输上。

假设备份需要3秒，传输需要10秒，那么我们的平均时间是

5分+3秒+10秒+1分钟（这一分钟是可能文件传过来之前正好运行了一次还原操作，导致错过需要再等一秒）

 

【4.5】查看同步情况

我等了5分多种（没有实时一直等候），查看数据，表已经同步过去了。

到此，大功告成

内容总结

以上是互联网集市为您收集整理的SqlServer事务日志传送（不停机不在主库加共享文件版本）全部内容，希望文章能够帮你解决SqlServer事务日志传送（不停机不在主库加共享文件版本）所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---