Create and Configure SQL Server 2016 Always On Availability Groups using Windows PowerShell

By:   |   Updated: 2017-08-08   |   Comments (7)   |   Related: > Availability Groups


Problem

In my previous tip on New SQL Server AlwaysOn Feature - Part 1 configuration, we have seen how we can configure Always On Availability Groups using SQL Server Management Studio. I would like to automate the process of creating the Availability Group. Can this be done using Windows PowerShell?

Solution

Leveraging Windows PowerShell to automate SQL Server administrative tasks is not new. The SQL Server PowerShell modules have been available since SQL Server 2008. As new features were added to the product, the corresponding modules get updated accordingly. Creation of SQL Server Availability Groups is no exception and there are cmdlets specifically for creating and configure AlwaysOn Availability Groups. This tip will walk you thru the process of creating and configuring SQL Server Availability Groups using Windows PowerShell. You can leverage the scripts whether you are deploying strictly on-premises or deploying to the cloud.

Here’s a high-level overview of the steps for your reference. I’m a big fan of defining processes. The success or failure of an automation framework rely so much on how you define your process. Hence, the overview of the steps:

  1. Enable the SQL Server Always On Availability Groups feature (on all replicas)
  2. Create the Availability Group endpoint (on all replicas)
  3. Start the Availability Group endpoint (on all replicas)
  4. Create login and grant CONNECT permissions to the SQL Server service account (on all replicas)
  5. Create the Availability Group replicas objects
  6. Create the Availability Group (on the primary replica)
  7. Join the secondary replicas and databases to the Availability Group
  8. Create the Availability Group listener name (on the primary replica)

There are several assumptions made throughout this tip:

  • The Windows Server Failover Cluster has already been created and joined to an Active Directory domain. Refer to this series of tips on Installing, Configuring and Managing Windows Server Failover Cluster using PowerShell for more details (Part 1, Part 2, Part 3, Part 4)
  • The SQL Server instances used as Availability Group replicas have been installed as default instances
  • Full and log backups of the databases on the primary replica have been taken and restored in the NORECOVERY mode on the secondary replicas in preparation for the data synchronization process
  • The Active Directory domain account that you are using to create and configure the SQL Server Availability Group is a member of the local Administrators group on all of the failover cluster nodes and the sysadmin role on the SQL Server instances

Step #1: Enable the SQL Server Always On Availability Groups feature (on all replicas)

Use the Enable-SqlAlwaysOn PowerShell cmdlet to enable the SQL Server Always On Availability Groups feature on all of the replicas. The -Force parameter will restart the SQL Server service without prompting for confirmation.

Enable-SqlAlwaysOn -ServerInstance INSTANCENAME -Force

Step #2: Create the Availability Group endpoint (on all replicas)

Use the New-SqlHADREndpoint PowerShell cmdlet to create the Availability Group endpoints on all of the replicas, using Endpoint_AG as the name with 5022 as port number. The endpoints will use the AES algorithm for encryption and will require the Availability Group connection to use encryption. 

New-SqlHADREndpoint -Path "SQLSERVER:\SQL\SERVERNAME\Default" -Name "Endpoint_AG" -Port 5022 -EncryptionAlgorithm Aes -Encryption Required

Step #3: Start the Availability Group endpoint (on all replicas)

Unlike when using T-SQL to create an Availability Group endpoint and starting it immediately, you have to use the  Set-SqlHADREndpoint PowerShell cmdlet to start the Availability Group endpoints on all of the replicas. The -State parameter sets the state of the endpoint. 

Set-SqlHADREndpoint -Path "SQLSERVER:\SQL\SERVERNAME\Default\Endpoints\Endpoint_AG" -State Started

Step #4: Create login and grant CONNECT permissions to the SQL Server service account

Because the SQL Server service account will impersonate the SQL Server instance when connecting to the replicas – from primary to secondary and vice versa – you need to create it as a SQL Server login and grant it the CONNECT permissions to the endpoint. The same SQL Server service account is used for all of the Availability Group replicas. Unfortunately, there is no corresponding PowerShell cmdlet to achieve this so you need to use T-SQL and run it using the Invoke-SqlCmd PowerShell cmdlet. 

#Create the T-SQL commands
$createLogin = “CREATE LOGIN [TESTDOMAIN\sqlservice] FROM WINDOWS;” 
$grantConnectPermissions = “GRANT CONNECT ON ENDPOINT::Endpoint_AG TO [TESTDOMAIN\sqlservice];”

