Replication的犄角旮旯（五）--关于复制identity列

 

 

《Replication的犄角旮旯》系列导读

Replication的犄角旮旯（一）--变更订阅端表名的应用场景

Replication的犄角旮旯（二）--寻找订阅端丢失的记录

Replication的犄角旮旯（三）--聊聊@bitmap

Replication的犄角旮旯（四）--关于事务复制的监控

Replication的犄角旮旯（五）--关于复制identity列

Replication的犄角旮旯（六）-- 一个DDL引发的血案(上)（如何近似估算DDL操作进度）

Replication的犄角旮旯（七）-- 一个DDL引发的血案(下)（聊聊logreader的延迟）

Replication的犄角旮旯（八）-- 订阅与发布异构的问题

Replication的犄角旮旯（九）-- sp_setsubscriptionxactseqno，赋予订阅活力的工具

---------------------------------------华丽丽的分割线--------------------------------------------

 

今天被群友问到复制环境中identity属性的问题。在此通过几个测试说明一下identity列是如何在复制环境中实现的；

以下测试均是基于SQLSERVER 2012 SP1下的事务复制环境；

先抛出几个测试目的；

1、identity列和not for replication的关系，发布端及订阅端何时添加not for replication属性

2、快照初始化、备份初始化、不初始化订阅对not for replication参数的依赖

3、如何添加not for replication，在哪添加not for relication，以及替代not for replication的方法

 

先解释一下not for replication

NOT FOR REPLICATION

在 CREATE TABLE 语句中，可为 IDENTITY 属性、FOREIGN KEY 约束和 CHECK 约束指定 NOT FOR REPLICATION 子句。 如果为 IDENTITY 属性指定了该子句，则复制代理执行插入时，标识列中的值将不会增加。 如果为约束指定了此子句，则当复制代理执行插入、更新或删除操作时，将不会强制执行此约束。

http://msdn.microsoft.com/zh-cn/library/ms174979.aspx

简单说，对于identity、外键约束、check约束，可以通过指定not forreplication避免订阅端写入数据失败；

 

测试开始：

　　先在同一个实例下创建两个库test_byxl_1、test_byxl_2，分别作为本次测试的发布库和订阅库；

　　

　　test_byxl_1下创建tb_ident_1表，结构如下

create table test_byxl_1.dbo.tb_ident_1 (id int primary key identity ,name varchar(10))

　　添加这个表的发布，只创建publication、添加article即可；

　　

　　注意：默认的article属性中对identity的管理是手动的，这里默认即可；

　　然后再来看一下这个表的属性，已经开启了发布表的not for replication属性；

　　

　　这时候再添加订阅，并通过快照初始化，订阅端也同样有这个属性

　　

　　

　　这种按照默认配置并通过快照方式初始化的情况，可以满足identity列的同步问题；这也是最常见的情况；

===================华丽丽的分割线========================

但如果是备份初始化或者不初始化呢？

由于通过备份初始化的订阅端，不会主动添加not for replication子句，因此需要手动添加not for replication子句

alter table tb_ident_1 alter column id add not for replication

对于不初始化的情况，要么在create table的时候对identity列添加not for replication属性，要么同备份初始化一样，在同步前通过alter table 的方式添加not for replication属性；

 

===================华丽丽的分割线========================

 

有同学继续问到：那发布端一定需要not for replication属性么？

答案是no！

但为什么添加发布的时候，系统会在发布表上添加not for replication属性呢？

答案是为了方便今后通过快照初始化的订阅可以顺利拥有not for replication属性。因为在创建publication并添加article时，发布服务器并不需要也没有必要知道你即将在这个publication里添加什么类型的订阅；因此为了方便，就在创建publication的时候，检查每个article是否拥有identity列，并自动加上not for publication属性；

为此，我做了个测试来验证第一个问题（发布端一定需要not for replication属性么？）

测试开始：

　　首先，在发布端删掉not for publication属性

alter table test_byxl_1.dbo.tb_ident_1 alter column id drop not for replication

　　观察一下发布表和订阅表的属性，可以看到发布端的not for replication已经被删掉，而这个语句并没有被复制到订阅端，因此订阅端的not for replication属性仍然存在；

　　

 

　　

　　发布端写入一条测试数据，并检查发布端、订阅端数据，可以发现可以正常同步到订阅端；因此验证了上面的问题（发布端一定需要not for replication属性么？）；

  set identity_insert test_byxl_1.dbo.tb_ident_1 on 
  insert into test_byxl_1.dbo.tb_ident_1(id,name) values(10,'beijing')
  set identity_insert test_byxl_1.dbo.tb_ident_1 off

  select * from  test_byxl_1.dbo.tb_ident_1
  select * from  test_byxl_2.dbo.tb_ident_1

　　

 

===================华丽丽的分割线========================

 

我们再来做个测试；先验证一下，当订阅端没有not for replication时，会发生什么情况；

为此，我分别在发布、订阅端创建了了一个tb_ident_2表，并创建不初始化的订阅

create table test_byxl_1.dbo.tb_ident_2 (id int primary key identity ,name varchar(10))
create table test_byxl_2.dbo.tb_ident_2 (id int primary key identity ,name varchar(10))

发布端自动添加了not for replication属性，而订阅端由于手动创建，没有这个属性

　　

　　

　　插入测试数据

  set identity_insert test_byxl_1.dbo.tb_ident_2 on 
  insert into test_byxl_1.dbo.tb_ident_2(id,name) values(11,'shanghai')
  set identity_insert test_byxl_1.dbo.tb_ident_2 off

  select * from  test_byxl_1.dbo.tb_ident_2
  select * from  test_byxl_2.dbo.tb_ident_2

　　大约1分钟以后，分发代理job开始重试

　　

　　检查distribution.dbo.msrepl_errors表，出现identity_insert的错误

　　

　　手动添加订阅端的not for replication属性，为了能快速看到效果，再手动启停一下分发代理作业；　　

  alter table test_byxl_2.dbo.tb_ident_2 alter column id add not for replication

　　检测订阅端数据，发现数据已同步到订阅端；

　　

 

===================华丽丽的分割线========================

 

最后一个测试，有没有替代not for replication的方法？

我们先去掉tb_ident_2订阅端的not for replication属性

  alter table test_byxl_2.dbo.tb_ident_2 alter column id drop not for replication

然后修改订阅端对tb_ident_2表的ins存储过程，手动在insert语句前后添加identity_insert开关；

　　

　　执行保存后，再写入测试数据；订阅端已接收到最新数据；

　　

 

===================华丽丽的分割线========================

结论：

1、not for replication只有在订阅表上添加时才有效，发布表在创建发布时由系统添加只是为了快照初始化订阅时可以方便表结构同步；

2、not for replication属性，可以在create table时创建，也可以通过alter table XXX alter column ident_col add not for replication的方式后期添加；

3、通过快照初始化订阅时，不必关心identity列的同步问题，默认设置就好；

4、可以通过其他方法替代alter table XXX alter column ident_col add not for replication（如修改订阅端存储过程），但强烈不推荐；原因，你懂的……

 

欢迎拍砖