Configuring SQL Server 2014 AlwaysOn Availability Group using PowerShell


Introduction

Earlier on I was trying to search for some scenarios to implement AlwaysOn Availability Groups using PowerShell, although I was able to find some interesting post however non of them represented a complete scenario starting from a fresh windows server installation (using PowerShell), so I decided to write this blog as quick walkthrough the PowerShell scripts needed.

Sample Scenario

In this scenario I will be using a 2 Nodes setup with the following software already installed:

  • Windows Server 2012 R2
  • Both Servers are joined to the domain

Configuration Steps:

To make the scenario simple I be using a Domain Account that has a Local Administrator Permission on both nodes. In addition, all scripts below will be running using PowerShell (Run as Admin) so Lets get started:

1- Install SQL Server

We need to install a standalone setup on each node. I will do that using the below sample unattended SQL Setup Script:

Setup.exe /q /ACTION=Install /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="<DomainName\UserName>" /SQLSVCPASSWORD="<StrongPassword>" /SQLSYSADMINACCOUNTS="<DomainName\UserName>" /AGTSVCACCOUNT="<DomainName\UserName>" /AGTSVCPASSWORD="<StrongPassword>"   /IACCEPTSQLSERVERLICENSETERMS

2- Add Windows Failover Cluster

We need to install it on each node. I will do that using the below script:

Import-Module ServerManager

Add-WindowsFeature Failover-Clustering –IncludeAllSubFeature

Install-WindowsFeature -Name Failover-Clustering –IncludeManagementTools

3- Configure Windows Failover Cluster

Run the below script on the 1st Node and make sure to update the configuration parameters with your values:

#----------------------------------------------------
# Configuration Parameters
#----------------------------------------------------
$Server1 = "Server1"
$Server2 = "Server2"
$IPAddress = "X.X.X.X"
$AlwaysOnClusterName = "ClusetrName"
$QuorumFileSharePath = "\\FileSharePath\"

#----------------------------------------------------
# Create Cluster Service
# Create a new cluster ‘AlwaysOnCluster’ with nodes.
#----------------------------------------------------
Import-Module FailoverClusters
New-Cluster –Name $AlwaysOnClusterName –Node $Server1,$Server2 -StaticAddress $IPAddress -NoStorage
# Add Quorum
Set-ClusterQuorum -NodeAndFileShareMajority $QuorumFileSharePath

4- Configure AlwaysOn Failover Cluster

Now for the fun part, I have configured the whole AlwaysOn Configuration in one script that needed to be ran on the 1st Node. In addition, I have added the proper comments so take your time to review it  and make sure to update the configuration parameters with your values:

#Set execution policy
Set-ExecutionPolicy unrestricted

# Import SQLPS Module
Import-Module “sqlps” -DisableNameChecking

#----------------------------------------------------
# Configuration Parameters
#----------------------------------------------------
#Server Names
$Server1 = "Server1"
$Server2 = "Server2"
$ServerInstance1 = '$Server1\Instance'
$ServerInstance2 = '$Server2\Instance'

#AlwaysOn TempDB Names
$tmpDB1 = "AG1-TempDB"

#Availability Group Names
$AG1Name="AG-1"

#Availability Group Listener
$AGListner1Name = "Listener1"
$Listner1IP_Mask = "X.X.X.X/X.X.X.X"
$ListnerPort= "1433"
$FileSharePath = "\\FileSharePath\"

#Service Accounts
$SQLSVCAccount = "Domain\SVCAccount"
$AGDomain = ".contoso.com" #Keep the '.' before domain name

#AlwaysOn EndPoints
$AOEndpointName = "AlwaysOnEndpoint"
$AOPort = "5022"
$AOEncryptionAlgorithm = "AES"

#----------------------------------------------------
# Enable AlwaysOn on Servers
#----------------------------------------------------
Enable-SqlAlwaysOn –ServerInstance $Server1
Enable-SqlAlwaysOn –ServerInstance $Server2

#----------------------------------------------------
# Create Endpoints
#----------------------------------------------------
#####1st Server
$AOtmpPath = "SQLSERVER:\SQL\$Server1\default"
New-SqlHadrEndpoint -Path $AOtmpPath -Name $AOEndpointName -Port $AOPort -EncryptionAlgorithm $AOEncryptionAlgorithm

# start the endpoint
$AOtmpPath = "SQLSERVER:\SQL\$Server1\default\endpoints\AlwaysOnEndpoint"
Set-SqlHadrEndpoint –Path $AOtmpPath –State "Started";

####2nd Server
$AOtmpPath = "SQLSERVER:\SQL\$Server2\default"
New-SqlHadrEndpoint -Path $AOtmpPath -Name $AOEndpointName -Port $AOPort -EncryptionAlgorithm $AOEncryptionAlgorithm

