SqlAGReplica: 'ReadOnlyRoutingList' is a ReadOnly property

[Zuldan]

Details of the scenario you try and problem that is occurring:

If I change a setting on a replica (bringing it out of desired state), for example FailoverMode then I receive "'ReadOnlyRoutingList' is a ReadOnly property" when performing a consistency check.

VERBOSE: [LABSERVER02]: LCM:  [ End    Resource ]  [[xWaitForAvailabilityGroup]WaitForAvailabilityGroupMyLab]
VERBOSE: [LABSERVER02]: LCM:  [ Start  Resource ]  [[xSQLServerAlwaysOnAvailabilityGroupReplica]SQLAOGroupMyLabReplica]
VERBOSE: [LABSERVER02]: LCM:  [ Start  Test     ]  [[xSQLServerAlwaysOnAvailabilityGroupReplica]SQLAOGroupMyLabReplica]
VERBOSE: [LABSERVER02]:                            [[xSQLServerAlwaysOnAvailabilityGroupReplica]SQLAOGroupMyLabReplica] 2017-04-20_21-12-30: Connected to SQL LABSERVER02
VERBOSE: [LABSERVER02]:                            [[xSQLServerAlwaysOnAvailabilityGroupReplica]SQLAOGroupMyLabReplica] 2017-04-20_21-12-30: 'FailoverMode' should be 'Manual' but is 'Automatic'
VERBOSE: [LABSERVER02]: LCM:  [ End    Test     ]  [[xSQLServerAlwaysOnAvailabilityGroupReplica]SQLAOGroupMyLabReplica]  in 0.5160 seconds.
VERBOSE: [LABSERVER02]: LCM:  [ Start  Set      ]  [[xSQLServerAlwaysOnAvailabilityGroupReplica]SQLAOGroupMyLabReplica]
VERBOSE: [LABSERVER02]:                            [[xSQLServerAlwaysOnAvailabilityGroupReplica]SQLAOGroupMyLabReplica] 2017-04-20_21-12-30: Preferred module SqlServer found.
VERBOSE: [LABSERVER02]:                            [[xSQLServerAlwaysOnAvailabilityGroupReplica]SQLAOGroupMyLabReplica] 2017-04-20_21-12-30: Importing SqlServer module.
VERBOSE: [LABSERVER02]:                            [[xSQLServerAlwaysOnAvailabilityGroupReplica]SQLAOGroupMyLabReplica] 2017-04-20_21-12-30: Connected to SQL LABSERVER02
VERBOSE: [LABSERVER02]:                            [[xSQLServerAlwaysOnAvailabilityGroupReplica]SQLAOGroupMyLabReplica] 2017-04-20_21-12-30: Connected to SQL LABSERVER01
VERBOSE: [LABSERVER02]:                            [[xSQLServerAlwaysOnAvailabilityGroupReplica]SQLAOGroupMyLabReplica] 2017-04-20_21-12-30: Getting the effective permissions for the login 'NT SERVICE\ClusSvc' on 'MSSQLSERVER'.
VERBOSE: [LABSERVER02]:                            [[xSQLServerAlwaysOnAvailabilityGroupReplica]SQLAOGroupMyLabReplica] 2017-04-20_21-12-30: Connected to SQL LABSERVER02
Invoke-CimMethod : 'ReadOnlyRoutingList' is a ReadOnly property.
At C:\Lab\SQLAOAG.ps1:20 char:5
+     Invoke-CimMethod -CimSession $ServerName -Name PerformRequiredCon ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-CimMethod], CimException
    + FullyQualifiedErrorId : PropertyAssignmentException,Microsoft.Management.Infrastructure.CimCmdlets.InvokeCimMethodCommand
    + PSComputerName        : LABSERVER02

The DSC configuration that is using the resource (as detailed as possible):

xSQLServerAlwaysOnAvailabilityGroupReplica "SQLAOGroup$($AG)Replica"
{
    DependsOn                     = "[xWaitForAvailabilityGroup]WaitForAvailabilityGroup$AG"
    Ensure                        = 'Present'
    PsDscRunAsCredential          = $Credential
    Name                          = $Node.NodeName
    AvailabilityGroupName         = $AG
    SQLServer                     = $Node.NodeName
    SQLInstanceName               = $Node.SQLInstanceName
    AvailabilityMode              = 'SynchronousCommit'
    ConnectionModeInPrimaryRole   = 'AllowAllConnections'
    FailoverMode                  = 'Manual'
    PrimaryReplicaSQLServer       = $AGClusterData.PrimaryAG
    PrimaryReplicaSQLInstanceName = $Node.SQLInstanceName
}

Version of the Operating System, SQL Server and PowerShell the DSC Target Node is running:

Server: Windows 2012 R2 (Update)
SQL Server: 2016 SP1 CU2
Powershell: 5.1

What module (SqlServer or SQLPS) and which version of the module the DSC Target Node is running:

SqlServer v20.0

Version of the DSC module you're using, or 'dev' if you're using current dev branch:

