NOBTAの気ままにITブログ

Azure全般 / SQL Serverに関する情報を発信していきます。


スポンサーリンク

SQL Server AlwaysOn 可用性グループ間トランザクション レプリケーションの構築方法について [Azure/SQL Server]


スポンサーリンク

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) を使用

 

f:id:nobtak:20200621024931p:plain

  

[前提条件]

  • 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 可用性グループとで、リモート パブリッシャ、リモート ディストリビューターを構成

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」-「右クリック」-「プロパティ」を選択します。

f:id:nobtak:20200621034731p:plain

11) 「ページの選択」:「オプション」-「復旧モデル」:「完全」に変更し、「OK」を選択します。

f:id:nobtak:20200621034951p:plain

12) 3号機上で ディストリビューション データベース「distribution」の完全バックアップ、トランザクション ログのバックアップを採取し、任意のフォルダに保存します。

13) 3号機上で以下のクエリを実行し、可用性グループ (本例では「AG2」)に可用性データベースとして データベース「distribution」を追加します。

use master
go
alter availability group [AG2]
add database [distribution]
go

14) 3号機上および4号機上で、可用性グループ (本例では「AG2」)に可用性データベースとして データベース「distribution」が追加されていることを確認します。

※ 該当の可用性グループ -「右クリック」-「ダッシュボードの表示」を選択し、 可用性データベース「distribution」の同期状態が「同期済み」になっていることを確認します。

 f:id:nobtak:20200621145220p:plain

 

f:id:nobtak:20200621145529p:plain

[補足]
可用性グループの設定で「シード処理のモード」:「自動」 を選択していない場合、T-SQL を使用して、可用性データベースとしてデータベース「distribution」を登録します。

 

f:id:nobtak:20200621150014p:plain

 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
go
sp_adddistpublisher @publisher= 's2k19-01', @distribution_db= 'distribution', @working_directory= '\\stje****.file.core.windows.net\dist-working'
go

sp_adddistpublisher @publisher= 's2k19-02', @distribution_db= 'distribution', @working_directory= '\\stje****.file.core.windows.net\dist-working'
go

 

※ SQL Server 認証でリモートパブリッシャに接続する場合は、@security_mode, @login, @password パラメータを追加します。

use master
go
sp_adddistpublisher @publisher= 's2k19-01', @distribution_db= 'distribution', @working_directory= '\\stje****.file.core.windows.net\dist-working',@security_mode=0,@login='sa',@password='********'  
go

sp_adddistpublisher @publisher= 's2k19-02', @distribution_db= 'distribution', @working_directory= '\\stje****.file.core.windows.net\dist-working',@security_mode=0,@login='sa',@password='********'
go

 

17) 4号機上で 16)で実行したクエリを実行し、リモート パブリッシャをディストリビューターに登録します。

18) 1号機上で以下のクエリを実行し、リモート パブリッシャをディストリビューターに登録します。

@distributor: ディストリビュータを構成した可用性グループ(本例では 3号機、4号機) の可用性リスナー名 (本例では「sqlag2」)

@password: 4)、8) で指定したパスワード

use master
go
sp_adddistributor @distributor='sqlag2',@password='********'
go

19) 2号機上で 18) のクエリを実行し、リモート パブリッシャをディストリビューターに登録します。

20) 3号機上で以下のクエリを実行し、既存パブリッシャーのリダイレクトパブリッシャ (本例では、1号機、2号機を構成する可用性グループの可用性リスナーを指定」) 先を登録します。

@original_publisher : パブリッシャを構成した可用性グループの各ノード名 (本例では 1号機、2号機)

@publisher_db : パブリッシャを構成した可用性グループの可用性データベース名 (本例では 「AGDB1」)

@redirected_publisher : パブリッシャを構成した可用性グループの可用性リスナー名 (本例では 「sqlag-lip」)

use distribution
go
exec sys.sp_redirect_publisher
@original_publisher = 'S2K19-01',
@publisher_db = 'AGDB1',
@redirected_publisher = 'sqlag-lip'
go
exec sys.sp_redirect_publisher
@original_publisher = 'S2K19-02',
@publisher_db = 'AGDB1',
@redirected_publisher = 'sqlag-lip'
go

※ 今回、ディストリビューション データベース(本例では「distribution」)を可用性データベースとして登録しているため、上記のクエリを 3号機で実行することにより、4号機上のディストリビューション データベース(本例では「distribution」)にも自動的に反映されます。

 

2. Azure 東日本リージョン上のSQL Server 可用性グループとAzure 西日本リージョン上のSQL Server 可用性グループとで、トランザクション レプリケーションを構成

1) SQL Server がインストールされた Azure 仮想マシン (可用性レプリカ プライマリ:1号機) にログインします。

2) SQL Server Management Studio (以下 SSMS) を起動し、管理者権限(sysadmin)が付与されたログインで該当のインスタンスに接続します。

3) 「レプリケーション」-「ローカル パブリケーション」-「右クリック」-「新しいパブリケーション」を選択します。 

f:id:nobtak:20200624201455p:plain

