You hear often how DevOps doesn’t work with databases, or conversely that the people responsible for databases don’t work with DevOps. Neither should be true, but both frequently are.
When people say that “DevOps doesn’t work with databases,” what they tend to mean is that it’s difficult to automate many aspects of database development and deployment, due to the persistence of data and the size of the database. While, historically, there has been some justification for this side of the argument, there isn’t any for its flip-side. If DevOps means Developers and Ops sharing their skills for mutual benefit, then why shouldn’t that work when applied to databases? In my experience, unfortunately, developers and administrators have a poor track record of collaboration and open communication.
Start with difficult, mainly manual, database development processes, then vigorously stir up emotions by employing poor personal interaction skills, and suddenly bad judgement is everywhere and database projects either fail, or don’t work as advertised, take a lot longer than they should, and cost a lot more. You’ll often see all of that combined. I have just such a story.
Let me start by saying that a substantial part of this story is my fault. I don’t think I can take personal responsibility for everything that went wrong. However, I am responsible for a healthy chunk of it. So, before we get to the story, let me explain why I think I’m partly at fault.
I’m probably not the nicest person in the world now, but I used to be a stone-cold jerk. I didn’t see my job as a service. I didn’t realize that by being a servant-based leader, I could get a lot more done. Yeah, my nickname was The Scary DBA, but I mostly got that from just looking scary. I had another nickname, Rant. You know, a play off my name. However, it was really a play off my personality. I’d snap easily, and start wailing about how something was just plain wrong, stupid, whatever. Quite rightly, it turned a lot of people off.
To quote the greatest philosophers of our age, I got better. I began to see that by being nothing but an irritant, even when bad or dumb things really did happen to the database, I wasn’t helping myself, nor my team, nor my employer. Since I liked the team and liked my job, I had started to turn things around. However, for one project, it was too late.
I can’t go into details about this project. I can’t name names. I don’t know who made all the decisions that were made, and other than my own part in its downfall, owned to up front, I can’t tell you who was responsible. So, in order to protect the innocent, I’m going to protect the guilty as well.
This project had already launched and failed a few times. Frankly, it was a difficult project, supporting a fundamental, but very complex, business process. Let’s face it, if it was easy, it probably wouldn’t have a lengthy, and hopefully entertaining, as well as enlightening, story attached.
An outside contractor was brought in who had a wild new idea: code first. Combine that with an Object Relational Mapping tool (ORM) and some special secret sauce that only he knew, and this difficult project would not only succeed, but he’d get it 100% complete within 18 months.
“Oh, but no DBAs. No database developers. They’re really not needed, thanks to the magic of the ORM tool and, I’ve been told, they’re the main reason that previous attempts at this project failed.”
Now, upon hearing this, the old me would have freaked. The newer me looked at old me in the mirror and said,
“See. This is your fault.”
I requested to be involved in the project as an observer so that I could take the superior and successful (well, in theory, they will be eventually) processes to other projects. This was granted, so I really did get to learn how things were done under this new methodology.
I went to classes and learned about code first development. I also learned all about this new ORM tool that was going to revolutionize our database development. The code first approach made some sense to me, although there were aspects of skipping database design entirely that bothered me. The ORM tool also seemed to be a miracle. It would not only write the queries for the database, it would build the database entirely and deploy it, all automatically. Heck, I’d been working for years at that point trying to make database deployments easier and more automated. I was even a very early adopter of Data Dude, which became Visual Studio Database Tools. I’m not simply a believer in DevOps. I’d already implemented one aspect of DevOps, the automation, multiple times on multiple projects. I just wish I had been better at practicing the foundation of DevOps, communication.
One thing I had learned, and it still holds true: the devil is in the details. So, I sat down and read through the documentation on the ORM tool, wrote some code, did a bunch of research on the internet. It seems that most, but not all, of the claims were true. However, there was a fairly long list of caveats and cautions about how to implement the ORM tool. I started documenting best practices and things to avoid. I put it all together and called a meeting with the development team.
Sitting across from me at the meeting were several people who, let’s be honest, I had treated badly in the past. We went through my document. I presented what I thought was a very good case for a light database developer involvement, just to be there to catch any queries that went south and needed to be tuned or rewritten manually. Based then on my research and now on very long experience, anywhere from 2-5% of queries generated from an ORM tool are weak at best, and seriously problematic at worst. You will need to write a few queries by hand, but not very many.
However, I was completely dismissed out of hand. Oh, and, asked not to take part in the project any more, even as an observer. And so, things went ahead without me.
And so, I spied on them.
Yeah, they’re using our database servers and I have ‘sa’ privileges, and this was development, not a production system, so I set up SQL trace and watched the queries being generated. I also looked at the database design regularly. They were violating just about every single suggestion I had made. They were effectively using the “Hello World” example from the ORM tool as a set of best practices and developing from there.
Of course, spying on people is not a nice thing to do, proving that back then I was not a nice person. Remember, also, that this situation was one of my own creation. If I hadn’t been so difficult in the past, they’d have had no cause to dismiss my input now.
The new code first project was running long; and during testing they were hitting issues with the database. All sorts of issues. Performance, deployments, more. So, they contact me or my team, right? Nope. They called in a Microsoft consultant. Great guy. We knew each other. He called me to let me know he was going to be in our office. He asked me about the project. I was honest. I explained that because I was an ass previously, I had been kept out of the project. Yes, my pride was wounded, but I really didn’t blame them for this; it was on me.
Of course, I knew some of what was going on, so I explained it to him. He then did something that I’m not sure I should thank him for or not. He invited me to the meeting.
I show up at the meeting and I’m getting a lot of unkind stares from people. My consultant friend shows up and says, “I hope you don’t mind, I invited Grant to sit in.” I said, and boy, I’m so lucky I said this, “I’m just here to observe. I won’t take part or talk at all.” With that, I was allowed to stay, and the development team proceeded to explain the issues to the consultant, and show him problematic code, which I’d already seen.
Now, remember, the entire database had been built in a fully-automatic fashion from the ORM tool. Most tables had no clustered index. Many of the tables, maybe even most, had no primary key. This was SQL Server, and yet there was not a single foreign key in the database. They had basically turned the relational storage engine into an object storage engine, but SQL Server is not an object storage engine. It was not working. Not at all.
The queries were…well, if you’ve seen some of the 5% of queries generated by an ORM tool that are problematic, it looked just like those. There were IN statements with 500 items or more, different parameter types for the same column, and lots more. The consultant saw all this and then started to address it.
First words out of his mouth were, “Well, as Grant probably already told you….”
The room went quiet. He outlined the issues with the database and the queries, frequently repeating something along the lines of “I’m sure Grant already said this but…”
I was dead silent the whole time, and if you know me well, that’s a miracle. No one looked me in the eye. I was starting to feel stiff and sore, because I was trying so hard not to move, lest I get noticed. The atmosphere in there was that bad.
And, throughout the meeting and afterwards, I was beating myself up. I recognized that because I had been extremely difficult, if not downright impossible, to work with in the past, my organization was now suffering. I had screwed up horribly. What’s more, I recognized that many of the people in the room were leaders in the organization. I’d burned bridges in the past with all of them. I decided then to get a new job.
The project kept going. It ran very long. It ran very over budget. Further, when it was finally delivered, the database as configured, worked, albeit slowly (they never did fix all those issues), but it was impossible to generate any reports.
The object storage model they’d imposed on SQL Server couldn’t be sliced across the tables in the way that was possible with a relational model. They could use the tool to pull data out, but that was all. For the reporting, they had to build a data mart database, designed fully as a relational database. It had primary keys, foreign keys and all the rest. They then had to build a special replication process, with more Microsoft consulting help (those guys rock by the way) in order to get the data from the barely functional database into the data mart.
Worse, deployments from the ORM tool worked wonderfully…for the first deployment. After that though, the tool didn’t protect the existing data. It treated the database like code. How do you deploy code? Drop the old stuff and put the new stuff in. Doing that in a database results in lots of pain. So, another process was built to capture the changes made in the database in one location and then create a safe script for getting the changes into production.
All this added time, cost, all sorts of overhead, and all of it was because I’d previously behaved as a big enough jerk that the development team, at every possible point, resisted having someone with database knowledge involved as they built a database.
So what lessons can we all learn?
First, don’t be a jerk. I hurt myself and negatively impacted my career in many ways because I was not acting like an adult, let alone a professional. More damagingly, my personal failings soured many the development staff on the whole database team. So, instead of just kicking me out, or heck, firing me, they decided to kick out anyone with database knowledge. That hurt the organization.
Next, databases are persnickety beasts. A successful database DevOps project will need someone on the team who has deeper knowledge of how the database behaves, whether or not they’re called a DBA. That person is going to help you develop the database, yes. They’re also going to help you safely deploy the database. Finally, and this is the most important part, they’re going to be around for ongoing support of both these processes, as well as deal with emergencies within the database system as they arise, as they will. All this is regardless of the type of database system you’re dealing with.
DevOps works, and works well with databases. However, DevOps really is about communication. If you’re not working hard to communicate across teams, if you’re actively isolating teams, then you will cause problems for yourself down the road. DevOps projects succeed all over the world, all the time. However, they succeed or fail on the culture of communication you build. So, as individuals, don’t be a jerk. As a team, be inclusive. As an organization, ensure that your jerks are kept in line and your teams are involving all the stakeholders in a project. Then, your DevOps implementation will also succeed.