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

此文背景、场景SQLServer数据库的主从复制时遇到的问题先说说发布订阅后手动新增表的操作方案一、只同步主库中的中间表，到了从库在这张表上建立触发器来分发到设备详细表。方案二、使用程序执行SQL语句来代替人工重新启动复制监视器的代理。以下有关数据库发布订阅的积累的问题也分享于此一、主库数据表中含有单引号‘ 引发的不同步的问题二、 主库表会经常增加，新增表后如何操作？三、如何避免频繁的新增表操作？四、如何根据事务序列号查找SQL语句？五、使用SQL Server发布数据库快照遇到错误：对路径“xxxxx”访问被拒绝的解决方法六、SQL Server 配置管理器不见了如何找到？

此文背景、场景

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

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

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

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

先回忆一下如果新增表时人工同步如何操作： 假如主库中新增了一个表test06 右建发布名->点击属性 在上面的发布对象列中看不到新增的表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

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

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

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

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

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

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

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

如果需要动态创建架构一样的多个表，可使用一张中间表，数据都向这个中间表中插入，然后使用从库中的中间表的触发器在从库中新建多个表。如你有 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'

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

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

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

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