4) ウィザードを進め、「パブリケーション データベース」画面でパブリケーション データベースを選択後、「次へ」を選択します。(本例では 1号機、2号機で構成された可用性グループの可用性データベース「AGDB1」を選択) 
5) 「トランザクション レプリケーション」を選択後、「次へ」を選択します。 

f:id:nobtak:20200624201722p:plain

6) 「アーティクル」画面で、パブリッシュするテーブルなどを選択後、「次へ」を選択します。 

※ 必要に応じて「アーティクルのプロパティ」からアーティクルのプロパティ設定を変更します。

f:id:nobtak:20200624202212p:plain

7) 「テーブル行のフィルター選択」画面で、必要に応じてフィルター条件を追あし、「次へ」を選択します。 

f:id:nobtak:20200624202355p:plain

8) 「スナップショット エージェント」画面で、「スナップショットをすぐに作成し、...」を選択後、「次へ」を選択します。

f:id:nobtak:20200624202528p:plain

9) 「エージェント セキュリティ」画面で、スナップショット エージェントおよびログリーダー エージェントのセキュリティ設定を実施後、「次へ」を選択します。

f:id:nobtak:20200624202924p:plain

10) 「ウィザードのアクション」画面で、「パブリケーションを作成する」にチェック後、「次へ」を選択します。

f:id:nobtak:20200624203035p:plain

11) 「ウィザードの完了」画面で、「パブリケーション名」欄に任意のパブリケーション名を入力後、「完了」を選択します。 

f:id:nobtak:20200624203227p:plain

12) 正常にパブリケーションの作成が完了したことを確認後、「閉じる」を選択します。 

f:id:nobtak:20200624203347p:plain

13) 「レプリケーション」-「ローカル パブリケーション」-「作成したパブリケーション」-「右クリック」-「新しいサブスクリプション」を選択します。 

f:id:nobtak:20200624203514p:plain

14) 該当のパブリケーションが選択されていることを確認後、「次へ」を選択します。

f:id:nobtak:20200624203856p:plain

15) 「ディストリビューション エージェントの場所」画面で、「プッシュ サブスクリプション」を選択後、「次へ」を選択します。

f:id:nobtak:20200624204123p:plain

16) 「サブスクライバ」画面で、「サブスクライバの追加」より、サブスクライバ、サブスクリプション データベースを選択後、「次へ」を選択します。(本例では、サブスクライバに 3号機、4号機で構成された可用性グループの可用性リスナー名「sqlag2」、サブスクリプション データベースに可用性データベース「AGDB2」を選択) 

f:id:nobtak:20200624204322p:plain

17) 「ディストリビューション エージェントのセキュリティ」画面で、ディストリビュータへの接続、サブスクライバへの接続に使用するアカウントを指定後、「次へ」を選択します。

f:id:nobtak:20200624204927p:plain

18) 「同期スケジュール」画面で、「エージェント スケジュール」を指定し、「次へ」を選択します。(本例では「連続実行する」を選択)

f:id:nobtak:20200624205139p:plain

19) 「サブスクリプションの初期化」画面で、サブスクリプションの初期化方法を選択後、「次へ」を選択します。

f:id:nobtak:20200624205401p:plain

20) 「ウィザードのアクション」画面で、「サブスクリプションを作成する」にチェック後、「次へ」を選択します。
f:id:nobtak:20200624205703p:plain

21) 「ウィザードの完了」画面で、設定内容を確認後、「完了」を選択します。 

f:id:nobtak:20200624205854p:plain

22) 正常にサブスクリプションの作成が完了したことを確認後、「閉じる」を選択します。 

f:id:nobtak:20200624210104p:plain

 

[補足]

レプリケーション モニターを起動することで、レプリケーションのステータスを確認することができます。今回の構成の場合は、レプリケーション モニタの接続先に指定するリモート ディストリビュータに対して、ディストリビュータを構成している可用性グループの可用性リスナー経由で接続します。

 

1) 「レプリケーション モニターの起動」からレプリケーション モニタを起動し、「パブリッシャの追加」を選択します。

f:id:nobtak:20200624211327p:plain

f:id:nobtak:20200624211459p:plain

 

2) 「追加」-「ディストリビュータを指定し、そのパブリッシャを追加する」を選択後、ディストリビュータを構成している可用性グループの可用性リスナーで接続し、「OK」 を選択します

f:id:nobtak:20200624211754p:plain

f:id:nobtak:20200624211931p:plain

 

f:id:nobtak:20200624215046p:plain

 

3) パブリッシャ(可用性グループのプライマリ レプリカ)に接続し、レプリケーションの状態を確認します。

f:id:nobtak:20200624215203p:plain

 

 

SQL Server AlwaysOn 可用性グループ間でレプリケーションを構成することはあまりないかと思いますが、SQL Server 2016 SP2 CU3、SQL Server 2017 CU6 以降のバージョンを使用することで、ディストリビューション データベースを可用性データベースに追加することが可能になっており、今まで単一障害ポイントになっていたディストリビュータの可用性を高めることが出来ました。

現時点において、まだSSMSから設定することが出来ないため、構築する手間が掛かりますが、本ブログが参考になれば幸いです。

 

SQL Server 2016 以降であれば、分散型可用性グループ機能を使用することも可能となります。  

 


スポンサーリンク