SqlAGReplica: Tries to modify AG properties that were not specified

[codykonior]

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

I'm trying to use SQLAGReplica to modify a property (ReadOnlyRoutingConnectionUrl) on an AG after it has been created.

This fails because the SQLAGReplica resource sets two default properties:

• AvailabilityMode = AsynchronousCommit
• FailoverMode = Manual

This means it tries to set them every time you access the AG even though you may only want to modify one property.

Verbose logs showing the problem

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.
VERBOSE: [SEC1N1]: LCM:  [ End    Set      ]  [[SqlAGReplica]Blah]  in 3.0580 seconds.
PowerShell DSC resource MSFT_SqlAGReplica  failed to execute Set-TargetResource functionality with error message: System.InvalidOperationException: Failed to alter the availability group replica 'DAC1N1'. ---> 
System.Management.Automation.MethodInvocationException: Exception calling "Alter" with "0" argument(s): "Alter failed for Availability Replica 'DAC1N1'. " ---> Microsoft.SqlServer.Management.Smo.FailedOperationException: Alter 
failed for Availability Replica 'DAC1N1'.  ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: 
The Alter operation is not allowed.  The operation attempted to change the configuration of availability replica 'DAC1N1' to the asynchronous-commit availability mode with automatic failover, which is an invalid configuration.  
Either change the failover mode to manual or the availability mode to synchronous commit, and retry the operation.
   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType, Boolean retry)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries, Boolean retry)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplFinish(StringCollection alterQuery, ScriptingPreferences sp)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImpl()
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImpl()
   at Microsoft.SqlServer.Management.Smo.AvailabilityReplica.Alter()
   at CallSite.Target(Closure , CallSite , AvailabilityReplica )
   --- End of inner exception stack trace ---
   at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
   at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
   --- End of inner exception stack trace --- 
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + 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 5.901 seconds

Suggested solution to the issue

The defaults on the parameters should be removed. If the resource detects it needs to add an AG Replica it can check for nulls in these properties and then set the same defaults. But if the replica is being modified, the existing settings won't be changed for these because there's no default set.

The DSC configuration that is used to reproduce the issue (as detailed as possible)

$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

SQL Server edition and version the target node is running

2012

SQL Server PowerShell modules present on the target node

Current

The operating system the target node is running

2012

Version and build of PowerShell the target node is running

WMF 5.1

Version of the DSC module that was used ('dev' if using current dev branch)

dev

[johlju]

Agree that is should be able to set these properties individually, labeling it as a bug.

[fculpo]

Hi,

Any news on this ?
I have the smae issue, each time i replay the dsc module the Alter on AG fails

[codykonior]

I have a PR in to fix database ownership; after that's merged I will resubmit my PR for read-only routing which fixes (at least some of) this as part of it. So not long now.