Powershell 5.1 脚本化配置 Windows 2016 & SQL Server 2016 无域 AlwaysOn AG

环境： 虚拟机 Windows Server 2016（powershell 5.1）+ SQL Server 2016 CTP3


基本使用 Powershell 脚本配置完成的。这里在创建证书和端点时用的是SQL命令，其实PS有相关命令可以完成。此脚本配置AG，都是基本的操作命令，命令执行前后都没有逻辑判断和检查。如果完整一些，是可以做成自动化或一键配置之类的。

===================================================================================
网络及安全配置
===================================================================================

#更改服务器名称并重启
$ServerName="Server134"
Rename-Computer -NewName $ServerName -restart -force


#设置 IP & DNS
$IPAddr="192.168.2.134"
$SubMask="255.255.255.0"
$GateWay=""
$DNSArray=@("192.168.2.2")
$net = Get-WMIObject win32_networkadapterconfiguration -filter "index=$((Get-NetAdapterHardwareInfo -Name "以太网").Device)"
$net.EnableStatic($IPAddr,$SubMask) | Out-Null
$net.SetGateways($GateWay) | Out-Null
$net.SetDNSServerSearchOrder($DNSArray) | Out-Null

#设置 IP & DNS【另一方法】
$IPAddr="192.168.2.134"
$SubMask="255.255.255.0"
$GateWay=""
$DNS1="192.168.2.2"
$DNS2=""
netsh interface ipv4 set address name="以太网" source=static addr=$IPAddr mask=$SubMask gateway=$GateWay
netsh interface ipv4 set dns name="以太网" source=static addr=$DNS1 register=PRIMARY
netsh interface ipv4 add dns name="以太网" addr=$DNS2

#重启网卡
#Get-NetAdapter -Name "以太网" | Restart-NetAdapter


#关闭防火墙
#Set-NetFirewallProfile -Profile Private/Public/Domain -Enabled True/False
Set-NetFirewallProfile -Profile Public -Enabled False
Set-NetFirewallProfile -Profile Private -Enabled False


#备份并修改主机的host文件
Copy-Item C:\Windows\System32\drivers\etc\hosts C:\Windows\System32\drivers\etc\hosts_bak
CMD /C "echo. >> C:\Windows\System32\drivers\etc\hosts"
CMD /C "echo 192.168.2.133 server133.kk.com >> C:\Windows\System32\drivers\etc\hosts"
CMD /C "echo 192.168.2.134 server134.kk.com >> C:\Windows\System32\drivers\etc\hosts"

# 【另一方法】但在powershell中执行追加的字符都加了空格~~所以上面调用cmd环境来执行（也可直接执行cmd命令）
# Copy-Item C:\Windows\System32\drivers\etc\hosts C:\Windows\System32\drivers\etc\hosts_bak
# "192.168.2.133 server133.kk.com" | Out-File -Append C:\Windows\System32\drivers\etc\hosts
# "192.168.2.134 server134.kk.com" | Out-File -Append C:\Windows\System32\drivers\etc\hosts


#创建Windows统一账号，并添加到管理员组（如新增用户：admin）
$UserName="admin"
$Password="kk@11397QWER"
$hostname=hostname
$ObjOu=[ADSI]"WinNT://$hostname"
$ObjUser=$ObjOu.Create("User",$UserName)
$ObjUser.SetPassword($Password)
$ObjUser.SetInfo()

$ObjGrp=[ADSI]"WinNT://$hostname/administrators,group"
$ObjGrp.Add("WinNT://$hostname/$UserName")

#创建Windows统一账号 【另一方法】
$UserName="admin"
$Password="kk@11397QWER"
$Group="Administrators"
New-LocalUser -Name $UserName -Password (ConvertTo-SecureString -AsPlainText $Password -Force) -AccountNeverExpires
Add-LocalGroupMember -Group $Group -Member $UserName


#禁用 UAC 远程限制（稍后重启）
$Value=1
$PKeyPath="HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System"
New-ItemProperty -Path $PKeyPath -Name LocalAccountTokenFilterPolicy -Value $Value


#更改主机的DNS后缀名称（重启）
$DnsSuffix="kk.com"
$name="NV Domain" 
$PKeyPath="HKLM:\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters"
New-ItemProperty -Path $PKeyPath -Name $name -Value $DnsSuffix -PropertyType String

Restart-Computer -Force

===================================================================================
Windows 群集配置
===================================================================================

# 以 admin 登录服务器，以管理员身份运行 powershell


#安装 "故障转移群集管理器"
#Import-Module ServerManager
Install-WindowsFeature –Name Failover-Clustering –IncludeManagementTools

#安装 .NET Framework 3.5 (E盘为系统镜像文件ISO)
Install-WindowsFeature -Name NET-Framework-Core -Source E:\source\sxs


#测试群集（配置前的测试）
$Nodes="server133.kk.com,server134.kk.com"
Test-Cluster -Node $Nodes


#在其中一节点创建群集（名称：mycluster ；地址：192.168.2.199）
$ClusterName="mycluster"
$StaticAddress="192.168.2.199"
$Nodes=@("server133.kk.com","server134.kk.com")
$IgnoreNetwork=@("10.10.10.0/24")
New-Cluster –Name mycluster -Node $Nodes -AdministrativeAccessPoint DNS -NoStorage -StaticAddress $StaticAddress -IgnoreNetwork $IgnoreNetwork


===================================================================================
SQL Server 实例安装配置
===================================================================================

#各节点独立安装数据库,参考静默安装，提前设置好模板（此处数据库安装略……）


