SE の雑記

2016Azure Stack 向けの AlwaysOn 可用性グループ (AG) の環境を作成するための雑な手順です。
まだ自動化とか考えられていないので、一つ一つ手動で実行して下さい（

Azure Stack 側の準備

1. Market Place から以下を追加する
• SQL IaaS Extension
• Market Place の SQL Server インストール済み Windows イメージを展開する際に、SQL IaaS Extension を事前に追加していないとエラーになります
• Free SQL Server License: SQL Server 2017 Developer on Windows Server
• 検証なので、ひとまず、Developer Edition で
2. Azure Stack に SQL Server リソースプロバイダーを追加
• Microsoft Azure Stack で SQL データベースを使用する の手順を実施

仮想マシンの準備

1. Market Place に追加したイメージで SQL Server を 3 台デプロイ
   デプロイ時には可用性セットを指定する
• SQL Always On 可用性グループを使用して高可用性を提供する に次の記載があるため 3 台で構築
• SQL Always On インスタンスの構成にはさらに手順が必要で、3 台の VM (または物理マシン) が必要です。

Azure のネットワークの準備

1. 各 VM の NSG で TCP 1433 を許可しておく
2. Azure LB を作成し、デプロイ時に指定した可用性セットをバックエンドプールとして指定
3. プローブポートとして 59999 を指定
4. 作成した Azure LB のパブリック IP の DNS 名に「ag-ln.local.cloudapp.azurestack.external」を設定
   (AG のリスナーに「AG-LN」を指定することを想定した設定)

クラスターの構築

1. 今回はワークグループクラスターで構築したため、各環境で次のコマンドを実行し、サフィックスを追加

$DNSSuffix = "local.cloudapp.azurestack.external"
Set-ItemProperty registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters -Name "NV Domain" -Value $DNSSuffix
Restart-Computer -Force

2. 各環境で次のコマンドを実行して WSFC の機能を追加

Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools

3. 個別の DNS を構築していないため、各環境の HOSTS を書き換えてひとまず名前解決

notepad c:\Windows\system32\drivers\etc\hosts
==
10.1.0.6	AG-01.internal.azurestack.local
10.1.0.7	AG-02.internal.azurestack.local
10.1.0.8	AG-03.internal.azurestack.local
10.1.0.6	AG-01
10.1.0.7	AG-02
10.1.0.8	AG-03
==

4. 次のコマンドを実行してクラスターを構築 (VM 作成時に指定した管理者アカウント (元 Administrator) で実行)

$StaticIP = "10.1.0.100"
$NODE = @("AG-01", "AG-02", "AG-03")
New-Cluster -Name WSFC-CNO -StaticAddress $StaticIP -node $Node -AdministrativeAccessPoint Dns
Get-ClusterResource "Cluster IP Address" | Get-ClusterParameter
Get-ClusterResource "Cluster IP Address" | Set-ClusterParameter -Multiple @{Address="169.254.1.1";SubnetMask="255.255.255.255";OverrideAddressMatch=1;EnableDhcp=0}
Stop-ClusterGroup -Name "Cluster Group"
Start-ClusterGroup -Name "Cluster Group"

AlwaysOn 可用性グループの作成

1. 各環境で次のコマンドを実行して、SQL Server 向けファイアウォールを設定

New-NetFirewallRule -Name "SQL Server" -DisplayName "SQL Server" -Protocol "TCP" -LocalPort 1433, 5022, 59999

2. 各ノードで次の SQL を実行

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MASTER KEY Passw0rd'
CREATE LOGIN AlwaysOnEndpoint WITH PASSWORD = 'AlwaysOn Passw0rd',CHECK_EXPIRATION=OFF
CREATE USER AlwaysOnEndpoint FOR LOGIN AlwaysOnEndpoint
EXEC sp_configure 'contained database authentication', 1
GO
RECONFIGURE
GO
-- 検証した SQL Server 2017 の環境では、AG の接続が WSFC から実施できていないかったため、権限を補正
-- https://support.microsoft.com/en-us/help/2847723/cannot-create-a-high-availability-group-in-microsoft-sql-server-2012
GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM]
GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM]

