カピバラ好きなエンジニアブログ

ITのこととか趣味のカメラのことなどを赴くままに書いていきます。カピバラの可愛さこそ至高。

SQL Serverレプリケーション(2012→2016)をやってみた(トランザクションレプリケーション編)

はじめに

この記事は前記事からの続きになります。
一からレプリケーション設定をしたいという方は、まずそちらからご確認ください。

  • 前回の記事

capybara-engineer.hatenablog.com


今回は前回事前準備をしたSQL Serverをベースに、2つのDB間でトランザクションレプリケーションを設定していきます。

構築する環境情報は以下です。

手順は以下を参考に進めていきます。
docs.microsoft.com





尚、今回行うトランザクションレプリケーションは、高頻度で更新されるDBの読み取り専用DBを構築する場合などに適しています。
docs.microsoft.com


また、レプリケーションする際のデータの同期方法にもプッシュ式とプル式があり、プッシュ式はレプリケーション元からレプリケーション先へのニアリアルタイムデータ同期、プル式はレプリケーション先からの要求を受けてからのデータ同期を行うようになっています。
今回はプッシュ式で構築します。
docs.microsoft.com


実施内容


実施作業

パブリッシャーを構成

この手順ではAdventureWorks2012 サンプル データベースの Product テーブルからフィルター選択したサブセットをパブリッシュします。
わかりやすく言うと、特定のテーブルProductを同期対象として設定します。

パブリケーションを作成し、アーティクルを定義する

最初にSQL Server エージェントが開始されているか確認します。
f:id:live-your-life-dd18:20200212172221p:plain


[Replication]を右クリックし、[New Publication...]を選択してパブリケーションのウィザードを開きます。
f:id:live-your-life-dd18:20200212172319p:plain


『Next』ボタンを押下
f:id:live-your-life-dd18:20200212172450p:plain


パブリケーション対象のDBを選択し、『Next』ボタンを押下
f:id:live-your-life-dd18:20200212172523p:plain


表示されたパブリケーション方法から、今回実施するトランザクションパブリケーションを選択します。
f:id:live-your-life-dd18:20200212172731p:plain


各パブリケーション方法については、以下に載せておきます。

パブリケーション方法 説明(Google翻訳使用)
Snapshot publication パブリッシャーは、スケジュールされた間隔で、パブリッシュされたデータのスナップショットをサブスクライバーに送信します。
Transactional publication パブリッシャーは、パブリッシュされたデータの初期スナップショットを受信した後、サブスクライバートランザクションをストリーミングします。
Peer-to-Peer publication ピア - ピア公開により、マルチマスターレプリケーションが可能になります。パブリッシャーは、トポロジ内のすべてのピアにトランザクションをストリーミングします。すべてのピアノードは変更を読み書きでき、変更はトポロジ内のすべてのノードに伝播されます。
Merge publication パブリッシャーとサブスクライバーは、サブスクライバーが公開データの初期スナップショットを受信した後、公開データを個別に更新できます。変更は定期的にマージされます。 Microsoft SQL Server Compact Editionは、マージパブリケーションのみをサブスクライブできます。



パブリケーション方法が決まったら今度はアーティクル(わかりやすく言うと同期対象)を設定します。
f:id:live-your-life-dd18:20200212173626p:plain


今回はサンプルDBの特定テーブルのみ同期するように設定します。
f:id:live-your-life-dd18:20200212173759p:plain


テーブルのフィルター選択ページで[SafetyStockLevel]列を指定したWhere句を追加します。
これを設定しない場合は、取得元データを全件取得するようになります。
f:id:live-your-life-dd18:20200212174152p:plain


Filterが設定されています。
f:id:live-your-life-dd18:20200212174653p:plain


スナップショットエージェントの設定で、[スナップショットをすぐに作成し、サブスクリプションを初期化できるようにそのスナップショットを保持する] チェック ボックスをオンにします。
f:id:live-your-life-dd18:20200212174753p:plain


エージェントセキュリティの設定で、[スナップショット エージェントのセキュリティ設定を使用する] チェック ボックスをオフにします。
そのあと各エージェントを実行するWindowsアカウントを指定します。(repl_snapshot、repl_logreaderの2つ)
※アカウント名は『コンピュータ名\ユーザ名』で記載する必要があるのでご注意ください。
f:id:live-your-life-dd18:20200212175152p:plain


f:id:live-your-life-dd18:20200212181608p:plain


アクションウィザードページで[パブリケーションの作成]チェックボックスをオンにします。
f:id:live-your-life-dd18:20200212181700p:plain


