7

I've been playing around with SMO to check out some properties recently, and have come across a problem when trying to gather information on the read-routing order in SQL Server 2016.

With earlier versions the routing list was quite simple, the list would be processed in order presented, but with 2016 they introduced the round-robin algorithm allowing you to have multiple secondary replicas accept the read traffic.

While this data is accessible through T-SQL it does not appear as though SMO has been updated to reflect this, which means that you cannot accurately get, or set the configuration that way as it is still a simple string collection.

Is there some attribute that I am missing that would allow me to get accurate data for SQL Server 2016? (example SMO call for C# below)

It is just the round-robin info that does not seem to be accessible, it lists all of the replicas that are in the read-routing list, it just does not seem to differentiate the various groups of replicas that would exist.

Connect bug report. Connect is dead...

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace SmoTesting
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Enter the servername");
            string connectServer = Console.ReadLine();
            Console.WriteLine("Enter the AG name");
            string agName = Console.ReadLine();

            Server srv = new Server();

            try
            {
                srv = new Server(connectServer);
                srv.ConnectionContext.StatementTimeout = 60; //timeout after 60 seconds running the query

                foreach (AvailabilityGroup ag in srv.AvailabilityGroups)
                {
                    if (ag.Name == agName)
                    {
                        ag.PrimaryReplicaServerName.ToString());
                        foreach (AvailabilityReplica ar in ag.AvailabilityReplicas)
                        {
                            if (ar.Name.ToString() == "connectServer")
                            {
                                foreach (Object obj in ar.ReadonlyRoutingList)
                                {
                                    Console.WriteLine("  {0}", obj);
                                }
                            }
                        }
                    } 
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.InnerException.ToString());
            }
            finally
            {
                srv.ConnectionContext.Disconnect();
            }

            Console.WriteLine("press a key");
            Console.Read();
        }
    }
}
0

1 Answer 1

6
+75

The issue was fixed in SSMS 17.3, however you'll need to load the "applocal" assembly and not the one from the GAC. In addition, in order to be "backwards compatible", the ReadOnlyRoutingList property will stay a string and act/behave just like it did before. The newly added LoadBalancedReadOnlyRoutingList (and a getter/setter with it) will have the correct representation you're looking for.

Connect is dead...

Long live Connect!

Code Repro (cut up for readability) To See New Properties and Methods:

#Load the Applocal assembly from SSMS 17.4
[System.Reflection.Assembly]::LoadFrom('C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Microsoft.SqlServer.SMO.dll')

#Create a new server instance hitting the listener named SQL2016Listen
$SI = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "SQL2016Listen"

#Get the list of Availability Replicas for an Availability Group called "SeedingTest"
$ARs = $SI.AvailabilityGroups | where {$_.Name -eq "SeedingTest"} | select AvailabilityReplicas

#Grab the first replica in the list of replicas and show all of the members that have "load" somewhere in the name
$ARs.AvailabilityReplicas[0] | get-member | where {$_.name -like "*load*"} | select name

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.