EC2上にSQL Server Always On 可用性グループを構築！【Always On 可用性グループ設定編】

はじめに

SQL Server on EC2を用いて、Always On 可用性グループを構築する方法を調べましたが1つの媒体にまとまってるのを見つけられなかったため記事を執筆することにしました！
今回は、Always On 可用性グループ設定編に該当します。SQL ServerのAlways On 可用性グループ設定を筆頭に構築していきます。
また、構築順序は下記の通り参照してください。
AWS CloudFormation編 > OS設定編 > WSFC設定編 > Always On 可用性グループ設定編

前提

・対象OS：Windows Server 2022
・対象SQL Serverエディション：SQL Server 2022 Standard Edition
・AWSアカウントが既にあること
・東京リージョンを使用

構築リソース情報

下記表の値を用いて、構築する

項目	値
1号機(ホスト名：PublicInstance1)のプライマリIP	10.0.1.100
1号機(ホスト名：PublicInstance1)のセカンダリIP(クラスタコアリソース用)	10.0.1.101
1号機(ホスト名：PublicInstance1)のセカンダリIP(サービス用)	10.0.1.102
2号機(ホスト名：PublicInstance2)のプライマリIP	10.0.2.100
2号機(ホスト名：PublicInstance2)のセカンダリIP(クラスタコアリソース用)	10.0.2.101
2号機(ホスト名：PublicInstance2)のセカンダリIP(サービス用)	10.0.2.102
Managed Microsoft ADのDNS 1a側のIP	10.0.3.204 ※リソース作成後に自動採番されるため、左記と違う可能性あり
Managed Microsoft ADのDNS 1c側のIP	10.0.4.112 ※リソース作成後に自動採番されるため、左記と違う可能性あり
WSFCのクラスタ名	poc-cluster
可用性グループ名	poc-sqlserver

概要図

Always On 可用性グループの設定

SQL Serverの起動するサービスアカウント設定

[スタート] > [Microsoft SQL Server 2022] > [SQL Server 構成マネージャー]

[SQL Server のサービス] > [SQL Server]を右クリック > [プロパティ]

[参照]

[場所]

[ディレクトリ全体] > [OK]

[オブジェクト名]を入力 > [名前の確認]

[OK]

[OK]

[パスワード]を入力 > [適用]

[はい]

[Alaways On 可用性グループ] > [Alaways On 可用性グループを有効にする]をチェック > [OK]

[OK]

[SQL Server のサービス] > [SQL Server]を右クリック > [再起動]

※2号機でも同様にサービスアカウントとAlways On 可用性グループを有効にし、再起動

Always On 可用性グループ用データベースフォルダと初回同期バックアップフォルダ作成

管理者でPowershellを開き、下記コマンドを実行する

New-Item -Path "C:\SQL_DATA" -ItemType Directory

※2号機でも同様にコマンドを実行

管理者でPowershellを開き、下記コマンドを実行する
※1号機のみでOK

New-Item -Path "C:\ShareBackup" -ItemType Directory
New-SmbShare -Name ShareBackup -Path "C:\ShareBackup" -FullAccess Everyone

SQL Serverのログイン追加

ドメインユーザをサインアウトし、AdministratorでRDP接続
※ローカル管理者で入りたいため、ユーザ名はlocal\Administratorと入力

[スタート] > [Microsoft SQL Server Tools 19] > [SQL Server Management Studio Management Studio 19]

[接続]

[セキュリティ] > [ログイン] > [新しいログイン]

[検索]

[場所]

[ドメインユーザとパスワード]を入力 > [OK]

[ディレクトリ全体]を選択 > [OK]

[オブジェクト名]へサービスユーザーアカウントを入力 > [OK]

ログイン名が入力される

[サーバー ロール] > [sysadmin]をチェック > [OK]

ログイン一覧に追加される

※2号機でも同様にログイン追加

Always On 可用性グループ用データベースを作成

ローカル管理者をサインアウトし、サービスユーザーアカウントでRDP接続

[スタート] > [Microsoft SQL Server Tools 19] > [SQL Server Management Studio Management Studio 19]

ユーザー名がサービスユーザーアカウントが入ってることを確認し、[接続]

[新しいクエリ]

下記のSQL クエリを実行する

