SqlServerDsc: Speed up testing

[johlju]

Details of the scenario you tried and the problem that is occurring:
We are getting close to the (AppVeyor) time limit of 60 minutes, currently around ~50 minutes. I suggest we look at steps to improve the code and the build worker.

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

Version of the Operating System, SQL Server and PowerShell the DSC Target Node is running:
n/a

What module (SqlServer or SQLPS) and which version of the module the DSC Target Node is running:
n/a

Version of the DSC module you're using, or 'dev' if you're using current dev branch:
Dev

[johlju]

The AppVeyor build worker only has 4GB of memory so maybe we should stop and instances after we tested finished. I'm only raising this here as a reminder if integration tests starting to go slower, we could try that as an option. Use the Service resource to stop instances, and use the same to start up the instances before next integration tests.

I don't really know if this helps anything, just throwing it up here as a reminder.

[johlju]

Yesterday, for the first time, I hit the 60 minute window for running tests in AppVeyor. It took longer than normal to download the SQL Server media, so the tests could not finish in time.
After I merged the latest integration tests in PR I will look if there is anyway to speed up the tests. Shaving off seconds here an there would help in the long run. If that is not possible then we might need to turn off some integration tests.

[johlju]

Maybe if we ask AppVeyor kindly maybe we can get more run time. But that would only help this repository, not the forked repositories. For example if a contributor connect their AppVeyor account to their forked repository, to test a change before sending in a pull request, they would hit the 60 minute window and might not be able to test their change.
So I thought, that if we could get more run time, then maybe we could add environment variables that tell if a test should run or not.
This can be done by the contributors, thru the AppVeyor interface, on the forked repository.

[johlju]

There seems to be an issue with AppVeyor and SqlAG; the speed degrades farther and farther down in the test we go. See testing of Set-TargetResource. First It-block starts at 943 ms and last It-block to run is taking 5.14 seconds. Make a total time to run around 10 minutes. 😞
I have seen this problem with Pester before on a older insider build of Windows 10, it got slower and slower as the test ran.

https://ci.appveyor.com/project/johlju/sqlserverdsc/build/9.0.130.0#L1865

Please not that the same test (the whole test) take 18 seconds (25 seconds with code coverage) to complete locally on a Windows 10 (latest insider, PS Version 5.1.17063.1000).
Running locally on Windows Server 2012 R2 with PS version 5.0.10586.117 takes 57 seconds to complete the unit test with code coverage turned on. Updating to the lastest Windows Management Framework 5.1, version 5.1.14409.1005, improves that result further ~45 seconds with code coverage). Running locally on Windows Server 2016 with PS version 5.1.14393.1944 takes ~43 seconds.

PS version	Time to run SqlAG.Tests	OS
5.0.10586.117	~57 seconds	Windows Server 2012 R2
5.1.14393.1715	~600 seconds	AppVeyor Build Worker
5.1.14393.1944	~43 seconds	Windows Server 2016
5.1.14409.1005	~45 seconds	Windows Server 2012 R2
5.1.17063.1000	~18 seconds	Windows 10 (build 17063.rs_prerelease)

[johlju]

Speed test

Run times from test run #1460.

Updated 2020-05-16

Individual unit and integration test run time in CI pipeline

Common test of DscResource.Tests are not listed here, since those needs to be run regardless, and not as easy to make changes to (they effect all resource modules).

Unit