#Run the T-SQL commands using Invoke-SqlCmd
Invoke-SqlCmd -ServerInstance SERVERNAME -Query $createLogin
Invoke-SqlCmd -ServerInstance SERVERNAME -Query $grantConnectPermissions

Step #5: Create the Availability Group replicas objects

Creating the Availability Group requires assigning replicas. To do this, you can use the New-SqlAvailabilityReplica PowerShell cmdlet to create an Availability Group replica. You can either create the Availability Group first before creating the replicas or create the replicas as template objects first and, then, pass them as parameters when creating the Availability Group.  This example will create the replicas first as template objects, configuring them with the endpoint URL and port number, synchronous replication and automatic failover. The -AsTemplate parameter creates the replica as a template object. The -Version parameter is the SQL Server major version number: 11 for SQL Server 2012, 12 for SQL Server 2014 and 13 for SQL Server 2016.

#Create the primary replica as a template object
$primaryReplica = New-SqlAvailabilityReplica -Name SERVERNAME-NODE1 -EndpointUrl “TCP://SERVERNAME-NODE1.TESTDOMAIN.COM:5022” -AvailabilityMode “SynchronousCommit” -FailoverMode 'Automatic' -AsTemplate -Version 13  

#Create the secondary replica as a template object
$secondaryReplica = New-SqlAvailabilityReplica -Name SERVERNAME-NODE2 -EndpointUrl “TCP://SERVERNAME-NODE2.TESTDOMAIN.COM:5022” -AvailabilityMode “SynchronousCommit” -FailoverMode 'Automatic' -AsTemplate -Version 13 

Step #6: Create the Availability Group (on the primary replica)

After the Availability Group replicas have been created as template objects, you can use the New-SqlAvailabilityGroup PowerShell cmdlet to create the Availability Group. The -InputObject parameter specifies the SQL Server instance acting as the primary replica. The -AvailabilityReplica parameter specifies an array of Availability Group replica objects created using the template objects. The -Database parameter specifies an array of database names that you want to add to the Availability Group. 

#Create the Availability Group
New-SqlAvailabilityGroup -InputObject SERVERNAME -Name "AG_Prod" -AvailabilityReplica ($primaryReplica, $secondaryReplica) -Database @("Northwind","pubs")

Step #7: Join the secondary replicas and databases to the Availability Group

Interestingly, even when you provide the list of replicas to the New-SqlAvailabilityGroup PowerShell cmdlet, it still does not add all of the secondary replicas. You still need to use the Join-SqlAvailabilityGroup PowerShell cmdlet to accomplish this task. 

#Join replicas to the Availability Group
Join-SqlAvailabilityGroup -Path “SQLSERVER:\SQL\SERVERNAME\Default” -Name “AG_Prod”

In addition to adding the secondary replica, you also need to add the database on the secondary replicas to the Availability Group. Use the Add-SqlAvailabilityDatabase PowerShell cmdlet to accomplish this task. 

#Join database in the secondary replicas to the Availability Group
Add-SqlAvailabilityDatabase -Path "SQLSERVER:\SQL\WSFC2016-NODE2\Default\AvailabilityGroups\AG_Prod" -Database "Northwind"

Step #8: Create the Availability Group listener name (on the primary replica)

Proceed to create the Availability Group listener name after creating the Availability group. Use the New-SqlAvailabilityGroupListener PowerShell cmdlet to accomplish this task, specifying the listener name, the static IP address and the port number. 

#Create the Availability Group listener name
New-SqlAvailabilityGroupListener -Name "AGListenerName" -staticIP "172.16.0.45/255.255.0.0" -Port 1433 -Path "SQLSERVER:\SQL\SERVERNAME\DEFAULT\AvailabilityGroups\AG_Prod"

A sample PowerShell script to perform steps #1 to #8 is shown below. In the example script, a 2-node Windows Server Failover Cluster named WSFC2016-NODE1 and WSFC2016-NODE2 running standalone default SQL Server instances is used. WSFC2016-NODE1 is used as the primary replica. 

#Variable for an array object of Availability Group replicas
$replicas = @()

#Variable for T-SQL command
$createLogin = "CREATE LOGIN [TESTDOMAIN\sqlservice] FROM WINDOWS; " 
$grantConnectPermissions = “GRANT CONNECT ON ENDPOINT::Endpoint_AG TO [TESTDOMAIN\sqlservice];” 


