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.
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.
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
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.
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"
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.