当前位置:   article > 正文

SQLServer数据库的发布订阅读写分离主从复制对新增表的自动同步深究_sqlserver新增表数据自动推送

sqlserver新增表数据自动推送

此文背景、场景

此文背景:读这篇文章的时候,假定您已经做了发布订阅的事了,但是在主库中不断的有新增的表产生,这时候新增的表不会自动同步到从库中,我这里主要解决这个问题的,在这里不再探讨如何从头做SQLserver的发布订阅,如果您还不会如何做SQLserver发布订阅,可以移步网络上其他文章。

SQLServer数据库的主从复制时遇到的问题

最近搞SQLServer数据库的主从复制时遇到一个问题,每个公司可能业务不一样,对数据库的架构也不一样,我们公司的数据由于设备多,每台设备每分钟产生一条数据,数据库采用的是每台设备单独生成一张表的数据,这样一来在数据库的读写分离的时候就遇到一个问题,因为设备的增加不知道是什么时候,设备增加了,会自动创建表,而做了发布订阅后,后来新增加的表,不会自动同步到从库中,于是开始想解决方案。
在列出方案前,我先操作一下,发布订阅后手动新增表的操作

先说说发布订阅后手动新增表的操作

先回忆一下如果新增表时人工同步如何操作:
假如主库中新增了一个表test06
右建发布名->点击属性
sqlserver本地发布
发布属性
在上面的发布对象列中看不到新增的表test06 , 此时去掉勾选右侧里的 “仅显示列表中已选中的项目”
点击确定即可。
此时只是把这个表加入了发布的队列中,此时从库中不会出现test06表,我们还要重新启动一下复制监视器中的代理。
操作如下:

复制监视器
过一会儿,代理启动完毕 100%时, 发现从库中已经有了 test06了,这是手动去更新新增表的操作,如果你的业务中不经常新增表,那么了解到此也已足够了,然而,我的设备表是不定时的自动新增表的,就不能手动去同步了。这时候就得另一个解决方案了。

方案一、只同步主库中的中间表,到了从库在这张表上建立触发器来分发到设备详细表。

也就是说,在主库中我建一个中间表table1, 把所有设备发来的数据都存放在这个表中,使用设备ID来区分每台设备的数据,从库中只同步这个表table1即可,而在从库中的table1表上建立触发器,当新增数据的时候, 再分发到每台设备的详细表中。这样不用操心发布订阅的新增表的问题了,可是后来想下这个方法解决了主从复制新增表的问题,但是新的问题又来了,在从库中的触发器中把数据insert 到每台设备中,这很明显对从数据库来说又有写入的操作了,本来做读写分离就是写库读库分离开,而现在又回到了从库中有写入的操作(且这个写入操作很频繁,每秒都会产生很多),所以不可取,还要继续探究其他方案。

方案二、使用程序执行SQL语句来代替人工重新启动复制监视器的代理。

在主库中为了能发现有新增设备表的情况 ,总得有一个地方可以监控到新增了设备表,要么你使用存储过程去轮循设备总表(就是记录所有设备ID的一张表)和你记录设备的表是不是有新增,要么你使用其他方法,总之,要找到新增表的时刻。
我这里使用的方案是 所有的设备数据过来后,都会入一张临时表t1, 这张表中有触发器再去建立设备详细表,我就在此时下手脚了,在这个触发器中判断 如果有新增的设备时,执行如下语句 :

use Test --主库
go

EXEC sp_addarticle @publication = N'test01',            --发布名称
                   @article = N'test06',                --新增的表名
                   @source_owner = N'dbo',
                   @source_object = N'test06',          --新增的表名
                   @type = N'logbased',
                   @description = NULL,
                   @creation_script = NULL,
                   @pre_creation_cmd = N'drop',
                   @schema_option = 0x000000000803509F,
                   @identityrangemanagementoption = N'manual',
                   @destination_table = N'test06',      --新增的表名
                   @destination_owner = N'dbo',
                   @vertical_partition = N'false', --是否要启用筛选列,启用时,删除所有列,单独配置相关列(必须包含主键列),不启用时,自动添加所有列
                   @ins_cmd = N'CALL sp_MSins_dbo_test06', -- 触发器命名规则,以新增的表名后辍
                   @del_cmd = N'CALL sp_MSdel_dbo_test06',
                   @upd_cmd = N'SCALL sp_MSupd_dbo_test06',
				   @force_invalidate_snapshot=1;
go
--重新启动sqlserver中的复制监视器的代理
EXEC sys.sp_startpublication_snapshot @publication = N'test01' ; --发布名称
go
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24

这样就可以在新增表时,自动把新增的表同步到从库中去了。

以下有关数据库发布订阅的积累的问题也分享于此

一、主库数据表中含有单引号‘ 引发的不同步的问题

主库中一个字段内容中含有单引号’, 会导致从库同步时出错,解决方案,尽量使入库的字段内容中不含有单引号,如含有,可进行转义后再入库。(也可能不是这个原因)

二、 主库表会经常增加,新增表后如何操作?

新增表后,点击分发服务器右键的属性,点击项目,勾选新增的表,点击确定。再点击查看复制监视器,点击代理,重新启动代理即可,此时,要注意一直跟踪,直到数据真正正式同步才可离开。

三、如何避免频繁的新增表操作?

如果需要动态创建架构一样的多个表,可使用一张中间表,数据都向这个中间表中插入,然后使用从库中的中间表的触发器在从库中新建多个表。如你有 table01, table02,…等多个表,字段都一样(有这种需求,分表操作),这时可以使用主库中只建一张表,再向从库中同步数据,然后这个从库中的表建一个触发器,使用这个触发器来分发到多个数据表中。

四、如何根据事务序列号查找SQL语句?

use distribution
go
select * from
dbo.MSarticles m
where exists (select mc.article_id from MSrepl_commands mc where mc.xact_seqno=0x0000060700016FF6000D00000000 AND mc.article_id = m.article_id )

EXEC Sp_browsereplcmds
@xact_seqno_start='0x0000060700016FF6000D00000000',
@xact_seqno_end='0x0000060700016FF6000D00000000'
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

五、使用SQL Server发布数据库快照遇到错误:对路径“xxxxx”访问被拒绝的解决方法

开始-> 所有程序 -> SQL Server 配置管理器 -> 在左边栏选择”SQL Server服
务“->在右侧面板中"SQL Server 代理”一行上右击,选择“属性”,
在弹出的对话框中选择"内置账户“->LocalSystem即可。

六、SQL Server 配置管理器不见了如何找到?

找到目录:C:\Windows\SysWOW64,并且找到该目录下的 SQLServerManager12.msc 文件,也有的是11文件,根据你安装的版本不同而定。

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/凡人多烦事01/article/detail/155958
推荐阅读
相关标签
  

闽ICP备14008679号