SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server 2016 のドメインユーザーを使用しない可用性グループをスクリプトベースで作成してみる

leave a comment

AlwaysOn 可用性グループの可用性レプリカ間の接続ですが、ドメインユーザーやミラーアカウントを使用しなくても、データベースミラーリングと同様に、エンドポイントの設定に証明書を使用することで、デフォルトの「NT Service\MSSQLSERVER」で SQL Server のサービスを起動していても、設定をすることができます。

実際の設定方法については、以下が参考になります。
例:証明書を使用したデータベース ミラーリングの設定 (Transact-SQL)
データベース ミラーリング エンドポイントで発信接続に証明書を使用できるようにする (Transact-SQL)
データベース ミラーリング エンドポイントで着信接続に証明書を使用できるようにする (Transact-SQL)
非ドメイン環境上のサーバー間でミラーリングを構築する方法について

簡易ですが、エンドポイント間の接続に証明書を使用した AlwaysOn の環境をスクリプトベースで作成する方法についてまとめてみたいと思います。

設定の前提としては、

  • SQL Server のインストールが終了している
  • WSFC の構築が完了している
  • Administrator でログインし、各可用性レプリカで Administrator のパスワードが同一
  • 「Enable-PSRemoting」が、各可用性レプリカで実行されており、「Invoke-Command」 で接続できる
  • プライマリのノードで PowerShell を実行
  • コンピューター名で名前解決ができる

となっています。

■AlwaysOn の有効化

今回は 3 ノードの環境 (2016-WSFC-01/02/03) に対して、AlwaysOn を構築していきます。

最初に、各ノードで AlwaysOn を有効化します。GUI の場合は SQL Server 構成マネージャーで実行している作業ですね。

AlwaysOn の有効化に合わせて、SQL Server の既定のインスタンスのポートとエンドポイントのポートについて F/W のルールを作成しています。

# 一台目のノードでスクリプトを実行し、可用性グループを作成する
$cred = Get-Credential
$Node = @("2016-WSFC-01", "2016-WSFC-02", "2016-WSFC-03")


# AlwaysOn の有効化と必要となる F/W の設定
$Script = {
$ENV:PSModulePath = [System.Environment]::GetEnvironmentVariable("PSModulePath","Machine") 
Import-Module SQLPS -DisableNameChecking 
 
Enable-SQLAlwaysOn -ServerInstance $ENV:COMPUTERNAME -Force 
New-NetFirewallRule -Name "SQL Server" -DisplayName "SQL Server" -Protocol "TCP" -LocalPort "1433" > $null 
New-NetFirewallRule -Name "SQL Server AlwaysOn Endpoint" -DisplayName "SQL Server" -Protocol "TCP" -LocalPort "5022" > $null 
}
Invoke-Command -ComputerName $Node -ScriptBlock $Script -Credential $cred

 

■マスターキーと着信接続で使用するユーザーの作成

証明書を作成するためにはマスターキーの作成が必要となります。

また、着信接続で使用する証明書を関連付けられたユーザーについても合わせて作成をします。

# 全ノードでマスターキーとエンドポイント用のユーザーを作成
$Node | %{Invoke-Sqlcmd -ServerInstance $_ -Query "CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MASTER KEY Passw0rd'"}
$Node | %{Invoke-Sqlcmd -ServerInstance $_ -Query "CREATE LOGIN AlwaysOnEndpoint WITH PASSWORD = 'AlwaysOn Passw0rd',CHECK_EXPIRATION=ON;CREATE USER AlwaysOnEndpoint FOR LOGIN AlwaysOnEndpoint"}

 

■エンドポイントの作成

次にエンドポイントの作成を行います。

最初にプライマリのノードでエンドポイントと証明書を作成し、証明書を各ノードで使用するためにファイルとしてバクアップを行います。


# 一台目でエンドポイントで使用する証明書を作成
Invoke-Sqlcmd -ServerInstance $Node[0] -Query "CREATE CERTIFICATE AlwaysOnEndpoint_Cert AUTHORIZATION AlwaysOnEndpoint WITH SUBJECT = 'AlwaysOn Endpoint',START_DATE = '01/01/2015',EXPIRY_DATE = '01/01/2100'"

# 証明書を使用したエンドポイントを作成
$endpoint =  New-SqlHadrEndpoint "AlwaysOnEndpoint" -Port 5022 -Path "SQLSERVER:\SQL\$($Node[0])\Default" -AuthenticationOrder Certificate -Certificate "AlwaysOnEndpoint_Cert"
Set-SqlHadrEndpoint -InputObject $endpoint -State "Started"
Invoke-Sqlcmd -ServerInstance $Node[0] -Query "GRANT CONNECT ON ENDPOINT::AlwaysOnEndpoint TO AlwaysOnEndpoint"

