搜尋本站文章

2017-06-05

[SQL Server][Replication]:複寫使用者正在插入至 NOT FOR REPLICATION 識別欄位時...遭遇錯誤

如果交易式複寫,可以成功的使用快照集初始化訂閱,但卻失敗於從備份初始化交易式訂閱。

有可能就是踩到 觸發程序(Trigger) 這個雷...


若已經確認在訂閱者端(Subscriber)的資料表上,有設定 NOT FOR REPLICATION。
接下來,請檢查資料表上是否有觸發程序(Trigger)。

若資料表上有觸發程序(Trigger),請與 RD 討論其影響:

在單向的交易式複寫機制下,是否可以停用此觸發程序(Disable Trigger),或是加入設定 NOT FOR REPLICATION 等。

遭遇的錯誤

-- 01_Error_NOT_FOR_REPLICATION


-- 02_在訂閱者端(Subscriber),停用觸發程序(Trigger)


-- 03_複寫機制恢復正常運作





發生錯誤的情境

採取單向的交易式複寫機制,資料由發行者端(Publisher)複寫到訂閱者端(Subscriber)。但資料表的 IDENTITY_INSERT 屬性設定為 ON,因此需要明確提供此識別欄位(identity column)的資料值。


可能的原因
  1. 在訂閱者端(Subscriber)的資料表,沒有設定 NOT FOR REPLICATION。
  2. 在訂閱者端(Subscriber)的資料表,有設定 NOT FOR REPLICATION,但資料表上有使用者觸發程序(Trigger)。
  3. 採用從備份初始化交易式訂閱(Initialize a Transactional Subscription from a Backup ),但資料表上有使用者觸發程序(Trigger)。

初步觀察 - 交易式複寫
  1. 失敗:從備份初始化交易式訂閱
  2. 成功:使用快照集初始化訂閱



錯誤訊息

Explicit value must be specified for identity column in table tablename either 
when IDENTITY_INSERT is set to ON or when a replication user is inserting 
into a NOT FOR REPLICATION identity column.


當 IDENTITY_INSERT 設定為 ON 或複寫使用者正在插入至 NOT FOR REPLICATION 
識別欄位時,必須為資料表 'xyz_log' 的識別欄位指定外顯值。


可行的解決方案
  
已知需求:採取單向的交易式複寫

  1. 不採用從備份初始化交易式訂閱,改用快照集(Snapshot)方式來初始化複寫。
  2. 確認在訂閱者端(Subscriber)的資料表上,需要設定 NOT FOR REPLICATION。
  3. 若已確認在訂閱者端(Subscriber)的資料表上,已有設定 NOT FOR REPLICATION。
    • 接下來,請檢查資料表上是否有觸發程序。
    • 若有,請與RD其影響,確認是否可以停用、設定NOT FOR REPLICATION等。

-- 04_資料表設定:不可複寫


-- 05_資料表_NOT FOR REPLICATION


-- 06_在訂閱者端(Subscriber),停用觸發程序(Trigger)


-- 07_發行項,預設:不包含「複製使用者觸發程序」





檢視遭遇的錯誤訊息

當 IDENTITY_INSERT 設定為 ON 或複寫使用者正在插入至 NOT FOR REPLICATION 
識別欄位時,必須為資料表 'xyz_log' 的識別欄位指定外顯值。

執行批次指令時發生錯誤,請嘗試個別執行指令。

正在初始化


嘗試執行的命令:
declare @event_type SMALLINT = 1, @distributor_major_version SMALLINT = 13, 
@distributor_minor_version SMALLINT = 0, @distributor_build_number SMALLINT = 4202,
 @totalruntime INT = 75094, @totalworktime INT = 0, @totalnumtrans INT = 0, 
@numtranspersec REAL = 0.00, @totalnumcmds INT = 0, @numcmdspersec REAL = 0.00, 
@totalskippedcmds INT = 0, @totalidletime INT = 0 
if object_id(N'sys.sp_repl_generate_subscriber_event') 
is not null exec sys.sp_repl_generate_subscriber_event @event_state = @event_type,@distribut

(交易序號: 0x0000004800000140004200000000,命令識別碼: 1)

錯誤訊息:
當 IDENTITY_INSERT 設定為 ON 或複寫使用者正在插入至 NOT FOR REPLICATION 
識別欄位時,必須為資料表 'xyz_log' 的識別欄位指定外顯值。 
(來源: MSSQLServer,錯誤號碼: 545)
取得說明: http://help/545

當 IDENTITY_INSERT 設定為 ON 或複寫使用者正在插入至 NOT FOR REPLICATION 
識別欄位時,必須為資料表 'xyz_log' 的識別欄位指定外顯值。 
(來源: MSSQLServer,錯誤號碼: 545)
取得說明: http://help/545

-- 08_Error_NOT_FOR_REPLICATION


-- 09_Error_NOT_FOR_REPLICATION





建立或修改觸發程序,加入 NOT FOR REPLICATION


NOT FOR REPLICATION
適用於: SQL Server 2008 至 SQL Server 2017。

範例:
ALTER TRIGGER [dbo].[Update_Stock] 
   ON  [dbo].[Orders]
   AFTER  INSERT
NOT for Replication — '標註不用於複寫'
...





參考資料

從備份初始化交易式訂閱 (複寫 Transact-SQL 程式設計)
https://msdn.microsoft.com/zh-tw/library/ms147834(v=sql.130).aspx

為交易式發行集啟用使用備份的初始化 (SQL Server Management Studio)
https://msdn.microsoft.com/zh-tw/library/ms152556(v=sql.130).aspx

複寫識別欄位
https://msdn.microsoft.com/zh-tw/library/ms152543.aspx

停用複寫的檢查條件約束
https://msdn.microsoft.com/zh-tw/library/ms190235.aspx

使用 NOT FOR REPLICATION 控制條件約束、識別和觸發程序
https://technet.microsoft.com/zh-tw/library/ms152529(v=sql.105).aspx

Marking Identity Columns as “Not For Replication” in existing Publication
EXEC sys.sp_identitycolumnforreplication
https://blogs.msdn.microsoft.com/repltalk/2012/03/05/marking-identity-columns-as-not-for-replication-in-existing-publication/

All about “Not for Replication”
https://blogs.msdn.microsoft.com/repltalk/2010/02/22/all-about-not-for-replication/

CREATE TRIGGER (Transact-SQL)
https://msdn.microsoft.com/zh-tw/library/ms189799.aspx

Problem with replicating identity column
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/12f48fb2-e5a0-445c-93ac-62c23cd294fe/problem-with-replicating-identity-column?forum=sqlreplication

使用快照集初始化訂閱
https://msdn.microsoft.com/zh-tw/library/ms151795.aspx

不使用快照集初始化交易式訂閱
https://msdn.microsoft.com/zh-tw/library/ms151705.aspx