sql server日志传送实践(基于server 2008 R2)

时间:2022-08-28 16:36:12

SQL Server 2008 R2 主从数据库同步

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

sql server日志传送(基于server 2008 R2)

一、准备工作:

主数据库服务器:

OSWindows 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

从数据库服务器:

OSWindows 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,如下图所示:

sql server日志传送实践(基于server 2008 R2)

//从CXSlaveDB中,没有数据库Northwind,如下图所示:

sql server日志传送实践(基于server 2008 R2)

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

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

win + R ---> lusrmgr.msc回车,如下图所示:

sql server日志传送实践(基于server 2008 R2)

//设置sqladmin的权限

sql server日志传送实践(基于server 2008 R2)

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

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

sql server日志传送实践(基于server 2008 R2)

sql server日志传送实践(基于server 2008 R2)

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

sql server日志传送实践(基于server 2008 R2)

sql server日志传送实践(基于server 2008 R2)

三、配置SQLServer日志传送

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

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

sql server日志传送实践(基于server 2008 R2)

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

sql server日志传送实践(基于server 2008 R2)

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

sql server日志传送实践(基于server 2008 R2)

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

sql server日志传送实践(基于server 2008 R2)

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

sql server日志传送实践(基于server 2008 R2)

//事务日志备份设置,如下图所示:

sql server日志传送实践(基于server 2008 R2)

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

sql server日志传送实践(基于server 2008 R2)

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

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

sql server日志传送实践(基于server 2008 R2)

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

sql server日志传送实践(基于server 2008 R2)

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

sql server日志传送实践(基于server 2008 R2)

注:

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

sql server日志传送实践(基于server 2008 R2)

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

sql server日志传送实践(基于server 2008 R2)

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

sql server日志传送实践(基于server 2008 R2)

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

sql server日志传送实践(基于server 2008 R2)

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

sql server日志传送实践(基于server 2008 R2)

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

sql server日志传送实践(基于server 2008 R2)

//在下图中点击  确定 后,一目了然了!

sql server日志传送实践(基于server 2008 R2)

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

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

sql server日志传送实践(基于server 2008 R2)

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

//创建名为 雇员通讯录

sql server日志传送实践(基于server 2008 R2)

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

sql server日志传送实践(基于server 2008 R2)

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

sql server日志传送实践(基于server 2008 R2)

思考:

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

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

附:

1. 如何删除Northwind(备用 / 只读)数据库

sql server日志传送实践(基于server 2008 R2)

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

sql server日志传送实践(基于server 2008 R2)

原始出处 :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删除:

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

sql server日志传送实践(基于server 2008 R2)

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

sql server日志传送实践(基于server 2008 R2)

使用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、确保网络不会成为瓶颈。

sql server日志传送实践(基于server 2008 R2)的更多相关文章

  1. SQL Server 日志传送[转载]

    http://jimshu.blog.51cto.com/3171847/590413 SQL Server 2012 日志传送 一.准备: 数据库为完全恢复模式,并事先做一次完全备份. 共享一个文件 ...

  2. MS SQL 事物日志传送能否跨数据库版本吗?

    SQL SERVER的事物日志传送(log shipping)功能,相信很多人都使用过或正在应用,这是MS SQL提供的一个非常强大的功能,一般需要一个主数据库服务器(primary/producti ...

  3. 清除系统日志及数据库(sql server)日志最佳实践

    在一个项目中遇到的问题:系统日志过大,后来用delete语句删除了(相当的慢),结果数据库日志又变成很大了(差不多10G),所以又得把数据库日志删除. 方法: --备份系统中的部份日志--SELECT ...

  4. sql server 日志传送问题整理

    1.数据库备用/只读状态恢复为联机 SELECT DATABASEPROPERTYEX('ty_szum_oa_v2_bak','IsInStandBy') restore database ty_s ...

  5. SQL Server 2008 R2的发布订阅配置实践

    纸上得来终觉浅,绝知此事要躬行.搞技术尤其如此,看别人配置SQL SERVER的复制,发布-订阅.镜像.日志传送者方面的文章,感觉挺简单,好像轻轻松松的,但是当你自己去实践的时候,你会发现还真不是那么 ...

  6. SQL Server高可用——日志传送(4-3)——使用

    原文:SQL Server高可用--日志传送(4-3)--使用 顺接上一篇:SQL Server高可用--日志传送(4-2)--部署 本文为本系列最重要的一篇,讲述如何使用日志传送及一些注意事项.从上 ...

  7. SQL Server高可用——日志传送(4-1)——概论

    原文:SQL Server高可用--日志传送(4-1)--概论 本文作为学习总结,部分内容出自联机丛书及其他书籍 日志传送是什么? SQLServer 2012之前(2012出现了AlwaysOn), ...

  8. 【转载】SQL Server 2012 日志传送

    SQL Server 2012 日志传送 一.准备: 数据库为完全恢复模式,并事先做一次完全备份. 共享一个文件夹,主机备份放在这个文件夹,而且客户机有权访问这个共享文件夹. 二.基本配置 1.启动配 ...

  9. 一张图解释SQL Server集群、镜像、复制、日志传送

    一张图解释SQL Server集群.镜像.复制.日志传送 本文版权归作者所有,未经作者同意不得转载.

随机推荐

  1. DataTable、List使用groupby进行分组和分组统计;List、DataTable查询筛选方法

    DataTable分组统计: .用两层循环计算,前提条件是数据已经按分组的列排好序的. DataTable dt = new DataTable(); dt.Columns.AddRange(new ...

  2. Oracle sysdate 时间加减

    加法 select sysdate,add_months(sysdate,12) from dual;        --加1年 select sysdate,add_months(sysdate,1 ...

  3. Working with Data » Getting started with ASP.NET Core and Entity Framework Core using Visual Studio » 排序、筛选、分页以及分组

    Sorting, filtering, paging, and grouping 7 of 8 people found this helpful By Tom Dykstra The Contoso ...

  4. 【测试】使用hr用户下的employees和departments表写一条SQL语句,(MG连接)

    SQL> select * from employees d, departments t where d.department_id=t.department_id; rows selecte ...

  5. Timus 1746 Hyperrook

    题意:在一个n维坐标系中,坐标的范围是0到m - 1,如果两个点坐标只有一个维度的坐标不同则可以相互移动,给出p个点,问任意两个点之间路径为d的个数是多少,答案与p取模. 解法:只需要考虑两个点之间不 ...

  6. 一些嵌入式和FPGA相关模块的开源

    工作一年,整理下手头做过的东西,分享出来,希望能帮到大家. 嵌入式方面,主要集中在Xilinx家的器件上,ZYNQ居多.Linux相关的就不贴了,网上的资料太多,xilinx-wiki上资料都是比较全 ...

  7. magento添加系统sections配置时应注意的事项

    (1)只有在新增sections是需要增加对应的acl配置,这个配置可以放在config.xml中或者放在adminhtml.xml中 <adminhtml> <acl> &l ...

  8. Java &comma;python面向对象的继承及其区别

    JAVA JAVA继承基本样式 class Demo extends Object{ Demo(int a){ this(); } Demo(){ super(); } } java默认继承Objec ...

  9. 深度学习attention 机制了解

    Attention是一种用于提升基于RNN(LSTM或GRU)的Encoder + Decoder模型的效果的的机制(Mechanism),一般称为Attention Mechanism.Attent ...

  10. C&num;通过盘符获取剩余空间

    public static long GetHardDiskSpace(string str_HardDiskName) { ; str_HardDiskName = str_HardDiskName ...