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

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

SQL Serverのトランザクションレプリケーションを設定してみた(Distributor+Subscriber構成)

はじめに

タイトル通り、以前はMicrosoftの手順に従ってDistributorをPublisherと同じインスタンスに構築しましたが、今度はレプリカ先のSubscriber側に作成していきます。
今回はPublishar、Subscriber共にSQL Server2016で実施します。

前回記事は以下
capybara-engineer.hatenablog.com
capybara-engineer.hatenablog.com


実施内容


実施作業

構成確認&補足知識

前回までで参考にしたチュートリアルでは以下のような構成となっていました。
※マージレプリケーション用のものは除外済み
f:id:live-your-life-dd18:20200224104113p:plain


この時の各エージェントと実行されているタスクの流れは以下のようになっています。
※こう理解したという内容を書いているので、もしかしたら細かい部分が間違っているかもしれません

  • スナップショットエージェント

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

  • ログリーダーエージェント

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

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


以下のチュートリアル手順を見ていると、どの手順がPublishar、Distributor、Subscriberのどこで設定されるべき手順なのかがちょっとわかりにくいです。
docs.microsoft.com
docs.microsoft.com


そこで公式ドキュメントの確認して、どこで設定されるか確認した結果を踏まえて明確に分離したところ、以下のようになりました。
これはトランザクションレプリケーションに絞って書いたものですが、基本的に実行するエージェントはDistributorで設定する必要があり、それに関してはPublisharで設定する内容はほとんどありません。
※Publisharで設定する内容がないわけではない
f:id:live-your-life-dd18:20200224105021p:plain


またPublisharとDistributorを分ける場合は、以下の手順でDistributor側でリモートPublisharの設定をする必要があります。
docs.microsoft.com


他にも、今回のように同じドメイン内に存在しないコンピュータ同士でレプリケーションする場合は、エージェントが接続を確立するために以下の通り認証方式を設定する必要があります。
f:id:live-your-life-dd18:20200224111919p:plain


仮にプッシュサブスクリプションでDistributorとSubscriberを接続させたい場合、ディストリビューションエージェントのWindowsアカウントをDistributor及びSubscriberで作成します。
チュートリアルはこのパターンです
f:id:live-your-life-dd18:20200224112605p:plain


ここでサブスクリプションという言葉が出てきましたが、これは簡単に言うとPublisharとSubscriberのデータの同期方法です。
プッシュサブスクリプションとプルサブスクリプションの2通りあります。
それぞれ、Publishar→Subscriberへ変更を同期するのか、Subscriber→Publisharへ変更を同期するのかの違いがあります。
それぞれの違いについては以下の表に記載しておきます。
f:id:live-your-life-dd18:20200224113001p:plain

docs.microsoft.com


サブスクリプションの作成方法についてはこちらをご確認ください。
docs.microsoft.com


今回の構築で特に詰まったのは上記表でいうエージェントの権限設定です。
公式ドキュメントを確認しながら見やすいようにこちらも表にしたので、載せておきます。
f:id:live-your-life-dd18:20200224113616p:plain

docs.microsoft.com


以上のことを踏まえた上で、以下の構成の構築をしていきます。

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

事前準備

①必要なアカウントを作成します。

  • Publishar側

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

  • Subscriber側

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


レプリケーション用のサンプルDBを作成します。
具体的な手順は以下記事参照
capybara-engineer.hatenablog.com

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

ここまでできたら実際に構築を行なっていきます。

Distributor構築

まずはレプリケーション先のサーバーでDistributionを作成します。

Replicationフォルダを右クリック→[Configure Distribution...]を押下してDistribution構築ウィザードを立ち上げます。
f:id:live-your-life-dd18:20200224120924p:plain

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


Distributorは現在のサーバー上に構築するようにします。
f:id:live-your-life-dd18:20200220181548p:plain


スナップショットフォルダーは事前に共有化設定済みのフォルダを指定します。
f:id:live-your-life-dd18:20200224121224p:plain

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


※今回はPublisharが別サーバ―なので、権限は全アカウントに対してフル権限を設定します。
f:id:live-your-life-dd18:20200224121403p:plain


Distribution Databaseは規定値のまま設定します。
f:id:live-your-life-dd18:20200221095340p:plain


※この時database fileとdtabase log fileを別のディスクドライブに設定するとパフォーマンス効率が向上します。
f:id:live-your-life-dd18:20200221095435p:plain


次にDistributorで操作するPublisharを設定します。
今回は別サーバ上のPublisharを設定するので、チェックを外してAddボタンからPublisharの追加をします。
f:id:live-your-life-dd18:20200221095730p:plain


接続情報を入力してConnectボタンを押下します。
f:id:live-your-life-dd18:20200221095830p:plain


接続が確認出来たらチェックが付いた状態でPublisharに追加されるので、そのまま次へいきます。
f:id:live-your-life-dd18:20200221095924p:plain


