SqlAGReplica: InnerException: Invalid column name 'required_copies_to_commit'

[shurick81]

Details of the scenario you tried and the problem that is occurring:
I am trying to configure availability group.
I get following error when applying DSC:

VERBOSE: [FKV04SPDB]: LCM:  [ End    Set      ]  [[SqlAGReplica]AddReplica]  in 3.5350 seconds.
PowerShell DSC resource MSFT_SqlAGReplica  failed to execute Set-TargetResource functionality with error message:
Creating the Availability Group Replica 'AG01' failed on the instance 'SPIntra01'. InnerException: Invalid column name
'required_copies_to_commit'.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : fkv04spdb
VERBOSE: [FKV04SPDB]: LCM:  [ End    Set      ]
The SendConfigurationApply function did not succeed.
    + CategoryInfo          : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
    + FullyQualifiedErrorId : MI RESULT 1
    + PSComputerName        : fkv04spdb

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

        SqlServerLogin AddNTServiceClusSvc
        {
            Ensure               = 'Present'
            Name                 = $SQLServiceAccountCredential.UserName
            LoginType            = 'WindowsUser'
            ServerName           = $nodeName
            InstanceName         = $SQLInstanceName
            PsDscRunAsCredential = $SPInstallAccountCredential
        }

        SqlServerPermission AddNTServiceClusSvcPermissions
        {
            Ensure               = 'Present'
            ServerName           = $nodeName
            InstanceName         = $SQLInstanceName
            Principal            = $SQLServiceAccountCredential.UserName
            Permission           = 'AlterAnyAvailabilityGroup', 'ViewServerState'
            PsDscRunAsCredential = $SPInstallAccountCredential
            DependsOn            = '[SqlServerLogin]AddNTServiceClusSvc'
        }

        SqlAlwaysOnService 'EnableAlwaysOn'
        {
            Ensure               = 'Present'
            ServerName           = $nodeName
            InstanceName         = $SQLInstanceName
            RestartTimeout       = 120
            PsDscRunAsCredential = $SPInstallAccountCredential
        }
        
        SqlServerEndpoint HADREndpoint
        {
            EndPointName         = 'HADR'
            Ensure               = 'Present'
            Port                 = 5022
            ServerName           = $nodeName
            InstanceName         = $SQLInstanceName
            PsDscRunAsCredential = $SPInstallAccountCredential
        }

        SqlServerEndpointPermission SQLConfigureEndpointPermission
        {
            Ensure               = 'Present'
            ServerName           = $nodeName
            InstanceName         = $SQLInstanceName
            Name                 = 'HADR'
            Principal            = $SQLServiceAccountCredential.UserName
            Permission           = 'CONNECT'
            PsDscRunAsCredential = $SPInstallAccountCredential
            DependsOn            = '[SqlServerEndpoint]HADREndpoint'
        }

        $clusterDependency = '[WindowsFeature]RSATClusteringCmdInterface';

        if ( $NodeName -eq $SQLWSFCFirstNode )
        {
            
            SqlAG PrimaryAG
            {
                Ensure                  = 'Present'
                Name                    = $SQLAGName
                ServerName              = $nodeName
                InstanceName            = $SQLInstanceName
                PsDscRunAsCredential    = $SPInstallAccountCredential
                DependsOn               = '[SqlServerEndpoint]HADREndpoint', '[SqlServerPermission]AddNTServiceClusSvcPermissions'
            }

            SqlAGListener AvailabilityGroupListener
            {
                Ensure                  = 'Present'
                ServerName              = $nodeName
                InstanceName            = $SQLInstanceName
                AvailabilityGroup       = $SQLAGName
                Name                    = $mainParameters.SQLListenerName
                IpAddress               = $mainParameters.SQLListenerIPAddress
                Port                    = 1433
                PsDscRunAsCredential    = $SPInstallAccountCredential
                DependsOn               = '[SqlAG]PrimaryAG'
            }

        } else {

            SqlAGReplica AddReplica
            {
                Ensure                      = 'Present'
                Name                        = $SQLAGName
                AvailabilityGroupName       = $SQLAGName
                ServerName                  = $nodeName
                InstanceName                = $SQLInstanceName
                PrimaryReplicaServerName    = $SQLWSFCFirstNode
                PrimaryReplicaInstanceName  = $SQLInstanceName
                ProcessOnlyOnActiveNode     = $true
                PsDscRunAsCredential        = $SPInstallAccountCredential
            }

        }