7.0.0.0

[Zuldan]

I think I found the root cause. The error only appears if the config on the node changes and it's not the primary. Config for the AOAG can only be changed on a node if the node is primary (Microsoft has set this requirement).

So to replicate this you would:

1. On Node01 (Primary) make a change.
2. Failover the AOAG to Node02 (Secondary)
3. On Node01 (Secondary) perform a consistency check

So I guess we could either say this is by design or the DSC resource should only connect to the Primary node when making changes. What are everyone's thoughts?

[johlju]

I agree. But it looks like the resource do connect to the Primary replica. If it is not the primary replica, it connect to the primary replica. But it seems there is a problem somewhere here. https://github.com/PowerShell/SqlserverDsc/blob/dev/DSCResources/MSFT_SqlAGReplica/MSFT_SqlAGReplica.psm1#L241

But this should be easier to look into when you provided the steps to reproduce the behaviour.

[aalmeida89]

Hello guys, I am having the same problem, but not only on the secondary, but the primary too.
You resolved it ?

DSC configuration:
SqlAGReplica AddReplica
{
Ensure = "Present"
Name = $Node.NodeName + "" + $Node.InstanceName
AvailabilityGroupName = $Node.AGName
ServerName = $Node.NodeName
InstanceName = $Node.InstanceName
PrimaryReplicaServerName = ( $AllNodes | Where-Object { $.Role -eq 'Primary' } ).NodeName
PrimaryReplicaInstanceName = ( $AllNodes | Where-Object { $.Role -eq 'Primary' } ).InstanceName
ReadOnlyRoutingConnectionUrl = 'TCP://' + $Node.NodeName + "." + $ENV:USERDNSDOMAIN + ":31244"
#ReadOnlyRoutingList = 'SQLDsc-2\SQLCore2016'
AvailabilityMode = 'SynchronousCommit'
ConnectionModeInPrimaryRole = 'AllowAllConnections'
ConnectionModeInSecondaryRole = 'AllowAllConnections'
FailoverMode = 'Manual'
PsDscRunAsCredential = $domainSetup
}

ERROR:
'ReadOnlyRoutingList' is a ReadOnly property.
+ CategoryInfo : InvalidOperation: (:) [], CimException
+ FullyQualifiedErrorId : PropertyAssignmentException
+ PSComputerName : SQLDsc-1
'ReadOnlyRoutingList' is a ReadOnly property.
+ CategoryInfo : InvalidOperation: (:) [], CimException
+ FullyQualifiedErrorId : PropertyAssignmentException
+ PSComputerName : SQLDsc-2

Server: Windows 2016
SQL Server: 2016 SP1 CU2
Powershell: 5.1
SQLServerDSC: dev (11.0)

[johlju]

@aalmeida89 The node that is assigned to Name in the above configuration is it your secondary replica?

The target node should connect to the Primary according to this code.

https://github.com/PowerShell/SqlServerDsc/blob/29f2fc48f65fdf44d7e98a11dc0bf78de07db4a0/DSCResources/MSFT_SqlAGReplica/MSFT_SqlAGReplica.psm1#L250-L260

And here it will get the replica (that is assigned to Name).

https://github.com/PowerShell/SqlServerDsc/blob/29f2fc48f65fdf44d7e98a11dc0bf78de07db4a0/DSCResources/MSFT_SqlAGReplica/MSFT_SqlAGReplica.psm1#L306-L311

And here it will set the routing list. I wonder if it will go out to the secondary and try to update it, but it is not allowed becuase it is not running under the primary role. Is it that this setting can only be set when the replica is running under the primary role?

https://github.com/PowerShell/SqlServerDsc/blob/29f2fc48f65fdf44d7e98a11dc0bf78de07db4a0/DSCResources/MSFT_SqlAGReplica/MSFT_SqlAGReplica.psm1#L374-L378

[johlju]

A given read-only routing list takes effect only when the local replica is running under the primary role. This list must be specified on a replica-by-replica basis, as needed.
What Replica Properties Do you Need to Configure to Support Read-Only Routing?

So I assume the bold text above 'must be specified on a replica-by-replica basis, as needed' is meaning that this needs to be set on each replica,

You must be connected to the server instance that hosts the current primary replica.
Prerequisites

And that says, to set this you must be connected to the primary replica. I suppose this means that to be able to set this, the "secondary" must (can) only be configured once it starts to run under the primary role.

Can someone confirm this?

[codykonior]

You modify the read only routing list for each replica (including the primary replica) but only from the primary replica.

It doesn't matter if that replica is primary or secondary.

[codykonior]

I've been testing this out. I have a secondary SEC1N1 and a primary DAC1N1 with an incorrect ReadOnlyRoutingConnectionUrl (say 'tcp://DAC1N2.lab.com:1433').

$configurationData = @{
    AllNodes = @(
        @{
            NodeName = '*'
            PsDscAllowPlainTextPassword = $true
            PsDscAllowDomainUser = $true
        }
        @{
         NodeName = 'localhost'
        }
    )
}

