手動によるサブスクリプションの初期化を選択した際にエラーとなるカラムのデータ型と回避策について

はじめに

こんにちはDXソリューション統括部の鈴木です。

今回は、SQL ServerやSQL Managed Instanceにおいてレプリケーションを構成する際に、手動によるサブスクリプションの初期化を行った場合にそのままだとエラーになるカラムのデータ型、及びエラーの回避策についてご紹介します。

 

なお、今回検証対象としたデータ型は以下の通りです。

・真数
bigint, numeric, bit, smallint, decimal, int, tinyint, money
 
・概数
float
 
・日付と時刻
datetime, datetime2, timestamp
 
・文字列
char, varchar, text
 
・Unicode文字列
nchar,  nvarchar
 
・バイナリ文字列
binary, varbinary, image
 
・その他
rowversion, uniqueidentifier, xml, IDENTITYプロパティ

 

データ型一覧はこちら→データ型 (Transact-SQL) – SQL Server | Microsoft Learn

「手動によるサブスクリプションの初期化ってなに?」という方はこちら→手動によるサブスクリプションの初期化 – SQL Server | Microsoft Learn

 

エラーになるデータ型とエラー内容

先述したデータ型の中で、手動によるサブスクリプションの初期化を行った際にそのままだとエラーになったのは、

①timestamp②rowversion③IDENTITYプロパティ

の三つ、どれも自動で値が生成されるものでした。

 

それぞれの詳細はこちら↓

rowversion (Transact-SQL) – SQL Server | Microsoft Learn

IDENTITY (プロパティ) (Transact-SQL) – SQL Server | Microsoft Learn

 

これらの列が同期対象のテーブルに存在している場合のエラー内容ですが、

①②の場合はSQL エラーメッセージ 273が表示され、

#レプリケーションモニター「ディストリビューターからサブスクライバーまでの履歴」にて

Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

③の場合は以下のメッセージが表示されます。

#レプリケーションモニター「ディストリビューターからサブスクライバーまでの履歴」にて

Cannot update identity coulm ‘[IDENTITY(NOT FOR REPLICATION)を指定した列]’

 

エラーの回避策

timestamp/rowversion型

timestamp/rowversion型はどちらも同じエラーとなるためまとめてご紹介します。(timestampはrowversionのシノニム)

 

これらのデータ型の場合の回避策として、

レプリケーション構成時にtimestamp/rowversion型の列を同期対象から外す

という方法が有効です。

 

具体的には、

・sp_addarticle実行時に「@vertical_partition = N’true’」を指定

・sp_articlecolumnにより同期対象列を調整

・ sp_articleviewを実施(場合によっては省略可)

の三点をレプリケーション構成手順の一部として実施します(T-SQLの場合)。

 

実施後にパブリケーションのプロパティを確認すると、timestamp/rowversion型の列に☑が入っていないことが分かります。

 

サンプルコードはこちら→列フィルターの定義および変更 – SQL Server | Microsoft Learn

sp_addarticle (Transact-SQL) – SQL Server | Microsoft Learn

sp_articlecolumn (Transact-SQL) – SQL Server | Microsoft Learn

sp_articleview (Transact-SQL) – SQL Server | Microsoft Learn

 

 

IDENTITYプロパティ

IDENTITYプロパティにおけるエラーは、サブスクライバー側で該当テーブルのIDENTITY列に対して値が更新される処理が失敗したために発生します。

 

回避策としては、

①サブスクライバー側に作成するテーブルの列にはIDENTITY列を指定しない

②テーブル作成時のID列に「NOT FOR REPLICATION」を付与し、レプリケーション構築時のsp_addarticleのオプションに「@identityrangemanagementoption=N’manual’」を指定する

の二つがあります。


これらの方法により、レプリケーション時にサブスクライバー側のIDENTITY列に対する直接的な値の更新・挿入が発生しないため、エラーを回避することができます。

 

sp_addarticle (Transact-SQL) – SQL Server | Microsoft Learn

 

おわりに

以上、手動によるサブスクリプションを実施する場合にそのままだとエラーとなるデータ型とその回避策についてご紹介してきました。

本来であればレプリケーション実施の備えとしてテーブルのデータ型や構成を調整していくのが理想ですが、レプリケーションの構成手順の方で調整が必要になった場合には上記の方法を候補の一つとしてご検討いただければと思います。

最後までお付き合いいただきありがとうございました!

いいね (この記事が参考になった人の数:1)
(↑参考になった場合はハートマークを押して評価お願いします)
読み込み中...

注意事項・免責事項

※技術情報につきましては投稿日時点の情報となります。投稿日以降に仕様等が変更されていることがありますのでご了承ください。

※公式な技術情報の紹介の他、当社による検証結果および経験に基づく独自の見解が含まれている場合がございます。

※これらの技術情報によって被ったいかなる損害についても、当社は一切責任を負わないものといたします。十分な確認・検証の上、ご活用お願いたします。

※当サイトはマイクロソフト社によるサポートページではございません。パーソルクロステクノロジー株式会社が運営しているサイトのため、マイクロソフト社によるサポートを希望される方は適切な問い合わせ先にご確認ください。
 【重要】マイクロソフト社のサポートをお求めの方は、問い合わせ窓口をご確認ください