Refer to the steps below on how to Setup SQL 2017 AlwaysOn Availability Group with PowerShell
Components installed in this lab
Hostname | IP Address | Remarks |
---|---|---|
lab-sql1.lab.local | 172.16.16.201 | SQL 2017 Enterprise Server |
lab-sql2.lab.local | 172.16.16.202 | SQL 2017 Enterprise Server |
sqlclus.lab.local | 172.16.16.210 | Failover Cluster |
sqlag.lab.local | 172.16.16.212 | SQL Listener |
lab-ad01.lab.local | 172.16.16.200 | AD Domain Controller & File Share Witness |
A. Preparation of service account for SQL Server (Normal Domain user will do)
- Create a new SQLSVC (Service Account for SQL) account with normal domain user right
#SQL Account
Import-Module ActiveDirectory
$SQLAdmin = "sqladmin"
#Normal Domain User Account will do
New-ADUser -Name $SQLAdmin -UserPrincipalName [email protected] -AccountPassword(ConvertTo-SecureString '[email protected]' –asplaintext –force) `
-PassThru | Enable-ADAccount
B. Initialize of New Hard Disk
- Assigned a 30GB HDD and format it with NTFS partition with Drive Letter S on both SQL Servers
#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
C. Setup Windows Failover Cluster
- Enable Windows Failover Cluster Role
Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools
Import-Module FailoverClusters
$SQLNode1 = "lab-sql1"
$SQLNode2 = "lab-sql2"
$SQLClusterName = "SQLCLUS"
$SQLClusterIP = "172.16.16.210"
#You can ignore this if you do not want to perform pre-test
Test-Cluster -Node $SQLNode1, $SQLNode2
- Create a New Cluster with Name = SQLCLUS and IP Address = 172.16.16.210
#New Failover Cluster
New-Cluster -Name $SQLCLusterName -Node $SQLNode1, $SQLNode2 -StaticAddress $SQLClusterIP
- Create a new folder C:\SQLFSW for File Share Withness with full access share permission for SQL Server computers’ account in LAB-AD01 Server
New-Item -Path "C:\SQLFSW" -ItemType "directory"
New-SmbShare -Name "SQLFSW" -Path "C:\SQLFSW" -FullAccess "lab\lab-sql1$","lab\lab-sql2$","lab\SQLCLUS$"
- Assign Full Access NTFS permission for SQL Server computers’ account
$Users= @('lab-sql1
- Configure File Share Witness (FSW)
Set-ClusterQuorum -Cluster sqlclus -FileShareWitness \\LAB-AD01\SQLFSW #Verify Cluster Resources are provisioned successfully Get-Cluster | Format-List * Get-ClusterResource
D. Prepare SQL Configuration File for unattended installation
- Refer to the link below on how to generate the SQL Configuration file https://aventistech.com/2018/07/silent-installation-of-sql-2014-with-ini-file-with-gmsa-service-account/
- Open the SQLConfig.ini file and comment out the UIMODE="Normal"
;UIMODE="Normal"
- Enable .NET 3.5
Add-WindowsFeature -Name NET-Framework-Core
- Mount the SQL 2017 ISO File as D and start the installation
D:\setup.exe /SQLSVCPASSWORD="[email protected]" /SAPWD="[email protected]" /ConfigurationFile=C:\Temp\SQL.ini /IAcceptSQLServerLicenseTerms /q
- Download and install SQL Management Studio as it is no longer included in the SQL2016 ISO file
C:\Temp\SSMS-Setup-ENU.exe /install /quiet /noestart
- Verify the SQL Services is installed and running on both SQL Servers
Get-Service | ? Name -like "*SQL*"
E. Installation of SQL Always On Cluster
- Install PowerShell Module for SQL Server and enable TCP/IP for Remote Connection for both SQL Servers
#Install SQL PowerShell Module Install-PackageProvider -Name "Nuget" -MinimumVersion "2.8.5.201" -Force Install-Module -Name SqlServer Import-Module SqlServer # Enable TCP/IP Get-CimInstance -Namespace root/Microsoft/SqlServer/ComputerManagement14 -ClassName ServerNetworkProtocol -Filter "ProtocolName = 'Tcp'" | Invoke-CimMethod -Name SetEnable Restart-Service "MSSQLSERVER"
You will NOT able to manage LAB-SQL2 Server with SQL Management Studio if TCP/IP is NOT enabled
- Enable SQL AlwaysOn on both SQL Servers
Enable-SqlAlwaysOn -ServerInstance "SQL1" -Force Enable-SqlAlwaysOn -ServerInstance "SQL2" -Force
- Enable Inbound TCP 1433 & 5022 if Windows Firewall is enabled (Domain Profile)
New-NetFirewallRule -DisplayName "Allow SQL TCP5022" -Direction Inbound -Action Allow -Profile Domain -Enabled True New-NetFirewallRule -DisplayName "Allow SQL TCP1433" -Direction Inbound -Action Allow -Profile Domain -Enabled True
- Create & Start the SQL AG Endpoint on both SQL Servers
New-SqlHADREndpoint -Path "SQLSERVER:\SQL\lab-SQL1\Default" -Name "SQLAG" ` -Port 5022 -EncryptionAlgorithm Aes -Encryption Required Set-SqlHADREndpoint -Path "SQLSERVER:\SQL\lab-SQL1\Default\Endpoints\SQLAG" -State Started
- Create login and grant CONNECT permissions to the SQL Server service account
#Create the T-SQL commands $createLogin = “CREATE LOGIN [lab\sqladmin] FROM WINDOWS;” $grantConnectPermissions = “GRANT CONNECT ON ENDPOINT::SQLAG TO [lab\sqladmin];” #Run the T-SQL commands using Invoke-SqlCmd Invoke-SqlCmd -ServerInstance $SQLNode1 -Query $createLogin Invoke-SqlCmd -ServerInstance $SQLNode1 -Query $grantConnectPermissions
F. Creating SQL Always On Availability Group
- Create a New Database on LAB-SQL1
$DBName = "UAT_SQLAG" $CreateDB = "CREATE DATABASE $DBName" Invoke-Sqlcmd -ServerInstance $SQLNode1 -Query $CreateDB
- Create a Folder for SQL Backup in LAB-AD01 and assign full permission to SQL Service account (sqladmin)
$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 "lab\sqladmin" -AccessRight Full -comfirm:$false
- Backup the UAT_DB to the Share Folder
$BackupUNC = "\\LAB-AD01\SQLBackup\$DBName.bak" $DBBackup = "BACKUP DATABASE $DBName TO DISK ='$BackupUNC'" Invoke-Sqlcmd -ServerInstance $SQLNode1 -Query $DBBackup
- Restore the DB to LAB-SQL2 Server
#Restore BAckup DB to 2nd Node $RestoreDB="RESTORE DATABASE $DBName FROM DISK = '$BackupUNC' WITH NORECOVERY" Invoke-Sqlcmd -ServerInstance $SQLNode2 -Query $RestoreDB
- Create a New SQL Availability Group with
- AG Group Name = [UAT_SQLAG]
- DB to be added = [UAT_SQLAG]
- Availability Mode = Syncronous_Commit
#Create SQLAG Group $SQLAG = "CREATE AVAILABILITY GROUP [UAT_SQLAG] WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY, DB_FAILOVER = OFF, DTC_SUPPORT = NONE) FOR DATABASE [UAT_SQLAG] REPLICA ON N'LAB-SQL1' WITH (ENDPOINT_URL = N'TCP://lab-sql1.lab.local:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SEEDING_MODE = MANUAL, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)),N'LAB-SQL2' WITH (ENDPOINT_URL = N'TCP://lab-sql2.lab.local:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SEEDING_MODE = MANUAL, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)); " Invoke-Sqlcmd -ServerInstance $SQLNode1 -Query $SQLAG
- Join LAB-SQL2 (Replica) & Database to AG Group
$SQLJoin = "ALTER AVAILABILITY GROUP [UAT_SQLAG] JOIN;" $SQLDBSync = "ALTER DATABASE [UAT_SQLAG] SET HADR AVAILABILITY GROUP = [UAT_SQLAG];" Invoke-Sqlcmd -ServerInstance $SQLNode2 -Query $SQLJOIN Invoke-Sqlcmd -ServerInstance $SQLNode2 -Query $SQLDBSync
- Create Listener Name and IP Address
#Create the Availability Group listener name New-SqlAvailabilityGroupListener -Name "UAT_SQLAG" -staticIP "172.16.16.212/255.255.255.0" -Port 1433 -Path "SQLSERVER:\SQL\LAB-SQL1\DEFAULT\AvailabilityGroups\UAT_SQLAG"
SQL 2017 AlwaysOn Avalability Group is up and running now
To Test the Failover from LAB-SQL1 to LAB-SQL2 is working fine
PS C:\Users\administrator.LAB> Switch-SqlAvailabilityGroup -Path SQLSERVER:\Sql\lab-sql2\DEFAULT\AvailabilityGroups\UAT_SQLAG -Verbose VERBOSE: Performing the operation "Switch-SqlAvailabilityGroup" on target "[UAT_SQLAG]". VERBOSE: ALTER AVAILABILITY GROUP [UAT_SQLAG] FAILOVER;
LAB-SQL2 is become Primary Role while LAB-SQL1 is Secondary now
,'lab-sql2
- Configure File Share Witness (FSW)
D. Prepare SQL Configuration File for unattended installation
- Refer to the link below on how to generate the SQL Configuration file https://aventistech.com/2018/07/silent-installation-of-sql-2014-with-ini-file-with-gmsa-service-account/
- Open the SQLConfig.ini file and comment out the UIMODE="Normal"
- Enable .NET 3.5
- Mount the SQL 2017 ISO File as D and start the installation
- Download and install SQL Management Studio as it is no longer included in the SQL2016 ISO file
- Verify the SQL Services is installed and running on both SQL Servers
E. Installation of SQL Always On Cluster
- Install PowerShell Module for SQL Server and enable TCP/IP for Remote Connection for both SQL Servers
You will NOT able to manage LAB-SQL2 Server with SQL Management Studio if TCP/IP is NOT enabled
- Enable SQL AlwaysOn on both SQL Servers
- Enable Inbound TCP 1433 & 5022 if Windows Firewall is enabled (Domain Profile)
- Create & Start the SQL AG Endpoint on both SQL Servers
- Create login and grant CONNECT permissions to the SQL Server service account
F. Creating SQL Always On Availability Group
- Create a New Database on LAB-SQL1
- Create a Folder for SQL Backup in LAB-AD01 and assign full permission to SQL Service account (sqladmin)
- Backup the UAT_DB to the Share Folder
- Restore the DB to LAB-SQL2 Server
- Create a New SQL Availability Group with
- AG Group Name = [UAT_SQLAG]
- DB to be added = [UAT_SQLAG]
- Availability Mode = Syncronous_Commit
- Join LAB-SQL2 (Replica) & Database to AG Group
- Create Listener Name and IP Address
SQL 2017 AlwaysOn Avalability Group is up and running now
To Test the Failover from LAB-SQL1 to LAB-SQL2 is working fine
LAB-SQL2 is become Primary Role while LAB-SQL1 is Secondary now
,'sqlclus
- Configure File Share Witness (FSW)
D. Prepare SQL Configuration File for unattended installation
- Refer to the link below on how to generate the SQL Configuration file https://aventistech.com/2018/07/silent-installation-of-sql-2014-with-ini-file-with-gmsa-service-account/
- Open the SQLConfig.ini file and comment out the UIMODE="Normal"
- Enable .NET 3.5
- Mount the SQL 2017 ISO File as D and start the installation
- Download and install SQL Management Studio as it is no longer included in the SQL2016 ISO file
- Verify the SQL Services is installed and running on both SQL Servers
E. Installation of SQL Always On Cluster
- Install PowerShell Module for SQL Server and enable TCP/IP for Remote Connection for both SQL Servers
You will NOT able to manage LAB-SQL2 Server with SQL Management Studio if TCP/IP is NOT enabled
- Enable SQL AlwaysOn on both SQL Servers
- Enable Inbound TCP 1433 & 5022 if Windows Firewall is enabled (Domain Profile)
- Create & Start the SQL AG Endpoint on both SQL Servers
- Create login and grant CONNECT permissions to the SQL Server service account
F. Creating SQL Always On Availability Group
- Create a New Database on LAB-SQL1
- Create a Folder for SQL Backup in LAB-AD01 and assign full permission to SQL Service account (sqladmin)
- Backup the UAT_DB to the Share Folder
- Restore the DB to LAB-SQL2 Server
- Create a New SQL Availability Group with
- AG Group Name = [UAT_SQLAG]
- DB to be added = [UAT_SQLAG]
- Availability Mode = Syncronous_Commit
- Join LAB-SQL2 (Replica) & Database to AG Group
- Create Listener Name and IP Address
SQL 2017 AlwaysOn Avalability Group is up and running now
To Test the Failover from LAB-SQL1 to LAB-SQL2 is working fine
LAB-SQL2 is become Primary Role while LAB-SQL1 is Secondary now
)
ForEach ($user in $Users) {$Acl = (Get-Item C:\SQLFSW).GetAccessControl('Access')
$Ar = New-Object System.Security.AccessControl.FileSystemAccessRule($user, "FullControl", "ContainerInherit,ObjectInherit", "None", "Allow")
$Acl.SetAccessRule($Ar)
Set-Acl -path C:\SQLFSW -AclObject $Acl }
- Configure File Share Witness (FSW)
D. Prepare SQL Configuration File for unattended installation
- Refer to the link below on how to generate the SQL Configuration file https://aventistech.com/2018/07/silent-installation-of-sql-2014-with-ini-file-with-gmsa-service-account/
- Open the SQLConfig.ini file and comment out the UIMODE=”Normal”
- Enable .NET 3.5
- Mount the SQL 2017 ISO File as D and start the installation
- Download and install SQL Management Studio as it is no longer included in the SQL2016 ISO file
- Verify the SQL Services is installed and running on both SQL Servers
E. Installation of SQL Always On Cluster
- Install PowerShell Module for SQL Server and enable TCP/IP for Remote Connection for both SQL Servers
You will NOT able to manage LAB-SQL2 Server with SQL Management Studio if TCP/IP is NOT enabled
- Enable SQL AlwaysOn on both SQL Servers
- Enable Inbound TCP 1433 & 5022 if Windows Firewall is enabled (Domain Profile)
- Create & Start the SQL AG Endpoint on both SQL Servers
- Create login and grant CONNECT permissions to the SQL Server service account
F. Creating SQL Always On Availability Group
- Create a New Database on LAB-SQL1
- Create a Folder for SQL Backup in LAB-AD01 and assign full permission to SQL Service account (sqladmin)
- Backup the UAT_DB to the Share Folder
- Restore the DB to LAB-SQL2 Server
- Create a New SQL Availability Group with
- AG Group Name = [UAT_SQLAG]
- DB to be added = [UAT_SQLAG]
- Availability Mode = Syncronous_Commit
- Join LAB-SQL2 (Replica) & Database to AG Group
- Create Listener Name and IP Address
SQL 2017 AlwaysOn Avalability Group is up and running now
To Test the Failover from LAB-SQL1 to LAB-SQL2 is working fine
LAB-SQL2 is become Primary Role while LAB-SQL1 is Secondary now