SQL Server 2019 でトランザクション レプリケーションの更新可能なサブスクリプションを有効にする
はじめに・注意点
今回紹介する内容は、SQL Server 2019 でレプリケーションを行うためのものですが、 その中の機能のひとつである「更新可能なサブスクリプション」を有効にしています。
この機能は以前の SQL Server のバージョンではデフォルトで有効になっていましたが、(おそらく)2017 から無効になっています。
今後の SQL Server のバージョンではこの機能が削除される可能性がありますので、 既存の環境をバージョンアップするなどの場合を除き新規構築などではマージ レプリケーションなどで代用することをお勧めします。
環境
サーバーと SQL Server を２台使用します。Windows Server はインストール直後の状態で追加設定は特にしていない状態です。
- 一台目 (パブリケーション側)
-
- Windows Server 2019 Standard Edition
- SQL Server 2019 Developer Edition (Standard Edition 以上必須)
- SQL Server Management Studio 18.9.1
- 二台目 (サブスクライバ側)
-
- Windows Server 2019 Standard Edition
- SQL Server 2019 Express Edition
- SQL Server Management Studio 18.9.1
目的
１台目のサーバー、２台目のサーバーどちらのデータベースのテーブルレコードが更新された場合でも お互いのサーバーのテーブルレコードが同期されるようにします。
ちなみにパブリケーション側は１台あれはいいですが、サブスクライバ側は何台あっても構いません。 また、ひとつのサーバーに複数の SQL Server のインスタンスがあっても動作します。
SQL Server のインストール
SQL Server のインストールは本 Tips のメインではないので詳細は省きます。レプリケーションに関連する箇所のみ記載します。
パブリケーション側をインストールする際は Standard Edition 以上が必要です。本 Tips はテスト環境として Developer Edition をインストールしています。
サブスクライバ側は Express Edition で問題ありません。もちろん DB の構成によって他の Edition でも問題ありません。
「機能の選択」では「データベース エンジン サービス」と「SQL Server レプリケーション」を選択します。 これはパブリケーション側、サブスクライバ側どちらも同じです。
「インスタンスの構成」はパブリケーション側、サブスクライバ側どちらもデフォルトとします。 インスタンス名を変更した場合でも後のサーバーの指定でインスタンス名を指定するかしないかだけの違いになります。
パブリケーション側では「SQL Server エージェント」を自動起動するように設定します。この設定は後で変えることもできます。
本 Tips ではレプリケーションを SQL Server の sa アカウントで認証します。そのため SQL Server 認証を有効にします。 もしセキュリティ面で sa アカウントを使用しない場合は後の説明で SQL Server 認証を行っている箇所を Windows 認証にするか他のアカウントに置き換えてください。
この設定は両方のサーバーで同じにします。
SQL Server Management Studio のインストール
ダウンロードしてきた SQL Server Management Studio をインストールします。インストール画面はインストールするだけですので説明は省略します。
インストール後再起動を求められた場合は Windows を再起動します。また、Windows Update でパッチがある場合はインストールしておきます。
パブリケーション側のデータベースとテーブルを作成
パブリケーション側でテーブルを作成すればサブスクライバ側は自動でテーブルやレコードが複製されます。
作成の仕方は通常の手順と同じなので詳細は割愛します。
SQL Server Management Studio で「パブリケーションDB」という名前でデータベースを作成しています。
「ユーザー」という名前のテーブルを作成して以下のように列を登録します。 レコードの同期さえ確認できればいいのでテーブルの名前や数、列の内容はなんでも構いません。 ただし主キーだけは必要です。
レプリケーション作成後にレコードが同期されるか確認するためにレコードを何件か追加しておきます。
リモート接続の設定
お互いのサーバーのデータベースにリモートアクセスできるように設定してください。 詳しくは下記の Tips にまとめています。
フォルダの権限設定
パブリケーション側のサーバーで以下のフォルダのプロパティを開きます。 フォルダのパスは SQL Server のバージョンやインスタンス名によって異なります。
- C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\ReplData
セキュリティタブから「SERVICE」アカウントを追加しフルコントロールに設定します。
ディストリビューションの構成
今回ディストリビューションはパブリケーションと同じサーバーで作成します。
SQL Server Management Studio を起動し「レプリケーション」を右クリックして「ディストリビューションの構成」を選択します。
「次へ」をクリックします。
「<自身のサーバー>を独自のディストリビューターにする」にチェックをいれます。
あとはそのまま「次へ」をクリックしていって完了させます。
パブリケーションの作成
本来次の図のようにウィザードから作成するのですが、 SQL Server 2019 ではウィザードから作成しても「更新可能なサブスクリプション」が有効にならずテーブル間のレコードの同期ができません。
そのためここでは T-SQL を使用してパブリケーションを作成します。
以下は SQL Server 認証でパブリケーションを作成するための SQL です。 パスワードは設定に合わせて変えてください。
-- トランザクションパブリケーションを追加するための宣言
DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publicationDB = N'パブリケーションDB';
SET @publication = N'パブリケーション';
SET @login = 'sa';
SET @password = 'saパスワード';
USE [パブリケーションDB]
-- パブリケーションデータベースでトランザクションレプリケーションを有効にする。
EXEC sp_replicationdboption
@dbname=@publicationDB,
@optname=N'publish',
@value = N'true';
-- sp_addlogreader_agent を実行して、エージェントジョブを作成します。
EXEC sp_addlogreader_agent
@publisher_login = @login,
@publisher_password = @password,
@publisher_security_mode = 0;
-- 即時更新、キュー更新、およびプルサブスクリプションをサポートするトランザクションパブリケーションを作成します。
EXEC sp_addpublication
@publication = @publication,
@status = N'active',
@allow_sync_tran = N'true',
@allow_queued_tran = N'true',
@allow_pull = N'true',
@independent_agent = N'true',
-- 関連するデフォルトのプロパティを明示的に宣言する
@conflict_policy = N'pub wins';
-- デフォルトのスケジュールを使用して、パブリケーションの新しいスナップショットジョブを作成します。
EXEC sp_addpublication_snapshot
@publication = @publication,
@publisher_login = @login,
@publisher_password = @password,
@publisher_security_mode = 0;
GO
Windows 認証にする場合は「sp_addlogreader_agent」「sp_addpublication_snapshot」の箇所を以下のように変更します。
SQL Server 認証
@publisher_login = @login,
@publisher_password = @password,
@publisher_security_mode = 0;
Windows 認証
@job_login = @login,
@job_password = @password,
@publisher_security_mode = 1;
正常に作成されれば以下のように表示されます。
また、作成されたパブリケーションのプロパティを開き「更新可能なサブスクリプション」が有効になっていることを確認してください。
アーティクルの指定
本来ウィザードで作成すれば同時にアーティクルの指定を行うのですが、 上記スクリプトにはアーティクル処理を入れてませんのでプロパティから指定します。
左のメニューから「アーティクル」を選択肢、同期させたいテーブルにチェックをいれ OK ボタンをクリックします。
同期させるテーブルの列には「msrepl_tran_version」が追加され、同時にテーブルトリガーも追加されます。
サブスクリプションの作成
先ほど作成したパブリケーションを右クリックして「新しいサブスクリプション」を選択します。
「次へ」をクリックします。
作成したパブリケーションを選択します。
「ディストリビューター <サーバー名> ですべてのエージェントを実行する」にチェックを入れます。
「SQL Server サブスクライバーの追加」ボタンをクリックします。
サブスクライバとして登録するサーバー(＋インスタンス名)を指定します。
サブスクライバ側のデータベースがない場合は新しく作ります。すでにある場合は選択項目が表示されるので選択します。
データベースを指定したら「次へ」をクリックします。
サブスクライバが複数いる場合はここで必要な数だけ登録できます。
「...」ボタンをクリックします。
ここではエージェントの実行を sa アカウントにしていますが、環境に合わせて設定してください。
「連続実行する」を選択します。
「更新可能なサブスクリプション」の設定は運用に合わせてください。
「変更を同時にコミットする」の場合は即座に同期されますが、すべてのサーバーが稼働している必要があります。
「変更をキューに登録し、可能な場合はコミット」はキューに変更が溜まるため、サーバーが稼働していなくても稼働時にコミットされます。ただし、同期タイミングは遅いです。
「定義済みのリンク サーバーまたはリモート サーバーを使用する」にチェックをいれます。
「今すぐ」を選択します。
「次へ」をクリックします。
「完了」ボタンをクリックします。
エラーがなければ閉じます。
しばらくするとサブスクライバ側にデータベースが作成され、テーブルも作成されていることを確認できます。
レコードも同期されていることを確認できます。
もししばらくたっても同期されない場合は以下の操作を行ってください。
作成されたサブスクリプションを右クリックして「再初期化」を選択します。
「新しいスナップショットを使用する」にチェックをいれ「再初期化するように設定」ボタンをクリックします。
エラーが出る場合は再度実行してください。
動作確認
パブリケーション側のテーブルを追加・変更してみます。
数秒程度待つとサブスクライバ側に同期されていることを確認できます。
今度はサブスクライバ側のレコードを変更してみます。
パブリケーション側に同期されていることを確認できます。
複数のサブスクライバを登録している場合はサブスクライバ間で同期されていることも確認してみてください。