dsccommunity / dsccommunity/SqlServerDsc

SqlAG: Throws exception when BasicAvailabilityGroup is set to $false

Open
#900 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug 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:
When configuring an Availability Group on SQL Server 2017 and the configuration contains the parameter BasicAvailabilityGroup and set to $false it throws an error.

This is probably due to that it doesn't compare the BasicAvailabilityGroup value correctly if we look at the verbose output.

2017-11-04_13-26-24: 'BasicAvailabilityGroup' should be 'False' but is ''

Full output below.

ERBOSE: [SQLTEST]: LCM:  [ Start  Resource ]  [[xSQLServerAlwaysOnAvailabilityGroup]SQL2017-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1]
VERBOSE: [SQLTEST]: LCM:  [ Start  Test     ]  [[xSQLServerAlwaysOnAvailabilityGroup]SQL2017-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1]
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2017-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] Information: PowerShell module SqlServer not found, trying to use older SQLPS module.
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2017-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] Importing PowerShell module SQLPS.
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2017-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] Connected to SQL instance 'sqltest\SQL2017'.
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2017-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] 2017-11-04_13-26-24: 'BasicAvailabilityGroup' should be 'False' but is ''
VERBOSE: [SQLTEST]: LCM:  [ End    Test     ]  [[xSQLServerAlwaysOnAvailabilityGroup]SQL2017-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1]  in 0.8910 seconds.
VERBOSE: [SQLTEST]: LCM:  [ Start  Set      ]  [[xSQLServerAlwaysOnAvailabilityGroup]SQL2017-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1]
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2017-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] Information: PowerShell module SqlServer not found, trying to use older SQLPS module.
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2017-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] Importing PowerShell module SQLPS.
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2017-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] Information: PowerShell module SqlServer not found, trying to use older SQLPS module.
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2017-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] Importing PowerShell module SQLPS.
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2017-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] Connected to SQL instance 'sqltest\SQL2017'.
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2017-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] Getting the effective permissions for the login 'NT SERVICE\ClusSvc' on 'SQL2017'.
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2017-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] Information: PowerShell module SqlServer not found, trying to use older SQLPS module.
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2017-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] Importing PowerShell module SQLPS.
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2017-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] Connected to SQL instance 'SQLTEST\SQL2017'.
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2017-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] The cluster login 'NT SERVICE\ClusSvc' has the required permissions.
VERBOSE: [SQLTEST]: LCM:  [ End    Set      ]  [[xSQLServerAlwaysOnAvailabilityGroup]SQL2017-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1]  in 1.3120 seconds.
PowerShell DSC resource MSFT_xSQLServerAlwaysOnAvailabilityGroup  failed to execute Set-TargetResource functionality with error message: Failed to alter the availability group 'TESTCLU02-AG1'. InnerException: Exception calling "Alter" w
ith "0" argument(s): "Alter failed for Availability Group 'TESTCLU02-AG1'. "
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : sqltest.shamn.se

A workaround is simply to remove BasicAvailabilityGroup = $false from the configuration. But I think the resource should handle this.

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

                xSQLServerAlwaysOnAvailabilityGroup $resourceConfigName
                {
                    Ensure = 'Present'
                    Name = $Node.AvailabilityGroupName
                    SQLInstanceName = $Node."$($currentSqlInstance)InstanceName"
                    SQLServer = $Node.NodeName
                    BasicAvailabilityGroup = $false

                    PsDscRunAsCredential = $SqlAdministratorCredential

                    DependsOn = "[xSQLServerEndpoint]$($currentSqlInstance)-HADREndpoint",
                                "[xSQLServerPermission]$($currentSqlInstance)-AddNTServiceClusSvcPermissions"
                }

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

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

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

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 by locating the xSQLServerAlwaysOnAvailabilityGroup resource and its handling of the BasicAvailabilityGroup parameter, then reproduce the configuration against SQL Server 2017 using the SQLPS module. Done means BasicAvailabilityGroup = $false is compared and applied correctly without reporting an empty value or failing the availability group alteration.

Written by the indexing model from the issue text.

Assessment

Tech stack
powershell, sql
Domain
database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.