パブリケーション名を入力して、『Finish』ボタンを押下します。
f:id:live-your-life-dd18:20200212181840p:plain


成功と出たら設定は完了です。
f:id:live-your-life-dd18:20200212181944p:plain

スナップショット生成の状態を表示する

先ほど作成したパブリケーションで右クリックして、[スナップショットエージェントの状態の表示]を選択します。
f:id:live-your-life-dd18:20200212182111p:plain


スナップショットジョブが正常に終了しているのを確認します。
f:id:live-your-life-dd18:20200212182304p:plain

ディストリビューション エージェントのログインを PAL(Pablication Access List) に追加する

作成したパブリケーションのアクセスリストにrepl_distributionユーザを追加します。
f:id:live-your-life-dd18:20200212182953p:plain


追加されたことを確認します。
f:id:live-your-life-dd18:20200212183039p:plain

  • PALについてはこちら

パブリケーション アクセス リストのログインの管理 - SQL Server | Microsoft Docs

サブスクリプションの作成

ここまでの設定で同期元の設定とデータの配布の設定を行なってきました。
この設定で今度は同期先の設定を行なっていきます。

サブスクリプションを作成する

作成したパブリケーションで右クリックして、[新しいサブスクリプション]を選択します。
f:id:live-your-life-dd18:20200212183738p:plain


サブスクリプションを作成するウィザードが開始します。
f:id:live-your-life-dd18:20200212183825p:plain


対象のパブリケーションを指定して、『Next』ボタンを押下します。
f:id:live-your-life-dd18:20200212183944p:plain


[ディストリビューターですべてのエージェントを実行する(push subscriptions)]を選択し、『Next』ボタンを押下します。
f:id:live-your-life-dd18:20200212184101p:plain

  • push subscriptionsとpull subscriptionsの違いは以下を参照

パブリケーションのサブスクライブ - SQL Server | Microsoft Docs


対象のサブスクライバーが表示されないので、[サブスクライバーの追加]を選択します。
f:id:live-your-life-dd18:20200212184956p:plain


[サーバーへの接続]ダイアログボックスが開くので、レプリケーション先DBのログイン情報を入力し『接続』ボタンを押下します。
f:id:live-your-life-dd18:20200212192721p:plain


以下のエラーが発生しました。
f:id:live-your-life-dd18:20200212192757p:plain


以下のエラーを見る限りパブリケーション(2012)とサブスクライバー(2016)の互換性レベルがよろしくないとのこと。

選択したサブスクライバーは、選択したパブリケーションの最小バージョン互換性レベルを満たしていません。



現在構築しようとしているのは

の構成ですが、以下を見る限りサポートされていそうな気がします。


f:id:live-your-life-dd18:20200212193208p:plain


色々調べてみたら原因っぽい内容がありました。
sqlserverscribbles.com

※翻訳後※
エラー:
選択したサブスクライバーは、選択したパブリケーションの最小バージョン互換性レベルを満たしていません。

解決:
上位バージョンのSSMSを使用してサブスクライバーを作成する(SSMSはサブスクライバーバージョン以上である必要があります)か、TSQLを使用してサブスクライバーを作成します。



SSMSがサブスクライバーバージョン以上であることとか聞いてない...

①TSQLでのサブスクリプション作成

とりあえず以下の手順を参考にTSQLで設定してみます。
プッシュ サブスクリプションの作成 - SQL Server | Microsoft Docs


以下のTSQLでサブスクリプションの作成を行います。

use AdventureWorks2012
exec sp_addsubscription @publication = N'AdvWorksProductTrans' 
  ,@subscriber = N'WIN-1052ST7RDDL'
  ,@destination_db = N'ProductReplica'
  ,@subscription_type = N'push';



f:id:live-your-life-dd18:20200212211036p:plain

②TSQLでのサブスクリプション設定

以下のTSQLでパブリケーションに対してサブスクリプションを同期するためにスケジュールされたエージェント ジョブを追加します。(TSQLよくわかっていないので、変な書き方してるかもしれません)

use AdventureWorks2012
exec sp_addpushsubscription_agent @subscriber = N'WIN-1052ST7RDDL'
  ,@subscriber_db = N'ProductReplica'
  ,@publication = N'AdvWorksProductTrans' 
  ,@job_login = N'WIN-RFQ3U85JCLM\repl_distribution'
  ,@job_password = N'*********';



f:id:live-your-life-dd18:20200212211933p:plain

※追記※
元のSQLだとサブスクライバーにパブリッシャーのWindowsアカウントでログインしようとしてしまうので、サブスクライバーへのログインはSQL認証を使うように修正しました。

