dsccommunity / dsccommunity/SqlServerDsc
SqlAG: Throws exception when BasicAvailabilityGroup is set to $false
Nobody has claimed this yet.
- 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
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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