# 証明書をのバックアップを取得
New-Item -Path "C:\certtemp" -ItemType Directory
Invoke-Sqlcmd -ServerInstance $Node[0] -Query "BACKUP CERTIFICATE AlwaysOnEndpoint_Cert TO FILE = 'C:\certtemp\certbackup.cer' WITH PRIVATE KEY (FILE='C:\certtemp\certbackup.pvk', ENCRYPTION BY PASSWORD='Enc Passw0rd')"

 

これで、PowerShell を実行しているノードについてはエンドポイントの設定が終わりましたので、使用した証明書を使用して他のノードでもエンドポイントを作成します。

今回は WinRM で各ノードに証明書のファイルを配置したいので、ReadAllBytes で読み込んで、それを Invoke-Command で渡し、WriteAllBytes で書き出すことで各ノードにコピーしています。

証明書のファイルは数 KB の小さいものですので、この方法を使用しているのですが、WinRM 経由で簡単にファイルをコピーする方法はあったりするものなのでしょうか?

# 管理共有経由でのファイルコピーもできるかもしれませんが。


# 証明書をセカンダリーにコピー
$cer = [System.IO.File]::ReadAllBytes("C:\certtemp\certbackup.cer")
$pvk = [System.IO.File]::ReadAllBytes("C:\certtemp\certbackup.pvk")
$script = {
param($cer,$pvk)
New-Item -Path "C:\certtemp" -ItemType Directory > $null
[System.IO.File]::WriteAllBytes("C:\certtemp\certbackup.cer",$cer)
[System.IO.File]::WriteAllBytes("C:\certtemp\certbackup.pvk",$pvk)
}
Invoke-Command -ComputerName ($Node[1..($Node.Count -1)]) -ScriptBlock $Script -ArgumentList $cer,$pvk


# コピーした証明書を使用して 2 / 3 台目にエンドポイントを作成
$Node[1..($Node.Count -1)] | %{Invoke-Sqlcmd -ServerInstance $_ -Query "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')"}
Invoke-command -ComputerName ($Node[1..($Node.Count -1)]) -ScriptBlock {
$endpoint =  New-SqlHadrEndpoint "AlwaysOnEndpoint" -Port 5022 -Path "SQLSERVER:\SQL\$($ENV:COMPUTERNAME)\Default" -AuthenticationOrder Certificate -Certificate "AlwaysOnEndpoint_Cert"
Set-SqlHadrEndpoint -InputObject $endpoint -State "Started"}
$Node[1..($Node.Count -1)] | %{Invoke-Sqlcmd -ServerInstance $_ -Query "GRANT CONNECT ON ENDPOINT::AlwaysOnEndpoint TO AlwaysOnEndpoint"}

 

■可用性グループの作成

ここまでで、接続の下準備は終わりましたので、可用性グループを作成していきます。

最初に可用性グループを作成し、各ノードをレプリカとして登録します。


# 可用性グループの作成
$Suffix = "wsfc.local"
$AGName = "AG01"

