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

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

                                    Auth：chaoqun.guo 2020-07-27

【0】需求

前提

（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: emp_bak	备份文件夹（本地）	主库日志文件备份
D:dba_tools	DBA工具文件夹（本地）	复制事务备份到从库

从库：

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

【1.4】软件包示例

【1.5】最佳实践基本步骤

环境前提：
（1）两台机器均为：windows server2012R2+sql server2016
（2）主库机器：10.20.50.23   从库机器：10.20.50.42
（3）现在是做 sql server主从（利用备份还原的方式）
主从实现操作：
（1）主库备份到本地一个目录 ：D: emp_bak
（2）从库新建了一个共享目录：d:copy_file
对 administrator/administrators/log_shipping/Users  账户共享，
其中 log_shipping 是新建的所属 Users组的普通windows用户，它和 Users 都给了只读权限。
（3）在主库上，新建了一个 windows计划任务。使用administrators用户作为启动用户
定时复制 主库上 D: emp_bak 文件夹中的文件到 D:log_shipping 文件夹。
（4）从库sql server代理使用log_shipping启动。
定时还原 从库共享文件夹  D:log_shipping  中的备份文件

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

【2.1】主从库安装.netframework 3.5

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

    

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

    

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

    

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

    

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

        

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

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

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

    

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

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

    

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

    

（1）这个需要开共享，且需要给everyone 读取权限，也可以给sql server代理启动账户以读取权限，强烈推荐后者

（2）比如代理的 log_shipping 输入进去，然后点击添加，给只读权限即可。（然后这个账户所在用户组也需要添加进来，比如Users）

（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: emp_bak est.bak' with stats=10

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

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

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

restore database test from disk='D: emp est.bak' with norecovery,replace

,move 'test' to 'D:Program FilesMSSQL1MSSQL13.MSSQLSERVERMSSQLDATA est.mdf'

,move 'test_log' to 'D:Program FilesMSSQL1MSSQL13.MSSQLSERVERMSSQLDATA est_log.ldf'

（3）核验

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

    

（4）删除bak文件

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

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

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

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

    

（2）事务日志备份设置

我们设置备份目录为实现预设创建好的 d: emp_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）计划任务脚本内容

<1> copy unc（不推荐）

Rem 使用直接复制方式（不建议使用，因为如果主从没有相同的windows账户和密码，则无法免密直接访问UNC）
    echo %date%%time% 1>>output.log
replace /a d:	emp_bak* \10.20.50.42copy_file >>d:dba_toolsoutput.log 2>>d:dba_toolserror.log

<2> net use （推荐）