#启用 "SQL Server 和 Windows 身份验证模式" (1:Windows模式; 2:混合模式)
$Value=2
$name="LoginMode"
$Instance="MSSQLSERVER"
$KeyPath="HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL*$Instance\MSSQLServer"
Set-ItemProperty -Path $KeyPath -Name $name -Value $Value


#配置端口、服务账号（在安装时可先配置好）
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
$MachinObj=New-Object("Microsoft.SqlServer.Management.SMO.WMI.ManagedComputer")

$SqlInsObj=$MachinObj.ServerInstances["MSSQLSERVER"]
$p=$SqlInsObj.ServerProtocols["Tcp"]
$ip=$p.IPAddresses["IPAll"]
$ip.IPAddressProperties["TcpDynamicPorts"].value=""
$ip.IPAddressProperties["TcpPort"].value="1433"
$p.Alter()

$SqlSrvObj=$MachinObj.Services["MSSQLSERVER"]
$SqlAgtObj=$MachinObj.Services["SQLSERVERAGENT"]
$SqlSrvObj.SetServiceAccount("server133\admin","kk@11397QWER")
$SqlAgtObj.SetServiceAccount("server133\admin","kk@11397QWER")
$SqlSrvObj.Alter()
$SqlAgtObj.Alter()
Restart-Service -Name $SqlSrvObj.Name -Force
[System.Threading.Thread]::sleep(10000) #start-sleep -s 10
start-Service -Name $SqlAgtObj.Name


===================================================================================
SQL Server AG 配置
===================================================================================

#启用 AlwaysOn AG 功能(需要重启实例服务，参考上面的脚本)
Import-Module SQLPS
Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\LocalHost\Default -Force


#各节点创建主密钥、证书、端点、用户（注意各节点的证书名称不一样，方便区分而已）
Invoke-Sqlcmd "
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'kk@11397QWER'
GO
CREATE CERTIFICATE cer_MyAlwaysAG_01 WITH SUBJECT='cer_MyAlwaysAG_01',EXPIRY_DATE='9999-12-31'
GO
BACKUP CERTIFICATE cer_MyAlwaysAG_01 TO FILE='\\HZC\share\SQL2016\cer_MyAlwaysAG_01.cer'
GO
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE cer_MyAlwaysAG_01, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL)
GO
CREATE LOGIN aguser WITH PASSWORD='aguser',CHECK_POLICY=OFF
GO
CREATE USER aguser FOR LOGIN aguser
GO"


#各节点证书备份完成后，此时每个节点都还原其他节点的证书
Invoke-Sqlcmd "
USE master
GO
CREATE CERTIFICATE cer_MyAlwaysAG_02 AUTHORIZATION aguser FROM FILE='\\HZC\share\SQL2016\cer_MyAlwaysAG_02.cer'
GO
GRANT CONNECT ON ENDPOINT:: Endpoint_Mirroring TO aguser
GO"


#主节点：完整模式数据库，进行一次完整备份和日志备份
$db="DemoDB"
$ins="server133"
$bf="\\HZC\share\SQL2016"
Invoke-Sqlcmd -Query "CREATE DATABASE $db"
Backup-SqlDatabase -Database $db -BackupFile "$($bf)\$($db)_Full.bak" -ServerInstance $ins
Backup-SqlDatabase -Database $db -BackupFile "$($bf)\$($db)_Log.bak"  -ServerInstance $ins -BackupAction Log

#辅节点：还原数据库并处于还原状态
$db="DemoDB"
$ins="server134"
$bf="\\HZC\share\SQL2016"
Restore-SqlDatabase -Database $db -BackupFile "$($bf)\$($db)_Full.bak" -ServerInstance $ins -NoRecovery
Restore-SqlDatabase -Database $db -BackupFile "$($bf)\$($db)_Log.bak"  -ServerInstance $ins -RestoreAction Log -NoRecovery


=====================================
#以下都在主节点数据库服务器执行
=====================================
#创建可用性副本 
$s1="server133"
$s2="server134"
$db="DemoDB"
$ag="MyAlwaysOnAG"

$PrimaryReplica= New-SqlAvailabilityReplica `
	-Name $s1 `
	-EndpointURL "TCP://$s1.kk.com:5022" `
	-AvailabilityMode "SynchronousCommit" `
	-FailoverMode "Automatic" `
	-Version 11 `
	-AsTemplate

$SecondaryReplica= New-SqlAvailabilityReplica `
	-Name $s2 `
	-EndpointURL "TCP://$s2.kk.com:5022" `
	-AvailabilityMode "SynchronousCommit" `
	-FailoverMode "Automatic" `
	-Version 11 `
	-AsTemplate

#创建可用性组
New-SqlAvailabilityGroup `
	-Name $ag `
	-Path "SQLSERVER:\SQL\$s1\Default" `
	-AvailabilityReplica @($PrimaryReplica,$SecondaryReplica) `
	-Database $db
	
#将辅助副本加入
Join-SqlAvailabilityGroup `
	-Path "SQLSERVER:\SQL\$s2\Default" `
	-Name $ag 

#将副本数据库加入
Add-SqlAvailabilityDatabase `
	-Path "SQLSERVER:\SQL\$s2\Default\AvailabilityGroups\$ag" `
	-Database $db

=====================================
配置完成！没有截图。

参考：

SQL Server 静默安装   【KK ——专注数据】

SQL Server 2016 无域群集配置 AlwaysON 可用性组  【KK ——专注数据】

使用 PowerShell 在 Azure VM 中配置 Always On 可用性组

在阿里云ECS上轻松实现无域控的SQL Server AlwaysOn可用性组