Configure SQL Server 2012 AlwaysOn Availability Groups Read-Only Routing using PowerShell

By: Edwin Sarmiento   |   Updated: 2013-03-27   |   Comments   |   Related: > Availability Groups

Problem

In a previous tip on Configure SQL Server 2012 AlwaysOn Availability Group Read-Only Routing using T-SQL, we have seen how we can configure read-only routing and to automatically redirect the read-only workloads to a secondary replica after a failover. Can this be done using Windows PowerShell?

Solution

As Windows PowerShell has been tightly integrated with SQL Server 2012, more cmdlets have been added that can be used to configure AlwaysOn Availability Groups. Now, if you're like me, you probably won't memorize every single PowerShell cmdlet that pertains to AlwaysOn Availability Groups. Fortunately, we can use the Get-Command cmdlet to explore the available cmdlets.

To start off, we can either open the Windows PowerShell console from SQL Server Management Studio by right-clicking on any node,

or by importing the SQLPS module from the native Windows PowerShell environment

Once the SQLPS module has been loaded, you can use the Get-Command cmdlet to explore the different cmdlets that you can use to configure AlwaysOn Availability Groups.

PS SQLSERVER:\> Get-Command -Module SQLPS -Name *SqlAvailability*

The one that we are interested in configuring read-only routing is the Set-SqlAvailabilityReplica cmdlet.

Enable Read-Only Routing using Windows PowerShell

As mentioned in the previous tip, we need to provide a read_only_routing_url on all of the replicas that we will be configuring for read-only routing, and a read-only routing list for each replica that will act as primary. The parameter values that we have provided in the previous tip will be the same ones that we will be using for the examples provided below.

An example of using the Set-SqlAvailabilityReplica cmdlet is shown below

There are only two parameters that we need for this cmdlet. Obviously, the -ReadOnlyRoutingConnectionUrl parameter will be our read_only_routing_url whereas the -InputObject parameter is a parameter value that is an object of type Microsoft.SqlServer.Management.Smo.AvailabilityReplica.

I know it's a bit confusing but simply put, we are just passing the AvailabilityGroup replica as an object to the -InputObject parameter instead of a string value. You can always explore the different properties of the AvailabilityReplicas collection by displaying all of them in a tabular list. You can navigate to the AvailabilityReplicas node of the object hierarchy under AvailabilityGroups. Alternatively, you can open the Windows PowerShell console from within one of the Availability Group replicas using SQL Server Management Studio. Keep in mind that the name of my AlwaysOn Availability Group is AlwaysOnAG_Prod and the SQL Server instance on which I am running this on is ALWAYSON-AG1.

We need to assign this object to a variable so that we can pass it to the -InputObject parameter.

Once we have the object assigned to a variable, we can now use it in the Set-SqlAvailabilityReplica cmdlet as shown below.

This needs to be done on all of the replicas on your Availability Group. After configuring the read_only_routing_url, we need to provide a read-only routing list for each replica that will act as primary. We will then use the -ReadOnlyRoutingList parameter to provide a list of the replicas. An example of using the -ReadOnlyRoutingList parameter is shown below

Set-SqlAvailabilityReplica -ReadOnlyRoutingList "ALWAYSON-AG2" -InputObject $primary

The command above simply assigns the replica ALWAYSON-AG2 in the read-only routing list to the primary replica ALWAYSON-AG1, represented by the object $primary.

A complete PowerShell script for my 2-node Availability Group configuration is shown below.

If you're keen on improving the script even further, you can iterate thru the list of replicas and generate the Set-SqlAvailabilityReplica command dynamically.

Set-Location SQLSERVER:\SQL\ALWAYSON-AG1\Default\AvailabilityGroups\AlwaysOnAG_Prod\AvailabilityReplicas
foreach($replicas in Get-ChildItem)
{ 
     #Assign the Name property value to a variable for easier assignment
 $replica = $replicas.Name
 
 #Dynamically generate the Set-SqlAvailabilityReplica command to configure the read_only_routing_url
 Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://$replica.testdomain2.local:1433" 
    -InputObject $replicas 
}

The section on configuring the read-only routing list is a bit challenging for me. If I need to iterate thru the list of replicas and check based on their role (for example, using the ($replicas.Role -eq "Primary") condition) I would need to pass the variables in an array for later access. This is because the -ReadOnlyRoutingList parameter requires both the primary and secondary replicas. Either one of them might not be readily available just yet as we are iterating thru the list of replicas. I would like to see how you would approach this scenario.

Next Steps

• Review this tip on Configure SQL Server 2012 AlwaysOn Availability Groups Read-Only Routing using T-SQL
• Take a look at this PowerShell example on MSDN for configuring AlwaysOn Availability Groups Read-Only Routing for more details.
• Configure your AlwaysOn Availability Groups with readable secondary's and read-only replica to see if this fits your high availability strategies.

About the author

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