SqlAgReplica: Failure to change AvailabilityMode and FailoverMode

[ShawnHardwick]

Problem description

For an AG replica where configured with AvailabilityMode to SynchronousCommit and FailoverMode set to Automatic, if the SqlAgReplica resource is requested to change the AvailabilityMode to 'AsynchronousCommit' and FailoverMode to Manual, then the resource returns An internal error occurred.

Verbose logs

VERBOSE: Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = Resourceset,'className' = MSFT_DSCLocalConfigurationManager,'namespaceName' = roo
t/Microsoft/Windows/DesiredStateConfiguration'.
VERBOSE: An LCM method call arrived from computer server1 with user sid S-1-5-21-577582919-1435025626-1914702595-3668828.
VERBOSE: [server1]: LCM:  [ Start  Set      ]  [[SqlAGReplica]DirectResourceAccess]
VERBOSE: [server1]:                            [[SqlAGReplica]DirectResourceAccess] Preferred module SqlServer found. (SQLCOMMON0023)
VERBOSE: [server1]:                            [[SqlAGReplica]DirectResourceAccess] Importing PowerShell module 'SqlServer' with version '21.1.18221' from path 'C:\
Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18221\SqlServer.psm1'. (SQLCOMMON0025)
VERBOSE: [server1]:                            [[SqlAGReplica]DirectResourceAccess] Found PowerShell module SqlServer already imported in the session. (SQLCOMMON002
6)
VERBOSE: [server1]:                            [[SqlAGReplica]DirectResourceAccess] Connecting as current user 'NAM\puppet_ag' using integrated security. (SQLCOMMON
0054)
VERBOSE: [server1]:                            [[SqlAGReplica]DirectResourceAccess] Connected to SQL instance 'server1.nam.ent.duke-energy.com\DEV'. (SQLCOMMO
N0018)
VERBOSE: [server1]:                            [[SqlAGReplica]DirectResourceAccess] Getting the effective permissions for the login 'NT SERVICE\ClusSvc' on 'DEV'. (
SQLCOMMON0048)
VERBOSE: [server1]:                            [[SqlAGReplica]DirectResourceAccess] Found PowerShell module SqlServer already imported in the session. (SQLCOMMON002
6)
VERBOSE: [server1]:                            [[SqlAGReplica]DirectResourceAccess] Connecting as current user 'domain1\user1' using integrated security. (SQLCOMMON
0054)
VERBOSE: [server1]:                            [[SqlAGReplica]DirectResourceAccess] Connected to SQL instance 'server1\DEV'. (SQLCOMMON0018)
VERBOSE: [server1]:                            [[SqlAGReplica]DirectResourceAccess] Returning the results of the query `
            EXECUTE AS LOGIN = 'NT SERVICE\ClusSvc'
            SELECT DISTINCT permission_name
            FROM fn_my_permissions(null,'SERVER')
            REVERT
        `. (SQLCOMMON0057)
VERBOSE: [server1]:                            [[SqlAGReplica]DirectResourceAccess] The recommended account 'NT SERVICE\ClusSvc' is missing one or more of the follo
wing permissions: Connect SQL, Alter Any Availability Group, View Server State (SQLCOMMON0052)
VERBOSE: [server1]:                            [[SqlAGReplica]DirectResourceAccess] Getting the effective permissions for the login 'NT AUTHORITY\SYSTEM' on 'DEV'. 
(SQLCOMMON0048)
VERBOSE: [server1]:                            [[SqlAGReplica]DirectResourceAccess] Found PowerShell module SqlServer already imported in the session. (SQLCOMMON002
6)
VERBOSE: [server1]:                            [[SqlAGReplica]DirectResourceAccess] Connecting as current user 'domain1\user1' using integrated security. (SQLCOMMON
0054)
VERBOSE: [server1]:                            [[SqlAGReplica]DirectResourceAccess] Connected to SQL instance 'server1\DEV'. (SQLCOMMON0018)
VERBOSE: [server1]:                            [[SqlAGReplica]DirectResourceAccess] Returning the results of the query `
            EXECUTE AS LOGIN = 'NT AUTHORITY\SYSTEM'
            SELECT DISTINCT permission_name
            FROM fn_my_permissions(null,'SERVER')
            REVERT
        `. (SQLCOMMON0057)
