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 thePowerShell 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="<DomainNameUserName>" /SQLSVCPASSWORD="<StrongPassword>" /SQLSYSADMINACCOUNTS="<DomainNameUserName>" /AGTSVCACCOUNT="<DomainNameUserName>" /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 = ‘$Server1Instance’
$ServerInstance2 = ‘$Server2Instance’
#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 = “DomainSVCAccount”
$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$Server1default”
New-SqlHadrEndpoint -Path $AOtmpPath -Name $AOEndpointName -Port $AOPort -EncryptionAlgorithm $AOEncryptionAlgorithm
start the endpoint
$AOtmpPath = “SQLSERVER:SQL$Server1defaultendpointsAlwaysOnEndpoint”
Set-SqlHadrEndpoint –Path $AOtmpPath –State “Started”;
####2nd Server
$AOtmpPath = “SQLSERVER:SQL$Server2default”
New-SqlHadrEndpoint -Path $AOtmpPath -Name $AOEndpointName -Port $AOPort -EncryptionAlgorithm $AOEncryptionAlgorithm
start the endpoint
$AOtmpPath = “SQLSERVER:SQL$Server2defaultendpointsAlwaysOnEndpoint”
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$Server1default”
$svr = Get-Item $AOtmpPath
$db1 = New-Object Microsoft.SqlServer.Management.Smo.Database($svr, $tmpDB1);
$db1.Create();
#—————————————————-
#Initial Backup for the DB
#—————————————————-
cd “SQLSERVER:SQL$Server1defaultdatabases”
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$Server1default”
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$Server2default”
Join-SqlAvailabilityGroup –Path $AOtmpPath –Name $AG1Name;
#———————————————
#Join Replica Database on a Secondary replica
#———————————————
$agpath1 = “SQLSERVER:SQL$Server2defaultAvailabilityGroups$AG1Name”
Add-SqlAvailabilityDatabase –Path $agpath1 –Database $tmpDB1
#———————————————
#Create a Listener using Static IPs
#———————————————
$agpath1 = “SQLSERVER:SQL$Server1defaultAvailabilityGroups$AG1Name”
$ag = Get-Item $agpath1; #Validate AG Path
New-SqlAvailabilityGroupListener -Name $AGListner1Name –Path $agpath1 –StaticIp $Listner1IP_Mask –Port $ListnerPort;
Share this:
Categories: Cluster, powershell script, SQL Server Configuration Manager