sql server日志传送实践（基于server 2008 R2）

SQL Server 2008 R2 主从数据库同步

相关参考：http://blog.itpub.net/30126024/viewspace-2639526/

sql server日志传送（基于server 2008 R2）

一、准备工作：

主数据库服务器：

OS：Windows Server 2008 R2    DB: SQL Server 2008 R2

Hostname : CXMasterDB 

IP: 192.168.1.224/24    dg: 192.168.1.1

DNS: 192.168.1.19    DNS: 202.96.209.133

从数据库服务器：

OS：Windows Server 2008 R2    DB: SQL Server 2008 R2

Hostname : CXSlaveDB 

IP: 192.168.1.225/24    dg: 192.168.1.1

DNS: 192.168.1.19    DNS: 202.96.209.133

/*自增部分--实现遇到的问题

1.权限问题，在不使用图中sqladmin时，单纯的NT账户居然无法访问UNC地址，导致无法复制。我不知道为什么，我一开始想直接构建，不用新建什么sqladmin管理员代理账户。后面用NT账户无法访问，我还是屈服了。

2.服务器名问题，我用的虚拟机，克隆了几个机器来用作测试环境，所以服务器名是一样的。但是我安装sql server的时候，没注意，安装的时候服务器名都是一样的。

　然后日志传送就出了大问题。后来我改了服务器名，一个DB1，一个DB2，但我发现sql server并没有修改，所以要手动修改@@servername。

　　

select @@ServerName


--查看当前所有数据库服务器名称
select * from Sys.SysServers

--修改数据库服务器名称
sp_dropserver 'old_servername'
sp_addserver 'new_servername','local'

 

3.日志传送本身，概念就是，首先以全备还原辅助数据库（standby模式，与norecovery模式相同，是可以读、备份的，并且也具备后续持续还原事务日志的功能），然后备份日志到UNC文件路径。

　　一直在思考，standby模式，有查询时是否可以还原事务日志，或还原事务日志时是否可以查询。很明显，这2种操作都是不可行的。

然后辅助数据库把UNC上的日志备份文件，都拷贝到辅助数据库本机目录上来，然后辅助数据库本机目录上进行事务日志还原。以此来达到主从同步，但是，很明显，这个是有时差的，我们这里设置的是15S。

但实际时间并不只有这么点，这只是备份开始时间、还原开始时间。如果15S内数据量很大，那么备份、还原数据就需要更多的时间，延迟将会更大，并且频繁的备份与恢复，也会影响数据库性能。

作为的主从同步，其实这是一个咳咳，不知道该咋描述的功能，数据量稍大，估计都要30S~1M的时间。看企业能不能承受吧，反正我觉得蛋疼。

4.监视服务器，这个玩意其实就是放几个监控作业到监视服务器上去，主要是为了多个DB做此操作便于统一管理与监控，让专门的监控服务器去查探运行状况，统一做警报等。

https://blog.csdn.net/dba_huangzj/article/details/8312872

5.总结：这东西花了我3个小时才部署好，感觉还是自己基础不够好，一些基本的windows知识与sql server实例、权限、服务 等知识的欠缺。

-----------分割线

-- update by 20190312
昨天试了一下，在2014 环境，sql server2014企业版
full1        2241   新建表test4.test5
trn1        2242   新建表test6.test7
full2        2243   新建表test8.test9
trn2        2245   新建表test10.test11 
1.利用full1初始化从库，开始5分钟一次的事物日志传送
2.主库构建 test12.test13
3.然后从库standby模式，试了好多次。后续日志传送所有作业都是成功的，但都是只有full1中的数据。
trn1和trn2中的数据没有过来。。后续构建的test12.test13表数据也没有同步。
4.再尝试，直接还原full1+trn1，standby模式，然后开启日志传送也是只有最高test7数据。
作业全部成功，后续的trn2不会自动还原，后续的test12.test13也没有
5.最终尝试，使用full1+trn1+trn2 还原到从库，然后standby模式。
设置好日志传送，5分钟一次同步，立马在主库构建test12和test13。
5分钟后从库已同步了表test12.test13

初步结论(不知道是不是忽略了哪里)，好像是必须要使用最新的事务日志，
完整备份不影响。

*/

//主数据库Northwind，如下图所示：

//从CXSlaveDB中，没有数据库Northwind，如下图所示：

二、创建SQL  Server数据库的管理员用户和共享文件夹权限，设置SQL Server服务和SQL Server代理的登录用户为sqladmin

1. 在主数据库CXMasterDB服务器上创建用户sqladmin，如下图所示：

win + R ---> lusrmgr.msc回车，如下图所示：

//设置sqladmin的权限

2. 同理，在从数据库CXSlaveDB服务器上创建用户sqladmin且加入administrators组，其他都删除之！

3. 分别在主从数据库服务器上创建用于存放主从备份日志文件的共享文件夹DB_Backpup且共享权限和NTFS权限，如如下图所示：

4. 分别从主数据库服务器上和从数据库服务器上打开SQLServer配置管理器，将SQLServer服务和SQLServer代理服务的“登录身份为”sqladmin用户且启动模式为：自动，如下图所示：

三、配置SQLServer日志传送

1. 在主数据库服务器CXMasterDB上配置

//用sqladmin连接到本地SQL  Server数据库服务器

//在数据库实例中，配置 服务器身份验证模式  和 服务器代理帐户

2. 在主数据库服务器CXMasterDB中的Northwind数据库的属性的配置

//在Northwind数据库的属性 --> 选项 的配置

//在Northwind数据库的属性 --> 事务日志传送 的配置

