# Configuring SQL Server 2014 AlwaysOn Availability Group using PowerShell

- Link: https://sharepointtechnicalsupport.com/2015/05/06/configuring-sql-server-alwayson-availability-group-powershell/
- Published: 2015-05-06T04:45:45+05:30
- Author: Deviprasad Panda

**_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="<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:

 * [  X ](https://sharepointtechnicalsupport.com/2015/05/06/configuring-sql-server-alwayson-availability-group-powershell/?share=twitter)
 * [  Facebook ](https://sharepointtechnicalsupport.com/2015/05/06/configuring-sql-server-alwayson-availability-group-powershell/?share=facebook)
 * [  WhatsApp ](https://sharepointtechnicalsupport.com/2015/05/06/configuring-sql-server-alwayson-availability-group-powershell/?share=jetpack-whatsapp)
 * [  Email ](https://sharepointtechnicalsupport.com/2015/05/06/configuring-sql-server-alwayson-availability-group-powershell/?subject=%5BShared%20Post%5D%20Configuring%20SQL%20Server%202014%20AlwaysOn%20Availability%20Group%20using%20PowerShell&body=https%3A%2F%2Fsharepointtechnicalsupport.com%2F2015%2F05%2F06%2Fconfiguring-sql-server-alwayson-availability-group-powershell%2F&share=email)
 * [  Telegram ](https://sharepointtechnicalsupport.com/2015/05/06/configuring-sql-server-alwayson-availability-group-powershell/?share=telegram)
 * [  LinkedIn ](https://sharepointtechnicalsupport.com/2015/05/06/configuring-sql-server-alwayson-availability-group-powershell/?share=linkedin)
 * [  Tumblr ](https://sharepointtechnicalsupport.com/2015/05/06/configuring-sql-server-alwayson-availability-group-powershell/?share=tumblr)
 * [  Pinterest ](https://sharepointtechnicalsupport.com/2015/05/06/configuring-sql-server-alwayson-availability-group-powershell/?share=pinterest)

### Like this:

Like Loading…

### _Related_