I have a solution which includes two SQL Server projects and one SQL Server Integration Services project. I can build these on a build server like Jenkins or TFS, but in order to test them they need to be deployed to a DEV database server (because they’re database projects).
Questions
- Should I create a SQL Server instance and SSIS instance just for running automated tests (separate from DEV).
- The model seems to be build, test, deploy, but it seems in my case I might need build->deploy->test->deploy?
Hi Mark,
Thanks for getting in touch! It is a pretty common CI model to have build -> deploy -> test -> deploy. The general process that is followed is that after deploying to a test environment automated tests are run. I have seen it done via TFS and also TeamCity but I cannot see why it could not also be done with Jenkins.
Damian Brady did an NDC talk that included a build/deploy/test/deploy structure as part of it: https://vimeo.com/171704607
And there is also a TeamCity plugin that can poll Octopus for when a deployment is completed written by Matt Richardson that could give you some ideas of triggers: https://github.com/matt-richardson/teamcity-octopus-build-trigger-plugin
Octopus can fit very nicely into the deploy parts of the model.
As for your first question if you should create separate instances for running automated tests, that really is up to you with your infrastructure but it would be a recommended solution.
Let me know if you have any further questions or if I can clarify anything I have said.
Vanessa
Thanks for the great answer! Great video too., quite funny! 
Hi Mark,
I passed on your comment to Damian 
Let me know if you have any questions along the way.
Vanessa