back to article The Agile and the Continuous: Database Drift ... Neat film title but something to avoid

In DevOps the talk is of development and operations, of continuous pipelines and agile updates, of rolling out builds. But it often overlooks something rather critical – the database. Be it in development or production environment, the database is a critical piece of infrastructure. And everybody in an organisation wants a …

  1. deadlockvictim

    Entity Framework

    Our devs have Entity Framework. They don't need no steeeekin' DBAs.

    Besides, the DBAs have something against the devs using NVARCHAR(MAX) for all of our text fields, DATETIME2(7) for dates and they get all stroppy when we want to use GUIDs for primary keys.

    Prima donnas, that's what they are.

    The database is somewhere where you dump data. That's all it is.

    1. sabroni Silver badge
      Coffee/keyboard

      Re: Our devs have Entity Framework. They don't need no steeeekin' DBAs.

      Thanks for that! Needed a little humour to lighten the mood.

  2. Bill M

    Crossing the fence

    Over the decades I have crossed the fence a few times from Dev to Sys Admin to Jack of all Trades to DBA and finally back to Dev.

    This had been a curious journey but left me with an understanding of most sides of the fences. But the bottom line is that many things are 24*7 mission critical and I have been on a fair few panic conference calls when things have gone tits up. I remember one when I was DBA'ing with too many people on it, but with a Dev I was homing in on the cause of the issue but the Global IT Director kept butting in and wittering on, so I told him to either shut up or f*** off. He did shut down up, but telling your Global IT Director to f*** off can be a high risk strategy with regard to one's ongoing employment. We diagnosed the issue and I created the missing indexes, albeit on the fly with zero change control but sometimes a DBA it is required to do such things when the company is losing wedges of money every minute. The next day the Global IT Director sent me an email giving a cash bonus, a pay rise and invite to lunch. The only advice he gave me was to use the word please to prefix any request for him to f*** off.

    The moral of the story is that Dev's & DBA's need to be able communicate and get on well with each other, because it is inevitable that every now and then the proverbial will hit the fan and the solution will need the skills of both.

  3. David Harper 1

    Crossing the Fence 2

    Like Bill M, I've been on both sides of the fence, as well as straddling it for a while when I managed a cluster of MySQL databases for my development team before the in-house 100%-Oracle DBA team would admit that MySQL was actually a database system.

    During my developer days, I worked alongside some bright young things who were evangelising Ruby-on-Rails. According to them, ActiveRecord was the dog's bollocks. You never had to write a line of SQL, and it automatically ensured the referential integrity of the database without the need for anything as 1970s as foreign key constraints. Imagine their surprise when the production database started to show inconsistencies such as phantom relationships and missing entities. When I was asked to add foreign key constraints, I ran a consistency check on the existing data and found a huge mess. I left the team to become a DBA soon afterwards.

    1. Doctor Syntax Silver badge

      Re: Crossing the Fence 2

      Like Bill and David I've been on both sides including having a job that covered development and production. My one question of those who need to make frequent changes to the database is "Why?".

      Once a database, or at least a line of business database, in in production it's holding data that's practically and legally significant to the business; the work the database does is paying your wages. Making changes has costs both in terms of risks and performance, the latter possibly involving downtime. In code your can patch and re-release. If you damage the database you may not be able to recover lost data and may not have the information to reconstruct what's missing if you have to revert to a backup.

      As a developer you really should have a good understanding of the data so that by the time the system goes into production the schema should provide a good representation of the real world. If you are making frequent schema changes you need to stop, review and put the database into the shape it should have been in the first place. If there are frequent changes of requirement of kinds you really can't anticipate (experience comes in useful here) then maybe you need to review your strategy.

      1. Anonymous Coward
        Anonymous Coward

        Re: Crossing the Fence 2

        Seems there are a few of us who have walked both sides of the line - although technically I walked the line as a DBA/Developer.

        DBA's don't have to be a speed bump to DevOps sprints, but it does need good collaboration between the DBAs and the Developers. Indeed, having a DBA working alongside the DevOps (or just the Devs) is a way to achieve this. It's why I was moved into the Dev department, and why I came to write a lot of the SQL scripts needed by the Dev team. Can't say it was perfect, but it worked.

  4. SVV

    Fixing up the problems with Continuous Delivery and DevOps

    "They would have to wait between seven and twelve business days until the DBA got around to it."

    Hmmm, if only we'd had someone to plan and co-ordinate the release, sort of like a manager of some kind. Then the synchronisation problem could be solved..... and we'd be able to put a process in place that solves such problems in the future. Some sort of "release management" process maybe? Maybe we'll have to have some DevOps that are a bit more Ops than Dev to do this..... and to balance that out, the remaining DevOps can be more Dev than Ops.

    By the time they've worked through all the problems that occur due to their Continuous Delivery thesis causing problems, I suspect the situation will end up looking a lot like Dev and Ops being more seperate again, with management co-ordination between the two playing a pivotal role. But it's still a good idea that everybody should have a good understanding of both.

    1. Pascal Monett Silver badge

      Re: Fixing up the problems with Continuous Delivery and DevOps

      You can mix and change titles as much as you wish, the function of DBA and the function of Dev (Ops or not) are not going to change.

      As either one of these functions is quite complex and requires specialization, it is a very rare person who can reliably pretend to be both, therefor each should listen to the other.

      1. Doctor Syntax Silver badge

        Re: Fixing up the problems with Continuous Delivery and DevOps

        "As either one of these functions is quite complex and requires specialization, it is a very rare person who can reliably pretend to be both"

        Pascal, I rarely disagree with you. But in this instance I have to. I found it easier to to mix the two roles. Where I've been DBA for a system with an external vendor and a developer where the roles were split. I found each role more challenging than combining the two. Being DBA for what you develop feeds back into ensuring that what you develop is right and as a developer you understand what the database is doing. As DBA for someone else's code I've had to spend time going through the code to find out what they were doing to crash the engine (why did it become by job to teach someone else's devs what they should know?).

        1. handleoclast

          Re: Fixing up the problems with Continuous Delivery and DevOps

          Being DBA for what you develop feeds back into ensuring that what you develop is right and as a developer you understand what the database is doing.

          Until this thread, it never occurred to me that the two would be separated to that extent (I've obviously led a sheltered life).

          Fred Brooks had this to say:

          Show me your flowcharts and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won’t usually need your flowcharts; they’ll be obvious.

          Linus Torvalds had this to say

          Bad programmers worry about the code. Good programmers worry about data structures and their relationships.

          Yes, Linus was talking about data structures in the code itself, but anyone who thinks the principle doesn't extend to databases that are accessed by code is an idiot.

          I've encountered enough problems when the devs did design the database (but didn't think things through). The idea of two different teams working on the code and the db with little communication between the teams gives me the heebie-jeebies.

  5. Nate Amsden

    risk adverse

    I remember a time many years ago at a company we were in the midst of maybe a 6 hour outage, we determined the most likely way to get the app up and going again was to truncate some tables on the Oracle server that the app was stuck with. In theory the data in these tables was not critical and could be re generated if needed but the developers were not 100% sure.

    So the time came for I think the VP of engineering at the time to request the DBA to truncate those tables or maybe he was a director I forget this was over 10 years ago. I'll never forget the response it was so funny. The DBA said something along the lines of "I'm not truncating shit until you get the CEO of the company on the phone to approve that change", a few minutes later the CEO approved it, tables were truncated and the app came back online. Had to repeat that process a couple of more times over the next year I think(not needing the CEO's approval anymore).

    1. Doctor Syntax Silver badge

      Re: risk adverse

      The DBA said something along the lines of "I'm not truncating shit until you get the CEO of the company on the phone to approve that change"

      On the phone? A real DBA would want it in writing.

  6. cquinton99

    State-Based vs. Migration Based

    Great insights.

    Yes, there are two ways to tackle database version control: state-based (declarative) and migration-based (imperative). However, I think it’s worth shining more light on the qualities of the two, especially as they apply to DevOps.

    The migration-based (imperative) is the best solution for teams looking to safely automate and accelerate the database release process. Leaders like Martin Fowler make the point in depth. ( see article: https://www.martinfowler.com/articles/evodb.html) The issue of multiple developers and parallel development efforts leading to Frankenscripts is only an issue when there is no governance. It’s a matter of making the process change to check in all code – including database code – into source code control. As a further step, you can treat database code just like app code and manage change deployments to databases using automation tools (tools built to specifically automate and manage database deployments are out there – go out and find the one that’s right for you) – effectively eliminating undocumented, out-of-band changes.

    If everyone has to check in code, and if automation is the only way to make changes, you eliminate the Frankenscript scenario. And let’s be honest: a process change to apply source code control to database code is a relatively simple fix; if you are really struggling to ask developers to check in database code, you have issues much worse than your database release process.

    There are two fundamental reasons why the migration-based approach is the indisputable preference for bringing DevOps to the database release process. With migrations, the change itself is a first-class artifact, enabling the “build once, deploy often” philosophy, and enabling predictability, consistency, and automatability. The production release is not going to be a surprise if you are deploying the same artifact to it as you did to the test server. Next, the migration-based approach promotes small, incremental changes – another central tenant of Agile software development. The state-based approach doesn’t meet either of these requirements and necessitates manual review and oversight in addition to its other shortcomings.

    For more complex database environments, the state-based approach is actually substantially worse than the migration-based approach. Let’s be serious: you really want or expect everyone who needs to work with the database to contend with the full declarative state model of a large, complex database? And yet somehow asking developers to check in code is hard?

    And are you really going to rely on a tool to generate a script to get from an already complicated state to the future state you have declared without having it trip over potential data migrations and the scores of views, procedures, and functions that are interleaved in a complex web of dependencies? And when something inevitably goes wrong, such as the simple case of splitting a table, are you going to force developers to hand craft the solutions with a complex state representation? Honestly, who are you kidding?! A state-based approach to large, complex databases is impractical unless you are an academic employing a team full of data architects. For those of us software developers in the real world, the small, incremental changes with a migration-based approach is the only practical way to for us to work with large, complex database systems in an Agile/DevOps environment.

POST COMMENT House rules

Not a member of The Register? Create a new account here.

  • Enter your comment

  • Add an icon

Anonymous cowards cannot choose their icon

Other stories you might like