Click here to monitor SSC

Continuous Delivery for Databases – a Pipe Dream?

Published 19 June 2014 7:39 am

I’m a DBA for a large healthcare firm here in the US. You’d have to put a gun to my head before I let my developers run automatic database deployments through to production without me checking the release first“.

I had just explained our ideas for running continuous integration, automated testing and automated release processes for SQL Server database modifications, and this wasn’t quite the reaction I’d hoped for. Was the idea of continuous delivery for the database really such a pipe dream? Since then, we’ve been working hard to try to understand this conflict.

That DBA was thinking about continuous deployment. But continuous delivery and continuous deployment are very different beasts. Continuous delivery is about always being ready, at any point in time, to release your software. Continuous deployment, on the other hand, is about having such confidence in your automated testing and deployment procedures, that you automatically release a change straight from development into production (via other test servers of course!) without human intervention.

For a healthcare DBA, continuous deployment for the database is a pipe dream, primarily for compliance and security reasons. However, Continuous Delivery practices can really help, in such environments, to ease the often-painful path to shipping software. It means that the development team implement extensive and automated integration testing, with early and regular deployments to production-like environments, with approval gates for DBAs and compliance staff to check prospective releases long before they need to go to production.

Still, even two years on from hearing the “put a gun to my head” declaration, we’re still seeing relatively few customers practise full continuous delivery for the database, a very small number practising continuous deployment, and a far higher number still not doing either.

So what’s the biggest stumbling block? Is it the available technology and tools, lack of time, no management buy-in, or something harder to pin down, such as team processes? Of course, it’s probably a tangled web of all of these things. The only way forward is to unpick one knot at a time.

From our experiences at Red Gate with continuous delivery, and from speaking to our customers, these are the knots to tackle first:

  • Get buy-in from the whole team - make sure the business fully understands the benefits of being ready to deploy at all times, and the processes required to get there. Without this your change program will fall flat before it has even started
  • Get the database, kicking and screaming, into source control - and your team regularly checking in changes
  • Fully automate your testing and deployment processes - this will still require a lot of investigative work (and perseverance), but the tools are getting stronger all the time

There is no simple short-cut to implementing continuous delivery. For most of us, it is a long-term goal that requires many step-wise changes; however, it is no longer a pipe-dream.  Is it worthwhile?  I’d say so. Being able to ship database changes frequently and safely provides a number of benefits to any organisation, however complex its systems happen to be. That DBA won’t need a gun to his head since the checks, investigations, and tests that precede deployment will be made far easier to perform.