-- データベース作成
CREATE DATABASE SampleDB
ON PRIMARY (
    NAME = 'SampleDB_Data',
    FILENAME = 'C:\SQL_DATA\SampleDB_Data.mdf',
    SIZE = 10MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
LOG ON (
    NAME = 'SampleDB_Log',
    FILENAME = 'C:\SQL_DATA\SampleDB_Log.ldf',
    SIZE = 5MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 1MB
);

■パラメータ参考
NAME: データファイルまたはログファイルの論理名
FILENAME: ファイルの保存場所とファイル名
SIZE: 初期サイズ
MAXSIZE: 最大サイズ
FILEGROWTH: ファイルの成長単位（サイズが不足したときに追加されるサイズ）

クエリが正常に実行される

[データベース] > [作成DB] > [新しいクエリ]

下記のSQL クエリを実行

-- テーブルの作成
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    HireDate DATE
);
GO

-- サンプルデータの挿入
INSERT INTO Employees (FirstName, LastName, HireDate)
VALUES 
    ('John', 'Doe', '2024-01-15'),
    ('Jane', 'Smith', '2024-02-20'),
    ('Michael', 'Johnson', '2024-03-25'),
    ('Emily', 'Davis', '2024-04-30'),
    ('Chris', 'Brown', '2024-05-10');
GO

-- データの確認
SELECT * FROM Employees;

問題なくデータ確認

[データベース] > [作成DB] > [タスク] > [バックアップ]

デフォルトで[OK]

[OK]

[Always On 高可用性] > [可用性グループ] > [新しい可用性グループ ウィザード]

[次へ]

[可用性グループ名]を入力 > [次へ]

[対象DB]を✅ > [次へ]

[レプリカ] > [レプリカの追加]

サーバー名に2号機を入力 > [接続]

[自動フェールオーバー]を✅ > [次へ]

[完全なデータベースとログ バックアップ]を選択 > [ファイル共有のパスを Windows 形式で指定]を共有フォルダ名を入力 > [次へ]
※共有フォルダ名パスの入力注意

[次へ]

[完了]

[閉じる]

対象DBが追加される

[可用性グループ リスナー] > [リスナーの追加]

[リスナーのDNS名]を入力 > [ポート]を1433と入力 > [ネットワーク モード]は静的IPを選択 > [追加]

1号機のサービス用IPを入力 > [OK]

2号機のサービス用IPを入力 > [OK]

[OK]

リスナーが作成される

接続確認

東京リージョンにSQL Serverを含んだEC2構築(ami-086acc6b2cb62480c)
PublicSubnet1に配置
Security Groupは1号機と2号機と同一
ドメイン(例：poc.com)参加済み

RDP接続

[スタート] > [Microsoft SQL Server Tools 19] > [SQL Server Management Studio Management Studio 19]

可用性グループ名を入力 > [接続]

[新しいクエリ]

下記のSQL クエリを実行

USE SampleDB;
GO

-- データの確認
SELECT * FROM Employees;

問題なくデータ確認

[対象可用性グループ] > [フェールオーバー]
(1号機より実施)

[次へ]

[次へ]
1号機から2号機へ

[接続]

[接続]

[次へ]

[完了]

[閉じる]

セカンダリに変更される(1号機側)

3号機を用いて、下記SQL クエリを実行

INSERT INTO Employees (FirstName, LastName, HireDate)
VALUES 
    ('John', 'Joe', '2024-07-01'),
    ('Jane', 'Shane', '2024-07-05'),
    ('Michael', 'Jodan', '2024-07-10'),
    ('Emily', 'Curry', '2024-07-20'),
    ('Chris', 'Poul', '2024-07-30');
GO

再度可用性グループをフェールオーバー(2号機から１号機へ)

3号機を用いて、データ確認

-- データの確認
SELECT * FROM Employees;

クラスタノード1号機、可用性グループノード1号機が主系状態で、1号機を停止

EC2コンソールより、1号機を停止

３号機を用いて、データ確認
(クラスタノード2号機、可用性グループノード2号機が主系状態)

SELECT GETDATE() AS CurrentDateTime;

-- データの確認
SELECT * FROM Employees;

最後に

Always On 可用性グループ設定編、問題なく構築することができましたか？以上で、全ての構築が完了です。
Cloudformationのクリーンアップはお忘れずに実施してくださいね！

参照

AWS Directory Serviceで「Managed Microsoft AD」を構築した際の「管理用サーバー」の設定方法
【SQL Server on EC2】インスタンス間でAlwaysOnを構成する [1. OS準備編]
（1/4）AWS EC2でWindowsクラスタ検証：共有ストレージ検討編