Gathering Read Routing information using SMO is inaccurate in SQL Server 2016
7
votes
1
answer
228
views
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();
}
}
}
Asked by Nic
(4063 rep)
Aug 2, 2017, 06:22 PM
Last activity: Feb 13, 2018, 05:45 PM
Last activity: Feb 13, 2018, 05:45 PM