VERBOSE: [server1]:                            [[SqlAGReplica]DirectResourceAccess] The cluster login 'NT AUTHORITY\SYSTEM' has the required permissions. (SQLCOMMON
0053)
VERBOSE: [server1]: LCM:  [ End    Set      ]  [[SqlAGReplica]DirectResourceAccess]  in 13.2660 seconds.
An internal error occurred. 
    + CategoryInfo          : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
    + FullyQualifiedErrorId : HRESULT 0x8007054f
    + PSComputerName        : localhost

VERBOSE: Operation 'Invoke CimMethod' complete.
VERBOSE: Time taken for configuration job to complete is 21.42 seconds
{"rebootrequired":null,"indesiredstate":true,"errormessage":""}

DSC configuration

$script:ErrorActionPreference = 'Stop'
$script:WarningPreference = 'SilentlyContinue'
function new-pscredential{
[CmdletBinding()]
param (
[parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$true)]
[string]$user,
[parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$true)]
[string]$password
)
$secpasswd = ConvertTo-SecureString $password -AsPlainText -Force
$credentials = New-Object System.Management.Automation.PSCredential ($user, $secpasswd)
return $credentials
}
$response = @{
indesiredstate = $false
rebootrequired = $false
errormessage = ''
}
$invokeParams = @{
Name = 'SqlAGReplica'
ModuleName = 'SqlServerDsc'
Method = 'set'
Property = @{
'availabilitymode' = 'AsynchronousCommit';
'connectionmodeinprimaryrole' = 'AllowAllConnections';
'connectionmodeinsecondaryrole' = 'AllowAllConnections';
'failovermode' = 'Manual';
'ensure' = 'present';
'name' = 'server1\DEV';
'availabilitygroupname' = 'AGAGTD01';
'servername' = 'server1.domain.com';
'instancename' = 'DEV';
'primaryreplicaservername' = 'DBAGTD01';
'primaryreplicainstancename' = 'DEV';
'endpointhostname' = 'server1.domain.com';
'psdscrunascredential' = ([PSCustomObject]@{
'user' = 'domain1\user1';
'password' = '[REDACTED]'
} | new-pscredential)
}
}
try{
$result = Invoke-DscResource @invokeParams -verbose
}catch{
$response.errormessage = $_.Exception.Message
return ($response | ConvertTo-Json -Compress)
}
# keep the switch for when Test passes back changed properties
switch($invokeParams.Method){
'Test'{
$response.indesiredstate = $result.InDesiredState
return ($response | ConvertTo-Json -Compress)
}
'Set'{
$response.indesiredstate = $true
$response.rebootrequired = $result.RebootRequired
return ($response | ConvertTo-Json -Compress)
}
}

Suggested solution

AvailabilityMode cannot be set to AsynchronousCommit when FailoverMode set to Automatic. The SqlAgReplica resource currently updates each property in alphabetical order with individual alter() commends (it seems). This means that it attempts to alter the AvailabilityMode before modifying the FailoverMode.

The suggestion solutions might be:

• Consider why each property change is an individual alter() command. Instead perform the change as one giant alter() command.
• Combine the AvailabilityMode and FailoverMode properties into one alter()
• Place the FailoverMode conditional before the AvailabityMode conditional assuming there aren't other implications for this for other values.

SQL Server edition and version

Microsoft SQL Server 2019 (RTM-CU4) (KB4548597) - 15.0.4033.1 (X64)   Mar 14 2020 16:10:35   Copyright (C) 2019 Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

SQL Server PowerShell modules

Name      Version    Path
----      -------    ----
SqlServer 21.1.18221 C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18221\SqlServer.psd1
SQLPS     15.0       C:\Program Files (x86)\Microsoft SQL Server\150\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1

Operating system

OsName               : Microsoft Windows Server 2019 Standard
OsOperatingSystemSKU : StandardServerEdition
OsArchitecture       : 64-bit
WindowsVersion       : 1809
WindowsBuildLabEx    : 17763.1.amd64fre.rs5_release.180914-1434
OsLanguage           : en-US
OsMuiLanguages       : {en-US}