Version of the Operating System, SQL Server and PowerShell the DSC Target Node is running:
Windows Server 2016
WMF 5.1
What module (SqlServer or SQLPS) and which version of the module the DSC Target Node is running:
I use SqlServerDsc 10.0.0.0 module. Don't know about SqlServer or SQLPS.

Version of the DSC module you're using, or 'dev' if you're using current dev branch:
I just install from gallery via DSC:

                    PSModule "PSModule_$_"
                    {
                        Ensure              = "present" 
                        Name                = "SqlServerDsc"
                        Repository          = "PSGallery"
                        InstallationPolicy  = "trusted"
                        RequiredVersion     = "10.0.0.0"    
                    }

[shurick81]

I found this similar issue:
https://social.msdn.microsoft.com/Forums/azure/en-US/3e5db95a-0231-4e29-b0c0-68c4d8e9583d/exception-occurred-microsoftsqlservermanagementcommonexecutionfailureexception?forum=sqltools

Is there a way to guarantee what module version to use?

One more thing to mention is that we use SQL Server 2017 Enterprise.

[johlju]

So this is a problem with SQLPS module, maybe we should document that 🤔 It seems that it was solved using SqlServer module.

SQLPS is part of SQL Server, but SQL Server Team are moving away (I think) from SQLPS in favor of SqlServer module (which is not the same as SqlServerDsc). SqlServer module is published in the PowerShell Gallery. It can be downloaded from there; https://www.powershellgallery.com/packages/SqlServer/
If SqlServer module is present on the target node, then that will be used instead of the "older" SQLPS module.

[johlju]

@randomnote1 Are you familiar with this issue? Is this a know problem with the SQLPS module? Should we add a requirement for this resource (in the README.md) that SqlServer module must be used?

[randomnote1]

I think I recall hearing something about this before, but I'm not sure. I've been relying on the SQLPS module (SQL 2014 & 2016) and haven't experienced this yet.

I don't know how well could enforce the requirement to use the SqlServer module because all the resources rely on the Import-SqlPsModule helper function. I would imagine this would end up being a module-wide decision with the SqlServer module being referenced in the manifest as a dependent module. That's a pretty big breaking change, but since the SqlServer module is the way of the future, it's probably worth considering.

[shurick81]

Thank you very much for considering this.

Any dirty workaround for me? :)
My alternative now is running SQL queries from DSC via Script resource.

[randomnote1]

@shurick81, I would try installing the SqlServer module from the PSGallery. The helper function looks for this module to be present first, and prefers it over SQLPS.

[shurick81]

Thanks, I run Install-Module SqlServer -RequiredVersion 21.0.17199 -Force -AllowClobber on each SQL machine and now I get another error:

PowerShell DSC resource MSFT_SqlAGReplica  failed to execute Set-TargetResource functionality with error message:
Failed to join the availability group replica 'AG01'. InnerException: An error occurred while attempting to access
availability replica 'FKV04SPDB\SPINTRA01' in availability group 'AG01'.  The availability replica is not found in the
availability group configuration.  Verify that the availability group and availability replica names are correct, then
retry the command.
Failed to join local availability replica to availability group 'AG01'.  The operation encountered SQL Server error
41167 and has been rolled back.  Check the SQL Server error log for more details.  When the cause of the error has
been resolved, retry the ALTER AVAILABILITY GROUP JOIN command.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : fkv04spdb

VERBOSE: [FKV04SPDB]: LCM:  [ End    Set      ]
The SendConfigurationApply function did not succeed.
    + CategoryInfo          : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
    + FullyQualifiedErrorId : MI RESULT 1
    + PSComputerName        : fkv04spdb

However, it can be completely another issue to look at.

[randomnote1]

Cool! Got past one issue :)

Yes, it looks like you're on to a different issue. Verify the FKV04SPDB\SPINTRA01 instance is added to the availability group as a replica. The name must match what is listed in SSMS in my experience.

[johlju]

@randomnote1 I didn't mean enforce the use of it, I thought we could just mention in the README.md for SqlAGreplica (under requirements section) that SqlServer is needed in some scenarios for SQL Server 2017.
On the other hand, this is probably a bug, I would believe this should be supported with the built-in SQLPS as long that is shipped with SQL Server.
So maybe hold of on this if this is getting fixed. People searching for this error will hit this issue once it has been indexed.

[emyatsuna]

Hi, I am also getting the same error. The manual workaround running the Install-Module SqlServer -RequiredVersion 21.0.17199 -Force using PS solves my problem. Is there an update with this one? Thanks.

[johlju]

One suggestion was to add SqlServer module as a requirement (in the documentation) so no other workaround.

Anyone know if there still is a SQLPS in SQL2019?

[johlju]

Reopen to add this to the documentation.