SQL Server AlwaysOn 可用性グループに所属する可用性データベース間でトランザクション レプリケーションを構築する方法について確認する機会がありましたので、自分の整理も兼ねて、まとめてみようと思います。
[構成例]
Azure 東日本リージョン
パブリッシャ :
S2K19-01 (1号機)
S2K19-02 (2号機)
※ SQL Server 2019 AlwaysOn 可用性グループ (WSFC)
※ SQL Server は既定のインスタンス (MSSQLSERVER) を使用
Azure 西日本リージョン
リモート ディストリビュータ／サブスクライバ :
S2K19-03 (3号機)
S2K19-04 (4号機)
※ SQL Server 2019 AlwaysOn 可用性グループ (WSFC)
※ SQL Server は既定のインスタンス (MSSQLSERVER) を使用
[前提条件]
- Azure 東日本／西日本の各リージョンの Azure 仮想マシン上に SQL Server AlwaysOn 可用性グループ および 可用性リスナーが構築されていること。
Azure 仮想マシン上に SQL Server AlwaysOn 可用性グループ および 可用性リスナーを構築する方法については、以下の URL を参照。
- SQL Server 2016 SP2 CU3、SQL Server 2017 CU6 以降を使用していること。※ ディストリビューション データベースを可用性データベースとして登録する構成にするための条件となります。
- 1. Azure 西日本リージョン上のSQL Server 可用性グループとAzure 東日本リージョン上のSQL Server 可用性グループとで、リモート パブリッシャ、リモート ディストリビューターを構成
- 2. Azure 東日本リージョン上のSQL Server 可用性グループとAzure 西日本リージョン上のSQL Server 可用性グループとで、トランザクション レプリケーションを構成
1. Azure 西日本リージョン上のSQL Server 可用性グループとAzure 東日本リージョン上のSQL Server 可用性グループとで、リモート パブリッシャ、リモート ディストリビューターを構成
1) SQL Server がインストールされた Azure 仮想マシン (可用性レプリカ プライマリ：3号機) にログインします。
2) SQL Server Management Studio (以下 SSMS) を起動し、管理者権限(sysadmin)が付与されたログインで該当のインスタンスに接続します。
3) 「新しいクエリ」を選択します。
4) 以下のクエリを実行し、可用性レプリカ プライマリ：3号機 をディストリビュータとして構成します。
| use master go sp_adddistributor @distributor=@@servername, @password='********' go |
5) SQL Server がインストールされた Azure 仮想マシン (可用性レプリカ セカンダリ：4号機) にログインします。
6) SQL Server Management Studio (以下 SSMS) を起動し、管理者権限(sysadmin)が付与されたログインで該当のインスタンスに接続します。
7) 「新しいクエリ」を選択します。
8) 以下のクエリを実行し、可用性レプリカ セカンダリ：4号機 をディストリビュータとして構成します。
| use master go sp_adddistributor @distributor=@@servername, @password='********' go |
9) 3号機上で以下のクエリを実行し、ディストリビューション データベース「distribution」を作成します。
| use master go sp_adddistributiondb 'distribution' go |
10) 9)で作成した ディストリビューション データベース「distribution」-「右クリック」-「プロパティ」を選択します。
11) 「ページの選択」:「オプション」-「復旧モデル」:「完全」に変更し、「OK」を選択します。
12) 3号機上で ディストリビューション データベース「distribution」の完全バックアップ、トランザクション ログのバックアップを採取し、任意のフォルダに保存します。
13) 3号機上で以下のクエリを実行し、可用性グループ (本例では「AG2」)に可用性データベースとして データベース「distribution」を追加します。
| use master go alter availability group [AG2] add database [distribution] go |
14) 3号機上および4号機上で、可用性グループ (本例では「AG2」)に可用性データベースとして データベース「distribution」が追加されていることを確認します。
※ 該当の可用性グループ -「右クリック」-「ダッシュボードの表示」を選択し、 可用性データベース「distribution」の同期状態が「同期済み」になっていることを確認します。
[補足]
可用性グループの設定で「シード処理のモード」:「自動」 を選択していない場合、T-SQL を使用して、可用性データベースとしてデータベース「distribution」を登録します。
T-SQLを使用して可用性データベースを追加する方法については、以下のURLを参照。
15) 4号機上で以下のクエリを実行し、ディストリビューターのメタデータを作成します。
※ ディストリビューター データベース「distribution」は既に可用性データベースとして登録されているため、ディストリビューターのメタデータのみが作成されます。
| use master go sp_adddistributiondb 'distribution' go |
16) 3号機上で以下のクエリを実行し、リモート パブリッシャをディストリビューターに登録します。
@publisher : Azure 東日本リージョンに構築した SQL Server 可用性グループに所属するレプリカ名を指定 (本例では 1号機、2号機)
@distribution_db : ディストリビューション データベース名を指定 (本例では「distribution」)
@working_directory : 各ディストリビューター (本例では 3号機、4号機) からアクセス可能な共有フォルダパスを指定
|
use master sp_adddistpublisher @publisher= 's2k19-02', @distribution_db= 'distribution', @working_directory= '\\stje****.file.core.windows.net\dist-working' |
※ SQL Server 認証でリモートパブリッシャに接続する場合は、@security_mode, @login, @password パラメータを追加します。
|
use master sp_adddistpublisher @publisher= 's2k19-02', @distribution_db= 'distribution', @working_directory= '\\stje****.file.core.windows.net\dist-working',@security_mode=0,@login='sa',@password='********' |
17) 4号機上で 16)で実行したクエリを実行し、リモート パブリッシャをディストリビューターに登録します。
18) 1号機上で以下のクエリを実行し、リモート パブリッシャをディストリビューターに登録します。
@distributor: ディストリビュータを構成した可用性グループ(本例では 3号機、4号機) の可用性リスナー名 (本例では「sqlag2」)
@password: 4)、8) で指定したパスワード
|
use master |
19) 2号機上で 18) のクエリを実行し、リモート パブリッシャをディストリビューターに登録します。
20) 3号機上で以下のクエリを実行し、既存パブリッシャーのリダイレクトパブリッシャ (本例では、1号機、2号機を構成する可用性グループの可用性リスナーを指定」) 先を登録します。
@original_publisher : パブリッシャを構成した可用性グループの各ノード名 (本例では 1号機、2号機)
@publisher_db : パブリッシャを構成した可用性グループの可用性データベース名 (本例では 「AGDB1」)
@redirected_publisher : パブリッシャを構成した可用性グループの可用性リスナー名 (本例では 「sqlag-lip」)
|
use distribution |
※ 今回、ディストリビューション データベース(本例では「distribution」)を可用性データベースとして登録しているため、上記のクエリを 3号機で実行することにより、4号機上のディストリビューション データベース(本例では「distribution」)にも自動的に反映されます。
2. Azure 東日本リージョン上のSQL Server 可用性グループとAzure 西日本リージョン上のSQL Server 可用性グループとで、トランザクション レプリケーションを構成
1) SQL Server がインストールされた Azure 仮想マシン (可用性レプリカ プライマリ：1号機) にログインします。
2) SQL Server Management Studio (以下 SSMS) を起動し、管理者権限(sysadmin)が付与されたログインで該当のインスタンスに接続します。
3) 「レプリケーション」-「ローカル パブリケーション」-「右クリック」-「新しいパブリケーション」を選択します。
4) ウィザードを進め、「パブリケーション データベース」画面でパブリケーション データベースを選択後、「次へ」を選択します。(本例では 1号機、2号機で構成された可用性グループの可用性データベース「AGDB1」を選択)
5) 「トランザクション レプリケーション」を選択後、「次へ」を選択します。
6) 「アーティクル」画面で、パブリッシュするテーブルなどを選択後、「次へ」を選択します。
※ 必要に応じて「アーティクルのプロパティ」からアーティクルのプロパティ設定を変更します。
7) 「テーブル行のフィルター選択」画面で、必要に応じてフィルター条件を追あし、「次へ」を選択します。
8) 「スナップショット エージェント」画面で、「スナップショットをすぐに作成し、...」を選択後、「次へ」を選択します。
9) 「エージェント セキュリティ」画面で、スナップショット エージェントおよびログリーダー エージェントのセキュリティ設定を実施後、「次へ」を選択します。
10) 「ウィザードのアクション」画面で、「パブリケーションを作成する」にチェック後、「次へ」を選択します。
11) 「ウィザードの完了」画面で、「パブリケーション名」欄に任意のパブリケーション名を入力後、「完了」を選択します。
12) 正常にパブリケーションの作成が完了したことを確認後、「閉じる」を選択します。
13) 「レプリケーション」-「ローカル パブリケーション」-「作成したパブリケーション」-「右クリック」-「新しいサブスクリプション」を選択します。
14) 該当のパブリケーションが選択されていることを確認後、「次へ」を選択します。
15) 「ディストリビューション エージェントの場所」画面で、「プッシュ サブスクリプション」を選択後、「次へ」を選択します。
16) 「サブスクライバ」画面で、「サブスクライバの追加」より、サブスクライバ、サブスクリプション データベースを選択後、「次へ」を選択します。(本例では、サブスクライバに 3号機、4号機で構成された可用性グループの可用性リスナー名「sqlag2」、サブスクリプション データベースに可用性データベース「AGDB2」を選択)
17) 「ディストリビューション エージェントのセキュリティ」画面で、ディストリビュータへの接続、サブスクライバへの接続に使用するアカウントを指定後、「次へ」を選択します。
18) 「同期スケジュール」画面で、「エージェント スケジュール」を指定し、「次へ」を選択します。(本例では「連続実行する」を選択)
19) 「サブスクリプションの初期化」画面で、サブスクリプションの初期化方法を選択後、「次へ」を選択します。
20) 「ウィザードのアクション」画面で、「サブスクリプションを作成する」にチェック後、「次へ」を選択します。
21) 「ウィザードの完了」画面で、設定内容を確認後、「完了」を選択します。
22) 正常にサブスクリプションの作成が完了したことを確認後、「閉じる」を選択します。
[補足]
レプリケーション モニターを起動することで、レプリケーションのステータスを確認することができます。今回の構成の場合は、レプリケーション モニタの接続先に指定するリモート ディストリビュータに対して、ディストリビュータを構成している可用性グループの可用性リスナー経由で接続します。
1) 「レプリケーション モニターの起動」からレプリケーション モニタを起動し、「パブリッシャの追加」を選択します。
2) 「追加」-「ディストリビュータを指定し、そのパブリッシャを追加する」を選択後、ディストリビュータを構成している可用性グループの可用性リスナーで接続し、｢OK｣ を選択します。
3) パブリッシャ(可用性グループのプライマリ レプリカ)に接続し、レプリケーションの状態を確認します。
SQL Server AlwaysOn 可用性グループ間でレプリケーションを構成することはあまりないかと思いますが、SQL Server 2016 SP2 CU3、SQL Server 2017 CU6 以降のバージョンを使用することで、ディストリビューション データベースを可用性データベースに追加することが可能になっており、今まで単一障害ポイントになっていたディストリビュータの可用性を高めることが出来ました。
現時点において、まだSSMSから設定することが出来ないため、構築する手間が掛かりますが、本ブログが参考になれば幸いです。
SQL Server 2016 以降であれば、分散型可用性グループ機能を使用することも可能となります。