$replica = (
$Node | %{New-SqlAvailabilityReplica `
    -Name "$_" `
    -EndpointURL "TCP://$($_).$($Suffix):5022" `
    -AvailabilityMode "SynchronousCommit" `
    -FailoverMode "Automatic" `
    -ConnectionModeInSecondaryRole AllowAllConnections `
    -Version 13 `
    -AsTemplate}
)

New-SqlAvailabilityGroup `
    -Name $AGName `
    -Path "SQLSERVER:\SQL\$($Node[0])\DEFAULT" `
    -AvailabilityReplica $replica `
    -DtcSupportEnabled `
    -DatabaseHealthTrigger

# セカンダリを可用性グループに追加
$Node[1..($Node.Count -1)] | %{Join-SqlAvailabilityGroup `
    -Path "SQLSERVER:\SQL\$($_)\Default" `
    -Name $AGName}

 

次にリスナーを作成します。

今回のリスナーは DHCP で IP を割り当てるリスナーとして作成をしています。

# リスナーの作成
$ListenerName = "SQL-LN"

New-SqlAvailabilityGroupListener -Name $ListenerName -Port 1433 -DhcpSubnet "10.0.0.0/255.0.0.0"`
-Path "SQLSERVER:\SQL\$($Node[0])\Default\AvailabilityGroups\$AGName"

ここまでで、可用性データベースが含まれていない可用性グループが作成できました。

最後に可用性データベースを追加していきたいと思います。

最初にデータベースを作成し、レプリカにリストアをします。

作成直後のデータベースはサイズが小さいのでここも、ReadAllBytes / WriteAllBytes で転送しています。

# データベースの作成
$Database = "AGDB01"

Invoke-Sqlcmd -ServerInstance $Node[0] -Query "CREATE DATABASE [$Database]"

# データベースのバックアップ
Invoke-Sqlcmd -ServerInstance $Node[0] -Query "BACKUP DATABASE [$Database] TO  DISK = N'$Database.bak' WITH FORMAT, INIT, COMPRESSION,  STATS = 10"
Invoke-Sqlcmd -ServerInstance $Node[0] -Query "BACKUP LOG [$Database] TO  DISK = N'$Database.bak' WITH NOFORMAT, NOINIT, COMPRESSION,  STATS = 10"

# バックアップをレプリカにリストア
$BackupDirectory = (Get-ItemProperty "registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer").BackupDirectory
$Backup = [System.IO.File]::ReadAllBytes((Join-path $BackupDirectory "$database.bak"))
$script = {
param($Backup,$Database)
$BackupDirectory = (Get-ItemProperty "registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer").BackupDirectory
[System.IO.File]::WriteAllBytes((Join-path $BackupDirectory "$database.bak"),$Backup)
Invoke-Sqlcmd -ServerInstance $ENV:COMPUTERNAME -Query "RESTORE DATABASE [$Database] FROM  DISK = N'$Database.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5"
Invoke-Sqlcmd -ServerInstance $ENV:COMPUTERNAME -Query "RESTORE LOG [$Database] FROM  DISK = N'$Database.bak' WITH  FILE = 2,  NORECOVERY,  NOUNLOAD,  STATS = 5"
}
Invoke-Command -ComputerName ($Node[1..($Node.Count -1)]) -ScriptBlock $Script -ArgumentList $Backup, $Database

これで可用性グループにデータベースを追加する準備ができましたので、最後の作業としてデータベースを追加します。

# 可用性データベースの登録
Add-SqlAvailabilityDatabase -Path "SQLSERVER:\SQL\$ENV:COMPUTERNAME\Default\AvailabilityGroups\$AGName" -Database "$Database"
$Node[1..($Node.Count -1)] | %{Invoke-Sqlcmd -ServerInstance $_ -Query "ALTER DATABASE [$Database] SET HADR AVAILABILITY GROUP = [$AGName]"}

 

以上で、可用性グループの登録は終わりです。

Standard Edition 向けの基本的な可用性グループを作成する場合は以下のような形になるかと。

# Basic の可用性グループの作成
# 可用性グループの作成
$BasicAGName = "AG02"
$BasicDatabase = "AGDB02"

$replica = (
$Node[0..1] | %{New-SqlAvailabilityReplica `
    -Name "$_" `
    -EndpointURL "TCP://$($_).$($Suffix):5022" `
    -AvailabilityMode "SynchronousCommit" `
    -FailoverMode "Automatic" `
    -Version 13 `
    -AsTemplate}
)


New-SqlAvailabilityGroup `
    -Name $BasicAGName `
    -Path "SQLSERVER:\SQL\$($Node[0])\DEFAULT" `
    -BasicAvailabilityGroup `
    -AvailabilityReplica $replica `
    -DtcSupportEnabled `
    -DatabaseHealthTrigger 

# セカンダリを可用性グループに追加
Join-SqlAvailabilityGroup `
    -Path "SQLSERVER:\SQL\$($Node[1])\Default" `
    -Name $BasicAGName


# データベースの作成
Invoke-Sqlcmd -ServerInstance $Node[0] -Query "CREATE DATABASE [$BasicDatabase]"

# データベースのバックアップ
Invoke-Sqlcmd -ServerInstance $Node[0] -Query "BACKUP DATABASE [$BasicDatabase] TO  DISK = N'$BasicDatabase.bak' WITH FORMAT, INIT, COMPRESSION,  STATS = 10"
Invoke-Sqlcmd -ServerInstance $Node[0] -Query "BACKUP LOG [$BasicDatabase] TO  DISK = N'$BasicDatabase.bak' WITH NOFORMAT, NOINIT, COMPRESSION,  STATS = 10"

# バックアップをレプリカにリストア
$BackupDirectory = (Get-ItemProperty "registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer").BackupDirectory
$Backup = [System.IO.File]::ReadAllBytes((Join-path $BackupDirectory "$BasicDatabase.bak"))
$script = {
param($Backup,$BasicDatabase)
$BackupDirectory = (Get-ItemProperty "registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer").BackupDirectory
[System.IO.File]::WriteAllBytes((Join-path $BackupDirectory "$BasicDatabase.bak"),$Backup)
Invoke-Sqlcmd -ServerInstance $ENV:COMPUTERNAME -Query "RESTORE DATABASE [$BasicDatabase] FROM  DISK = N'$BasicDatabase.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5"
Invoke-Sqlcmd -ServerInstance $ENV:COMPUTERNAME -Query "RESTORE LOG [$BasicDatabase] FROM  DISK = N'$BasicDatabase.bak' WITH  FILE = 2,  NORECOVERY,  NOUNLOAD,  STATS = 5"
}
Invoke-Command -ComputerName $Node[1] -ScriptBlock $Script -ArgumentList $Backup, $BasicDatabase

Add-SqlAvailabilityDatabase -Path "SQLSERVER:\SQL\$ENV:COMPUTERNAME\Default\AvailabilityGroups\$BasicAGName" -Database "$BasicDatabase"
Invoke-Sqlcmd -ServerInstance $Node[1] -Query "ALTER DATABASE [$BasicDatabase] SET HADR AVAILABILITY GROUP = [$BasicAGName]"

 

CTP のバージョンが上がるたびに環境を作っていると、スクリプトベースで構築してしまわないと検証が億劫になってしまいますね。

 

 

今回のスクリプトの全体がこちらになります。

# 一台目のノードでスクリプトを実行し、可用性グループを作成する
$cred = Get-Credential
$Node = @("2016-WSFC-01", "2016-WSFC-02", "2016-WSFC-03")
$Suffix = "wsfc.local"

$AGName = "AG01"
$Database = "AGDB01"
$ListenerName = "SQL-LN"

$BasicAGName = "AG02"
$BasicDatabase = "AGDB02"


# AlwaysOn の有効化と必要となる F/W の設定
$Script = {
$ENV:PSModulePath = [System.Environment]::GetEnvironmentVariable("PSModulePath","Machine") 
Import-Module SQLPS -DisableNameChecking 
 
Enable-SQLAlwaysOn -ServerInstance $ENV:COMPUTERNAME -Force 
New-NetFirewallRule -Name "SQL Server" -DisplayName "SQL Server" -Protocol "TCP" -LocalPort "1433" > $null 
New-NetFirewallRule -Name "SQL Server AlwaysOn Endpoint" -DisplayName "SQL Server" -Protocol "TCP" -LocalPort "5022" > $null 
}
Invoke-Command -ComputerName $Node -ScriptBlock $Script -Credential $cred


# 全ノードでマスターキーとエンドポイント用のユーザーを作成
$Node | %{Invoke-Sqlcmd -ServerInstance $_ -Query "CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MASTER KEY Passw0rd'"}
$Node | %{Invoke-Sqlcmd -ServerInstance $_ -Query "CREATE LOGIN AlwaysOnEndpoint WITH PASSWORD = 'AlwaysOn Passw0rd',CHECK_EXPIRATION=ON;CREATE USER AlwaysOnEndpoint FOR LOGIN AlwaysOnEndpoint"}

# 一台目でエンドポイントで使用する証明書を作成
Invoke-Sqlcmd -ServerInstance $Node[0] -Query "CREATE CERTIFICATE AlwaysOnEndpoint_Cert AUTHORIZATION AlwaysOnEndpoint WITH SUBJECT = 'AlwaysOn Endpoint',START_DATE = '01/01/2015',EXPIRY_DATE = '01/01/2100'"

# 証明書を使用したエンドポイントを作成
$endpoint =  New-SqlHadrEndpoint "AlwaysOnEndpoint" -Port 5022 -Path "SQLSERVER:\SQL\$($Node[0])\Default" -AuthenticationOrder Certificate -Certificate "AlwaysOnEndpoint_Cert"
Set-SqlHadrEndpoint -InputObject $endpoint -State "Started"
Invoke-Sqlcmd -ServerInstance $Node[0] -Query "GRANT CONNECT ON ENDPOINT::AlwaysOnEndpoint TO AlwaysOnEndpoint"

# 証明書をのバックアップを取得
New-Item -Path "C:\certtemp" -ItemType Directory
Invoke-Sqlcmd -ServerInstance $Node[0] -Query "BACKUP CERTIFICATE AlwaysOnEndpoint_Cert TO FILE = 'C:\certtemp\certbackup.cer' WITH PRIVATE KEY (FILE='C:\certtemp\certbackup.pvk', ENCRYPTION BY PASSWORD='Enc Passw0rd')"


# 証明書をセカンダリーにコピー
$cer = [System.IO.File]::ReadAllBytes("C:\certtemp\certbackup.cer")
$pvk = [System.IO.File]::ReadAllBytes("C:\certtemp\certbackup.pvk")
$script = {
param($cer,$pvk)
New-Item -Path "C:\certtemp" -ItemType Directory > $null
[System.IO.File]::WriteAllBytes("C:\certtemp\certbackup.cer",$cer)
[System.IO.File]::WriteAllBytes("C:\certtemp\certbackup.pvk",$pvk)
}
Invoke-Command -ComputerName ($Node[1..($Node.Count -1)]) -ScriptBlock $Script -ArgumentList $cer,$pvk


# コピーした証明書を使用して 2 / 3 台目にエンドポイントを作成
$Node[1..($Node.Count -1)] | %{Invoke-Sqlcmd -ServerInstance $_ -Query "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')"}
Invoke-command -ComputerName ($Node[1..($Node.Count -1)]) -ScriptBlock {
$endpoint =  New-SqlHadrEndpoint "AlwaysOnEndpoint" -Port 5022 -Path "SQLSERVER:\SQL\$($ENV:COMPUTERNAME)\Default" -AuthenticationOrder Certificate -Certificate "AlwaysOnEndpoint_Cert"
Set-SqlHadrEndpoint -InputObject $endpoint -State "Started"}
$Node[1..($Node.Count -1)] | %{Invoke-Sqlcmd -ServerInstance $_ -Query "GRANT CONNECT ON ENDPOINT::AlwaysOnEndpoint TO AlwaysOnEndpoint"}


# 可用性グループの作成
$replica = (
$Node | %{New-SqlAvailabilityReplica `
    -Name "$_" `
    -EndpointURL "TCP://$($_).$($Suffix):5022" `
    -AvailabilityMode "SynchronousCommit" `
    -FailoverMode "Automatic" `
    -ConnectionModeInSecondaryRole AllowAllConnections `
    -Version 13 `
    -AsTemplate}
)

