Art of the DBA Rotating Header Image

October 20th, 2015:

A Month of SQLPS: Managing Availability Group Replicas

Now that we understand how to prepare our machines for an Availability Group, we can go about the business of creating an Availability Group. This can be fairly involved, but the SQLPS cmdlets can make this a more manageable process. To start the process, we need to look at the components that make up an Availability Group, the replica nodes. Let’s look at the available cmdlets for managing replicas:

Get-Command -Module SQLPS *AvailabilityReplica*


Starting with New-SqlAvailabilityReplica, how we use this cmdlet depends on whether or not we are creating a new Availability Group or adding a replica to a group that already exists. For an existing AG, we need to reference it using either its provider path or its related Smo.AvailabilityGroup object:

New-SqlAvailabilityReplica -Path 'SQLSERVER:\SQL\KIRK\DEFAULT\AvailabilityGroups\ENTERPRISE' -Name SPOCK -EndpointUrl 'TCP://SPOCK:5022' -AvailabilityMode SynchronousCommit -FailoverMode Automatic

Note the path we use, not to be confused with the path of the SQL Server instance. This path should be within the context of the primary node for the AG. Running this command will create the replica.

If we are creating a brand new AG, the process is slightly different. This is because we don’t have an existing AG to reference, we need to create our replicas in memory so they can be referenced later. We need to make use of the -AsTemplate switch, which creates the replica object in memory. We also need to specify what version of SQL replica we’re creating, by using the -Version argument, specifying 11 or 12 for 2012 or 2014 respectively:

$replicas = @()
$replicas += New-SqlAvailabilityReplica -Name SPOCK -EndpointUrl 'TCP://SPOCK:5022' -AvailabilityMode SynchronousCommit -FailoverMode Automatic -AsTemplate -Version 12

Note, with this command we’re adding the output of the cmdlet to a collection. This is our reference for when the new AG is created.

There is one other step required, for both scenarios, to make a replica fully part of the AG: we need to join it using Join-SqlAvailabilityGroup. To do this, run the cmdlet and specify either the provider path for the instance or its Smo.Server object. This completes your replica creation.

In the above examples, we specified the failover, endpoint URL, and availability options. To change these within Powershell, we can use the Set-SqlAvailabilityReplica cmdlet. The trick to this cmdlet is if we want to reference the replica, we need a different provider path to the object:

Set-SqlAvailabilityReplica -Path 'SQLSERVER:\SQL\KIRK\DEFAULT\AvailabilityGroups\Enterprise\AvailabilityReplicas\SPOCK' -FailoverMode Manual

To check on the state of the replica, we can use Test-SqlAvailabilityReplica, which will evaluate the AG Policy Based Management policies and return a special object, PowerShell.Hadr.AvailabilityReplicaHealthState (you can examine this with Get-Member on the Test-SqlAvailabilityReplica output):

Test-SqlAvailabilityReplica -Path 'SQLSERVER:\SQL\KIRK\DEFAULT\AvailabilityGroups\Enterprise\AvailabilityReplicas\SPOCK'


Finally, you can use Remove-SqlAvailabilityReplica by using the same path to the replica:

Remove-SqlAvailabilityReplica -Path 'SQLSERVER:\SQL\KIRK\DEFAULT\AvailabilityGroups\Enterprise\AvailabilityReplicas\SPOCK'

All of these cmdlets are wrappers for T-SQL, which means they all accept -Script to output the T-SQL instead of actually executing the command. Again, it’s the same output as if you had done the action in SSMS and used the script button to examine your output. This is a useful check if you’re learning how to use the module cmdlets to validate that what you’re trying to do is actually what is specified.

There’s a lot involved here to manage your replicas, from all the different cmdlet options to the different provider paths needed to execute the functions properly. A request I would make of the SQLPS team at Microsoft is to provide a series of Get- cmdlets that will accept the name strings and return the appropriate SMO objects, since these cmdlets will accept an -InputObject of the appropriate type. Next up, we’ll cover the actual Availability Group cmdlets, using these functions to create and manage our SQL Availability Groups from the Powershell command line.