Replication的犄角旮旯（五）--关于复制identity列
?
?
《Replication的犄角旮旯》系列導(dǎo)讀
Replication的犄角旮旯（一）--變更訂閱端表名的應(yīng)用場(chǎng)景
Replication的犄角旮旯（二）--尋找訂閱端丟失的記錄
Replication的犄角旮旯（三）--聊聊@bitmap
Replication的犄角旮旯（四）--關(guān)于事務(wù)復(fù)制的監(jiān)控
Replication的犄角旮旯（五）--關(guān)于復(fù)制identity列
Replication的犄角旮旯（六）-- 一個(gè)DDL引發(fā)的血案(上)（如何近似估算DDL操作進(jìn)度）
Replication的犄角旮旯（七）-- 一個(gè)DDL引發(fā)的血案(下)（聊聊logreader的延遲）
Replication的犄角旮旯（八）-- 訂閱與發(fā)布異構(gòu)的問題
Replication的犄角旮旯（九）-- sp_setsubscriptionxactseqno，賦予訂閱活力的工具
---------------------------------------華麗麗的分割線--------------------------------------------
?
今天被群友問到復(fù)制環(huán)境中identity屬性的問題。在此通過(guò)幾個(gè)測(cè)試說(shuō)明一下identity列是如何在復(fù)制環(huán)境中實(shí)現(xiàn)的；
以下測(cè)試均是基于SQLSERVER 2012 SP1下的事務(wù)復(fù)制環(huán)境；
先拋出幾個(gè)測(cè)試目的；
1、identity列和not for replication的關(guān)系，發(fā)布端及訂閱端何時(shí)添加not for replication屬性
2、快照初始化、備份初始化、不初始化訂閱對(duì)not for replication參數(shù)的依賴
3、如何添加not for replication，在哪添加not for relication，以及替代not for replication的方法
?
先解釋一下not for replication
NOT FOR REPLICATION在 CREATE TABLE 語(yǔ)句中，可為 IDENTITY 屬性、FOREIGN KEY 約束和 CHECK 約束指定 NOT FOR REPLICATION 子句。 如果為 IDENTITY 屬性指定了該子句，則復(fù)制代理執(zhí)行插入時(shí)，標(biāo)識(shí)列中的值將不會(huì)增加。 如果為約束指定了此子句，則當(dāng)復(fù)制代理執(zhí)行插入、更新或刪除操作時(shí)，將不會(huì)強(qiáng)制執(zhí)行此約束。
http://msdn.microsoft.com/zh-cn/library/ms174979.aspx
簡(jiǎn)單說(shuō)，對(duì)于identity、外鍵約束、check約束，可以通過(guò)指定not forreplication避免訂閱端寫入數(shù)據(jù)失敗；
?
測(cè)試開始：
先在同一個(gè)實(shí)例下創(chuàng)建兩個(gè)庫(kù)test_byxl_1、test_byxl_2，分別作為本次測(cè)試的發(fā)布庫(kù)和訂閱庫(kù)；
test_byxl_1下創(chuàng)建tb_ident_1表，結(jié)構(gòu)如下
1 create table test_byxl_1.dbo.tb_ident_1 (id int primary key identity ,name varchar(10)) View Code添加這個(gè)表的發(fā)布，只創(chuàng)建publication、添加article即可；
注意：默認(rèn)的article屬性中對(duì)identity的管理是手動(dòng)的，這里默認(rèn)即可；
然后再來(lái)看一下這個(gè)表的屬性，已經(jīng)開啟了發(fā)布表的not for replication屬性；
這時(shí)候再添加訂閱，并通過(guò)快照初始化，訂閱端也同樣有這個(gè)屬性
這種按照默認(rèn)配置并通過(guò)快照方式初始化的情況，可以滿足identity列的同步問題；這也是最常見的情況；
===================華麗麗的分割線========================
但如果是備份初始化或者不初始化呢？
由于通過(guò)備份初始化的訂閱端，不會(huì)主動(dòng)添加not for replication子句，因此需要手動(dòng)添加not for replication子句
alter table tb_ident_1 alter column id add not for replication對(duì)于不初始化的情況，要么在create table的時(shí)候?qū)dentity列添加not for replication屬性，要么同備份初始化一樣，在同步前通過(guò)alter table 的方式添加not for replication屬性；
?
===================華麗麗的分割線========================
?
有同學(xué)繼續(xù)問到：那發(fā)布端一定需要not for replication屬性么？
答案是no！
但為什么添加發(fā)布的時(shí)候，系統(tǒng)會(huì)在發(fā)布表上添加not for replication屬性呢？
答案是為了方便今后通過(guò)快照初始化的訂閱可以順利擁有not for replication屬性。因?yàn)樵趧?chuàng)建publication并添加article時(shí)，發(fā)布服務(wù)器并不需要也沒有必要知道你即將在這個(gè)publication里添加什么類型的訂閱；因此為了方便，就在創(chuàng)建publication的時(shí)候，檢查每個(gè)article是否擁有identity列，并自動(dòng)加上not for publication屬性；
為此，我做了個(gè)測(cè)試來(lái)驗(yàn)證第一個(gè)問題（發(fā)布端一定需要not for replication屬性么？）
測(cè)試開始：
首先，在發(fā)布端刪掉not for publication屬性
alter table test_byxl_1.dbo.tb_ident_1 alter column id drop not for replication觀察一下發(fā)布表和訂閱表的屬性，可以看到發(fā)布端的not for replication已經(jīng)被刪掉，而這個(gè)語(yǔ)句并沒有被復(fù)制到訂閱端，因此訂閱端的not for replication屬性仍然存在；
?
發(fā)布端寫入一條測(cè)試數(shù)據(jù)，并檢查發(fā)布端、訂閱端數(shù)據(jù)，可以發(fā)現(xiàn)可以正常同步到訂閱端；因此驗(yàn)證了上面的問題（發(fā)布端一定需要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 offselect * from test_byxl_1.dbo.tb_ident_1select * from test_byxl_2.dbo.tb_ident_1
?
===================華麗麗的分割線========================
?
我們?cè)賮?lái)做個(gè)測(cè)試；先驗(yàn)證一下，當(dāng)訂閱端沒有not for replication時(shí)，會(huì)發(fā)生什么情況；
為此，我分別在發(fā)布、訂閱端創(chuàng)建了了一個(gè)tb_ident_2表，并創(chuàng)建不初始化的訂閱
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))發(fā)布端自動(dòng)添加了not for replication屬性，而訂閱端由于手動(dòng)創(chuàng)建，沒有這個(gè)屬性
插入測(cè)試數(shù)據(jù)
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 offselect * from test_byxl_1.dbo.tb_ident_2select * from test_byxl_2.dbo.tb_ident_2大約1分鐘以后，分發(fā)代理job開始重試
檢查distribution.dbo.msrepl_errors表，出現(xiàn)identity_insert的錯(cuò)誤
手動(dòng)添加訂閱端的not for replication屬性，為了能快速看到效果，再手動(dòng)啟停一下分發(fā)代理作業(yè)；
alter table test_byxl_2.dbo.tb_ident_2 alter column id add not for replication檢測(cè)訂閱端數(shù)據(jù)，發(fā)現(xiàn)數(shù)據(jù)已同步到訂閱端；
?
===================華麗麗的分割線========================
?
最后一個(gè)測(cè)試，有沒有替代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然后修改訂閱端對(duì)tb_ident_2表的ins存儲(chǔ)過(guò)程，手動(dòng)在insert語(yǔ)句前后添加identity_insert開關(guān)；
執(zhí)行保存后，再寫入測(cè)試數(shù)據(jù)；訂閱端已接收到最新數(shù)據(jù)；
?
===================華麗麗的分割線========================
結(jié)論：
1、not for replication只有在訂閱表上添加時(shí)才有效，發(fā)布表在創(chuàng)建發(fā)布時(shí)由系統(tǒng)添加只是為了快照初始化訂閱時(shí)可以方便表結(jié)構(gòu)同步；
2、not for replication屬性，可以在create table時(shí)創(chuàng)建，也可以通過(guò)alter table XXX alter column ident_col add not for replication的方式后期添加；
3、通過(guò)快照初始化訂閱時(shí)，不必關(guān)心identity列的同步問題，默認(rèn)設(shè)置就好；
4、可以通過(guò)其他方法替代alter table XXX alter column ident_col add not for replication（如修改訂閱端存儲(chǔ)過(guò)程），但強(qiáng)烈不推薦；原因，你懂的……
?
歡迎拍磚
?
posted on 2015-02-12 17:18 NET未來(lái)之路 閱讀(...) 評(píng)論(...) 編輯 收藏轉(zhuǎn)載于:https://www.cnblogs.com/lonelyxmas/p/4288544.html
總結(jié)
以上是生活随笔為你收集整理的Replication的犄角旮旯（五）--关于复制identity列的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 《京东技术解密》读后感
- 下一篇： Sqlserver循环嵌套