SQLSERVER2008发布订阅（踩坑）增量同步数据（图文）

前言在网上查了N多资料，居然没有详细写如何配置增量同步的，我在自己配置的时候就遇到一些误区和麻烦，现在自己总结一下，留着以后遇到用的时候可以查看 一、环境准备 发布服务器：192.168.56.11操作系统版本WINDOWS2008数据库版本SQLSERVER 2008R2订阅服务器：192.168.56.12操作系统版本WINDOWS2012数据库版本SQLSERVER 2016 SP2 二、发布服务器配置1、创建快照存放文件夹，并设置为共享一般情况下，C盘为操作系统盘，不考虑作为快照存放位置D盘为数据库存放盘符，同样不考虑E盘为备份盘，可考虑 我这里的E盘作为数据库的备份文件存放盘符所以创建了一个文件夹BI_COPY，并设置为everyone 读写权限共享。这里过程过于简单就不截图了快照路径就是E:\BI_COPY快照实际是存放着你要同步的表的一个快照，如果数据量很大，这个文件夹也会很大这是我的理解，别人都不敢说，哈哈 2、创建分发数据库文件存放文件夹路径E:\BI_COPY_TEMP_DB因为我们将要配置的是事务日志的发布订阅的同步方式，所以分发数据库distribution实际上相当于存放着事务日志这是我的理解 ，别人都不敢说，哈哈 3、在发布服务器上打开SQLSERVER控制台一定要使用计算机名来登陆哦，不要使用IP登陆，不然后续配置一些东西的时候会遇到报错 下面就开始上图吧 4、配置分发点击复制右键，选择配置分发这个选项（这是第一个坑，当时被坑的不要不要的，差点被搞死）（如果你之前直接配置了发布向导之类的，这个选项就看不见了，建议是把你原来操作的，全部删除了重新来，不然又会遇到另一个坑，这里不详细写，在本文最后面写） 选择配置分发后，出现配置分发向导，直接下一步 选择分发服务器，我这里是发布和分发都放在发布服务器上，点下一步 设置前面已创建好的快照文件夹路径，下一步 这里就是坑的关键点，设置分发数据库文件所在路径分发数据库名称可以是默认也可以自己命名，我这里默认了（默认是存放在C盘的，如果不注意，就会把C盘撑爆） 直接下一步 直接下一步 完成即可 5、发布服务在发布服务器上操作右键点击本地发布，选择新建发布 出现新建发布向导，直接下一步 选择要发布的数据库，下一步（你要同步那些表，就选哪个库） 选择事务发布 选择要发布的表，勾选 勾选仅显示列表中已选中的项目，可以只显示你想要发布的表 右键点击该表，选择设置此表项目的属性 可修改订阅到的目标表名称，这个可随你心意选择截断现有对象中的所有数据（其实这个只要快照重新初始化的话，目标表的数据一样会重新初始化的） 点击确定后 直接下一步，下一步 勾选，立即创建快照并初始化（这里又有一个坑，千万别勾选下面那个计划运行代理，因为快照只要生成一次就够了） 下一步点击安全设置 选择在SQLSERVER代理服务器下运行选择使用SQLSERVER登录名，比如发布服务器的sa密码即可（当然，你也可以自己创建的一个用户，只需这个用户拥有发布数据库的db_owner权限即可） 设置好了，确定，下一步 下一步，设置发布名称，完成 6、订阅服务 在订阅服务器上进行操作在订阅服务器上创建一个数据库，这里过程过于简单就不赘述右键点击本地订阅，选择新建订阅 出现新建订阅向导，直接下一步 选择查找SQLSERVER发布服务器 输入帐密，连接登陆发布服务器 下一步 选择在分发服务器上运行所有代理，下一步 选择之前创建好的订阅数据库，下一步 点击这个... 选择在SQLSERVER代理服务运行选择SQLSERVER账户，这里是订阅服务器的sa帐密 确定，下一步 默认连续运行，下一步 默认立即初始化，下一步 默认创建订阅，下一步 点击完成，订阅完成 后续的，就是自己检查，发布订阅，数据是否成功。 7、各种骚操作在发布服务器上，可以查看 发布快照，分发步骤执行日志，订阅监视等 右键点击本地发布，选择启动复制监视器 这里自己研究就可以了，很简单的 这里就是增量同步了，日志读取器代理，实际就是做事务日志同步复制的 注意事项：1、在订阅库上通过同步复制过来的表，表上是可以新建与源表不同的索引的哦。（在发布订阅，第一次同步完成之后）2、遇到某些时候，有大量同步复制数据，影响到其他一些作业任务，可以通过设置计划任务来规避 点击右键，属性，计划，编辑， 这里看到默认是SQL SERVER代理启动时自动启动，看着选择日期啥的都是灰色，以为不可选？错了，改成重复执行后，就可以选择了。 执行间隔，开始时间，结束时间这些，都变成可选状态，自己操作吧。里面注意，把分发和LogReader的作业都修改一下计划，快照只需要一个就可以，所以不用管它 下面骚操作来啦，如果你之前看到那个 在本文最后写的，就是这里了 之前配置发布订阅的时候，比较菜鸟，不太会，就按着网上的，直接配了起来，结果，因为分发数据文件存放在了C盘，结果把C盘撑爆了。 这个时候怎么办，删除重来？是的，但是删除也是要有技巧的，不然还删不掉，好坑哦 1、首先，先把订阅服务器上的订阅服务删除2、把发布服务器上的发布任务及发布作业删除3、登陆master库，使用命令删除distribution库（就是分发库）命令如下：查询订阅信息，如果为空，则跳过exec sp_helpsubscriberinfo如果不为空，则删除exec sp_dropsubscriber  '查出的订阅名称'查询分发发布信息，如果为空，则跳过exec sp_helpdistpublisher如果不为空，则删除exec sp_dropdistpublisher ' 查出的分发名称'删除数据库exec sp_dropdistributiondb 'distribution'   --这个distribution是你创建的分发库 如果删不掉，设置offline再删除 alter database distribution set offlinedrop database distribution; 如果还是报错删不掉，那就可以试试，再创建发布分发，再删除。反复删，总能删掉 记得，把对应的数据文件和日志文件也要删了 昨天又踩到新坑了，记录总结一下。这个坑就是清除分发的问题，其实就是distribution这个库的问题。问题现象：早上查看监控，发现读IO爆满，使用SQL检查，如下： 物理读排序select top 10     (total_logical_reads/execution_count) as avg_logical_reads,    (total_logical_writes/execution_count) as avg_logical_writes,    (total_physical_reads/execution_count) as avg_phys_reads,     Execution_count,     statement_start_offset as stmt_start_offset, statement_end_offset as stmt_end_offset,substring(sql_text.text, (statement_start_offset/2), case when (statement_end_offset -statement_start_offset)/2 <=0 then 64000else (statement_end_offset -statement_start_offset)/2 end) as exec_statement,  sql_text.text,plan_text.*from sys.dm_exec_query_stats  cross apply sys.dm_exec_sql_text(sql_handle) as sql_textcross apply sys.dm_exec_query_plan(plan_handle) as plan_textorder by (total_physical_reads/execution_count) Desc 因为磁盘读IO占满了，所以只看物理读IO最高那个，我查到的是 嗯，这个游标占用了大量物理读IO，然后看详细的那个存储过程 sp_MSdelete_publisherdb_trans   发现实际就是这个分发清除的作业，而且这个可恶的分发清除作业居然是每10分钟跑一次。   怎么办呢？解决方案：找到这个作业，分发清除:distribution，右键，点击属性，选择步骤，编辑发现这个max_distretention=72（默认值是这个），把它修改成36或者缩短一些，具体可以根据你业务需求。我这里是缩短为36个小时 然后是设置执行间隔时间，选择计划，编辑，自己按业务需求设置一下执行时间和间隔。 我这里是选了一个业务空闲的时间来执行。 然后就是修改存储过程 sp_MSdelete_publisherdb_trans 将里面原本是delete top (2000)行的删除，可以设置成200000，或者500000,1000000，具体根据你需求， 可以先查询一下，一小时有多少数据库可以删除，自己设置一个合理的数值 （如果你的SQLSERVER是2016版本可能就找不到delete top 2000的代码，它用了参数替代，这个时候你需要自己给一个固定值 DELETE TOP (200000) MSrepl_commands WITH (PAGLOCK) from MSrepl_commands with (INDEX(ucMSrepl_commands))） 这里注意，选择一下distribution库进行查询，或者 use distributionSELECT T.[publisher_database_id],datepart(mm,[entry_time]) 'month', datepart(dd,[entry_time]) 'day', datepart(hh,[entry_time]) 'hour'    ,count(C.[xact_seqno]) 'count of commands'FROM [distribution].[dbo].[MSrepl_transactions](nolock) T JOIN [MSrepl_commands](nolock) C ON T.[xact_seqno] = C.[xact_seqno]GROUP BY T.[publisher_database_id]  ,datepart(mm,[entry_time])  , datepart(dd,[entry_time])  , datepart(hh,[entry_time])order by 1,2,3,4 OK，上面该设置的都设置好了，由于第一次执行删除大量数据的话，可能会比较耗时耗资源，可以先自己找个空闲时间，手动操作一波 EXEC dbo .sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 36 然后收缩一下数据库 DBCC SHRINKDATABASE ( '数据库名' ); 我这里是叫distribution DBCC SHRINKDATABASE ( 'distribution' ); 遇到开发要新增新的表进行同步复制怎么办？（这里如果是事务模式貌似会过一段时间出现报错无法同步，快照模式貌似可以） 在发布服务器上，右键你发布的服务器，点击属性 发布项目属性，点击项目选项卡，去掉勾选 仅显示列表中已选中的项目 然后选择要对新的表做同步复制的，设置好新表的相关属性，点击确定 右键发布服务器，选择启动复制监视器 选择代理，快照代理，重启启动一下快照代理，其实就是生成一下你新添加表的同步复制快照 快照生成完了之后，会自动进行同步复制，后面的事情就不需要管了，注意下如果是新添加大表，找个系统不那么繁忙的时间整就好。 注意： 1、在采用 事务 方式进行同步复制时，该表必须要有 主键，且 主键必须也要同步复制 2、第一波同步复制，如果操作有问题，可以先设置，所有表，目标对象，名称已被使用时的操作，设置为删除现有对象并创建新对象， 启动快照，同步完成后进行下一步骤3 3、弄好之后，再设置所有表， 目标对象，名称已被使用时的操作，设置为截断现有对象中的所有数据。 启动快照，重新初始化， 使用新快照，立即生成新快照 4、查看同步状态，只要不是快照不可用就正常了。（貌似这种操作只有在快照模式才能正常操作） 5、同步复制的主表， 不能进行truncate 和 drop 操作 6、新加的表要做同步复制，必须重新加入到发布里面，原有表新增字段则不需要（如果是全表选择的） 7、备库的表可以随意操作，不过都会在很快时间里同步回来，能永久更改的只有索引（如果索引没） 如果是给本来就已经在同步复制中的表，添加字段，同步复制功能会自动同步。（事务模式） 重要踩坑！！！ 在已经建立好同步复制功能后，想要再加入新的表进行同步复制，需要把同步复制删除，必须重新配置同步复制，否则发生奇葩报错，暂未找到原因。 报错如下： Replication-复制分发子系统: agent ***** scheduled for retry. 进程无法连接到 Subscriber“***”。 Replication-复制分发子系统: agent **** failed. 应用复制的命令时在订阅服务器上找不到该行。 DBCC TRACEOFF 3604, server process ID (SPID) 81. This is an informational message only; no user action is required. （虽然有试过，在已经建立好同步复制功能后，加入新的表进行同步复制，然后，重新启动一下快照代理，就成功的例子2008R2的发布，2016的订阅）

快照代理重启后（一般同步复制在正常跑的时候，快照代理是非启动状态），原表数据不会动，新表会加入同步复制。