SQL Server 2016 CTP 2.3 AlwaysOn インストール用ベーススクリプト.ps1

$cred = Get-Credential
$Node = @("2016-WSFC-01", "2016-WSFC-02", "2016-WSFC-03")

$script = {
$SQLSetup = "D:\setup.exe"
$InstallConfig = @'
;SQL Server 2016 CTP2.3 Configuration File
[OPTIONS]

; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter. 
ACTION="Install"

;license
IAcceptSQLServerLicenseTerms="True"

; Use the /ENU parameter to install the English version of SQL Server on your localized Windows operating system. 
ENU="False"

; Parameter that controls the user interface behavior. Valid values are Normal for the full UI,AutoAdvance for a simplied UI, and EnableUIOnServerCore for bypassing Server Core setup GUI block. 
;UIMODE="Normal"

; Setup will not display any user interface. 
QUIET="True"

; Setup will display progress only, without any user interaction. 
QUIETSIMPLE="False"

; Specify whether SQL Server Setup should discover and include product updates. The valid values are True and False or 1 and 0. By default SQL Server Setup will include updates that are found. 
;UpdateEnabled="True"
UpdateEnabled="False"

; Specify if errors can be reported to Microsoft to improve future SQL Server releases. Specify 1 or True to enable and 0 or False to disable this feature. 
ERRORREPORTING="True"

; If this parameter is provided, then this computer will use Microsoft Update to check for updates. 
USEMICROSOFTUPDATE="False"

; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, MDS, and Tools. The SQL feature will install the Database Engine, Replication, Full-Text, and Data Quality Services (DQS) server. The Tools feature will install Management Tools, Books online components, SQL Server Data Tools, and other shared components. 
FEATURES=SQLENGINE,REPLICATION,FULLTEXT,SSMS,ADV_SSMS

; Specify the location where SQL Server Setup will obtain product updates. The valid values are "MU" to search Microsoft Update, a valid folder path, a relative path such as .\MyUpdates or a UNC share. By default SQL Server Setup will search Microsoft Update or a Windows Update service through the Window Server Update Services. 
UpdateSource="MU"

; Displays the command line parameters usage 
HELP="False"

; Specifies that the detailed Setup log should be piped to the console. 
INDICATEPROGRESS="False"

; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system. 
X86="False"

; Specify the root installation directory for shared components.  This directory remains unchanged after shared components are already installed. 
INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"

; Specify the root installation directory for the WOW64 shared components.  This directory remains unchanged after WOW64 shared components are already installed. 
INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"

; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions. This parameter is required when installing the SQL Server Database Engine (SQL), Analysis Services (AS), or Reporting Services (RS). 
INSTANCENAME="MSSQLSERVER"

; Specify that SQL Server feature usage data can be collected and sent to Microsoft. Specify 1 or True to enable and 0 or False to disable this feature. 
SQMREPORTING="True"

; Specify the Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will incorporate the instance ID of the SQL Server instance. 
INSTANCEID="MSSQLSERVER"

; Specify the installation directory. 
INSTANCEDIR="C:\Program Files\Microsoft SQL Server"

; Agent account name 
AGTSVCACCOUNT="NT Service\SQLSERVERAGENT"

; Auto-start service after installation.  
AGTSVCSTARTUPTYPE="Manual"

; CM brick TCP communication port 
COMMFABRICPORT="0"

; How matrix will use private networks 
COMMFABRICNETWORKLEVEL="0"

; How inter brick communication will be protected 
COMMFABRICENCRYPTION="0"

; TCP port used by the CM brick 
MATRIXCMBRICKCOMMPORT="0"

; Startup type for the SQL Server service. 
SQLSVCSTARTUPTYPE="Automatic"

; Level to enable FILESTREAM feature at (0, 1, 2 or 3). 
FILESTREAMLEVEL="0"

; Set to "1" to enable RANU for SQL Server Express. 
ENABLERANU="False"

; Specifies a Windows collation or an SQL collation to use for the Database Engine. 
SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"

; Account for SQL Server service: Domain\User or system account. 
SQLSVCACCOUNT="NT Service\MSSQLSERVER"

; Windows account(s) to provision as SQL Server system administrators. 
SQLSYSADMINACCOUNTS="BUILTIN\Administrators"

; The number of Database Engine TempDB files. 
SQLTEMPDBFILECOUNT="8"

; Specifies the initial size of a Database Engine TempDB data file in MB. 
SQLTEMPDBFILESIZE="100"

; Specifies the automatic growth increment of each Database Engine TempDB data file in MB. 
SQLTEMPDBFILEGROWTH="100"

; Specifies the initial size of the Database Engine TempDB log file in MB. 
SQLTEMPDBLOGFILESIZE="100"

; Specifies the automatic growth increment of the Database Engine TempDB log file in MB. 
SQLTEMPDBLOGFILEGROWTH="100"

; Provision current user as a Database Engine system administrator for %SQL_PRODUCT_SHORT_NAME% Express. 
ADDCURRENTUSERASSQLADMIN="False"

; Specify 0 to disable or 1 to enable the TCP/IP protocol. 
TCPENABLED="1"

; Specify 0 to disable or 1 to enable the Named Pipes protocol. 
NPENABLED="0"

; Startup type for Browser Service. 
BROWSERSVCSTARTUPTYPE="Disabled"

; Add description of input argument FTSVCACCOUNT 
FTSVCACCOUNT="NT Service\MSSQLFDLauncher"
'@

Install-WindowsFeature -Name NET-Framework-Core

$ConfigFile = [System.IO.Path]::GetTempFileName()
Set-Content -Path $ConfigFile -Value $InstallConfig

Invoke-Expression "$SQLSetup /CONFIGURATIONFILE=$ConfigFile"
}

Invoke-Command -ComputerName $Node -ScriptBlock $Script -Credential $cred
# Invoke-Command -ComputerName $Node -ScriptBlock {Restart-Computer -Force} -Credential $cred

# 一台目のノードでスクリプトを実行し、可用性グループを作成する
$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)
}
$Node[1..($Node.Count -1)] | %{Invoke-Command -ComputerName $_ -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')"}
$Node[1..($Node.Count -1)] | %{Invoke-command -ComputerName $_ -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"
}
$Node[1..($Node.Count -1)] | %{Invoke-Command -ComputerName $_ -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}
#>