PowerShell version

Name                           Value
----                           -----
PSVersion                      5.1.17763.2931
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.17763.2931
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

SqlServerDsc version

Name         Version Path
----         ------- ----
SqlServerDsc 13.5.0  C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\13.5.0\SqlServerDsc.psd1

[johlju]

Instead perform the change as one giant alter() command.

Sounds like this is what should be done, and if there is a setting that cannot be set in a group with others then that should be an exempt. 🤔

[ShawnHardwick]

Instead perform the change as one giant alter() command.

Sounds like this is what should be done, and if there is a setting that cannot be set in a group with others then that should be an exempt. 🤔

I agree. I just don't know if there was a historical or technical reason why it was implemented this way.

[hollanjs]

@ShawnHardwick - are you working on a fix for this? If not, @johlju could you assign this to me so I could take care of it?

[ShawnHardwick]

@ShawnHardwick - are you working on a fix for this? If not, @johlju could you assign this to me so I could take care of it?

I am not working on this.

[johlju]

@hollanjs I assigned you. Please not that the unit tests are not run in the pipeline since they have not been converted to Pester 5. Since you want to work on this I will convert the tests so this change can be tested. The unit tests will be extensively changed, so wait to modify any tests for this change until the fix for issue #1753 is merged. 🙂 Converting the tests are gonna take me a few days since it a big test file.

[hollanjs]

@johlju - will hold till then. I'll keep an eye out for a notification on #1753 or word from you.

[johlju]

@hollanjs the unit test is now converted and merged. 🙂

[johlju]

@hollanjs I have not seen and PR for this for over a month. I will remove the assignment within a week unless I see a PR, so someone else can work on this.

[hollanjs]

@johlju - my apologies! The Holiday's and life events got in the way and I didn't write to update this thread on that.

I've made the changes and added some tests and plan to do a PR this week to resolve this.

I am running into an issue though, one which I'm hoping you might be able to help with. I setup the Azure Pipeline, like you wrote in the community testing guidelines, however, it keeps erroring out on the Build > Calculate ModuleVersion:

You can invoke the tool using the following command: dotnet-gitversion
Tool 'gitversion.tool' (version '5.12.0') was successfully installed.
ConvertFrom-Json : Conversion from JSON failed with error: Error parsing Infinity value. Path '', line 1, position 1.
At D:\a\_temp\0de7a11f-e31b-4efb-9936-af91ddf14537.ps1:5 char:41
+ $gitVersionObject = dotnet-gitversion | ConvertFrom-Json
+                                         ~~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (:) [ConvertFrom-Json], ArgumentException
+ FullyQualifiedErrorId : System.ArgumentException,Microsoft.PowerShell.Commands.ConvertFromJsonCommand
##[error]PowerShell exited with code '1'.

Link to my Azure Pipeline Not sure if the above link will be visible to you.

I can attempt to just do the PR to the main SqlServerDsc Azure Pipeline, and see if it also errors there, but I would like to get my Azure Pipeline squared away for future dev work.

[johlju]

Ah we haven't update the community guidelines! I will fix that as soon as possible (sending myself a reminder).

If you edit the pipeline, press the three dots to the right and choose Triggers

The click Yaml

The click on Get Sources and you find that "Shallow fetch" is checked:

Uncheck "Shallow fetch". Should look like above. Make sure to click on Save & Queue (drop down list has just Save), so the pipeline is saved with the change.

This was a recent change in Azure DevOps that they started to enforce this by default (prior default was not checked)

And, yes, I could see your pipeline build 🙂

[hollanjs]

@johlju - worked like a charm! You should see a PR shortly.

[ShawnHardwick]

I have not tested the PR, but thanks for the great work. I look forward to seeing this in the next release.

[johlju]

@ShawnHardwick If you can test the preview version that was released on merge, that have the change in in, that would be awesome. If there is still an issue, reopen the issue. 🙂

[johlju]

Preview released: https://www.powershellgallery.com/packages/SqlServerDsc/16.1.0-preview0012