dsccommunity / dsccommunity/SqlServerDsc

SqlAlwaysOnService: When the resource fails to enable AlwaysOn it still restarts the SQL Server service

Open
#804 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 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.

https://github.com/PowerShell/xSQLServer/blob/1a2d2693ee5caf5760badc221b39180213d868bd/DSCResources/MSFT_xSQLServerAlwaysOnService/MSFT_xSQLServerAlwaysOnService.psm1#L115-L129

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

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 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.