use AdventureWorks2012
exec sp_addpushsubscription_agent @subscriber = N'WIN-1052ST7RDDL'
  ,@subscriber_db = N'ProductReplica'
  ,@publication = N'AdvWorksProductTrans' 
  ,@job_login = N'WIN-RFQ3U85JCLM\repl_distribution'
  ,@job_password = N'j8daRNBx!'
  ,@subscriber_security_mode = N'0'
  ,@subscriber_login = N'dbuser'
  ,@subscriber_password = N'dbuser';

サブスクリプション設定を一度削除するには以下のSQLを実行することで削除が可能です。

use AdventureWorks2012
exec sp_dropsubscription  @publication=N'AdvWorksProductTrans' 
  ,@subscriber=N'WIN-1052ST7RDDL'
  ,@article=N'all';

 
 

サブスクライバー側のデータベース権限を設定する

サブスクリプションの設定ができたらサブスクライバー側のDBにログインし、パブリッシャー側で設定した方法と同じ方法でWindowsアカウントにデータベース権限を設定します。
f:id:live-your-life-dd18:20200212212109p:plain


repl_distributionユーザにレプリカDBの権限を設定します。
f:id:live-your-life-dd18:20200212212417p:plain


f:id:live-your-life-dd18:20200212212516p:plain


サブスクライバー側にProductReplica データベースがあるはずだが、見つからない...
f:id:live-your-life-dd18:20200212212652p:plain


一旦原因調査は保留にして、次の同期状態を確認してみます。
DBは手動で作成しないといけませんでした。
※手順は記事の下部参照※

サブスクリプションの同期状態を表示する

作成したパブリケーションを展開するとTSQLで作成したサブスクリプションが出てくるので、それを右クリックして同期の状態の表示を選択します。
f:id:live-your-life-dd18:20200212214048p:plain


『Monitor』ボタンを押下してレプリケーションモニターを確認します。
f:id:live-your-life-dd18:20200212214238p:plain


スナップショットエージェントはCompletedになっていますが、Log Reader Agentでエラーが出ています。
f:id:live-your-life-dd18:20200213095720p:plain


Errorの項目をダブルクリックして、より詳細のエラー内容を確認します。
f:id:live-your-life-dd18:20200213095936p:plain


以下のメッセージを確認すると、Log Reader Agentでは内部でsp_replcmdsのTSQLが実行されており、それがWIN-RFQ3U85JCLMで実行できないことでエラーとなっているようです。

エラーメッセージ:
プロセスは「WIN-RFQ3U85JCLM」で「sp_replcmds」を実行できませんでした。 (ソース:MSSQL_REPL、エラー番号:MSSQL_REPL20011)


①原因調査1

sp_replcmdsについては以下に記載がありました。
docs.microsoft.com


上記を見る限りだとsp_replcmdsはログリーダープロセスによって実行され、
f:id:live-your-life-dd18:20200213101906p:plain


sp_replcmdsを実行できるのはサーバーロールsysadminかDBロールdb_ownerのみとあります。
f:id:live-your-life-dd18:20200213101605p:plain


そこでログリーダーエージェントのプロセス実行ユーザを確認してみます。
f:id:live-your-life-dd18:20200213102112p:plain


ログリーダーエージェントは[WIN-RFQ3U85JCLM\repl_logreader]で実行されています。
f:id:live-your-life-dd18:20200213102245p:plain


見る限りdb_ownerのロールは正しく設定されてそうでした。
f:id:live-your-life-dd18:20200213102429p:plain

②原因調査2

似たような事象が発生している方がいました。
tutorialmore.com


以下を見るとJobの所有者がよろしくなさそうです。

解決した方法 # 1
おそらく、レプリケーションエージェントを実行するSQLエージェントジョブの所有者を変更する必要があるだけです。レプリケーションモニターを開きます。問題の出版物を選択します。 [エージェント]タブを選択します。ログリーダーをダブルクリックします。新しいウィンドウで、[アクション]ドロップダウンメニューを選択し、[ジョブプロパティ]を選択します。その画面の所有者を別のユーザーまたはSAに変更します。 [OK]をクリックし、エージェントを停止して再起動します。



確認してみると確かに所有者がAdministratorユーザになっています。
f:id:live-your-life-dd18:20200213105916p:plain
f:id:live-your-life-dd18:20200213105938p:plain


これをそれぞれ指定のユーザに変更します。
f:id:live-your-life-dd18:20200213110142p:plain
f:id:live-your-life-dd18:20200213110204p:plain


Agentを再起動してみましたが、結果としてダメでした。
f:id:live-your-life-dd18:20200213110433p:plain

