Setup SQL 2017 AlwaysOn Availability Group with PowerShell

Leave a Comment / By /

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)

  1. 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 'P@ssw0rd' –asplaintext –force) `
-PassThru | Enable-ADAccount

B. Initialize of New Hard Disk

  1. 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

  1. 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
  1. 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
  1. 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$"
  1. Assign Full Access NTFS permission for SQL Server computers’ account
$Users= @('lab-sql1

<ol start="5">

    <li>Configure File Share Witness (FSW)</li>

</ol>


<pre><code class="language-powershell ">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

  1. 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/
  2. Open the SQLConfig.ini file and comment out the UIMODE=”Normal”
;UIMODE="Normal"
  1. Enable .NET 3.5
Add-WindowsFeature -Name NET-Framework-Core
  1. Mount the SQL 2017 ISO File as D and start the installation
D:\setup.exe /SQLSVCPASSWORD="P@ssw0rd" /SAPWD="P@ssw0rd" /ConfigurationFile=C:\Temp\SQL.ini /IAcceptSQLServerLicenseTerms /q
  1. 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
  1. Verify the SQL Services is installed and running on both SQL Servers
Get-Service | ? Name -like "*SQL*"

E. Installation of SQL Always On Cluster

  1. 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

  1. Enable SQL AlwaysOn on both SQL Servers
Enable-SqlAlwaysOn -ServerInstance "SQL1" -Force
Enable-SqlAlwaysOn -ServerInstance "SQL2" -Force

SQLAO-Install-01

  1. 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
  1. 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
  1. 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

  1. Create a New Database on LAB-SQL1
$DBName = "UAT_SQLAG"
$CreateDB = "CREATE DATABASE $DBName"

Invoke-Sqlcmd -ServerInstance $SQLNode1 -Query $CreateDB
  1. 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
  1. 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
  1. 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
  1. 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
  1. 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
  1. 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"

SQLAO-Install-02

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;

SQLAO-Install-03

LAB-SQL2 is become Primary Role while LAB-SQL1 is Secondary now

,’lab-sql2

  1. Configure File Share Witness (FSW)


D. Prepare SQL Configuration File for unattended installation


    

  1. 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/

    2. 

  2. Open the SQLConfig.ini file and comment out the UIMODE=”Normal”

    3. 





    

  1. Enable .NET 3.5
    2. 





    

  1. Mount the SQL 2017 ISO File as D and start the installation
    2. 





    

  1. Download and install SQL Management Studio as it is no longer included in the SQL2016 ISO file
    2. 





    

  1. Verify the SQL Services is installed and running on both SQL Servers
    2. 





E. Installation of SQL Always On Cluster


    

  1. Install PowerShell Module for SQL Server and enable TCP/IP for Remote Connection for both SQL Servers
    2. 






You will NOT able to manage LAB-SQL2 Server with SQL Management Studio if TCP/IP is NOT enabled




    

  1. Enable SQL AlwaysOn on both SQL Servers
    2. 





SQLAO-Install-01


    

  1. Enable Inbound TCP 1433 & 5022 if Windows Firewall is enabled (Domain Profile)
    2. 





    

  1. Create & Start the SQL AG Endpoint on both SQL Servers
    2. 





    

  1. Create login and grant CONNECT permissions to the SQL Server service account
    2. 





F. Creating SQL Always On Availability Group


    

  1. Create a New Database on LAB-SQL1
    2. 





    

  1. Create a Folder for SQL Backup in LAB-AD01 and assign full permission to SQL Service account (sqladmin)
    2. 





    

  1. Backup the UAT_DB to the Share Folder
    2. 





    

  1. Restore the DB to LAB-SQL2 Server
    2. 





    

  1. Create a New SQL Availability Group with
    2. 



    

  • AG Group Name = [UAT_SQLAG]
    • 

  • DB to be added = [UAT_SQLAG]
    • 

  • Availability Mode = Syncronous_Commit
    • 





    

  1. Join LAB-SQL2 (Replica) & Database to AG Group
    2. 





    

  1. Create Listener Name and IP Address
    2. 





SQLAO-Install-02


SQL 2017 AlwaysOn Avalability Group is up and running now


To Test the Failover from LAB-SQL1 to LAB-SQL2 is working fine




SQLAO-Install-03


LAB-SQL2 is become Primary Role while LAB-SQL1 is Secondary now

,’sqlclus


    

  1. Configure File Share Witness (FSW)
    2. 





D. Prepare SQL Configuration File for unattended installation


    

  1. 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/
    2. 

  2. Open the SQLConfig.ini file and comment out the UIMODE=”Normal”
    3. 





    

  1. Enable .NET 3.5
    2. 





    

  1. Mount the SQL 2017 ISO File as D and start the installation
    2. 





    

  1. Download and install SQL Management Studio as it is no longer included in the SQL2016 ISO file
    2. 





    

  1. Verify the SQL Services is installed and running on both SQL Servers
    2. 





E. Installation of SQL Always On Cluster


    

  1. Install PowerShell Module for SQL Server and enable TCP/IP for Remote Connection for both SQL Servers
    2. 






You will NOT able to manage LAB-SQL2 Server with SQL Management Studio if TCP/IP is NOT enabled


    

  1. Enable SQL AlwaysOn on both SQL Servers
    2. 





SQLAO-Install-01


    

  1. Enable Inbound TCP 1433 & 5022 if Windows Firewall is enabled (Domain Profile)
    2. 





    

  1. Create & Start the SQL AG Endpoint on both SQL Servers
    2. 





    

  1. Create login and grant CONNECT permissions to the SQL Server service account
    2. 





F. Creating SQL Always On Availability Group


    

  1. Create a New Database on LAB-SQL1
    2. 





    

  1. Create a Folder for SQL Backup in LAB-AD01 and assign full permission to SQL Service account (sqladmin)
    2. 





    

  1. Backup the UAT_DB to the Share Folder
    2. 





    

  1. Restore the DB to LAB-SQL2 Server
    2. 





    

  1. Create a New SQL Availability Group with
    2. 



    

  • AG Group Name = [UAT_SQLAG]
    • 

  • DB to be added = [UAT_SQLAG]
    • 

  • Availability Mode = Syncronous_Commit
    • 





    

  1. Join LAB-SQL2 (Replica) & Database to AG Group
    2. 





    

  1. Create Listener Name and IP Address
    2. 





SQLAO-Install-02


SQL 2017 AlwaysOn Avalability Group is up and running now


To Test the Failover from LAB-SQL1 to LAB-SQL2 is working fine




SQLAO-Install-03


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 }




    

  1. Configure File Share Witness (FSW)
    2. 





D. Prepare SQL Configuration File for unattended installation


    

  1. 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/

    2. 

  2. Open the SQLConfig.ini file and comment out the UIMODE=”Normal”

    3. 





    

  1. Enable .NET 3.5
    2. 





    

  1. Mount the SQL 2017 ISO File as D and start the installation
    2. 





    

  1. Download and install SQL Management Studio as it is no longer included in the SQL2016 ISO file
    2. 





    

  1. Verify the SQL Services is installed and running on both SQL Servers
    2. 





E. Installation of SQL Always On Cluster


    

  1. Install PowerShell Module for SQL Server and enable TCP/IP for Remote Connection for both SQL Servers
    2. 






You will NOT able to manage LAB-SQL2 Server with SQL Management Studio if TCP/IP is NOT enabled




    

  1. Enable SQL AlwaysOn on both SQL Servers
    2. 





SQLAO-Install-01


    

  1. Enable Inbound TCP 1433 & 5022 if Windows Firewall is enabled (Domain Profile)
    2. 





    

  1. Create & Start the SQL AG Endpoint on both SQL Servers
    2. 





    

  1. Create login and grant CONNECT permissions to the SQL Server service account
    2. 





F. Creating SQL Always On Availability Group


    

  1. Create a New Database on LAB-SQL1
    2. 





    

  1. Create a Folder for SQL Backup in LAB-AD01 and assign full permission to SQL Service account (sqladmin)
    2. 





    

  1. Backup the UAT_DB to the Share Folder
    2. 





    

  1. Restore the DB to LAB-SQL2 Server
    2. 





    

  1. Create a New SQL Availability Group with
    2. 



    

  • AG Group Name = [UAT_SQLAG]
    • 

  • DB to be added = [UAT_SQLAG]
    • 

  • Availability Mode = Syncronous_Commit
    • 





    

  1. Join LAB-SQL2 (Replica) & Database to AG Group
    2. 





    

  1. Create Listener Name and IP Address
    2. 





SQLAO-Install-02


SQL 2017 AlwaysOn Avalability Group is up and running now


To Test the Failover from LAB-SQL1 to LAB-SQL2 is working fine




SQLAO-Install-03


LAB-SQL2 is become Primary Role while LAB-SQL1 is Secondary now














Leave a Comment 
Your email address will not be published. Required fields are marked *





 


 


 









 









 Scroll to Top