30-SQLServer事物日志传送的搭建

一、注意点

1、环境准备

IP	角色	操作系统版本	数据库版本	数据库名称
192.168.232.10	primary	Windows Server 2008 R2 Enterprise	SQL Server 2014	cardaddr
192.168.232.11	secondary	Windows Server 2008 R2 Enterprise	SQL Server 2014	cardaddr

 2、同步原理

　　对主节点上要同步的数据库定期做日志备份，然后从节点把主库上的日志备份先copy到自己机器上，然后再对数据库定期做日志恢复，从而来保证主备数据库数据的一致，所以数据库要的恢复模式要使用完整模式。

3、从节点数据库有2种模式

　　NORECOVERY（无恢复）模式：数据库不可访问，不能查询。

　　STANDBY（备用）模式：数据库可以进行查询处理，如果选择该种方式，刚搭建好之后，备库还是“正在还原”状态，再执行完一次日志还原后，会变成“备用/只读”状态。

4、要创建一个单独的系统用户启动数据库服务和代理服务，并且该用户的用户名和密码在主从服务器上要一样（或者使用administrator启用数据库和代理服务也行，但是要保证密码相同），不能使用默认的NT Service\MSSQLSERVER启动服务来创建事物日志传送，否则会报错，没有访问共享文件夹的权限，错误如下：

（1）使用NT Service\MSSQLSERVER启动服务的错误（搭建的时候就报错）

   

 

 （2）主从2边账号密码不一致的错误（能搭建成功，但是执行的时候copy作业失败）

    

 （3）启动数据库服务和代理服务的正确状态

  

 5、创建的用户和共享文件夹权限

   

 

   

  6、创建的用户和共享文件夹权限

 

 

 

 二、搭建步骤

1、先对要做事物日志同步的数据库做完整备份（图形化界面就能操作，备份不做详细介绍）

 

 2、把主库上的备份还原到从节点上（使用NORECOVERY模式）

     

 

    

 

     

 

 3、主节点上数据库右键-->任务---> 传送事物日志

   

 

 4、勾选“将此数据库启用为日志传送配置中的主数据库”，设置主库的日志备份策略

   

 

   

 

    

 

 5、添加辅助数据库（从节点）

  

 

  

 

   

 

    

 

    

 6、点击确认完成

    

 7、完成查看作业

　　（1）主节点2个作业：

    

     主库生成2个job，一个alert，一个backup，没搭建一个数据库的事物日志传送，主库就相应的生成一个backup的job，alert不会增加，一个实例就一个。

     alert作业：调用存储过程sys.sp_check_log_shipping_monitor_alert

     backup作业：调用sqllogship.exe命令

（2）备库生成3个作业：

    

    备库生成3个job，分别是alert、copy、restore，没搭建一个库的事物日志传送，从库就生成一个copy和restore作业，alert作业一个实例就一个

   alert作业：调用存储过程sys.sp_check_log_shipping_monitor_alert

   copy作业：调用sqllogship.exe命令

   restore作业：也是调用sqllogship.exe命令

8、查看事物日志传送的状态

（1）主库上查看

   

 

    

（2）备库上查看

   

 三、用到的SQL

1、查看restore的历史记录

命令：select * from msdb.dbo.restorehistory  where destination_database_name='cardaddr'  order by restore_date desc

   

 

 

　　restore_type：D代表完整备份还原，L代表日志文件还原。 

2、查看还原历史记录以及对应的数据文件名称

命令：

select
h.restore_date,h.destination_database_name,h.user_name,h.restore_type,f.destination_phys_name
from msdb.dbo.restorefile f
inner join msdb.dbo.restorehistory h
on f.restore_history_id = h.restore_history_id
where h.destination_database_name='cardaddr' order by h.restore_date desc

   

 

 

 3、查看执行作业的历史记录和对象的详细信息

命令：

select * from msdb.dbo.sysjobhistory s
inner join msdb.dbo.sysjobs j on s.job_id= j.job_id
where j.name ='LSRestore_192.168.232.10,1433_cardaddr'

  

 

 

 4、查看主库的事物日志情况（在主库上执行）

命令：select  * from msdb.dbo.log_shipping_primary_databases

 

 

 5、查看备库的事物日志情况

命令：select  * from msdb.dbo.log_shipping_secondary_databases

           select  * from msdb.dbo.log_shipping_secondary（可以查看对应的主库的是哪个）

 

 

 restore_mode：0 = 用 NORECOVERY 还原日志；

　　　　　　     1 = 使用 STANDBY 还原日志。

 

 

 

 

 

***************************************************

如下是个人开发系统，欢迎大家体验，纯属个人爱好，想一块玩的，私信。

易本浪账：www.jialany.com  

***************************************************