0

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.

Improve this question
1
  • 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. Commented Jul 21, 2019 at 14:31

1 Answer 1

Reset to default
1

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
2
  • 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. Commented Jul 21, 2019 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". Commented Jul 21, 2019 at 23:22

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.