Environment: Virtual Machine Windows Server 2016 (powershell 5.1) + SQL Server 2016 CTP3
Basically using Powershell script configuration. Here, when creating certificates and endpoints, we use SQL commands. In fact, PS has related commands to complete. This script is just a personal whim to try to script the way to configure AG, are the basic operation commands, commands before and after the execution of no logical judgment and inspection. If it is more complete, it can be made into automation or one-click configuration and so on.
===================================================================================
//Network and Security Configuration
===================================================================================
#Change the server name and restart
$ServerName="Server134"
Rename-Computer -NewName $ServerName -restart -force
#Setting 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 "Ethernet").Device)"
$net.EnableStatic($IPAddr,$SubMask) | Out-Null
$net.SetGateways($GateWay) | Out-Null
$net.SetDNSServerSearchOrder($DNSArray) | Out-Null
#Setting 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="Ethernet" source=static addr=$IPAddr mask=$SubMask gateway=$GateWay
netsh interface ipv4 set dns name="Ethernet" source=static addr=$DNS1 register=PRIMARY
netsh interface ipv4 add dns name="Ethernet" addr=$DNS2
#Restart NIC
#Get-NetAdapter-Name "Ethernet"| Restart-NetAdapter
#Close the firewall
#Set-NetFirewallProfile -Profile Private/Public/Domain -Enabled True/False
Set-NetFirewallProfile -Profile Public -Enabled False
Set-NetFirewallProfile -Profile Private -Enabled False
#Back up and modify host files of hosts
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"
# [Another way] But all the additional characters executed in the powershell are blank ~, so the cmd environment is called to execute (or the cmd command can be executed directly)
# 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
#Create a Windows Unified Account and add it to the Administrator Group (e.g. Added User: 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")
#Create a Windows Unified Account [Another Way]
$UserName="admin"
$Password="kk@11397QWER"
$Group="Administrators"
New-LocalUser -Name $UserName -Password (ConvertTo-SecureString -AsPlainText $Password -Force) -AccountNeverExpires
Add-LocalGroupMember -Group $Group -Member $UserName
#Disable UAC remote restrictions (restart later)
$Value=1
$PKeyPath="HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System"
New-ItemProperty -Path $PKeyPath -Name LocalAccountTokenFilterPolicy -Value $Value
#Change the DNS suffix name of the host (restart)
$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 cluster configuration
===================================================================================
# Log on to the server as admin and run the powershell as an administrator
#Install Fault Transfer Cluster Manager
#Import-Module ServerManager
Install-WindowsFeature –Name Failover-Clustering –IncludeManagementTools
#Install. NET Framework 3.5 (E disk is the system image file ISO)
Install-WindowsFeature -Name NET-Framework-Core -Source E:\source\sxs
#Test Cluster (pre-configuration testing)
$Nodes="server133.kk.com,server134.kk.com"
Test-Cluster -Node $Nodes
#Create a cluster at one of the nodes (name: mycluster; address: 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 Instance Installation Configuration
===================================================================================
#Each node installs the database independently, refers to the silent installation, and sets the template in advance.
#Enable "SQL Server and Windows Authentication Mode" (1:Windows Mode; 2: Mixed Mode)
$Value=2
$name="LoginMode"
$Instance="MSSQLSERVER"
$KeyPath="HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL*$Instance\MSSQLServer"
Set-ItemProperty -Path $KeyPath -Name $name -Value $Value
#Configure port and service account (configure first when installing)
[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 To configure
===================================================================================
#Enable the Always On AG function (you need to restart the instance service, refer to the script above)
Import-Module SQLPS
Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\LocalHost\Default -Force
#Each node creates the master key, certificate, endpoint and user (note that the certificate name of each node is different, it is convenient to distinguish only)
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"
#When the certificate backup of each node is completed, each node restores the certificates of other nodes.
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"
#Host node: complete mode database, complete backup and log backup
$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
#Auxiliary node: restore database and restore state
$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
=====================================
#The following are executed on the primary node database server
=====================================
#Create availability replicas
$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
#Create usability groups
New-SqlAvailabilityGroup `
-Name $ag `
-Path "SQLSERVER:\SQL\$s1\Default" `
-AvailabilityReplica @($PrimaryReplica,$SecondaryReplica) `
-Database $db
#Adding Auxiliary Copies
Join-SqlAvailabilityGroup `
-Path "SQLSERVER:\SQL\$s2\Default" `
-Name $ag
#Add a replica database
Add-SqlAvailabilityDatabase `
-Path "SQLSERVER:\SQL\$s2\Default\AvailabilityGroups\$ag" `
-Database $db
=====================================
//Configuration is complete! No screenshots.
Reference resources:
SQL Server Silent Installation [KK - Focused Data]
SQL Server 2016 Domain-free Cluster Configuration AlwaysON Availability Group [KK - Focused Data]
Use PowerShell
Configuring Always On availability groups in Azure VM
Easy Implementation of Domain-free SQL Server AlwaysOn Availability Group on Aliyun ECS