For a reliable CI process start with the source

Yesterday I nearly crashed my car on the magic roundabout in Swindon. (For non-Brits: don’t ask! 🙂 ) I had gone to visit a client who wanted some advice about how to achieve continuous delivery for their legacy SQL Server databases. This near death driving experience (slight risk of exaggeration) seemed like a good metaphor.

About feedback loops…

One of the principles of continuous delivery is that we want fast feedback loops. We want to know if our code works quickly. If it doesn’t we want to know why it is broken and we want to fix it before building more code on broken foundations and before we context switch to the next task. We also want to discover errors sooner, in less critical environments, as the more critical the environment where the error is found the more problematic it becomes to fix. Fixing bugs on dev is easier than fixing bugs on staging, which is easier than fixing bugs in production. (P.S. Don’t make hot fixes on production… or staging).

The thing about feedback loops is that if you don’t know where you started – you don’t know where to circle back too. Imagine the following scenario, where the developer does not use a source control system (properly) for DB changes:

  • A developer makes a change to dev
  • Now two things happen in parallel:
    • An automated build/test process kicks off. This build process takes a long time because [reasons]
    • While the developer waits they use the time to make more changes
  • Now the two processes end with the following results:
    • The first build fails and returns a super pretty and detailed report with bells on
    • The developer no longer has a reliable record of the state that triggered the test cycle because they are not using source control properly

The feedback loop is broken. The first automated test process returns an issue but the developer cannot work on the specific version that triggered the cycle to fix it. He should go back and start at the beginning (a very good place to start) but he can’t.

Complicated/broken source control processes cause extra risk of death by fire

To add insult to injury, after I nearly died in a fireball of burning petrol, rubber and human flesh (exaggeration may be getting out of hand), I got lost. For those that don’t know the magic roundabout, imagine 5 separate mini-roundabouts (@ Americans: roundabouts = traffic circles/rotaries) in a big loop which make up a massive, complicated, look-right, look-left, go, scream, honk, break, die type of thing. (Just look at the picture). After I nearly died I got confused and I took the wrong exit. I ended up going the wrong way, making my problems worse. I then had to U-turn and enter the complicated, twirly wirly, magic roundabout of death a second time and hope that this time I ended up pointing in the right direction afterwards.

Why did I go the wrong way? I had lost track of where I had entered the roundabout/feedback loop. I was unsure of my source.

Diagnosing and fixing the problem

When setting up CI or CD you need to start at the root. Your process can only ever be as stable as your foundations. Your foundations are source control.

The fundamental concept of source control is that you need to have a record of the state at any point in time. This means that you need to record the database state in source control. In this customer’s case there was a complicated branching strategy (I won’t go into it) which was based on a combination of create scripts from SQL Server Database Projects and update scripts (the patches). The result was that the nightly build would run but it was hard to apply the lessons learnt to each source control branch. It was also not possible to be confident which version in source control accurately represented the current state of production so hot fixes could not easily be made in source control and deployed using the normal process – hence, hot fixes happened on production and it was a struggle to cascade them down through the other environments and back into source control.

The DBA at this company had made valiant efforts to introduce CI (of sorts), automated testing and automated releases, and he had just about got as far as he could. The fundamental problems with their source control strategy was blocking them from making any progress. In order to fix the problem he is now going to talk with the developers and work out a source control strategy that is fit for purpose.

The moral of the story

If you want to adopt continuous integration (database or otherwise) it is critical that first you ensure that your source control strategy is up to scratch. This means that your versions must be immutable, your branching strategy must scale and you must be able to say with confidence which version is currently released to production. If your source control is in a healthy state the rest of CI/CD may follow relatively easily. If you are building on broken foundations your efforts may be in vain.

As an aside, it helps if your feedback loops are small and that your tests can run quickly. If each iteration that is tested is smaller and tests run quicker it is easier to understand and fix bugs quicker. This means you can deliver higher quality code faster. (But you should still sort out source control first.)

Also – don’t drive to Swindon. Get the train instead.

Leave a Reply

Your email address will not be published. Required fields are marked *