dsccommunity / dsccommunity/SqlServerDsc

SqlAGReplica: Add round-robin read only routing lists

Open
#1,313 2 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

enhancement help wanted
Dominant language
PowerShell
Stars
385
Forks
224
PR merge metrics
No merged PRs in 30d

Description

Details of the scenario you tried and the problem that is occurring

The SqlAGReplica resource accepts a ReadOnlyRoutingList of type [string[]]. On the SMO side this has a matching ReadOnlyRoutingList property of type StringCollection as that's how these lists were defined for SQL Server 2012 and 2014, and as of the latest patches should all be functional.

In SQL 2016 there's an additional SMO property LoadBalancedReadOnlyRoutingList of type System.Collections.Generic.IList<System.Collections.Generic.IList> and an associated read-only property of LoadBalancedReadOnlyRoutingListDisplayString. This is intended to allow the passing of server groups as follows:

READ_ONLY_ROUTING_LIST = (('Server1','Server2'), ('Server3', 'Server4', 'Server5'), 'Server6')

The question is how the DSC resource should be modified to support this:

  • You could change the type specification on ReadOnlyRoutingList from [string[]] to [object[]] to allow the possibility of passing in either a string array, or array of strings/arrays.
    The code would then decide if it's SQL 2016 and if so use LoadBalancedReadOnlyRoutingList and LoadBalancedReadOnlyRoutingListDisplayString for comparisons (it will be valid for both the old style list and the new style list). If SQL 2012/2014 are detected then ReadOnlyRoutingList and the current string matching will be used (as the newer property would throw an error).
    I think this is the easiest way to handle it but would it be classified as a breaking change? It doesn't actually break anything...
  • Alternately you could add an additional LoadBalancedReadOnlyRoutingList property. I think this is harder for users to use. I also don't know if there are DSC helper tools to add and fix references in all the files or if it all has to be copy-pasted from another property manually.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the SqlAGReplica resource and its ReadOnlyRoutingList property, then compare the SQL Server 2012/2014 ReadOnlyRoutingList behavior with the SQL Server 2016 LoadBalancedReadOnlyRoutingList and LoadBalancedReadOnlyRoutingListDisplayString properties described here. Done means the resource supports grouped round-robin routing on SQL Server 2016 while preserving the existing behavior on older versions.

Written by the indexing model from the issue text.

Assessment

Tech stack
powershell, sql
Domain
databases
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.