New-SqlAvailabilityGroup `
    -Name $AGName `
    -Path "SQLSERVER:\SQL\$($Node[0])\DEFAULT" `
    -AvailabilityReplica $replica `
    -DtcSupportEnabled `
    -DatabaseHealthTrigger

# セカンダリを可用性グループに追加
$Node[1..($Node.Count -1)] | %{Join-SqlAvailabilityGroup `
    -Path "SQLSERVER:\SQL\$($_)\Default" `
    -Name $AGName}

# リスナーの作成
New-SqlAvailabilityGroupListener -Name $ListenerName -Port 1433 -DhcpSubnet "10.0.0.0/255.0.0.0"`
-Path "SQLSERVER:\SQL\$($Node[0])\Default\AvailabilityGroups\$AGName"


# データベースの作成
Invoke-Sqlcmd -ServerInstance $Node[0] -Query "CREATE DATABASE [$Database]"

# データベースのバックアップ
Invoke-Sqlcmd -ServerInstance $Node[0] -Query "BACKUP DATABASE [$Database] TO  DISK = N'$Database.bak' WITH FORMAT, INIT, COMPRESSION,  STATS = 10"
Invoke-Sqlcmd -ServerInstance $Node[0] -Query "BACKUP LOG [$Database] TO  DISK = N'$Database.bak' WITH NOFORMAT, NOINIT, COMPRESSION,  STATS = 10"

