SqlSetup: Test-TargetResource should test whether service is running

[Robin-Bouwman]

Scenario: I am using SQLServerDSC 12.1.0.0 to install SQL Server 2016 SP2 on a Windows 2016 Server Datacenter Core VM on an A2 size plan in the ASDK for Microsoft Azure Stack (MAS). I am using this as a test and to play around with DSC and the azure DSC Extension.

The error: When the deployment process is finished, the MAS deployment gives me the following error.

Error message: DSC Configuration 'SQLConfiguration' completed with error(s). Following are the first few: PowerShell DSC resource MSFT_SqlSetup failed to execute Test-TargetResource functionality with error message: System.InvalidOperationException: Failed to connect to SQL instance 'MYSQLVM'.

Troubleshooting: Checking the SQL installation log, I find that the setup itself was successful. I followed this up by checking whether the MSSQLSERVER service was running. It turns out, it was not. I manually started the service; running the Start-DscConfiguration in verbose gave me a pass on all tests.

Cause: After checking the event log, I found out that the services time-out during start-up. In all likelihood, my VM is not powerful enough to handle the start-up of all services in a timely manner even though the server runs on the core edition and the VM size meets the minimum requirements for SQL Server 2016.

Quick solution: As this is a test case and it doesn't need to be pretty, I solved this problem by changing the default servicepipetimeout to 4 minutes. My deployment now passes. I chose not to increase the VM size as space on our ASDK is very limited. I also chose not to use the service resource to make sure the required services are running, because the resulting IF statement proved a bit too difficult to make with my current understanding of DSC.

Suggestion: In order to facilitate troubleshooting, I suggest the Test-TargetResource performs a test to see if the required services, such as MSSQLSERVER, are running before it tries to test the connection.

Code for those interested:

Registry Sevicespipetimeout { Key = "hklm:\system\currentcontrolset\control" ValueName = "servicespipetimeout" ValueData = "240000" ValueType = "Dword" Ensure = "Present"

[johlju]

I think you mean the Test-TargetResource of the SqlSetup resource? I think it should be possible to add a optional boolean parameter which will test that the service is is running. Not sure if we should do this by default since that would change the behavior today, so if we add an optional parameter VerifyServiceRunning or similar, and that is set to $true then that check is done. 🤔

Would it be possible to use the Service resource from PSDscResources, or xService resource from xPSDesiredStateConfiguration to do this instead? 🤔

[Robin-Bouwman]

Yes, that is exactly what I mean.

I tried adding the Service resource after the setup, this solved the problem of the service not running. However, because I added DSC as part on an Azure deployment, it would still result in a "failed" deployment because the Test-TargetResource from the SqlSetup resource would run before the Service resource. I tried placing the Service resource before the SqlSetup. In my case, this failed as well because the Test-TargetResource from the SqlSetup ran before the MSSQLSERVER service had completely started. I toyed with the idea of making the SqlSetup and Service resource dependent on each other, but this proved too difficult for me to make and would needlessly complicate things.

I am sure my particular problem will not arise on most production machines as these will be powerful enough to make sure the MSSQLSERVER service wont timeout on startup. I made my suggestion for two reasons:

1. I used small VM sizes to save resources during my test phase. I assume I wont be the only one making this choice. Having the deployment fail because the service is not running might make troubleshooting easier in those cases.
2. If someone runs into the same problem I did, my post at least provides some documentation and a possible solution for the problem. :wink:

[johlju]

Thank you for your detailed information in this issue! :smile: I labeled this as enhancement and help wanted so that someone in the community can run with this :)

[paoIpao]

Same problems, because on Azure I have Analysis Services installed but disabled and the test-targetsource go to error (I NOT have specified in features AS but go to error always..)

[johlju]

@paololec For your issue I think you should use the Service resource to enforce the service to the state 'Running'. I think changing the StartupType and State of a service is not something SqlSetup resource should do.

[johlju]

@paololec If you mean that AS feature is not part of your configuration and the SqlSetup fails anyway, then please open a separate issue that SqlSetup should ignore any feature not part of the configuration.

[paoIpao]

yes. it is.