I am designing a CI system where I need to leverage a database as part of the software build process for integration tests. Different developers on different branches of the same repo will want to run a build on their code. I am struggling a bit on how to design the the database integration. As part of the process the database schema or data itself might be changed. Also there are many different schema's used by the software depending on what code base we are building.

The problem I have is how to handle a set of db backups. These are pretty complex with the views, store procedures and tables etc. I could just restore the database each time programmatically for each build. However, that could cause a conflict between concurrent builds by different developers, if they attempt to use that database at the same time.

Ideally I can take a series of .bak files, restore them to a database, and programmatically create unique schemas to avoid any conflicts between builds.

However, that is a huge lift at this point because I need to track these dynamically generated schemas and point the software to them with some find/replace.

I can help but believe that this is a pretty normal problem to have and that engineering a similar solution has already been done. Is there a standard way of dealing with this?

The system is for windows software and I am using Jenkins with a shared library so I can do almost anything programmatically I need to do, just looking for the best solution.

  • 3
    CI builds should run in isolation, or at least they should not modify external state, otherwise you're destroying repeatability. So if you absolutely must access and modify a database during a build, you should really create it on the fly, populating it with initial data as needed for the build, and possibly destroying it afterwards if you don't need to look at the resulting schema for additional tests. – Hans-Martin Mosner Jul 21 '19 at 14:31

I think there's a few problems going on here, but we might be able to address a few of them.

However, that could cause a conflict between concurrent builds by different developers, if they attempt to use that database at the same time.

Why not implement a queue?

It sounds like you are a bit time strapped to solve the problem, so I will make some assumptions here depending on your environment (SQL Server given your Windows focus.)

Use Jenkins, setup a build queue that only runs once the previous job completes, and use something like Database Snapshots to run a developer's code, and quickly revert to the previous state (if it fails or w/e.)

In general methods to solve these types of problems also include - additional databases for testing, local database testing on dev machines deferring the final integration testing, container based testing (so each item is a one off and there's no central db, just an image restored from either a bak, some reference image, w/e).

I am also fairly certain you could buy something off the shelf that does most of this - Redgate certainly sells some CI/CD tools that purport to do exactly this.

| improve this answer | |
  • So my ultimate problem is that my CI system is using a centralized database server and I am trying to avoid building the infrastructure required to encapsulate the build process by leveraging dynamic schema names for each build. However, I am quickly finding that making dynamic schemas a reality is an incredibly big lift. I can't do a queue because this the only thing Jenkins does and I expect dozens of concurrent builds. I looked into redgate in depth and their focus is on CI for databases themselves rather than as a part of the CI process. The real solution here is containers unfortunately. – Mark Jul 21 '19 at 15:03
  • bleh :D Could you fake delay Jenkins jobs by adding a mutex in the database? Its not the best option, but if it can poll a db table you could pause all forward progress until its "free". – ConstantineK Jul 21 '19 at 23:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.