# バックアップをレプリカにリストア
$BackupDirectory = (Get-ItemProperty "registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer").BackupDirectory
$Backup = [System.IO.File]::ReadAllBytes((Join-path $BackupDirectory "$database.bak"))
$script = {
param($Backup,$Database)
$BackupDirectory = (Get-ItemProperty "registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer").BackupDirectory
[System.IO.File]::WriteAllBytes((Join-path $BackupDirectory "$database.bak"),$Backup)
Invoke-Sqlcmd -ServerInstance $ENV:COMPUTERNAME -Query "RESTORE DATABASE [$Database] FROM  DISK = N'$Database.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5"
Invoke-Sqlcmd -ServerInstance $ENV:COMPUTERNAME -Query "RESTORE LOG [$Database] FROM  DISK = N'$Database.bak' WITH  FILE = 2,  NORECOVERY,  NOUNLOAD,  STATS = 5"
}
Invoke-Command -ComputerName ($Node[1..($Node.Count -1)]) -ScriptBlock $Script -ArgumentList $Backup, $Database


Add-SqlAvailabilityDatabase -Path "SQLSERVER:\SQL\$ENV:COMPUTERNAME\Default\AvailabilityGroups\$AGName" -Database "$Database"
$Node[1..($Node.Count -1)] | %{Invoke-Sqlcmd -ServerInstance $_ -Query "ALTER DATABASE [$Database] SET HADR AVAILABILITY GROUP = [$AGName]"}


