オンプレミス環境からクラウド環境への移行に伴い、オンプレミス環境上に構築された SQL Server Always On 可用性グループ環境をクラウド上へ移行する機会も増えてきたのではないかと思います。
特にミドルウェアなどの制約がない場合は、Azure SQL Database や Azure SQL Managed Instance などのマネージド サービスに移行することで、データベースの運用コストを軽減できることが期待できるため、積極的に検討したほうが良いかと思います。
しかしながら、ミドルウェアなどの制約により、SQL Server をインストールした Azure 仮想マシン上にデータを移行し、運用する必要がある場合もあるかもしれません。
今回は、コストを下げつつ、高可用性＋ディザスタリカバリ (DR) を Azure 環境で実現する方式として、SQL Server Standard Edition 基本的な Always On 可用性グループ 間でリージョンを跨ぐ (東日本、西日本 間など) トランザクション レプリケーションを構築する方法について、自分の整理も兼ねて、まとめてみようと思います。
[構成例]
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 基本的な可用性グループ、可用性リスナーが構築されていること。
SQL Server Always On 基本的な可用性グループを Azure 環境上に構築する方法については、以下の URL を参照。
- Azure 西日本上の SQL Server Always On 基本的な可用性グループ環境で リモートディストリビュータ を構成
- Azure 東日本上の SQL Server Always On 基本的な可用性グループ 可用性データベースを リモート パブリッシャ として構成
- Azure 東日本リージョン上のSQL Server 可用性グループとAzure 西日本リージョン上のSQL Server 可用性グループとで、トランザクション レプリケーションを構成
- まとめ
Azure 西日本上の SQL Server Always On 基本的な可用性グループ環境で リモートディストリビュータ を構成
1) SQL Server がインストールされた Azure 仮想マシン (可用性レプリカ プライマリ側： S2K19-03 (3号機)) にログインします。
2) SQL Server Management Studio (以下 SSMS) を起動し、管理者権限(sysadmin)が付与されたログインで該当のインスタンスに接続します。
3)「新しいクエリ」を選択します。
4) 以下のクエリを実行し、可用性レプリカ プライマリ： S2K19-03 (3号機) をディストリビュータとして構成します。
| use master go sp_adddistributor @distributor=@@servername, @password='********' go |
5) SQL Server がインストールされた Azure 仮想マシン (可用性レプリカ セカンダリ：S2K19-04 (4号機)) にログインします。
6) SQL Server Management Studio (以下 SSMS) を起動し、管理者権限(sysadmin)が付与されたログインで該当のインスタンスに接続します。
7)「新しいクエリ」を選択します。
8) 以下のクエリを実行し、可用性レプリカ セカンダリ：S2K19-04 (4号機) をディストリビュータとして構成します。
| use master go sp_adddistributor @distributor=@@servername, @password='********' go |
9) 可用性レプリカ プライマリ側： S2K19-03 (3号機) で以下のクエリを実行し、ディストリビューション データベース「distribution」を作成します。
| use master go sp_adddistributiondb 'distribution' go |
10) 9)で作成した ディストリビューション データベース「distribution」-「右クリック」-「プロパティ」を選択します。
11) 「ページの選択」:「オプション」-「復旧モデル」:「完全」に変更し、「OK」を選択します。
12) 3号機上で ディストリビューション データベース「distribution」の完全バックアップ、トランザクション ログのバックアップを採取し、任意のフォルダに保存します。
13)「Always On 高可用性」-「可用性グループ」- 右クリック -「新しい可用性グループ」を選択します。
14) ページの選択 :「全般」-「可用性グループ名」に、ディストリビューション データベース (distribution) 用の可用性グループ名 (本例では「dist」) を入力後、可用性レプリカ欄に、S2K19-03 (3号機)、S2K19-04 (4号機) を追加し、「OK」を選択します。
※ SQL Server Always On 基本的な可用性グループの場合、1つの可用性グループに所属可能な可用性データベースは 1つに制限されているため、複数の可用性データベースを構築する必要がある場合、複数の基本的な可能性グループを構築する必要があります。
15) ディストリビューション データベース (distribution) 基本的な可用性グループ用に可用性リスナーを構成します。
※ 詳細な手順については、以下の URL を参照。
※ 正常性プローブ用のポートは一意である必要があります。そのため、複数の可用性リスナーを作成する場合、ポートが一意になるように 可用性リスナー用 Azure Load Balancer の 正常性プローブを構成します。
※ 既に可用性グループリスナー用の Azure Load Balancer を構築済みの場合、既存の Azure Load Balancer の正常性プローブで 新しい正常プローブを追加します。
[Azure Load Balancer 正常性プローブ構成例]
16) 3号機上で以下のクエリを実行し、可用性グループ (本例では「dist」)に可用性データベースとして データベース「distribution」を追加します。
| use master go alter availability group [dist] add database [distribution] go |
17) 3号機上および4号機上で、可用性グループ (本例では「dist」)に可用性データベースとして データベース「distribution」が追加されていることを確認します。
※ 該当の可用性グループ -「右クリック」-「ダッシュボードの表示」を選択し、 可用性データベース「distribution」の同期状態が「同期済み」になっていることを確認します。
[補足]
可用性グループの設定で「シード処理のモード」:「自動」 を選択していない場合、T-SQL を使用して、可用性データベースとしてデータベース「distribution」を登録します。
T-SQLを使用して可用性データベースを追加する方法については、以下のURLを参照。
18) 4号機上で以下のクエリを実行し、ディストリビューターのメタデータを作成します。
※ ディストリビューター データベース「distribution」は既に可用性データベースとして登録されているため、ディストリビューターのメタデータのみが作成されます。
| use master go sp_adddistributiondb 'distribution' go |
Azure 東日本上の SQL Server Always On 基本的な可用性グループ 可用性データベースを リモート パブリッシャ として構成
1) SQL Server がインストールされた Azure 仮想マシン (可用性レプリカ プライマリ側： S2K19-03 (3号機)) にログインします。
2) SQL Server Management Studio (以下 SSMS) を起動し、管理者権限(sysadmin)が付与されたログインで該当のインスタンスに接続します。
3)「新しいクエリ」を選択します。
4) 以下のクエリを実行し、リモート パブリッシャ を リモート ディストリビューター に登録します。
@publisher : Azure 東日本リージョンに構築した SQL Server 可用性グループに所属するレプリカ名を指定 (本例では S2K19-01 (1号機)、S2K19-02 (2号機))
@distribution_db : ディストリビューション データベース名を指定 (本例では「distribution」)
@working_directory : 各ディストリビューター (本例では S2K19-03 (3号機)、S2K19-04 (4号機)) からアクセス可能な共有フォルダパスを指定
可能な共有フォルダパスを指定
|
use master sp_adddistpublisher @publisher= 's2k19-02', @distribution_db= 'distribution', @working_directory= '\\stje****.file.core.windows.net\dist-working' |
※ 本例では、ディストリビュータのワーキング ディレクトリの共有フォルダパスを、Azure Files 共有 に指定。
※ 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='********' |
5) 3号機から4号機へディストリビューション データベース用の基本的な可用性グループ (本例では「dist」) を手動でフェーズオーバー後、4号機上で 4)で実行したクエリを実行し、リモート パブリッシャをディストリビューターに登録します。
6) SQL Server がインストールされた Azure 仮想マシン (可用性レプリカ プライマリ側： S2K19-01 (1号機)) にログインします。
7) SQL Server Management Studio (以下 SSMS) を起動し、管理者権限(sysadmin)が付与されたログインで該当のインスタンスに接続します。
8)「新しいクエリ」を選択します。
9) 1号機上で以下のクエリを実行し、リモート ディストリビュータ を登録します。
@distributor : ディストリビューション データベース用に作成した基本的な可用性グループ (本例では 3号機、4号機) の可用性リスナー名 (本例では「agldist」) を指定
@password :「Azure 西日本上の SQL Server Always On 基本的な可用性グループ環境で リモートディストリビュータ を構成」項番 4)、8) で指定したパスワードを指定
|
use master |
10) SQL Server がインストールされた Azure 仮想マシン (可用性レプリカ プライマリ側 : S2K19-02 (2号機)) にログインします。
11) SQL Server Management Studio (以下 SSMS) を起動し、管理者権限(sysadmin)が付与されたログインで該当のインスタンスに接続します。
12)「新しいクエリ」を選択します。
13) 2号機上で 9) のクエリを実行し、リモート ディストリビュータ を登録します。
14) 3号機上で以下のクエリを実行し、パブリッシャー および リダイレクト パブリッシャ先を登録します。 (リダイレクト パブリッシャ先には、1号機、2号機を構成する基本的な可用性グループの可用性リスナー名を指定。)
@original_publisher : パブリッシャを構成した可用性グループの各ノード名 (本例では 1号機、2号機)
@publisher_db : パブリッシャを構成した可用性グループの可用性データベース名 (本例では 「AGDB01」)
@redirected_publisher : パブリッシャを構成した可用性グループの可用性リスナー名 (本例では 「agl01」)
|
use distribution |
※ 今回、ディストリビューション データベース(本例では「distribution」)を可用性データベースとして登録しているため、上記のクエリを 3号機で実行することにより、4号機上のディストリビューション データベース(本例では「distribution」)にも自動的に反映されます。
Azure 東日本リージョン上のSQL Server 可用性グループとAzure 西日本リージョン上のSQL Server 可用性グループとで、トランザクション レプリケーションを構成
1) SQL Server がインストールされた Azure 仮想マシン (可用性レプリカ プライマリ：1号機) にログインします。
2) SQL Server Management Studio (以下 SSMS) を起動し、管理者権限(sysadmin)が付与されたログインで該当のインスタンスに接続します。
3) 「レプリケーション」-「ローカル パブリケーション」-「右クリック」-「新しいパブリケーション」を選択します。
4) ウィザードを進め、「パブリケーション データベース」画面でパブリケーション データベースを選択後、「次へ」を選択します。(本例では 1号機、2号機で構成された可用性グループの可用性データベース「AGDB01」を選択)
5) 「トランザクション レプリケーション」を選択後、「次へ」を選択します。
6) 「アーティクル」画面で、パブリッシュするテーブルなどを選択後、「次へ」を選択します。
※ 必要に応じて「アーティクルのプロパティ」からアーティクルのプロパティ設定を変更します。
7) 「テーブル行のフィルター選択」画面で、必要に応じてフィルター条件を追あし、「次へ」を選択します。
8) 「スナップショット エージェント」画面で、「スナップショットをすぐに作成し、...」を選択後、「次へ」を選択します。
9) 「エージェント セキュリティ」画面で、スナップショット エージェントおよびログリーダー エージェントのセキュリティ設定を実施後、「次へ」を選択します。
10) 「ウィザードのアクション」画面で、「パブリケーションを作成する」にチェック後、「次へ」を選択します。
11) 「ウィザードの完了」画面で、「パブリケーション名」欄に任意のパブリケーション名を入力後、「完了」を選択します。
12) 正常にパブリケーションの作成が完了したことを確認後、「閉じる」を選択します。
13) 「レプリケーション」-「ローカル パブリケーション」-「作成したパブリケーション」-「右クリック」-「新しいサブスクリプション」を選択します。
14) 該当のパブリケーションが選択されていることを確認後、「次へ」を選択します。
15) 「ディストリビューション エージェントの場所」画面で、「プッシュ サブスクリプション」を選択後、「次へ」を選択します。
16) 「サブスクライバ」画面で、「サブスクライバの追加」より、サブスクライバ、サブスクリプション データベースを選択後、「次へ」を選択します。(本例では、サブスクライバに 3号機、4号機で構成された可用性グループの可用性リスナー名「agl02」、サブスクリプション データベースに可用性データベース「AGDB2」を選択)
17) 「ディストリビューション エージェントのセキュリティ」画面で、ディストリビュータへの接続、サブスクライバへの接続に使用するアカウントを指定後、「次へ」を選択します。
18) 「同期スケジュール」画面で、「エージェント スケジュール」を指定し、「次へ」を選択します。(本例では「連続実行する」を選択)
19) 「サブスクリプションの初期化」画面で、サブスクリプションの初期化方法を選択後、「次へ」を選択します。
20) 「ウィザードのアクション」画面で、「サブスクリプションを作成する」にチェック後、「次へ」を選択します。
21) 「ウィザードの完了」画面で、設定内容を確認後、「完了」を選択します。
22) 正常にサブスクリプションの作成が完了したことを確認後、「閉じる」を選択します。
23) 2号機、4号機 (Always On 基本的な可用性グループ セカンダリレプリカ側) 上で以下のクエリを実行し、リンクサーバーオブジェクトを作成します。
※ @server パラメータには、リモートディストリビュータ側 (3号機、4号機) の基本的な可用性グループの可用性リスナー名を指定
|
EXEC master.dbo.sp_addlinkedserver @server = N'AGL02', @srvproduct=N'SQL Server' |
※ Always On 基本的な可用性グループのセカンダリ レプリカ側でリンクサーバーオブジェクトを作成しなければ、フェールオーバー発生後、正常にレプリケーションが動作しなくなります。
[補足]
レプリケーション モニターを起動することで、レプリケーションのステータスを確認することができます。今回の構成の場合は、レプリケーション モニタの接続先に指定するリモート ディストリビュータに対して、ディストリビュータを構成している可用性グループの可用性リスナー経由で接続します。
1) 「レプリケーション モニターの起動」からレプリケーション モニタを起動し、「パブリッシャの追加」を選択します。
2) 「追加」-「ディストリビュータを指定し、そのパブリッシャを追加する」を選択後、ディストリビュータを構成している可用性グループの可用性リスナーで接続し、｢OK｣ を選択します。
3) パブリッシャ(可用性グループのプライマリ レプリカ)に接続し、レプリケーションの状態を確認します。
まとめ
今回は、コストを下げつつ、高可用性＋ディザスタリカバリ (DR) を Azure 環境で実現する方式として、SQL Server Standard Edition 基本的な Always On 可用性グループ 間でリージョンを跨ぐ (東日本、西日本 間など) トランザクション レプリケーションの構築方法についてまとめてみました。
SQL Server AlwaysOn 基本的な可用性グループ間でレプリケーションを構成することはあまりないかと思いますが、SQL Server 2016 SP2 CU3、SQL Server 2017 CU6 以降のバージョンを使用することで、ディストリビューション データベースを可用性データベースに追加することが可能になっており、今まで単一障害ポイントになっていたディストリビュータの可用性を高めることができるようになりました。
現時点において、まだSSMSから設定することが出来ないため、構築する手間が掛かりますが、本ブログが参考になれば幸いです。
※ 2022年3月 現在