Rem 使用net use方式（建议使用，只需要拥有从库账户权限即可）
@echo off
for /l %%i in (1,1,6) do (
dir z:|find /c "trn"
if errorlevel 1 (net use z: \10.20.50.42log_shipping1433_17 $GTRwxhi@A6f#!wQ /user:log_shipping)
echo %date%%time% >>e:dba_toolsoutput.log
echo %date%%time% >>e:dba_toolserror.log
replace /a E:log_shipping_tran* z: >>e:dba_toolsoutput.log 2>>e:dba_toolserror.log
timeout /T 55 /NOBREAK
)

注意，如果目录、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: emp_bak	备份文件夹（本地）	主库日志文件备份
D:dba_tools	DBA工具文件夹（本地）	复制事务备份到从库
Copy_tranBak_file	Windows计划任务	调用bat复制备份文件到从库
LSBackup_test	数据库作业	test库事务日志备份/1分钟1次，备份到d: emp_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分多种（没有实时一直等候），查看数据，表已经同步过去了。

到此，大功告成

【4.6】把主库账户配置到从库，避免孤立用户

SELECT  'CREATE LOGIN [' + p.name + '] '
       + CASE WHEN p.type IN ( 'U', 'G' ) THEN 'FROM windows '
              ELSE ''
         END + 'WITH ' + CASE WHEN p.type = 'S'
                              THEN 'password = '
                                   + master.sys.fn_varbintohexstr(l.password_hash)
                                   + ' hashed, ' + 'sid = '
                                   + master.sys.fn_varbintohexstr(l.sid)
                                   + ', check_expiration = '
                                   + CASE WHEN l.is_expiration_checked > 0
                                          THEN 'ON, '
                                          ELSE 'OFF, '
                                     END + 'check_policy = '
                                   + CASE WHEN l.is_policy_checked > 0
                                          THEN 'ON, '
                                          ELSE 'OFF, '
                                     END
                                   + CASE WHEN l.credential_id > 0
                                          THEN 'credential = ' + c.name
                                               + ', '
                                          ELSE ''
                                     END
                              ELSE ''
                         END + 'default_database = '
       + p.default_database_name
       + CASE WHEN LEN(p.default_language_name) > 0
              THEN ', default_language = ' + p.default_language_name
              ELSE ''
         END
FROM    sys.server_principals p
       LEFT JOIN sys.sql_logins l
       ON p.principal_id = l.principal_id
       LEFT JOIN sys.credentials c
       ON l.credential_id = c.credential_id
WHERE   p.type IN ( 'S', 'U', 'G' )
       AND p.name NOT IN ( 'sa')
               AND p.name NOT LIKE '%##%'
               AND p.name NOT LIKE '%NT SERVICE%'
               AND p.name NOT LIKE '%NT AUTHORITY%'

【5】相关DMV、SP、系统表

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

 

 

主服务器表

 

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

【6】使用邮件服务监控

【6.1】配置邮件服务器

use msdb
go

if exists(
select 1 from sys.databases where name = 'msdb' and is_broker_enabled=0
)
begin
ALTER DATABASE msdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
alter database msdb set enable_broker;
end

--1.启用Database Mail扩展存储过程
go
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'Database Mail XPs', 1
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO
 

exec msdb..sysmail_add_account_sp
        @account_name            = 'dba_guochaoqun' --邮件帐户名称SQL Server内使用
       ,@email_address           = '815202984@qq.com' --发件人邮件地址
　　　　,@display_name            = '[10.20.50.42]'              --发邮件出去后显示的名称
       ,@mailserver_name         = 'smtp@qq.com'        --邮件服务器地址
       ,@mailserver_type         = 'SMTP'                --邮件协议SQL 2005只支持SMTP
       ,@port                    = 25                    --邮件服务器端口
       ,@username                = '81502984@qq.com' --用户名
       ,@password                = 'a123456!' --密码


--3.添加profile
exec msdb..sysmail_add_profile_sp
@profile_name = 'dba_profile'-- profile 名称
   ,@description  = 'dba mail profile'-- profile 描述
   ,@profile_id   = null

--4.映射account和profile
exec msdb..sysmail_add_profileaccount_sp  
@profile_name    = 'dba_profile'-- profile 名称
   ,@account_name    = 'dba_guochaoqun'-- account 名称
   ,@sequence_number = 1-- account 在profile中顺序

【6.2】监控脚本，发送表内容到邮件

use master;
declare @tableHTML nvarchar(max),@title varchar(2000),@monitor_name varchar(1000),@top_num int
set @title='[10.20.50.42] log_shipping error'
set @monitor_name='log_shipping'
set @top_num=10


if object_id('dba_monitor_count') is null
    create table dba_monitor_count(monitor_name varchar(1000),monitor_count int,record_time datetime)

if exists( select top(30) * from msdb..log_shipping_monitor_error_detail where datediff(minute,log_time,getdate())<10 ) 
and  not exists(select 1 from master..dba_monitor_count where monitor_name=@monitor_name and monitor_count>@top_num and convert(varchar(10),record_time,120)=convert(varchar(10),getdate(),120) )
begin

set @tableHTML = N'<H1>'+@title+'</H1>' 
+N'<div>'
+ N'<table border="1">' 
+ N'<tr>'
+ N'<th> db_name </th>' 
+ N'<th>   ________log_time_______    </th>' 
+ N'<th>message</th></tr>' 
+ CAST(
        (
         select top(30)
                td=isnull(database_name,'  '),'', 
                td=convert(varchar(100),log_time,20),'',
                td=message,''
                from msdb..log_shipping_monitor_error_detail
                order by log_time
                for xml path('tr')
        )
     as nvarchar(max)
    )
    
+'</table>' 
--print @tablehtml
    EXEC msdb..sp_send_dbmail
        @profile_name = 'dba_profile',
        @recipients = '815202984@qq.com;',
        @subject = @title,
        @body=@tablehtml,
        @body_format='HTML';
    if @@error=0
    begin
        if not exists(select 1 from master..dba_monitor_count where monitor_name=@monitor_name and convert(varchar(10),record_time,120)=convert(varchar(10),getdate(),120))
            insert into master..dba_monitor_count values(@monitor_name,1,getdate())
        else
            update dba_monitor_count set monitor_count=monitor_count+1 where monitor_name=@monitor_name and convert(varchar(10),record_time,120)=convert(varchar(10),getdate(),120)
    end
end

各种共享以及复制访问等情况是否会有问题。