12 Responses to “Continuous Delivery for Databases – a Pipe Dream?”

  1. Robert Young says:

    Having spent virtually all of my database life on one side or the other of VAR applications, both versions of ‘continuous’ are not practical. The reason is simple: in the VAR environment there are no clients with installs which are pure vanilla, aka unmodified. Even if a VAR has universally succeeded in partitioning both code and database modifications into separate spaces.

    For an organization large enough that it either has built its application from scratch, or bought the original source and modified itself (off-line from the vendor, has been common in mainframe legacy healthcare, as it happens), then it’s possible.

    Of course, Organic Normal Form™ schemas mean that data is fully orthogonal, and code which is explicit (no select * and such nonsense) to the data means that collisions will be rare to never.

    • Ben Rees says:

      Robert – thanks, that’s a good point. I’ve not distinguished here between users supporting in-house systems and those supporting 3rd party clients. These are (generally) two separate problems – most of what I’ve written here applies to the former and there are additional difficulties supporting remote installs which, as you say, have been modified by the clients.

      Though it’s not impossible! We have a couple of users who do have this scenario and implement a partial solution – a “core” app which is running under CD, with filters applied to filter out the modified, customer specific parts of the app/database. Of course it depends on the number and variation of your clients, but it can work in limited circumstances.

  2. willliebago says:

    The overhead of the “knots to tackle” are not insignificant and do not directly generate revenue. To some businesses, this will always be a pipe dream.

    • Ben Rees says:

      I agree – so the need is to build a case for the change in process with your boss and possibly your boss’s boss. For example, when we visit customers at Red Gate, interested in CD, we ask “Is your boss bought in to this? Or is this just an idea you’ve had?”. If the boss and/or team aren’t involved and bought in, it’s pretty unlikely the project will turn out well.

      NB: There are some really good books that put the case for this change. Two off the top of my head:

      1) A Practical Approach to Large-Scale Agile Development: How HP Transformed LaserJet FutureSmart Firmware.

      2) Lean Enterprise (Jez Humble, Barry O’Reilly, Joanne Molesky).

  3. Keith Rowley says:

    Working in the healthcare industry I understand the reluctance to let database changes be pushed without lots of review. While data breaches are the big worry in terms of Hipaa, “loosing” data can also be considered a Hipaa violation leading to huge fines etc.

    Outside of this I think a lot of the problem just comes down to the traditional turf war between developers and DBAs. My solution is to put them in a small closet together like a couple of fighting cats and let them work it out.

    • Ben Rees says:

      Keith – Thanks for your comments.

      For HIPAA we often here things like “If we’re going to have continuous delivery, we also need continuous control (and oversight) of what’s happening” – and this is obviously particularly relevant with the database compared to just implementing CD for a webapp. We recognise this and, over the last two years, have shifted a lot of effort towards this sort of control functionality (review steps embedded in the automated process, filters allowing you to remove certain objects, better control over data passing through the system and so on).

      On your second point – for me the Devs/DBA divide is similar to the Devs/Ops divide from a few years ago, before the advent of the DevOps culture. Hopefully, as has happened with DevOps, we’ll see DBAs and developers working more closely together as a single team, but this will happen at a different pace in different types of organisation.

  4. paschott says:

    Probably one of the keys to Continuous Delivery is a solid testing effort along the way and a good definition of when your work is “done”. We insist that our code and DB changes are tested and that they are released to our customer preview environment in order to be considered complete. With that definition, we have a lot more confidence about releasing to production at any time. The challenge tends to come with conflicting DB changes or perhaps changes that shouldn’t be released without the accompanying code changes. For that reason we tend to delay our merge of completed stories to the main branch until we’re ready to release. Once ready, the merge often takes a minute or two and we can push quickly.

    We have our team on board with being ready to push code at any point and the DB is in version control. Our tests are automated as we write new code or update old code. I don’t think we’ll be ready for continuous deployment any time soon, but I’d say we’re getting closer for our Dev and QA environments.

    • Ben Rees says:

      As I say, I think I could count the number of customers running full continuous deployment for their database on the fingers of one hand! Far more common is some sort of Continuous Delivery where, as you say, you have some sort of automated testing set up, but then manual steps before production. Then often, there will be manual test processes as well (on say, a UAT system) before changes go live.

      NB: On the separate of issue of DB/app updates needing to be deployed together – have you tried something like “branch by abstraction” as a pattern to help deal with this? Or is that a complexity too far? Slides 103-108 of the following slide deck, from Niek Bartholomeus explain the process in more detail:

      • paschott says:

        We do have a “Light/Dark” concept in our app, but when we have multiple stories all working at the same time, it’s hard to coordinate those changes properly, especially as we still don’t have everything promoting at the same time or even sequentially. It does make life a little easier when we need to push out changes for a smaller subset of our customers, though.

        Looking at that particular example, we don’t tend to need to do a lot of side-by-side DB Schema changes. Proc changes are more likely in those scenarios and we try to keep from adding versioned procs when possible. Slide 110 may be a little closer to what it seems we often do. :)

  5. TodConover says:

    Critical to all these “continuous” schemes is the objective. Continuous delivery is a technique only – a means to an end. If the DBA, and users, have no need for continuous delivery than so be it. Don’t get all excited that they are not contributing to the craze and somehow missing the boat. You say continuous delivery means always being ready to deliver. Maybe that isn’t a priority for them.

  6. paschott says:

    @Tod, I’d probably argue that most of those places have more need for some sort of Continuous Delivery than they’d admit, if only to have a streamlined release process. We went through years of manual and very tedious releases until we managed to get a handle around the whole process so it is now almost to the point of clicking some buttons. That releases the bits, updates configs, cycles any app pools or websites that need to be updated to take effect, and pushes DB code. All of that was tested prior to releasing to production so we can often push a production change in the middle of the day with no issues. Ben posted a good link above for the “Orchestration in Meatspace” presentation. It was pretty good and worth a little time perusing, though the video is likely helpful to get the most out of it. :)

    Sure, not everyone needs to be on CD, but some of the results of that process are likely desired by those who have to do the releases even if the “Continuous” piece isn’t strictly necessary.

Leave a Reply

Blog archive