configuration Blah {
    param(
        $Credential
    )
    Import-DscResource -Module SqlServerDsc

    Node $AllNodes.NodeName {    
        SqlAGReplica Blah {
            AvailabilityGroupName = 'AG1'
            ServerName = 'SEC1N1'
            InstanceName = 'MSSQLSERVER'
            Name = 'DAC1N1'
            ReadOnlyRoutingConnectionUrl = 'tcp://DAC1N1.lab.com:1433'
            AvailabilityMode = 'SynchronousCommit'
            FailoverMode = 'Automatic'
            PsDscRunAsCredential = $Credential
        }
    }
}

if (!$credential) { $credential = Get-Credential LAB\LocalAdministrator }
Blah -Credential $credential -ConfigurationData $configurationData
Start-DscConfiguration -Force -Wait -Verbose -Path Blah

The code tries to set the correct URL. In this example I have it connect to SEC1N1 knowing from what you said above the code will redirect it to the primary DAC1N1 (but the error will happen if you directly use DAC1N1 as well). It executes the correct statement which I caught in Profiler.

ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'DAC1N1' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'tcp://DAC1N1.lab.com:1433'))

It does however throw the error (only once, if you run the resource again it will detect the correct setting is in use and not touch it again).

VERBOSE: [SEC1N1]: LCM:  [ Start  Set      ]  [[SqlAGReplica]Blah]
VERBOSE: [SEC1N1]:                            [[SqlAGReplica]Blah] Found PowerShell module SqlServer already imported in the session.
VERBOSE: [SEC1N1]:                            [[SqlAGReplica]Blah] Found PowerShell module SqlServer already imported in the session.
VERBOSE: [SEC1N1]:                            [[SqlAGReplica]Blah] Connected to SQL instance 'SEC1N1'.
VERBOSE: [SEC1N1]:                            [[SqlAGReplica]Blah] Found PowerShell module SqlServer already imported in the session.
VERBOSE: [SEC1N1]:                            [[SqlAGReplica]Blah] Connected to SQL instance 'DAC1N1'.
VERBOSE: [SEC1N1]:                            [[SqlAGReplica]Blah] The recommended account 'NT SERVICE\ClusSvc' is missing one or more of the following permissions: Trying with 'NT AUTHORITY\SYSTEM'.
VERBOSE: [SEC1N1]:                            [[SqlAGReplica]Blah] Getting the effective permissions for the login 'NT AUTHORITY\SYSTEM' on 'MSSQLSERVER'.
VERBOSE: [SEC1N1]:                            [[SqlAGReplica]Blah] Found PowerShell module SqlServer already imported in the session.
VERBOSE: [SEC1N1]:                            [[SqlAGReplica]Blah] Connected to SQL instance 'SEC1N1'.
VERBOSE: [SEC1N1]:                            [[SqlAGReplica]Blah] The cluster login 'NT AUTHORITY\SYSTEM' has the required permissions.
'ReadOnlyRoutingList' is a ReadOnly property.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : PropertyAssignmentException
    + PSComputerName        : localhost
 
VERBOSE: [SEC1N1]: LCM:  [ End    Set      ]  [[SqlAGReplica]Blah]  in 0.8940 seconds.
The PowerShell DSC resource '[SqlAGReplica]Blah' with SourceInfo '::21::9::SqlAGReplica' threw one or more non-terminating errors while running the Set-TargetResource functionality. These errors are logged to the ETW channel 
called Microsoft-Windows-DSC/Operational. Refer to this channel for more details.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : NonTerminatingErrorFromProvider
    + PSComputerName        : localhost
 
VERBOSE: [SEC1N1]: LCM:  [ End    Set      ]
The SendConfigurationApply function did not succeed.
    + CategoryInfo          : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
    + FullyQualifiedErrorId : MI RESULT 1
    + PSComputerName        : localhost
 
VERBOSE: Operation 'Invoke CimMethod' complete.
VERBOSE: Time taken for configuration job to complete is 3.673 seconds

So I've been able to reproduce it and add information but not find the source of the bug yet.

[codykonior]

Ok I've got it.

The problem is that EVERY time the Set resource gets called, if no read only routing list has been set up, it will trigger anyway.

                    if ( $ReadOnlyRoutingList -ne $availabilityGroupReplica.ReadOnlyRoutingList )
                    {
                        $availabilityGroupReplica.ReadOnlyRoutingList = $ReadOnlyRoutingList
                        Update-AvailabilityGroupReplica -AvailabilityGroupReplica $availabilityGroupReplica
                    }

This is because the comparison is invalid - ReadOnlyRoutingList is a StringCollection not a string. So the comparison always fails and it tries to modify ReadOnlyRoutingList - but it can't because it's read only. The proper way to add URLs would be to call .Clear() and then .Add() each server in the list.

Though this is only valid for 2012-2014. In 2016 there's a newer mechanism to allow for read only routing sets.