#List all of the WSFC nodes all WSFC nodes; all SQL Server instances run DEFAULT instances
foreach($node in Get-ClusterNode)
{
   #Step 1: Enable SQL Server Always On High Availability feature
   Enable-SqlAlwaysOn -ServerInstance $node -Force 

   #Step 2: Create the Availability Group endpoints
   New-SqlHADREndpoint -Path "SQLSERVER:\SQL\$node\Default" -Name "Endpoint_AG" -Port 5022 -EncryptionAlgorithm Aes -Encryption Required 

   #Step 3: Start the Availability Group endpoint
   Set-SqlHADREndpoint -Path "SQLSERVER:\SQL\$node\Default\Endpoints\Endpoint_AG" -State Started
   
   #Step 4: Create login and grant CONNECT permissions to the SQL Server service account
   Invoke-SqlCmd -Server $node.Name -Query $createLogin
   Invoke-SqlCmd -Server $node.Name -Query $grantConnectPermissions 

   #Step 5: Create the Availability Group replicas as template objects
    $replicas += New-SqlAvailabilityReplica -Name $node -EndpointUrl "TCP://$node.TESTDOMAIN.COM:5022" -AvailabilityMode "SynchronousCommit" -FailoverMode "Automatic" -AsTemplate -Version 13 
}

#Step 6: Create the Availability Group, replace SERVERNAME with the name of the primary replica instance
New-SqlAvailabilityGroup -InputObject "WSFC2016-NODE1" -Name "AG_Prod" -AvailabilityReplica $replicas -Database @("Northwind")

#Step 7: Join the secondary replicas and databases to the Availability Group
Join-SqlAvailabilityGroup -Path “SQLSERVER:\SQL\WSFC2016-NODE2\Default” -Name “AG_Prod”
Add-SqlAvailabilityDatabase -Path "SQLSERVER:\SQL\WSFC2016-NODE2\Default\AvailabilityGroups\AG_Prod" -Database "Northwind" 

#Step 8: Create the Availability Group listener name (on the primary replica) 
New-SqlAvailabilityGroupListener -Name "AGLN" -staticIP "172.16.0.45/255.255.0.0" -Port 1433 -Path "SQLSERVER:\Sql\WSFC2016-NODE1\DEFAULT\AvailabilityGroups\AG_Prod"

You would need to modify the script when deploying Availability Groups with more than two replicas.

In this tip, you’ve learned how to create and configure a SQL Server Availability Group using Windows PowerShell. You can use this framework to automate your future deployments.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2017-08-08

Comments For This Article




Wednesday, August 7, 2019 - 5:32:04 PM - bass_player Back To Top (81997)

vivek,

I'm not a big fan of automatic seeding due to the fact that it can saturate your network bandwidth, especially for VLDBs. Always test and use with caution.

Should you wish to do so, just add the -SeedingMode parameter to the New-SqlAvailabilityReplica PowerShell cmdlet. Set the value to Automatic.

Wednesday, August 7, 2019 - 3:38:45 AM - vivek garg Back To Top (81990)

How can we include Automatic seeding Part in powershell? Adding Automatic seeding will remove few steps of Restoring Database onto secondary Instance and then joining it to AOAG.

Thursday, May 24, 2018 - 9:11:08 PM - Sam Back To Top (76021)

 GREAT POST, can't thank you enough.. The one and only step I had a hard time with is the -path for the endpoint... After some troubleshooting, I learned that if let's say you have an instance name called SQLSrvr\Instance1, then the path value is: 

-Path "SQLSERVER:\SQL\SQLSrvr\Instance1"

Similarly, for step create replica the name value is the instance name:

#Create the primary replica as a template object
$primaryReplica = New-SqlAvailabilityReplica -Name SQLSrvr\Instance1

 

Thursday, August 10, 2017 - 10:45:40 AM - bass_player Back To Top (64473)

Thanks for reading, Ted.

What do you mean by testing Availability Groups using HADR cmdlets? Are you referring to failover testing? Application connectivity testing?

Thursday, August 10, 2017 - 10:43:51 AM - bass_player Back To Top (64472)

 Thanks for pointing out the typo, Rob. This has been fixed.

 

Tuesday, August 8, 2017 - 1:24:12 PM - Ted Higgins Back To Top (64328)

Edward:

Great tip!  I'm guessing that we should use try ... catch logic in our own implementation[s] of the code.

Is there a way to test the AOAG using the HADR cmdlets?  If so, will you be demonstrating this in future tips?

Thanks for all the great info on HADR! 

 

Tuesday, August 8, 2017 - 8:42:01 AM - Robert Volk Back To Top (64306)

Hi Edwin-

There's a typo for the Step 7 scripts, the first snippet is repeated for the Add-SqlAvailabilityDatabase cmdlet example.

 














get free sql tips
agree to terms