//事务日志备份设置，如下图所示：

//在上图中，点击 计划…  后，弹出如下图所示：

一路 确定 ！见到下图为止！

//添加 辅助数据库实例和数据库

//连接到 辅助服务器实例和辅助数据库

//辅助数据库设置--->初始化辅助数据库

注：

    下面查看看，辅助服务器CXSlaveDB中数据库的 数据文件和日志文件 的文件夹的路径

//辅助数据库设置--->复制文件

//在上图中 击点  计划(E) … 则弹出如下图所示，设置从数据库还原日志的计划任务：

//辅助数据库设置--->还原事务日志

//在上图中 击点  计划(E) … 则弹出如下图所示，设置从数据库还原日志的计划任务：

//将配置信息导出到文件

//在下图中点击  确定 后，一目了然了！

三、验证SQL  Server 2008 R2主从数据库是否同步

1. 我们去从服务器CXSlaveDB看看是否有Northwind数据库，由下图所示，可见成功了！

2. 现在我们去主数据库服务器CXMasterDB中Northwind数据库添加如下图所示的表：

//创建表名为 雇员通讯录

//刷新下，就可见到 dbo.雇员通讯录 ，如下图所示：

3. 现在我们去从数据库服务器CXSlaveDB的Northwind数据库是否有表dbo.雇员通讯录，如下图所示：

思考:

     下面我们到主数据库服务器CXMasterDB的Northwind数据库中，删除表dbo.雇员通讯录

看看从数据库CXSlaveDB的Northwind数据库中的表dbo.雇员通讯录，是否也被删除了?

附：

1. 如何删除Northwind（备用 / 只读）数据库

//在数据库属性 –-> 选项 ---> 状态 --->数据库为只读 修改为 False即可！能删除吗???

 原始出处 ：http://beyondhdf.blog.51cto.com/229452/1228517

事务日志的使用~转自：https://blog.csdn.net/dba_huangzj/article/details/8313037

监控日志传送：

      在配置好日志传送之后，需要进行监控，监控备份、复制及还原的作业运作情况。这三类作业任何一个没有成功都意味着日志传送失败。

有两种方法可以监控辅助服务器是否与主服务器同步以及两者的时间差：

1、 使用“事务日志传送状态”

2、 执行master.dbo.sp_help_log_shipping_monitor

还可以使用sys.sp_check_log_shipping_monitor_alert来检查是否超过预先设置的阈值。如果超过了，存储过程会发出一个警报。

在监控服务器上执行存储过程：sp_help_log_shipping_monitor，可以看到主服务器和辅助服务器的信息，该结果和使用方法1中的结果基本一致。

故障排查：

日志传送由三部分组成：备份事务日志、复制文件和还原事务日志。所以当出现故障的时候，检查这三部分。

      可以查看SQL代理的日志传送作业历史和windows事件查看器来确认真正的错误信息。

      如复制文件失败，可能是网络不正常，如果还原失败，可能服务器不可用或者数据库处于standby模式时用户正在使用数据库。另外，如果数据库恢复模式改为“简单”，会中断日志传送，因为会截断日志。而不是备份日志。这时需要重新配置事务日志。

      要注意一点，在日志传送之外不应该存在任何其他的事务日志备份操作。因为这样会引起主服务器和辅助服务器的日志链不匹配，从而导致日志传送的中断。

数据库备份计划：

在日志传送中，，对于备份，要考虑以下几点：

l 数据库备份进程和事务日志备份进程不能并发运行。所以一个大型、活跃的数据库，备份可能要花费一段时间，引起日志快速的增长，从而导致辅助服务器和主服务器不同步。因为数据库备份完成之前无法及时地收到事务日志。

l 除日志传送之外不能有其他事务日志备份，因为会断开日志链。

l 截断事务日志将断开日志链，从而导致日志传送无法正常工作。

l 如果把数据库恢复模式转换成“简单”，那么SQLServer会截断事务日志。从而导致日志传送无法正常工作。

删除日志传送：

在删除日志传送数据库之前，先要删除数据库中的日志传送。当删除日志传送后，所有的时间表、作业、历史以及错误信息都会被删除。

使用ssms删除：

只需要把主服务器中下面红框处取消勾选即可。

 

也可以用下面那里点击【删除】

 

使用T-SQL删除：

在主服务器上：

1.  
   Use master;
2.  
    
3.  
   Sp_delete_log_shipping_primary_secondary @primary_database,@secondary_server,@secondary_database
4.  
    

 

这个命令是删除主服务器上的msdb.dbo.log_shipping_primary_secondaries表中辅助服务器的信息。

在辅助服务器上：

 

1.  
   Use master;
2.  
    
3.  
   Sp_delete_log_shipping_secondary_database @secondary_database;
4.  
    

 

删除辅助服务器上有关服务服务器的信息和作业。

然后再回到主服务器：

 

1.  
   Use master;
2.  
    
3.  
   Sp_delete_log_shipping_primary_database @database
4.  
    

 

该存储过程删除对应的信息和作业。

日志传送性能：

1、 日志传送备份目录存放到与数据库不同的磁盘驱动器上。并使用备份压缩（2008出现）

2、  需要监控I/O性能计数器以找到所有的瓶颈（如每个物理驱动器的队列平均程度大于2）

3、 在空间时段进行数据库管理活动（如索引碎片整理），因为碎片越多，日志文件越大，备份和还原的时间就越长。

4、为了确保角色切换中数据库能快速恢复，辅助服务器应该与主服务器完全一样的容量。

5、需要把文件复制目录与数据库分离。

6、确保网络不会成为瓶颈。