# start the endpoint
$AOtmpPath = "SQLSERVER:\SQL\$Server2\default\endpoints\AlwaysOnEndpoint"
Set-SqlHadrEndpoint –Path $AOtmpPath –State "Started";

#----------------------------------------------------
# Grant Permissions for EndPoints
#----------------------------------------------------
$SQLPermissionQry = "
USE [master]
GO
CREATE LOGIN $SQLSVCAccount FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
GRANT ALTER ANY AVAILABILITY GROUP TO $SQLSVCAccount
GO
GRANT CONNECT SQL TO $SQLSVCAccount
GO
GRANT VIEW SERVER STATE TO $SQLSVCAccount
GO
"

Invoke-Sqlcmd -Query $SQLPermissionQry -ServerInstance $ServerInstance1
Invoke-Sqlcmd -Query $SQLPermissionQry -ServerInstance $ServerInstance2

#----------------------------------------------------
#Create Temp DB for AG
#----------------------------------------------------
$AOtmpPath = "SQLSERVER:\SQL\$Server1\default"
$svr = Get-Item $AOtmpPath
$db1 = New-Object Microsoft.SqlServer.Management.Smo.Database($svr, $tmpDB1);
$db1.Create();

#----------------------------------------------------
#Initial Backup for the DB
#----------------------------------------------------
cd "SQLSERVER:\SQL\$Server1\default\databases"
Backup-SqlDatabase –ServerInstance $Server1 –Database $tmpDB1;

#------------------------------------------------
# Backup & Restore TempDBs to prepare for AlwaysOn
#------------------------------------------------
#Backup
Backup-SqlDatabase –ServerInstance $Server1 –Database $tmpDB1 –BackupFile "$FileSharePath$tmpDB1.bak";
Backup-SqlDatabase –ServerInstance $Server1 –Database $tmpDB1 –BackupAction Log –BackupFile "$FileSharePath$tmpDB1.trn";

# Restore
cd "SQLSERVER:\SQL\$Server1\default"
Restore-SqlDatabase –ServerInstance $Server2 –Database $tmpDB1 –BackupFile "$FileSharePath$tmpDB1.bak" –NoRecovery;
Restore-SqlDatabase –ServerInstance $Server2 –Database $tmpDB1 –RestoreAction Log –BackupFile "$FileSharePath$tmpDB1.trn" –NoRecovery;

#---------------------------------------------
#Create AG Replica
#It assumes SynchronousCommit + Automatic Failover
#---------------------------------------------
$PrimaryRepTCP = "TCP://$Server1$AGDomain" + ':' + "$AOPort"
$SecondaryRepTCP = "TCP://$Server2$AGDomain" + ':' + "$AOPort"

$Primary = new-sqlavailabilityreplica -Name $Server1 -EndpointUrl $PrimaryRepTCP -ConnectionModeInPrimaryRole "AllowAllConnections" -ConnectionModeInSecondaryRole "AllowAllConnections" –AvailabilityMode "SynchronousCommit" –FailoverMode "Automatic" -AsTemplate -Version 11;
$Secondary = new-sqlavailabilityreplica -Name $Server2 -EndpointUrl $SecondaryRepTCP -ConnectionModeInSecondaryRole "AllowAllConnections" –AvailabilityMode "SynchronousCommit" –FailoverMode "Automatic" -AsTemplate -Version 11;

#---------------------------------------------
#Create a new AG
#---------------------------------------------
$ag = New-SqlAvailabilityGroup -Name $AG1Name -AvailabilityReplica ($Primary, $Secondary) -Database $tmpDB1
#---------------------------------------------
#Join Availability Replica
#---------------------------------------------

$AOtmpPath = "SQLSERVER:\SQL\$Server2\default"
Join-SqlAvailabilityGroup –Path $AOtmpPath –Name $AG1Name;

#---------------------------------------------
#Join Replica Database on a Secondary replica
#---------------------------------------------
$agpath1 = "SQLSERVER:\SQL\$Server2\default\AvailabilityGroups\$AG1Name"
Add-SqlAvailabilityDatabase –Path $agpath1 –Database $tmpDB1

#---------------------------------------------
#Create a Listener using Static IPs
#---------------------------------------------
$agpath1 = "SQLSERVER:\SQL\$Server1\default\AvailabilityGroups\$AG1Name"
$ag = Get-Item $agpath1; #Validate AG Path
New-SqlAvailabilityGroupListener -Name $AGListner1Name –Path $agpath1 –StaticIp $Listner1IP_Mask –Port $ListnerPort;

And that’s all folks … hope you find it useful Smile

Comments (1)

  1. RJ says:

    Hi, could we use this script to set up AlwaysOn on any public cloud like AWS?

Skip to main content