PublisharからこのDistributorを操作するための管理パスワードを設定します。
※このパスワードを設定するとdistributor_adminというユーザが作成され、そのユーザのパスワードとして設定されます。
f:id:live-your-life-dd18:20200221095954p:plain


あとは規定値で進めます。
f:id:live-your-life-dd18:20200221100141p:plain
f:id:live-your-life-dd18:20200221100155p:plain


以下のようにエラーが出なければDistributorの設定は完了です。
f:id:live-your-life-dd18:20200221100226p:plain


※作成後Distributor用のユーザが作成されているので、ユーザのプロパティからdistribution databaseのdb_ownerロールを追加しておきます。
f:id:live-your-life-dd18:20200221101402p:plain


distributionデータベースが作成されたので、最初に作成したWindowsアカウントとそのデータベースをマッピングします。
f:id:live-your-life-dd18:20200224121904p:plain
f:id:live-your-life-dd18:20200224121943p:plain
f:id:live-your-life-dd18:20200224122009p:plain

Publishar構築(レプリケーション元)

次にレプリケーション元のサーバーでPublicationを作成します。

最初に作成したWindowsアカウントとレプリケーション元のDBのマッピングをします。
f:id:live-your-life-dd18:20200224122241p:plain

  • スナップショットアカウント

f:id:live-your-life-dd18:20200224122340p:plain
f:id:live-your-life-dd18:20200224122406p:plain

  • ログリーダーアカウント

f:id:live-your-life-dd18:20200224122520p:plain
f:id:live-your-life-dd18:20200224122545p:plain


そのあとLocal Publicationsフォルダを右クリック→[New Publication...]を押下してPublication構築ウィザードを立ち上げます。
f:id:live-your-life-dd18:20200224122811p:plain

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


今回はDistributorは別サーバー上に構築しているため、それを追加します。
f:id:live-your-life-dd18:20200224123200p:plain


接続の際はDistribution構築時に自動で作成されたdistributor_adminユーザとそのパスワードを使用して接続します。
※ここはそのDBに接続できるユーザとパスワードであれば上記のユーザでなくても問題ありません。
f:id:live-your-life-dd18:20200224123325p:plain


無事に接続できたら次に行きます。
f:id:live-your-life-dd18:20200224123414p:plain


ここで再度distributor_adminユーザのパスワードを設定します。
f:id:live-your-life-dd18:20200224123439p:plain


Publication対象のDBを選択します。
f:id:live-your-life-dd18:20200224123619p:plain


Publication方法を選択します。
トランザクションレプリケーションを行うので、ここでは[Transactional publication]を選びます。
f:id:live-your-life-dd18:20200224123653p:plain


レプリケーションするアーティクルを選択します。
f:id:live-your-life-dd18:20200224123824p:plain


データに対してのFilterは特にせずに次に行きます。
f:id:live-your-life-dd18:20200224123859p:plain


スナップショットをすぐに作成して初期化するチェックを入れて次に行きます。
f:id:live-your-life-dd18:20200224124205p:plain


スナップショットエージェントとログリーダーエージェントを実行するユーザを設定します。
f:id:live-your-life-dd18:20200224124341p:plain


Publicationを作成するチェックを入れて次にいきます。
f:id:live-your-life-dd18:20200224124424p:plain


Publication名を入れて終了を押下します。
f:id:live-your-life-dd18:20200224124540p:plain


うまくいけばここで成功するのですが、何故かログリーダーエージェントで設定したユーザでエラーが発生しました。
f:id:live-your-life-dd18:20200224124616p:plain


ログリーダーエージェントはDistributionデータベースに接続するはずなので、Distributor側で同じアカウント名・パスワードを持つWindowsアカウントを作成してみましたが、変わりませんでした。
f:id:live-your-life-dd18:20200224125431p:plain


試しにとDistributor側で作成したユーザを指定して再度実施してみたところ、エラーの内容がログリーダーエージェント用のアカウントからスナップショットエージェント用のアカウントのエラーに変わりました。
f:id:live-your-life-dd18:20200224125813p:plain

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


もしかしてと思い、スナップショットエージェント用のアカウントもDistributor側のアカウントを指定するように変更してみたところ、問題なくPublicationが作成されました。
恐らくですが、エージェントをメインで実行するのはDistributer側で、別サーバー上のPublisharで同名、同パスワードのアカウントを作成しておくことで、レプリケーション時に自動で実行してくれるということではないかと思っています。
※詳しくは確認していません。
f:id:live-your-life-dd18:20200224130050p:plain

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

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

スナップショットエージェントのステータス確認

設定できたらスナップショットエージェントのステータスを確認します。
f:id:live-your-life-dd18:20200224131424p:plain


共有フォルダにアクセスができなくてエラーになっているようです。
f:id:live-your-life-dd18:20200224131529p:plain


共有フォルダのセキュリティ設定で、作成したログリーダーエージェント用のアカウントとスナップショットエージェント用のアカウントを追加します。
※スナップショットのみフル権限、ログリーダーは読み取り権限にします。
※ついでに共有の権限にも設定しておきます。
f:id:live-your-life-dd18:20200224131946p:plain
f:id:live-your-life-dd18:20200224131952p:plain

