dsccommunity / dsccommunity/SqlServerDsc
SqlAlwaysOnService: When the resource fails to enable AlwaysOn it still restarts the SQL Server service
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 the resource fails to enable AlwaysOn, for example the cmdlet Enable-SqlAlwaysOn fails with an error, then the resource still restarts the SQL Server service. This is because there are no error handling in the Set-TargetResource function.
Error
VERBOSE: [APPVYR-WIN]: LCM: [ Start Set ]
VERBOSE: [APPVYR-WIN]: LCM: [ Start Resource ] [[xSQLServerAlwaysOnService]Integration_Test]
VERBOSE: [APPVYR-WIN]: LCM: [ Start Test ] [[xSQLServerAlwaysOnService]Integration_Test]
VERBOSE: [APPVYR-WIN]: [[xSQLServerAlwaysOnService]Integration_Test] Information: PowerShell module SqlServer not found, trying to use older SQLPS module.
VERBOSE: [APPVYR-WIN]: [[xSQLServerAlwaysOnService]Integration_Test] Importing PowerShell module SQLPS.
VERBOSE: [APPVYR-WIN]: [[xSQLServerAlwaysOnService]Integration_Test] Connected to SQL instance 'APPVYR-WIN\DSCSQL2016'.
VERBOSE: [APPVYR-WIN]: [[xSQLServerAlwaysOnService]Integration_Test] 2017-09-12_10-31-23: SQL Always On is disabled on "APPVYR-WIN\DSCSQL2016".
VERBOSE: [APPVYR-WIN]: [[xSQLServerAlwaysOnService]Integration_Test] 2017-09-12_10-31-23: SQL Always On is in the desired state for "APPVYR-WIN\DSCSQL2016": False.
VERBOSE: [APPVYR-WIN]: LCM: [ End Test ] [[xSQLServerAlwaysOnService]Integration_Test] in 4.9050 seconds.
VERBOSE: [APPVYR-WIN]: LCM: [ Start Set ] [[xSQLServerAlwaysOnService]Integration_Test]
VERBOSE: [APPVYR-WIN]: [[xSQLServerAlwaysOnService]Integration_Test] Information: PowerShell module SqlServer not found, trying to use older SQLPS module.
VERBOSE: [APPVYR-WIN]: [[xSQLServerAlwaysOnService]Integration_Test] Importing PowerShell module SQLPS.
VERBOSE: [APPVYR-WIN]: [[xSQLServerAlwaysOnService]Integration_Test] 2017-09-12_10-31-23: Enabling Always On for the instance APPVYR-WIN\DSCSQL2016
Could not obtain information about SQL Server Service 'MSSQL$DSCSQL2016'.
+ CategoryInfo : ResourceUnavailable: (MSSQL$DSCSQL2016:) [], CimException
+ FullyQualifiedErrorId : ServiceError,Microsoft.SqlServer.Management.PowerShell.Hadr.EnableSqlHADRServiceCommand
+ PSComputerName : localhost
VERBOSE: [APPVYR-WIN]: [[xSQLServerAlwaysOnService]Integration_Test] 2017-09-12_10-31-23: SQL Always On has been enabled on "APPVYR-WIN\DSCSQL2016". Restarting the service.
VERBOSE: [APPVYR-WIN]: [[xSQLServerAlwaysOnService]Integration_Test] Information: PowerShell module SqlServer not found, trying to use older SQLPS module.
VERBOSE: [APPVYR-WIN]: [[xSQLServerAlwaysOnService]Integration_Test] Importing PowerShell module SQLPS.
VERBOSE: [APPVYR-WIN]: [[xSQLServerAlwaysOnService]Integration_Test] Connected to SQL instance 'APPVYR-WIN\DSCSQL2016'.
VERBOSE: [APPVYR-WIN]: [[xSQLServerAlwaysOnService]Integration_Test] Getting SQL Server service information.
Cannot find any service with display name 'SQL Server ()'.
+ CategoryInfo : ObjectNotFound: (SQL Server ():) [], CimException
+ FullyQualifiedErrorId : NoServiceFoundForGivenDisplayName,Microsoft.PowerShell.Commands.GetServiceCommand
+ PSComputerName : localhost
VERBOSE: [APPVYR-WIN]: [[xSQLServerAlwaysOnService]Integration_Test] SQL Server service restarting.
VERBOSE: [APPVYR-WIN]: [[xSQLServerAlwaysOnService]Integration_Test] Information: PowerShell module SqlServer not found, trying to use older SQLPS module.
VERBOSE: [APPVYR-WIN]: [[xSQLServerAlwaysOnService]Integration_Test] Importing PowerShell module SQLPS.
VERBOSE: [APPVYR-WIN]: [[xSQLServerAlwaysOnService]Integration_Test] Connected to SQL instance 'APPVYR-WIN\DSCSQL2016'.
VERBOSE: [APPVYR-WIN]: [[xSQLServerAlwaysOnService]Integration_Test] 2017-09-12_10-31-23: SQL Always On is disabled on "APPVYR-WIN\DSCSQL2016".
VERBOSE: [APPVYR-WIN]: [[xSQLServerAlwaysOnService]Integration_Test] 2017-09-12_10-31-23: SQL Always On is in the desired state for "APPVYR-WIN\DSCSQL2016": False.
VERBOSE: [APPVYR-WIN]: LCM: [ End Set ] [[xSQLServerAlwaysOnService]Integration_Test] in 0.7880 seconds.
PowerShell DSC resource MSFT_xSQLServerAlwaysOnService failed to execute Set-TargetResource functionality with error message: Failed to ensure Always On is Present on the instance 'APPVYR-WIN\DSCSQL2016'.
+ CategoryInfo : InvalidOperation: (:) [], CimException
+ FullyQualifiedErrorId : ProviderOperationExecutionFailure
+ PSComputerName : localhost
VERBOSE: [APPVYR-WIN]: 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.784 seconds
The DSC configuration that is using the resource (as detailed as possible):
Any configuration that fails.
$ConfigurationData = @{
AllNodes = @(
@{
NodeName = 'localhost'
ComputerName = $env:COMPUTERNAME
InstanceName = 'DSCSQL2016'
RestartTimeout = 120
PSDscAllowPlainTextPassword = $true
}
)
}
Configuration MSFT_xSQLServerAlwaysOnService_EnableAlwaysOn_Config
{
param
(
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlInstallCredential
)
Import-DscResource -ModuleName 'xSQLServer'
node localhost {
xSQLServerAlwaysOnService 'Integration_Test'
{
Ensure = 'Present'
SQLServer = $Node.ComputerName
SQLInstanceName = $Node.InstanceName
RestartTimeout = $Node.RestartTimeout
PsDscRunAsCredential = $SqlInstallCredential
}
}
}
Version of the Operating System, SQL Server and PowerShell the DSC Target Node is running:
Windows Server 2016, SQL Server 2016, WMF 5.1
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 in DSCResources/MSFT_xSQLServerAlwaysOnService/MSFT_xSQLServerAlwaysOnService.psm1 at the Set-TargetResource code around lines 115-129, and review how Enable-SqlAlwaysOn reports failure. Reproduce the failed enable scenario if possible; done means a failed enable does not proceed to restart the SQL Server service and the resource still reports the failure.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- powershell, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Clearly specified
- Newbie friendliness
- 35/100