SqlServiceAccount: Timing issue in integration test for SqlServiceAccount

[johlju]

Details of the scenario you tried and the problem that is occurring:
It seems there is a timing issue in the resource SqlServiceAccount, or the helper function Restart-SqlService. This error is thrown seemingly random, but twice in a row for PR #1004.

I think it is because Set-TargetResource returns as soon as it has restart the service, and the integration test directly runs Get-TargetResource for the next test, and that test fails because the instance has not started on the build worker.

Is a solution to add a validation in the helper function Restart-SqlService that the instance is actually available? It could start a task that tries to connect, and use the Timeout parameter to thrown an error if connection has not succeeded within the timeout period.
Because this issue could be seen in other resource that uses this helper function, so resolving this in this helper function would maybe solve more potential problems.

https://ci.appveyor.com/project/PowerShell/sqlserverdsc/build/9.0.160.0#L8753

    Context When using configuration MSFT_SqlServiceAccount_DatabaseEngine_NamedInstance_Restore_Config
The 'Microsoft.PowerShell.Management' module was not imported because the 'Microsoft.PowerShell.Management' snap-in was already imported.
VERBOSE: Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = SendConfigurationApply,'className' = MSFT_DSCLocalConfigurationManager,'namespaceName' = root/Microsoft/Windows/DesiredStateConfiguration'.
VERBOSE: An LCM method call arrived from computer APPVYR-WIN with user sid S-1-5-21-1930758936-441951309-2831684283-1000.
VERBOSE: [APPVYR-WIN]: LCM:  [ Start  Set      ]
VERBOSE: [APPVYR-WIN]: LCM:  [ Start  Resource ]  [[SqlServiceAccount]Integration_Test]
VERBOSE: [APPVYR-WIN]: LCM:  [ Start  Test     ]  [[SqlServiceAccount]Integration_Test]
VERBOSE: [APPVYR-WIN]:                            [[SqlServiceAccount]Integration_Test] Information: PowerShell module SqlServer not found, trying to use older SQLPS module.
VERBOSE: [APPVYR-WIN]:                            [[SqlServiceAccount]Integration_Test] Importing PowerShell module SQLPS.
VERBOSE: [APPVYR-WIN]:                            [[SqlServiceAccount]Integration_Test] 2018-01-08_12-36-32: Connecting to WMI on 'APPVYR-WIN'.
VERBOSE: [APPVYR-WIN]:                            [[SqlServiceAccount]Integration_Test] 2018-01-08_12-36-32: Current service account is 'APPVYR-WIN\svc-SqlSecondary' for APPVYR-WIN\DSCSQL2016.
VERBOSE: [APPVYR-WIN]: LCM:  [ End    Test     ]  [[SqlServiceAccount]Integration_Test]  in 0.5810 seconds.
VERBOSE: [APPVYR-WIN]: LCM:  [ Start  Set      ]  [[SqlServiceAccount]Integration_Test]
VERBOSE: [APPVYR-WIN]:                            [[SqlServiceAccount]Integration_Test] Information: PowerShell module SqlServer not found, trying to use older SQLPS module.
VERBOSE: [APPVYR-WIN]:                            [[SqlServiceAccount]Integration_Test] Importing PowerShell module SQLPS.
VERBOSE: [APPVYR-WIN]:                            [[SqlServiceAccount]Integration_Test] 2018-01-08_12-36-32: Connecting to WMI on 'APPVYR-WIN'.
VERBOSE: [APPVYR-WIN]:                            [[SqlServiceAccount]Integration_Test] 2018-01-08_12-36-33: Setting service account to 'APPVYR-WIN\svc-SqlPrimary' for service MSSQL$DSCSQL2016.
VERBOSE: [APPVYR-WIN]:                            [[SqlServiceAccount]Integration_Test] 2018-01-08_12-36-49: Restarting 'DSCSQL2016' and any dependent services.
VERBOSE: [APPVYR-WIN]:                            [[SqlServiceAccount]Integration_Test] Information: PowerShell module SqlServer not found, trying to use older SQLPS module.
VERBOSE: [APPVYR-WIN]:                            [[SqlServiceAccount]Integration_Test] Importing PowerShell module SQLPS.
VERBOSE: [APPVYR-WIN]: LCM:  [ End    Set      ]  [[SqlServiceAccount]Integration_Test]  in 21.7950 seconds.
      [-] Should compile and apply the MOF without throwing 23s
        Expected: the expression not to throw an exception. Message was {PowerShell DSC resource MSFT_SqlServiceAccount  failed to execute Set-TargetResource functionality with error message: System.InvalidOperationException: Failed to connect to SQL instance 'APPVYR-WIN\DSCSQL2016'. }
            from C:\projects\sqlserverdsc\Tests\Integration\MSFT_SqlServiceAccount.Integration.Tests.ps1:364 char:21
            + ...               Start-DscConfiguration @startDscConfigurationParameters
            +                   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        365:                 } | Should -Not -Throw
        at Invoke-Assertion, C:\Program Files\WindowsPowerShell\Modules\Pester\4.1.1\Functions\Assertions\Should.ps1: line 209
        at <ScriptBlock>, C:\projects\sqlserverdsc\Tests\Integration\MSFT_SqlServiceAccount.Integration.Tests.ps1: line 345
VERBOSE: An LCM method call arrived from computer APPVYR-WIN with user sid S-1-5-21-1930758936-441951309-2831684283-1000.
[APPVYR-WIN]:                            [] The GET operation will be carried against a pending configuration since the latest configuration has not converged yet.
VERBOSE: [APPVYR-WIN]: LCM:  [ Start  Get      ]      
VERBOSE: [APPVYR-WIN]:                            [[SqlServiceAccount]Integration_Test] Information: PowerShell module SqlServer not found, trying to use older SQLPS module.
VERBOSE: [APPVYR-WIN]:                            [[SqlServiceAccount]Integration_Test] Importing PowerShell module SQLPS.
VERBOSE: [APPVYR-WIN]:                            [[SqlServiceAccount]Integration_Test] 2018-01-08_12-36-55: Connecting to WMI on 'APPVYR-WIN'.
VERBOSE: [APPVYR-WIN]: LCM:  [ End    Get      ]  [[SqlServiceAccount]Integration_Test]  in 0.5360 seconds.
VERBOSE: [APPVYR-WIN]: LCM:  [ End    Get      ]    in  0.5790 seconds.
      [+] Should be able to call Get-DscConfiguration without throwing 766ms
      [+] Should have set the resource and all the parameters should match 16ms

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

SqlServiceAccount Integration_Test
{
    ServerName     = $env:COMPUTERNAME
    InstanceName   = 'DSCSQL2016'
    ServiceType    = 'DatabaseEngine'
    ServiceAccount = $SqlServicePrimaryCredential
    RestartService = $true
}

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