再度実行してスナップショットエージェントが正常に完了したことを確認します。
f:id:live-your-life-dd18:20200224132121p:plain

Subscriber構築

最後にレプリケーション元のサーバーでSubscriptionの作成を行なっていきます。


Subscriberだからレプリケーション先のサーバー上で作成するんじゃないのかと思われるかもしれませんが、今回はプッシュサブスクリプションを使用して、Publisharの変更をSubscriberに対して同期するような方法になるので、Publication側で実施します。
※プルサブスクリプションであれば、Subscriber側で実施します。


Local Publicationsフォルダ配下のPublicationを右クリック→[New Subscriptions...]を押下してSubscription構築ウィザードを立ち上げます。
f:id:live-your-life-dd18:20200224132917p:plain

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


Publicationには、先ほど作成したものを指定します。
f:id:live-your-life-dd18:20200224133109p:plain


サブスクリプション方法を指定します。
今回はプッシュサブスクリプションを指定します。
※ここで、すべてのエージェントをDistributorで実行するように設定することになっているのに気が付きました。
※そもそもDistributorを分けた時点で、Publisharでエージェントは実行されないようですね。(間接的には実行されているかもしれませんが)
f:id:live-your-life-dd18:20200224133203p:plain


別サーバー上に構築しているSQL ServerをSubscriberとして指定します。
f:id:live-your-life-dd18:20200224133549p:plain
f:id:live-your-life-dd18:20200224133612p:plain


レプリカDBは新規で作成します。
f:id:live-your-life-dd18:20200224133630p:plain
f:id:live-your-life-dd18:20200224133705p:plain
f:id:live-your-life-dd18:20200224133757p:plain


ディストリビューションエージェントを実行するユーザを設定します。
f:id:live-your-life-dd18:20200224133845p:plain
f:id:live-your-life-dd18:20200224133935p:plain
f:id:live-your-life-dd18:20200224133947p:plain


同期スケジュールはすぐに実行するようにします。
f:id:live-your-life-dd18:20200224134025p:plain


Subscriptionの初期化も即時実行されるように設定します。
f:id:live-your-life-dd18:20200224134235p:plain


Subscriptionを作成するにチェックを付けます。
f:id:live-your-life-dd18:20200224134322p:plain


設定を完了します。
f:id:live-your-life-dd18:20200224134343p:plain


問題なければ作成は完了します。
f:id:live-your-life-dd18:20200224134404p:plain

レプリケーション確認

作成したレプリカDB内にレプリケーションしたテーブルが存在しているか確認したところ、何故か作成されていませんでした。
f:id:live-your-life-dd18:20200224134630p:plain


Job Historyを確認したところ、レプリカDBへのログインに失敗しているとのこと。
f:id:live-your-life-dd18:20200224135235p:plain

Message: Cannot open database "Replica2016" requested by the login. The login failed.



レプリカDBを作成した後にログインユーザに対してマッピングを行っていなかったので、設定します。
※スナップショットのユーザは不要な気もしましたが、念のため設定しました。
f:id:live-your-life-dd18:20200224135537p:plain
f:id:live-your-life-dd18:20200224135604p:plain
f:id:live-your-life-dd18:20200224135627p:plain


設定後少ししたらレプリカDBにレプリケーション指定したテーブルが存在していました。
f:id:live-your-life-dd18:20200224135819p:plain


レプリカDBに対してのクエリも問題なくできました。
f:id:live-your-life-dd18:20200224140102p:plain


レプリケーション元のDBでデータを更新します。
f:id:live-your-life-dd18:20200224140307p:plain


レプリケーション先のDBに即時反映されることが確認できました。
f:id:live-your-life-dd18:20200224140341p:plain

感想及び所感

最初は簡単にできるだろうと思っていましたが、思いの外時間がかかりました。

基本的には公式ドキュメントを読み漁りながら設定をしているのですが、中々ドキュメント通りに設定できなかったり、出力されたエラーについての情報が極端に少なかったりして、かなり大変でした。

一番効果的だった方法としては、やはりドキュメントの内容を図示化して全体の構成を理解することで、そのおかげで少しずつおかしい点が見えてきたので、やはりちゃんと理解することは大切だなと再認識しました。

ただ、Publicationのアクセスリストを設定しようとした際に、Distributorのユーザを追加できなかった(ドキュメントもない)ことなどドキュメントだけでは原因がわからないことが多かったです。
今回のようなDistributorをSubscriber側で構築したいという内容は、他の事例でも全然ありそうなものなので、ぜひチュートリアルに入れていただきたいものです。。

何はともあれ、設定できてよかったです。
もし認識が間違っていたり、変なこと言っていたりしたらご指摘ください。

最後に構築した構成図(修正版)を載せておきます。
f:id:live-your-life-dd18:20200224143307p:plain