3. 初期のプライマリサーバーとして使用するサーバーで次の SQL を実行
   
   (事前に「C:\certtemp」ディレクトリを作成しておく)

CREATE CERTIFICATE AlwaysOnEndpoint_Cert AUTHORIZATION AlwaysOnEndpoint
WITH SUBJECT = 'AlwaysOn Endpoint',START_DATE = '01/01/2015',EXPIRY_DATE = '01/01/2100'
CREATE ENDPOINT hadr_endpoint STATE = STARTED  AS TCP (
LISTENER_PORT=5022, LISTENER_IP = ALL  )
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE AlwaysOnEndpoint_Cert , ROLE = ALL)
GRANT CONNECT ON ENDPOINT::hadr_endpoint TO AlwaysOnEndpoint
BACKUP CERTIFICATE AlwaysOnEndpoint_Cert TO FILE = 'C:\certtemp\certbackup.cer' WITH PRIVATE KEY (FILE='C:\certtemp\certbackup.pvk', ENCRYPTION BY PASSWORD='Enc Passw0rd')

4. 「C:\certtemp」ディレクトリを残りの 2 台にコピーし、2 台で次の SQL を実行

CREATE CERTIFICATE AlwaysOnEndpoint_Cert AUTHORIZATION AlwaysOnEndpoint FROM FILE='C:\certtemp\certbackup.cer' WITH PRIVATE KEY (FILE='C:\certtemp\certbackup.pvk', DECRYPTION BY PASSWORD='Enc Passw0rd')
CREATE ENDPOINT hadr_endpoint STATE = STARTED  AS TCP (
LISTENER_PORT=5022, LISTENER_IP = ALL  )
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE AlwaysOnEndpoint_Cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL)
GRANT CONNECT ON ENDPOINT::hadr_endpoint TO AlwaysOnEndpoint

5. 初期のプライマリとして使用するサーバーで次の SQL を実行

CREATE AVAILABILITY GROUP AG01
FOR
REPLICA ON
'AG-01' WITH(
	ENDPOINT_URL = 'TCP://AG-01:5022',
	AVAILABILITY_MODE =
	 SYNCHRONOUS_COMMIT,
	FAILOVER_MODE = AUTOMATIC,
	SEEDING_MODE = AUTOMATIC,
	SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL),
	PRIMARY_ROLE (ALLOW_CONNECTIONS = ALL)
),
'AG-02' WITH(
	ENDPOINT_URL = 'TCP://AG-02:5022',
	AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
	FAILOVER_MODE = AUTOMATIC,
	SEEDING_MODE = AUTOMATIC,
	SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL),
	PRIMARY_ROLE (ALLOW_CONNECTIONS = ALL)
),
'AG-03' WITH(
	ENDPOINT_URL = 'TCP://AG-03:5022',
	AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
	FAILOVER_MODE = AUTOMATIC,
	SEEDING_MODE = AUTOMATIC,
	SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL),
	PRIMARY_ROLE (ALLOW_CONNECTIONS = ALL)
)
ALTER AVAILABILITY GROUP [AG01] GRANT CREATE ANY DATABASE;

6. セカンダリとして使用する 2 台で次の SQL を実行

ALTER AVAILABILITY GROUP [AG01] JOIN;
ALTER AVAILABILITY GROUP [AG01] GRANT CREATE ANY DATABASE;

AlwaysOn 可用性グループのリスナーの作成

1. プライマリで次の SQL を実行
   
   (エラーになった場合は、異なる IP をしていしたり IPv6 を無効にしたりいろいろやる)

USE [master]
GO
ALTER AVAILABILITY GROUP [AG01]
ADD LISTENER N'AG-LN' (
WITH IP
((N'10.1.0.110', N'255.255.255.0')
)
, PORT=1433);
GO

2. プライマリの PowerShell で次のコマンドを実行

$ClusterNetworkName = "Cluster Network 1"
$IPResourceName = "AG01_10.1.0.110"
$ILBIP = "192.168.102.42"
[int]$ProbePort = 59999
Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ILBIP";"ProbePort"=$ProbePort;"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"EnableDhcp"=0}
Stop-ClusterGroup "AG01"
Start-ClusterGroup "AG01"