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

此文背景、场景

此文背景：读这篇文章的时候，假定您已经做了发布订阅的事了，但是在主库中不断的有新增的表产生，这时候新增的表不会自动同步到从库中，我这里主要解决这个问题的，在这里不再探讨如何从头做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
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文件，根据你安装的版本不同而定。