Type	Test	Run time (~minutes)
Unit	SqlAG	29:57 29:33
Unit	SqlAGDatabase	6:49 7:13
Unit	SqlAgentAlert	1:03 0:55
Unit	SqlAgentFailsafe	0:16 0:14
Unit	SqlAgentOperator	0:33 0:29
Unit	SqlAGListener	0:41 0:43
Unit	SqlAGReplica	1:52 1:52
Unit	SqlAlias	1:23 1:19
Unit	SqlAlwaysOnService	0:13 0:13
Unit	SqlDatabase	0:18 0:19
Unit	SqlDatabaseDefaultLocation	0:16 0:16
Unit	SqlDatabasePermission	0:40 0:39
Unit	SqlDatabaseRole	0:55 0:53
Unit	SqlDatabaseUser	0:47 0:42
Unit	SqlRS	3:14 03:11
Unit	SqlRSSetup	0:40 0:32
Unit	SqlScript	0:07 0:06
Unit	SqlScriptQuery	0:09 0:05
Unit	SqlServerConfiguration	0:06 0:05
Unit	SqlServerDatabaseMail	1:13 0:52
Unit	SqlServerEndpoint	0:36 0:30
Unit	SqlServerEndpointPermission	0:10 0:07
Unit	SqlServerLogin	1:00 0:44
Unit	SqlServerMaxDop	0:28 0:20
Unit	SqlServerMemory	0:34 0:23
Unit	SqlServerPermission	0:12 0:09
Unit	SqlServerProtocol	0:18
Unit	SqlServerProtocolTcpIp	0:20
Unit	SqlServerReplication	1:13 0:39
Unit	SqlServerRole	0:43 0:30
Unit	SqlServerSecureConnection	0:33 0:18
Unit	SqlServiceAccount	0:29 0:17
Unit	SqlSetup	55:17 60:36
Unit	SqlWaitForAG	0:06 0:02
Unit	SqlWindowsFirewall	7:57 2:14
Unit	SqlServerDsc.Common	1:56 3:20
---	---	?

Integration

Type	Test	Run time (~minutes)
Integration	SqlAgentAlert	0:29 0:30
Integration	SqlAgentFailsafe	0:29 0:29
Integration	SqlAgentOperator	0:29 0:30
Integration	SqlAlwaysOnService	1:26 -
Integration	SqlDatabase	0:57 2:03
Integration	SqlDatabaseDefaultLocation	0:50 0:48
Integration	SqlDatabaseUser	2:37 2:36
Integration	SqlRS	6:10 6:00
Integration	SqlRSSetup	1:15 1:13
Integration	SqlScript	0:27 0:26
Integration	SqlScriptQuery	0:20 0:19
Integration	SqlServerDatabaseMail	0:31 0:30
Integration	SqlServerEndpoint	0:08 0:08
Integration	SqlServerLogin	2:32 2:44
Integration	SqlServerProtocol	0:35
Integration	SqlServerProtocolTcpIp	0:16
Integration	SqlServerReplication	0:39
Integration	SqlServerRole	2:09 2:33
Integration	SqlServerSecureConnection	0:36 0:23
Integration	SqlServiceAccount	3:07 3:05
Integration	SqlSetup	12:37 14:23
---	---	?

[johlju]

I gave it a try running unit tests in a Windows container on the build worker. My attempt failed, but maybe there is a solution to it, see the issue PowerShell/DscResource.Tests#204.

[johlju]

Another option is to remove the integration tests from the repository and create a separate repository for the integration tests using DscConfigurations templates.

[johlju]

I have update with the latest elapsed times using the new CI pipeline.

[Fiander]

Is this still actual?

https://github.com/pester/Pester/issues/1318
Does this have anything to do with it?

What version of Powershell does the pipeline use?

[johlju]

The problem is that certain tests are written to complex which results in extremely long run times. There are still some that need to be fixed but the worst has been resolved. It is fixed by not trying to reuse code in the tests but instead making context-blocks self-sustaining even if that means duplicating code.

The pipeline is using Pester 4. All tests need to be refactored for Pester 5, so that is gonna take a while before latest Pester can be supported. The pipeline need to be modified as well to support code coverage in pester 5.

[johlju]

The unit tests was also speed up by running the unit tests in PowerShell 7. But SqlSetup still takes 60 minutes to run (last time I verified, might be quicker today). It shouldn’t need to take that long to run. It’s like 40-50 minutes too long. :) Those tests still need to be refactored, I think I only refactored the tests for Get-TargetResource. I also refactored the code to make more units (functions) that could be to tested separately to increase the overall speed, when the mocking could be decreased.