③原因調査3

公式のトラブルシューティングツールのページにヒントがありました。
docs.microsoft.com


データベースに所有者が設定されていないことが原因とあったため、確認したところ確かにownerの設定はされていませんでした。
f:id:live-your-life-dd18:20200213112524p:plain


解消方法として以下のSQLを実行することで設定できるようです。

    • set the owner of the database to 'sa' or a specific user account, without the brackets.
EXEC sp_changedbowner ''
    • example for sa: exec sp_changedbowner 'sa'
    • example for user account: exec sp_changedbowner 'sqlrepro\administrator'



とりあえず以下のSQLを実行してみます。

EXEC sp_changedbowner 'sa'



問題なくownerが設定されているようでした。
f:id:live-your-life-dd18:20200213113012p:plain


ログリーダーエージェントジョブを再起動します。
f:id:live-your-life-dd18:20200213113221p:plain


エラー内容が変わったので、先ほどの原因は解消できたようです。
f:id:live-your-life-dd18:20200213114629p:plain

③原因調査4

JobのHistoryを確認すると以下の通り、プロキシを使用する権限がないことで実行を開始できない旨のメッセージが出ています。
f:id:live-your-life-dd18:20200213142656p:plain


メッセージ内容(日本語訳)

メッセージ
ステップ2の実行を開始できません(理由:JobOwner WIN-RFQ3U85JCLM \ repl_logreaderには、サブシステムLogReaderにプロキシ1を使用する権限がありません)



同事象が発生しているこの記事を参考に、Jobを実行しているユーザにプロキシを使用する権限を与えてみます。
dba.stackexchange.com


SQL Server AgentのProxiesから[Replication Transaction-Log Reader]を選択・右クリックしてプロパティを開きます。
f:id:live-your-life-dd18:20200213143241p:plain


以下の順でプリンシパルにユーザを追加します。
f:id:live-your-life-dd18:20200213143504p:plain


設定できました。
f:id:live-your-life-dd18:20200213143552p:plain


これが原因でもないようです。
f:id:live-your-life-dd18:20200213144953p:plain

③原因調査5

よくよく確認してみるとエラーメッセージに書いてました。
f:id:live-your-life-dd18:20200213145228p:plain


メッセージ内容

エラーメッセージ:
プロセスは、サーバー「WIN-1052ST7RDDL」上のデータベース「ProductReplica」にアクセスできませんでした。 (ソース:MSSQL_REPL、エラー番号:MSSQL_REPL20052)
ヘルプを取得:http:// help / MSSQL_REPL20052
ログインによって要求されたデータベース「ProductReplica」を開けません。ログインに失敗しました。 (ソース:MSSQLServer、エラー番号:4060)
ヘルプを取得:http:// help / 4060
ユーザー 'dbuser'のログインに失敗しました。 (ソース:MSSQLServer、エラー番号:18456)
ヘルプを取得:http:// help / 18456



>ログインによって要求されたデータベース「ProductReplica」を開けません。
このようにあるので、サブスクライバー側にこのDBが存在しないことが原因っぽいです。
Microsoftの手順をみて自動で作成されると思っていましたが、どうやらそうではなさそう。


DBを作成してログインユーザとマッピングさせます。
f:id:live-your-life-dd18:20200213153650p:plain


レプリケーションモニターでエラーが出ていないことを確認しました。
f:id:live-your-life-dd18:20200213153813p:plain

レプリケーションの待機時間を計測

トレーサートークンを使用して、レプリケーションと待機時間を確認します。
レプリケーションモニターから[トレーサートークン]タブを選択し、『Insert Tracer』ボタンを押下します。
f:id:live-your-life-dd18:20200213154026p:plain


パブリッシャー → ディストリビューター → サブスクライバー でそれぞれ経過時間があることから、レプリケーションは正常に行われています。
また、トータルレイテンシーから経過時間が6秒かかっていることが確認できます。
f:id:live-your-life-dd18:20200213154347p:plain

SQL実行確認

サブスクライバー側で実行確認
f:id:live-your-life-dd18:20200213154806p:plain


パブリッシャー側で特定データの更新を行います。
f:id:live-your-life-dd18:20200213155223p:plain


再度実行してサブスクライバー側で更新結果が反映されていることが確認できました。
f:id:live-your-life-dd18:20200213155308p:plain

感想及び所感

今回は思ったよりも詰まりました。。
最初はよく理解できなかったですが、構築しながら調べることでかなり理解できた気がしました。
次回はもう一つバージョン違いのサブスクライバーを準備して、パブリッシャー2台サブスクライバー1台構成が可能かを検証していきます。