SqlSetup: Create integration tests

[johlju]

We should start to create integration tests. xSQLServerSetup would be the ideal resource to start with since all other integration tests are dependent on that working (?). We need a SQL instance to test the other resources on.
If nobody else is up to it I would be happy to try to get some basic integration tests done. It would be a fun challenge.

I looked at @PlagueHO's integration tests in xNetworking and also the integration tests in SharePointDsc. And templates are here: https://github.com/PowerShell/DscResources/tree/master/Tests.Template

But... I need a bit more information how this works practically.

@kwirkykat @mbreakey3 @PlagueHO or anyone else with experience with this.

• To be able to create an integration test for xSQLServerSetup I would need the SQL Server media somewhere accessible, preferably all supported versions, either on the node or in a UNC path. How does this work?
• Does the node get clean (new) before each commit to PR? Or is there a need for the integration test to clean up (uninstall SQL in this case) before next commit? (I would guess/hope tests always run on a clean node)
• Is it possible to have a node with a pre-installed SQL Server instance (preferably an separate instance for all supported versions) so some integration tests can run aginst that/those so the finish quicker (than waiting for the xSQLServerSetup integration tests to finish).
• For some resources we need to use user accounts. It might work with local user accounts, but some resources might need an AD, for example using Failover cluster (?). That means it would need an AD as well. Is that possible? (Preferably the AD could be cleanup after the test run is finished, and not deploy a new AD each run)
• For some tests we might need two nodes with failover cluster (for AlwaysOn, and failover installs) to actually test stuff. Is that possible? (Failover cluster could be setup using DSC as well)
• How far do we go with integration tests? :)

[mbreakey3]

Ideally, integration tests should test all basic functionality. I'm not entirely sure how much setup is required to run most of the SqlServer configurations, but I would start with just the very simple cases - minimal arguments, etc. - and get those working and then build from there. I would check out our integration tests in PSDscResources to get started.
Go ahead and import any outside modules/plugins you need within the test - just make sure they get cleaned up in the 'finally' block at the end of the test. Each integration test should be independent of the others, so any setup that you need should be done before applying a configuration. (You can use Reset-Dsc to ensure this happens).

[mbreakey3]

Integration tests are a bit more flexible than unit tests since the way to write them can vary based on the nature of the resource, so we don't have as many hard rules on writing them. The main thing is that it's testing the actual functionality of the resource - making sure it really does what it says it does.

[PlagueHO]

Hi @johlju - I do love a good integration test! 😁

I've written a few tests that had dependencies on files that you can't put into the repo. See this one in xStorage. It depends on an ISO file, and if it doesn't exist the tests are skipped (this is so they can still run in AppVeyor).

I generally try to make integration tests run on AppVeyor, but sometimes it just isn't possible. And if that is the case then I think it is acceptable to have specific requirements to enable running of the tests. I believe that manually run tests are better than no tests. If the tests do require a more complex setup to run then it would be good to have a script available that will set up the test environment. But one thing to consider (and I haven't looked into this at all), but is Test Kitchen something that might help here?

What I also try to do is to provide both an "Install" and a "Uninstall" integration test for each resource (in the case of xStorage/xMountImage it is Mount/Dismount)- so that I can test both main processes, but also end up with the system cleaned and reset once the tests are completed.

E.g. the process for testing xSQLServerSetup could be something like this:

1. Integration test initialization: If SQL Server is already installed, uninstall it (not using the DSC resource).
2. Execute the Integration test for installing SQL Server.
3. Execute the Integration test for uninstalling SQL Server (assuming there is one).
4. In the Finally code ensure SQL Server has been uninstalled.

As for integration tests that depend on other nodes, that can get a bit trickier, but it is possible I reckon. For example, this one in xCert has a dependency on an ADCS CA Server. It will only run if one can be found.

I do find integration tests pick up at least as many bugs as unit tests (for me anyway) and actually can be more fun to write 😄

But if there is anything I can do to help, let me know.

[johlju]

Thanks for all the information! I think I need a crash course in AppVeyor. Are these assumptions correct?

1. Build = every push to a PR in our case?

   "Every build runs on a fresh virtual machine which is not shared with other builds and the state of which is not preserved between consequent builds. After the build is finished its virtual machine is decommissioned."
   https://www.appveyor.com/docs/build-configuration/#build-pipeline

2. I can not install each major version SQL Server on a separate clean AppVeyor server, they always need to be installed side-by-side to test more than one major version of SQL?

3. I can not point some integration tests to run an AppVeyor server with a pre-installed SQL Server instance? (each test need to start with installing SQL Server)

