Art of the DBA Rotating Header Image

A Month of SQLPS: Availability Group cmdlets

In the previous post, we covered how to create and manipulate Availability Group replicas. Assembling these replicas are necessary for building and using SQL Server’s AlwaysOn technology for high availability. Now it is on to the cmdlets in the SQLPS module that actually create the Availability Groups themselves. As always, we’ll start with looking at the cmdlets in the module:

Get-Command -Module SQLPS *SqlAvailabilityGroup


We talked about Join-SqlAvailabilityGroup in relation to the cmdlets for managing replicas. This cmdlet is important because while we can create replicas, they still need to be joined to the Availability Group. Calling this cmdlet is simply a matter of passing the provider path or the Smo.Server object that represents the node, then the Availability Group name:


This is necessary once the Availability Group is created, but what about actually creating it in the first place? For that we have the New-SqlAvailabilityGroup cmdlet. To use this, we need a couple things: the name of the Availability Group, the path or SMO.Server object for the primary node, and a collection of replicas.

New-SqlAvailabilityGroup -Name ENTERPRISE -Path SQLSERVER:\SQL\KIRK\DEFAULT -AvailabilityReplica $replicas

Again, even if we create this with the cmdlet, we still have to join the replicas. Make sure you keep that gotcha in mind. Otherwise, this is all we need to get a basic Availability Group up and running. Doesn’t seem hard, does it? Really, the bulk of the work is done by creating the replica objects.

If we want to change any settings for the Availability Group, we would use SqlSet-AvailabilityGroup. This is for changing settings like backup preference, failure condition levels, and the AG health check timeout. Now that an AG exists, the cmdlet requires the Smo.AvailabilityGroup object or the provider path for that AG:

Set-SqlAvailabilityGroup -Path SQLSERVER:\SQL\KIRK\DEFAULT\AvailabilityGroups\ENTERPRISE -AutomatedBackupPreference Secondary

Calling Remove-SqlAvailabilityGroup will destroy the AG with no extra action required:

Remove-SqlAvailabilityGroup -Path SQLSERVER:\SQL\KIRK\DEFAULT\AvailabilityGroups\ENTERPRISE

This is all pretty useful, but the strength of Availability Groups is fast failover to another node. Normally, to accomplish this you would connect to the node using SQL Server Management Studio and running the appropriate ALTER AVAILABILITY GROUP command. The Switch-SqlAvailabilityGroup cmdlet saves us a little bit of extra effort by avoiding the step of logging into the SQL Server. We can just call the cmdlet and specify the AG path within the node we want to fail to:

Switch-SqlAvailabilityGroup -Path SQLSERVER:\SQL\SPOCK\DEFAULT\AvailabilityGroups\ENTERPRISE

It’s important to note the difference in the path. Just as we want to run the SQL from the target node for the failover, we need to use the provider path for that target node.

We know the Test-SqlAvailabilityReplica will evaluate some Policy Based Management checks, and so will Test-SqlAvailabilityGroup. We can pass the usual arguments and get back a PowerShell.Hadr.AvailabilityGroupHealthState object with a HealthState property.

By covering these cmdlets, we know have a good foundation for how to get started with Availability Groups and Powershell. There are two other steps we need to complete our fully functioning AG: creating a listener and deploying a database. The next few posts will go over those cmdlets in detail, so you can understand the best way to automate the creation and management of this great piece of SQL Server technology.

Leave a Reply

Your email address will not be published. Required fields are marked *