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

By:   |   Comments   |   Related: > Availability Groups


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?


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,

Launch Windows PowerShell from SSMS

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

Load 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*

Discover the AlwaysOn Availability Groups PowerShell cmdlets

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

Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://ALWAYSON-AG1.TESTDOMAIN.LOCAL:1433" -InputObject "ALWAYSON-AG1.Object"

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.

PS SQLSERVER:\SQL\ALWAYSON-AG1\DEFAULT\AvailabilityGroups\AlwaysOnAG_Prod\AvailabilityReplicas> Get-Item ALWAYSON-AG1 | Format-List *

Display the object properties of the Availability Group replica

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

PS SQLSERVER:\SQL\ALWAYSON-AG1\DEFAULT\AvailabilityGroups\AlwaysOnAG_Prod\AvailabilityReplicas> $primary = Get-Item ALWAYSON-AG1

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

Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://ALWAYSON-AG1.TESTDOMAIN.LOCAL:1433" -InputObject $primary

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.

Set-Location SQLSERVER:\SQL\ALWAYSON-AG1\Default\AvailabilityGroups\AlwaysOnAG_Prod\AvailabilityReplicas

$primary = Get-Item ALWAYSON-AG1
$secondary = Get-Item ALWAYSON-AG2

Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://ALWAYSON-AG1.TESTDOMAIN.LOCAL:1433" -InputObject $primary

Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://ALWAYSON-AG2.TESTDOMAIN.LOCAL:1433" -InputObject $secondary

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

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

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

Comments For This Article

get free sql tips
agree to terms