はじめに
この記事は前記事からの続きになります。
一からレプリケーション設定をしたいという方は、まずそちらからご確認ください。
- 前回の記事
capybara-engineer.hatenablog.com
今回は前回事前準備をしたSQL Serverをベースに、2つのDB間でトランザクションレプリケーションを設定していきます。
構築する環境情報は以下です。
- 環境：AWS(EC2)
- OS：Windows Server 2012
- プライマリ SQL Server：2012 Standard
- セカンダリ SQL Server：2016 Standard
手順は以下を参考に進めていきます。
docs.microsoft.com
尚、今回行うトランザクションレプリケーションは、高頻度で更新されるDBの読み取り専用DBを構築する場合などに適しています。
docs.microsoft.com
また、レプリケーションする際のデータの同期方法にもプッシュ式とプル式があり、プッシュ式はレプリケーション元からレプリケーション先へのニアリアルタイムデータ同期、プル式はレプリケーション先からの要求を受けてからのデータ同期を行うようになっています。
今回はプッシュ式で構築します。
docs.microsoft.com
実施作業
パブリッシャーを構成
この手順ではAdventureWorks2012 サンプル データベースの Product テーブルからフィルター選択したサブセットをパブリッシュします。
わかりやすく言うと、特定のテーブルProductを同期対象として設定します。
パブリケーションを作成し、アーティクルを定義する
最初にSQL Server エージェントが開始されているか確認します。
[Replication]を右クリックし、[New Publication...]を選択してパブリケーションのウィザードを開きます。
『Next』ボタンを押下
パブリケーション対象のDBを選択し、『Next』ボタンを押下
表示されたパブリケーション方法から、今回実施するトランザクションパブリケーションを選択します。
各パブリケーション方法については、以下に載せておきます。
| パブリケーション方法 | 説明(Google翻訳使用) |
| Snapshot publication | パブリッシャーは、スケジュールされた間隔で、パブリッシュされたデータのスナップショットをサブスクライバーに送信します。 |
| Transactional publication | パブリッシャーは、パブリッシュされたデータの初期スナップショットを受信した後、サブスクライバーにトランザクションをストリーミングします。 |
| Peer-to-Peer publication | ピア - ピア公開により、マルチマスターレプリケーションが可能になります。パブリッシャーは、トポロジ内のすべてのピアにトランザクションをストリーミングします。すべてのピアノードは変更を読み書きでき、変更はトポロジ内のすべてのノードに伝播されます。 |
| Merge publication | パブリッシャーとサブスクライバーは、サブスクライバーが公開データの初期スナップショットを受信した後、公開データを個別に更新できます。変更は定期的にマージされます。 Microsoft SQL Server Compact Editionは、マージパブリケーションのみをサブスクライブできます。 |
パブリケーション方法が決まったら今度はアーティクル(わかりやすく言うと同期対象)を設定します。
今回はサンプルDBの特定テーブルのみ同期するように設定します。
テーブルのフィルター選択ページで[SafetyStockLevel]列を指定したWhere句を追加します。
これを設定しない場合は、取得元データを全件取得するようになります。
Filterが設定されています。
スナップショットエージェントの設定で、[スナップショットをすぐに作成し、サブスクリプションを初期化できるようにそのスナップショットを保持する] チェック ボックスをオンにします。
エージェントセキュリティの設定で、[スナップショット エージェントのセキュリティ設定を使用する] チェック ボックスをオフにします。
そのあと各エージェントを実行するWindowsアカウントを指定します。(repl_snapshot、repl_logreaderの2つ)
※アカウント名は『コンピュータ名\ユーザ名』で記載する必要があるのでご注意ください。
アクションウィザードページで[パブリケーションの作成]チェックボックスをオンにします。
パブリケーション名を入力して、『Finish』ボタンを押下します。
成功と出たら設定は完了です。
スナップショット生成の状態を表示する
先ほど作成したパブリケーションで右クリックして、[スナップショットエージェントの状態の表示]を選択します。
スナップショットジョブが正常に終了しているのを確認します。
ディストリビューション エージェントのログインを PAL(Pablication Access List) に追加する
作成したパブリケーションのアクセスリストにrepl_distributionユーザを追加します。
追加されたことを確認します。
- PALについてはこちら
サブスクリプションの作成
ここまでの設定で同期元の設定とデータの配布の設定を行なってきました。
この設定で今度は同期先の設定を行なっていきます。
サブスクリプションを作成する
作成したパブリケーションで右クリックして、[新しいサブスクリプション]を選択します。
サブスクリプションを作成するウィザードが開始します。
対象のパブリケーションを指定して、『Next』ボタンを押下します。
[ディストリビューターですべてのエージェントを実行する(push subscriptions)]を選択し、『Next』ボタンを押下します。
- push subscriptionsとpull subscriptionsの違いは以下を参照
パブリケーションのサブスクライブ - SQL Server | Microsoft Docs
対象のサブスクライバーが表示されないので、[サブスクライバーの追加]を選択します。
[サーバーへの接続]ダイアログボックスが開くので、レプリケーション先DBのログイン情報を入力し『接続』ボタンを押下します。
以下のエラーが発生しました。
以下のエラーを見る限りパブリケーション(2012)とサブスクライバー(2016)の互換性レベルがよろしくないとのこと。
選択したサブスクライバーは、選択したパブリケーションの最小バージョン互換性レベルを満たしていません。
現在構築しようとしているのは
- SQL Server 2012 → SQL Server 2012 → SQL Server 2016
の構成ですが、以下を見る限りサポートされていそうな気がします。
色々調べてみたら原因っぽい内容がありました。
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';
②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'*********';
※追記※
元の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アカウントにデータベース権限を設定します。
repl_distributionユーザにレプリカDBの権限を設定します。
サブスクライバー側にProductReplica データベースがあるはずだが、見つからない...
一旦原因調査は保留にして、次の同期状態を確認してみます。
DBは手動で作成しないといけませんでした。
※手順は記事の下部参照※
サブスクリプションの同期状態を表示する
作成したパブリケーションを展開するとTSQLで作成したサブスクリプションが出てくるので、それを右クリックして同期の状態の表示を選択します。
『Monitor』ボタンを押下してレプリケーションモニターを確認します。
スナップショットエージェントはCompletedになっていますが、Log Reader Agentでエラーが出ています。
Errorの項目をダブルクリックして、より詳細のエラー内容を確認します。
以下のメッセージを確認すると、Log Reader Agentでは内部でsp_replcmdsのTSQLが実行されており、それがWIN-RFQ3U85JCLMで実行できないことでエラーとなっているようです。
エラーメッセージ： プロセスは「WIN-RFQ3U85JCLM」で「sp_replcmds」を実行できませんでした。 （ソース：MSSQL_REPL、エラー番号：MSSQL_REPL20011）
①原因調査１
sp_replcmdsについては以下に記載がありました。
docs.microsoft.com
上記を見る限りだとsp_replcmdsはログリーダープロセスによって実行され、
sp_replcmdsを実行できるのはサーバーロールsysadminかDBロールdb_ownerのみとあります。
そこでログリーダーエージェントのプロセス実行ユーザを確認してみます。
ログリーダーエージェントは[WIN-RFQ3U85JCLM\repl_logreader]で実行されています。
見る限りdb_ownerのロールは正しく設定されてそうでした。
②原因調査２
似たような事象が発生している方がいました。
tutorialmore.com
以下を見るとJobの所有者がよろしくなさそうです。
解決した方法 # 1 おそらく、レプリケーションエージェントを実行するSQLエージェントジョブの所有者を変更する必要があるだけです。レプリケーションモニターを開きます。問題の出版物を選択します。 [エージェント]タブを選択します。ログリーダーをダブルクリックします。新しいウィンドウで、[アクション]ドロップダウンメニューを選択し、[ジョブプロパティ]を選択します。その画面の所有者を別のユーザーまたはSAに変更します。 [OK]をクリックし、エージェントを停止して再起動します。
確認してみると確かに所有者がAdministratorユーザになっています。
これをそれぞれ指定のユーザに変更します。
Agentを再起動してみましたが、結果としてダメでした。
③原因調査３
公式のトラブルシューティングツールのページにヒントがありました。
docs.microsoft.com
データベースに所有者が設定されていないことが原因とあったため、確認したところ確かにownerの設定はされていませんでした。
解消方法として以下のSQLを実行することで設定できるようです。
-
- set the owner of the database to 'sa' or a specific user account, without the brackets.
-
- example for sa: exec sp_changedbowner 'sa'
- example for user account: exec sp_changedbowner 'sqlrepro\administrator'
とりあえず以下のSQLを実行してみます。
EXEC sp_changedbowner 'sa'
問題なくownerが設定されているようでした。
ログリーダーエージェントジョブを再起動します。
エラー内容が変わったので、先ほどの原因は解消できたようです。
③原因調査４
JobのHistoryを確認すると以下の通り、プロキシを使用する権限がないことで実行を開始できない旨のメッセージが出ています。
メッセージ内容(日本語訳)
メッセージ ステップ2の実行を開始できません（理由：JobOwner WIN-RFQ3U85JCLM \ repl_logreaderには、サブシステムLogReaderにプロキシ1を使用する権限がありません）
同事象が発生しているこの記事を参考に、Jobを実行しているユーザにプロキシを使用する権限を与えてみます。
dba.stackexchange.com
SQL Server AgentのProxiesから[Replication Transaction-Log Reader]を選択・右クリックしてプロパティを開きます。
以下の順でプリンシパルにユーザを追加します。
設定できました。
これが原因でもないようです。
③原因調査５
よくよく確認してみるとエラーメッセージに書いてました。
メッセージ内容
エラーメッセージ： プロセスは、サーバー「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を作成してログインユーザとマッピングさせます。
レプリケーションモニターでエラーが出ていないことを確認しました。