# Basic の可用性グループの作成
# 可用性グループの作成
$replica = (
$Node[0..1] | %{New-SqlAvailabilityReplica `
    -Name "$_" `
    -EndpointURL "TCP://$($_).$($Suffix):5022" `
    -AvailabilityMode "SynchronousCommit" `
    -FailoverMode "Automatic" `
    -Version 13 `
    -AsTemplate}
)


New-SqlAvailabilityGroup `
    -Name $BasicAGName `
    -Path "SQLSERVER:\SQL\$($Node[0])\DEFAULT" `
    -BasicAvailabilityGroup `
    -AvailabilityReplica $replica `
    -DtcSupportEnabled `
    -DatabaseHealthTrigger 

# セカンダリを可用性グループに追加
Join-SqlAvailabilityGroup `
    -Path "SQLSERVER:\SQL\$($Node[1])\Default" `
    -Name $BasicAGName


# データベースの作成
Invoke-Sqlcmd -ServerInstance $Node[0] -Query "CREATE DATABASE [$BasicDatabase]"

# データベースのバックアップ
Invoke-Sqlcmd -ServerInstance $Node[0] -Query "BACKUP DATABASE [$BasicDatabase] TO  DISK = N'$BasicDatabase.bak' WITH FORMAT, INIT, COMPRESSION,  STATS = 10"
Invoke-Sqlcmd -ServerInstance $Node[0] -Query "BACKUP LOG [$BasicDatabase] TO  DISK = N'$BasicDatabase.bak' WITH NOFORMAT, NOINIT, COMPRESSION,  STATS = 10"

# バックアップをレプリカにリストア
$BackupDirectory = (Get-ItemProperty "registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer").BackupDirectory
$Backup = [System.IO.File]::ReadAllBytes((Join-path $BackupDirectory "$BasicDatabase.bak"))
$script = {
param($Backup,$BasicDatabase)
$BackupDirectory = (Get-ItemProperty "registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer").BackupDirectory
[System.IO.File]::WriteAllBytes((Join-path $BackupDirectory "$BasicDatabase.bak"),$Backup)
Invoke-Sqlcmd -ServerInstance $ENV:COMPUTERNAME -Query "RESTORE DATABASE [$BasicDatabase] FROM  DISK = N'$BasicDatabase.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5"
Invoke-Sqlcmd -ServerInstance $ENV:COMPUTERNAME -Query "RESTORE LOG [$BasicDatabase] FROM  DISK = N'$BasicDatabase.bak' WITH  FILE = 2,  NORECOVERY,  NOUNLOAD,  STATS = 5"
}
Invoke-Command -ComputerName $Node[1] -ScriptBlock $Script -ArgumentList $Backup, $BasicDatabase

Add-SqlAvailabilityDatabase -Path "SQLSERVER:\SQL\$ENV:COMPUTERNAME\Default\AvailabilityGroups\$BasicAGName" -Database "$BasicDatabase"
Invoke-Sqlcmd -ServerInstance $Node[1] -Query "ALTER DATABASE [$BasicDatabase] SET HADR AVAILABILITY GROUP = [$BasicAGName]"

<#
# エンドポイント等を初期化する場合に実行
$InitializeSQL = @"
DROP ENDPOINT AlwaysOnEndpoint
GO
DROP CERTIFICATE AlwaysOnEndpoint_Cert
GO
DROP User AlwaysOnEndpoint
GO
DROP LOGIN AlwaysOnEndpoint
DROP MASTER KEY
GO
"@
$Node | %{Invoke-Sqlcmd -ServerInstance $_ -Query $InitializeSQL}
#>

Written by masayuki.ozawa

9月 20th, 2015 at 12:25 am

Posted in SQL Server

Tagged with ,

Leave a Reply

*