Setup SQL 2017 Always On with PowerShell

Tutorial on how to Setup SQL 2017 Always On with PowerShell

Components used in this lab

Create a new Domain User Account called SqlAdmin as Service Account for SQL in AD Domain Controller

#SQL Account 
Import-Module ActiveDirectory 
$SQLAdmin = "sqladmin"
$UPN = "[email protected]"
$Password = "P@ssw0rd"

#Normal Domain User Account will do 
New-ADUser -Name $SQLAdmin -UserPrincipalName $UPN -AccountPassword(ConvertTo-SecureString $Password –asplaintext –force) `
-PassThru | Enable-ADAccount

Assigned a new 30GB Hard Disk from VMware and format it with NTFS partition with Drive Letter S on both SQL Servers as Database & Logs Volume

#Initialize of New Disk 
$NewDiskID = (Get-Disk | Where-Object IsOffline –Eq $True).Number
$NewDriveLetter = "S"

Set-Disk -Number $NewDiskID -IsOffline $false
Set-Disk -Number $NewDiskID -IsReadOnly $false

#initialize New Disk
Initialize-Disk -Number $NewDiskID -PartitionStyle GPT 

#New Partition
New-Partition –DiskNumber $NewDiskID -DriveLetter $NewDriveLetter -UseMaximumSize

#Format Volume
Format-Volume -DriveLetter $NewDriveLetter -FileSystem NTFS -Confirm:$false

#Uninitialize the disk - If anything goes wrong during the configuration
Clear-Disk -Number $NewDiskID

Enable Windows Failover Cluster Role on both SQL 2017 Servers

Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools

Perform a Pre-Test to ensure that both SQL 2017 Servers are ready for Windows Failover Cluster (Optional Steps)

Import-Module FailoverClusters

$SQL01 = "UAT-SQL01"
$SQL02 = "UAT-SQL02"
$SQLClusterName = "UAT-CLUS"
$SQLClusterIP = "192.168.1.65"

#You can ignore this if you do not want to perform pre-test 
Test-Cluster -Node $SQLNode1, $SQLNode2 

Create a Windows Cluster called UAT-CLUS with IP Address = 192.168.1.65

#New Failover Cluster
New-Cluster -Name $SQLCLusterName -Node $SQL01, $SQL02 -StaticAddress $SQLClusterIP  

Create a new share folder C:\SQLFSW for File Share Witness with full access share permission for SQL Server computers’ account in AD Domain Controller

$FSW = "C:\SQLFSW"
New-Item -Path $FSW -ItemType "directory"
New-SmbShare -Name "SQLFSW" -Path "C:\SQLFSW" -FullAccess "AVENTISLAB\UAT-SQL01$","AVENTISLAB\UAT-SQL02$","AVENTISLAB\UAT-CLUS$"

Assign Full Access NTFS permission for SQL Server computers’ account

$Computers= @('UAT-SQL01$','UAT-SQL02$','UAT-CLUS$')
ForEach ($Computer in $Computers) {

$Acl = (Get-Item $FSW).GetAccessControl('Access')
$Ar = New-Object System.Security.AccessControl.FileSystemAccessRule($user, "FullControl", "ContainerInherit,ObjectInherit", "None", "Allow")
$Acl.SetAccessRule($Ar)
Set-Acl -path $FSW -AclObject $Acl }

Configure File Share Witness (FSW)

Get-Cluster | Set-ClusterQuorum -FileShareWitness \\AD01\SQLFSW

#Verify Cluster Resources are provisioned successfully 
Get-Cluster | Get-ClusterQuorum

The following Cluster Resources are Online now

Get-ClusterResource

Name               State  OwnerGroup    ResourceType                 
----               -----  ----------    ------------                 
Cluster IP Address Online Cluster Group IP Address                   
Cluster Name       Online Cluster Group Network Name                 
File Share Witness Online Cluster Group File Share Witness  

Verify the Onwer of the Cluster Resources

Get-ClusterGroup

Name              OwnerNode State  
----              --------- -----  
Available Storage UAT-SQL02 Offline
Cluster Group     UAT-SQL02 Online 

Perform Failover from UAT-SQL02 to UAT-SQL01

Move-ClusterGroup -Name "Cluster Group" -Node UAT-SQL01 -Verbose

VERBOSE: Connecting to cluster on local computer UAT-SQL01.
VERBOSE: Initiating move of group 'Cluster Group' to UAT-SQL01.
VERBOSE: Moved group 'Cluster Group' from UAT-SQL02 to UAT-SQL01.

Name          OwnerNode State 
----          --------- ----- 
Cluster Group UAT-SQL01 Online

Generate a SQL Configuration File by following my previous post or Download the SQLConfig.ini used in this lab

Comment out UIMODE=”Normal” on the SQL Configuration File Generated

Enable .NET 3.5

Add-WindowsFeature -Name NET-Framework-Core

Mount the SQL 2017 ISO File as D Drive and start the unattended installation

• /SQLSVCPASSWORD – Service Account Password assigned for SQL Server Database Engine
• /AGTSVCPASSWORD – Service Account Password assigned for SQL Agent
• /SAPWD – SA Password

D:\Setup.exe /SQLSVCPASSWORD="P@ssw0rd" /AGTSVCPASSWOR="P@ssw0rd" /SAPWD="P@ssw0rd" /ConfigurationFile=C:\Temp\SQLConfig.ini /IAcceptSQLServerLicenseTerms /q

Download and install SQL Server Management Studio (SSMS) to manage SQL 2017 Server as SSMS is no longer included in SQL Installation file

Verify the SQL Services is installed and running on both SQL Servers

Get-Service | ? Name -like "*SQL*"

Status   Name               DisplayName                           
------   ----               -----------                           
Running  MSSQLSERVER        SQL Server (MSSQLSERVER)              
Stopped  SQLBrowser         SQL Server Browser                    
Running  SQLSERVERAGENT     SQL Server Agent (MSSQLSERVER)        
Running  SQLTELEMETRY       SQL Server CEIP service (MSSQLSERVER) 
Running  SQLWriter          SQL Server VSS Writer 

Refer to Install the SQL Server PowerShell module, or Install SqlServer Module without Internet Access

#Install SQL PowerShell Module
Install-PackageProvider -Name "Nuget" -MinimumVersion "2.8.5.201" -Force 
Install-Module -Name SqlServer

Enable TCP/IP for Remote Connection and Restart SQL Service on both SQL Servers

# Enable TCP/IP
Get-CimInstance -Namespace root/Microsoft/SqlServer/ComputerManagement14 -ClassName ServerNetworkProtocol -Filter "ProtocolName = 'Tcp'" | 
Invoke-CimMethod -Name SetEnable

Restart-Service "MSSQLSERVER"

Enable Inbound TCP 1433 & 5022 if Windows Firewall is enabled

New-NetFirewallRule -DisplayName "Allow SQL TCP5022" -Direction Inbound -Action Allow -Enabled True
New-NetFirewallRule -DisplayName "Allow SQL TCP1433" -Direction Inbound -Action Allow -Enabled True

Verify both SQL Servers can be connected with SSMS

You will NOT be able to manage Remote SQL Servers with SQL Management Studio if TCP/IP is NOT enabled

Enable SQL Always On and Restart SQL Service for both SQL Servers with PowerShell

Enable-SqlAlwaysOn -ServerInstance "UAT-SQL01" -Force
Restart-Service "MSSQLSERVER"

Enable-SqlAlwaysOn -ServerInstance "UAT-SQL02" -Force
Restart-Service "MSSQLSERVER"

Enable SQL AlwaysOn in SQL Server Configuration Manager – SQL Server Service – SQL Server (MSSQLSERVER) – AlwaysOn High Availability

Create EndPoints – SQLEndPoint used for SQL AlwaysOn Replication on both SQL Servers

#EndPoint in UAT-SQL01
New-SqlHADREndpoint -Path "SQLSERVER:\SQL\UAT-SQL01\Default" -Name "SQLEndPoint" -Port 5022 -EncryptionAlgorithm Aes -Encryption Required

Set-SqlHADREndpoint -Path "SQLSERVER:\SQL\UAT-SQL01\Default\Endpoints\SQLEndPoint" -State Started

#EndPoint in UAT-SQL02
New-SqlHADREndpoint -Path "SQLSERVER:\SQL\UAT-SQL02\Default" -Name "SQLEndPoint" -Port 5022 -EncryptionAlgorithm Aes -Encryption Required

Set-SqlHADREndpoint -Path "SQLSERVER:\SQL\UAT-SQL02\Default\Endpoints\SQLEndPoint" -State Started

Create a SQL login and grant CONNECT permissions to the SQL Service Account – SqlAdmin on both SQL Servers

#Create the T-SQL commands
$createLogin = “CREATE LOGIN [AVENTISLAB\sqladmin] FROM WINDOWS;” 
$grantConnectPermissions = “GRANT CONNECT ON ENDPOINT::SQLEndPoint TO [AVENTISLAB\sqladmin];”

#Run the T-SQL commands using Invoke-SqlCmd
Invoke-SqlCmd -ServerInstance "UAT-SQL01" -Query $createLogin
Invoke-SqlCmd -ServerInstance "UAT-SQL01" -Query $grantConnectPermissions

Create a new SQL Database called UAT_SQLDB on UAT-SQL01

$SQL01 = "UAT-SQL01"
$DBName = "UAT_SQLDB"
$CreateDB = "CREATE DATABASE $DBName"

Invoke-Sqlcmd -ServerInstance $SQL01 -Query $CreateDB

Ensure that the SQL Recovery Model is set to FULL for the new UAT_SQLDB

$SQL_RecoveryModel = "SELECT [Databases] = name, [RecoveryModel] = recovery_model_desc FROM sys.databases"
Invoke-SqlCmd -ServerInstance "UAT-SQL01" -Query $SQL_RecoveryModel

Databases RecoveryModel
--------- -------------
master    SIMPLE       
tempdb    SIMPLE       
model     FULL         
msdb      SIMPLE       
UAT_SQLDB FULL 

Create a Share Folder called SQLBackup on C:\SQLBackup Folder with Full Access Share Permission assigned to SQL Service Account (SqlAdmin) on AD Domain Controller

$BackupPath = "C:\SQLBackup"
$BackupShare = "SQLBackup"

New-Item -Path $BackupPath -ItemType Container
New-SmbShare -Path $BackupPath -Name $BackupShare

#Full Access Share Permission to the SQL Service Account
Grant-SmbShareAccess -Name $BackupShare -AccountName "AVENTISLAB\sqladmin" -AccessRight Full 

Backup UAT_SQLDB Database to SQLBackup on UAT-SQL01

$BackupUNC = "\\AD01\SQLBackup\$DBName.bak"

$DBBackup = "BACKUP DATABASE $DBName 
TO DISK ='$BackupUNC'"

Invoke-Sqlcmd -ServerInstance $SQL01 -Query $DBBackup

Backup UAT_SQLDB Log to SQLBackup

$LogBackup = "BACKUP LOG [UAT_SQLDB] TO  DISK = N'\\AD01\SQLBackup\UAT_SQLDB.trn' WITH NOFORMAT, NOINIT,  NAME = N'UAT_SQLDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10"
Invoke-Sqlcmd -ServerInstance $SQL01 -Query $LogBackup

Login to UAT-SQL02 and restore the SQL Database & Logs

#Restore Backup DB to 2nd Node
$SQL02 = "UAT-SQL02"
$DBName = "UAT_SQLDB"
$BackupUNC = "\\AD01\SQLBackup\$DBName.bak"


$RestoreDB="RESTORE DATABASE $DBName   
    FROM DISK = '$BackupUNC'   
    WITH NORECOVERY"

Invoke-Sqlcmd -ServerInstance $SQL02 -Query $RestoreDB

#Restore Backup Logs
$RestoreLogs = "RESTORE LOG [UAT_SQLDB] FROM DISK = N'\\AD01\SQLBackup\UAT_SQLDB.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10"
Invoke-Sqlcmd -ServerInstance $SQL02 -Query $RestoreLogs

Create a new SQL Availability Group on UAT-SQL01

• Group Name = SQLAG
• Database = UAT_SQLDB
• Endpoint URL = TCP://UAT-SQL01.AventisLab.local:5022 & TCP://UAT-SQL02.AventisLab.local:5022
• Availability Mode = Syncronous_Commit

#Create SQLAG Group
$SQLAG = "CREATE AVAILABILITY GROUP [SQLAG]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
DB_FAILOVER = OFF,
DTC_SUPPORT = NONE)
FOR DATABASE [UAT_SQLDB]
REPLICA ON N'UAT-SQL01' WITH (ENDPOINT_URL = N'TCP://UAT-SQL01.AventisLab.local:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, 
    SEEDING_MODE = MANUAL, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)),
    N'UAT-SQL02' WITH (ENDPOINT_URL = N'TCP://UAT-SQL02.AventisLab.local:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, 
    SEEDING_MODE = MANUAL, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));
    "

Invoke-Sqlcmd -ServerInstance $SQL01 -Query $SQLAG

Join Replica SQL Server (UAT-SQL02) and Database (UAT_SQLDB) to Availability Group

$SQLJoin = "ALTER AVAILABILITY GROUP [SQLAG] JOIN;"
$SQLDBSync = "ALTER DATABASE [UAT_SQLDB] SET HADR AVAILABILITY GROUP = [SQLAG];"

Invoke-Sqlcmd -ServerInstance $SQL02 -Query $SQLJOIN
Invoke-Sqlcmd -ServerInstance $SQL02 -Query $SQLDBSync

Create a new SQL Listener Name & IP Address

#Create the Availability Group listener name
New-SqlAvailabilityGroupListener -Name "SQLAO" -staticIP "192.168.1.68/255.255.255.0" -Port 1433 -Path "SQLSERVER:\SQL\UAT-SQL01\DEFAULT\AvailabilityGroups\SQLAG"

SQL 2017 Always On Availability Group is up and running now

Login to UAT-SQL02 and perform Failover from UAT-SQL01 to UAT-SQL02

Switch-SqlAvailabilityGroup -Path SQLSERVER:\Sql\UAT-SQL02\DEFAULT\AvailabilityGroups\SQLAG -Verbose

UAT-SQL02 had switched to Primary Role now

Reference Link

1. PowerShell-FailoverClusters

Open Notepad and save a file as "sql.udl"

Enter the SQL Listener Name, credential for SQL Database, and click Test Connection to ensure that you can establish connection to SQL Server successfully.

Assign a New IP Address (192.168.1.75) to Cluster Resource called "Cluster IP Address"

Get-ClusterResource -Name "Cluster IP Address" | Set-ClusterParameter -Name Address -Value "192.168.1.75"

Bring the Cluster Group offline & online to activate the changes

Stop-ClusterGroup -Name "Cluster Group"
Start-ClusterGroup -Name "Cluster Group" -Verbose