4. "DSC Team" (Microsoft) will not provide the SQL Server media .iso for each major version, on the AppVeyor server, or on any other location accessible by the integration test?

[PlagueHO]

@johlju - I doubt there is any (easy) way to create integration tests for xSQLServer that will work in AppVeyor because of the dependency on having access to either:

1. MS SQL Server install media
2. A AppVeyor build machine with SQL Server preinstalled.

But I still think you could create integration tests that run in your own environment if configured correctly (e.g. the media ISO's were made available).

In answer to your questions:

1. Yes, every PR would trigger an AppVeyor build to occur.
2. That is correct as far as I know. Although it would be possible to install SQL Server onto an AppVeyor build worker by including a step that downloaded the MSFT SQL Server Eval ISO (or possibly using some other package management system). It might be worth a try to see what would happen.
3. The only build worker types that are available on AppVeyor are:
   
   None of them contain SQL Server preinstalled as far as I know.
4. I don't think the DSC Team could provide this even if they wanted to (although perhaps if all the AppVeyor machines were hosted in Azure it might be possible 😄).

All that said, it might be worth asking the AppVeyor team if they'd consider including a build worker that contains SQL Server. If they could do this then that might enable integration tests in AppVeyor.

[johlju]

@PlagueHO I saw yesterday that they did have the express installation of each major SQL Server installed on each build server, but the service was not started by default. But if so, then there are already stuff present on the build servers which might make the test unreliable (already SQL stuff on it) or full SQL Server will possible break when installing (never tried that combo). But I will see how far the Express version can be used for some of the integration tests.
I also thought about fetching the eval version of SQL Server. If that works then we could at least test the latest version of SQL Server. But then i preferably would like to have a clean build server.

I will dig into AppVeyor a bit more. I will also checkout Test Kitchen if it can help in any way.
I did activate AppVeyor on my xSQLServer fork so I can test easily with a test branch.

If nothing else I will create a basic integration tests to run on a server and .iso provided by the user. I don't think all contributors will have lab environments to run integration tests, so would have been awesome to actually be able to test on a push. But we have to use what we have and go from there. 😄

[johlju]

Update 2017-08-13: Added the failed test with Hyper-V in AppVeyor.

We got simple integration tests going with PR #710. Doing integration testing for the advanced installation options using xSQLServerSetup which depends on a cluster and AD will not work with AppVeyor in the way we use it now.

I'm currently working on option 5.1 for the resource module xFailOverCluster.

Options

Option 1 - Docker in AppVeyor- did not work

Setting up a several Docker images in the AppVeyor build worker. Would work if Failover Clustering would run on the server core docker image. It does not (today).

Option 2 - Hyper-V in AppVeyor - did not work

Setting up a several virtual servers i Hyper-V in the AppVeyor build worker. Would work if the build worker host supported nested virtualization. It does not today.

Option 3 - My personal Azure subscription - not secure

Setting up an environment for integration tests in my personal Azure subscription is not secure. There is no secure way to set up a test environment for public use and allow PR's to test against it. Application ID and Password will be available publicly.

Option 4 - AppVeyor Build Cloud - works, but costs money

This would work and be secure using an AppVeyor Build Cloud against Azure, where the configuration against an Azure subscription would be secure in the AppVeyor portal, and then each PR could be tested as well. But that will be a monthly cost of $500 (for open source projects) for the DSC Resource Kit.
But it would enable us to have an a working domain already ready for running integration tests against My thought with this was just provisioning the servers that are needed for the integration test).

Option 5 - Add a new manual testing framework

Option 5.1 - Manual tests in Azure

I'm currently working with the DscConfigurations repository to see if that can be used to able integration testing for xFailOverCluster. xFailOverCluster has similar problems as xSQLServerSetup that it needs several servers for testing (AD etc.). If that works, the same setup can be used here.

Option 5.2 - Manual tests in Hyper-V

Not started work on this. Running the integration tests in Hyper-V, using for example LabBuilder or Lability.

[johlju]

This is from PR #1295 by @codykonior

I ran it on my 5-node lab which can be built with my OftenOn PowerShell module and the script executed in a PowerShell admin console on CHWK01 to reproduce.
...
This separate test is pretty huge and in this gist so as not to clutter up the PR but the final bit which is captured in the gif is easy enough to understand..

@codykonior This is really super awesome work! We really need to have a manual integration tests that can be run locally. It sounds that you have already done a lot of work for this.

I would be really interested in merging something that, at least starts to, cover option 5.2.