Enjoy the Podcast?
People who play an active role in delivering database schema updates and who are interested in what DevOps is and how it can help. Your role may be as a developer, DBA, manager or something entirely different since DevOps is not a responsibility of one individual, but a shared way of working that effects many different roles.
This talk is called DevOps 101 for a reason. It is an introduction to DevOps aimed at data folk. This session will be relevant to experienced and inexperienced data people alike – but if you have already adopted DevOps and are looking for advanced DevOps content or the answers to difficult DevOps problems you will probably be disappointed by this session. Tweet me and let’s start a separate conversation instead.
This is an introduction to database DevOps, that is all.
In 2009 John Allspaw and Paul Hammond delivered the session “10 deploys per day – Dev & ops cooperation at Flickr.” In forty six minutes they changed the way millions of people would think about the software delivery process for years to come. It didn’t have a name yet, but DevOps was born. DevOps folk preached about the cloud, automation, rapid delivery and any database technology that wasn’t relational…
In 2013 Kenny Gorman declared “The DBA is Dead”.
For the record, I don’t believe that, but a lot of people do. What is certain is that the world of IT is changing, and the traditional DBA role, and most other data roles, are changing with it.
I’m going to explain what DevOps is, where it came from, and its implications for SQL Server. We’ll cover the human and technical basics of database DevOps – and I’m going to discuss some changes that data folk need to make.
Why I Want to Present This Session:
I love DevOps. I also love relational databases.
And I enjoy solving hard cultural (and technical) problems.
Open disclosure: This session will feature demos of multiple tools, including Microsoft tools, open source tools and third party tools. Call me a heretic, but I’ve seen a lot of DevOps/CI/CD/DLM sessions where presenters just show their favourite tool or technique and it feels like a sales pitch. Having specialised in the database DevOps space for six years my aim is to demonstrate various different techniques and their relative pros/cons – which I think is more useful. Comments on this approach are very welcome. I will read them and may make changes based on feedback.
Slides and other resources:
The slides, video and all the resources associated with this talk can be found at:
Brent Ozar: In this next session at GroupBy, Alex Yates is going to be talking about DevOps for database professionals. So Alex, go ahead and take it away.
Alex Yates: Hi there, so I want to talk about DevOps. I know that DevOps often feels like it lives in a different part of the world from SQL Server but I think that’s largely a shame because I actually think that getting the database right when we apply DevOps is one of the hardest and most difficult bits and almost one of the most important bits, DevOps claims there are many, many benefits, reliable deployments, quick deployments, quick feedback loops and database deployments are one of the hardest things out there, and when they go wrong they go wrong badly and people can get fired. So if we can get some of those benefits for SQL Server, I think we’re all in a much, much, much better place. A bit of setting the context, this is called DevOps 101. If you’re already doing DevOps and you’re looking for a really advanced DevOps session here, this isn’t the session. This is an introduction session about DevOps for SQL Server professionals who have kind of heard the word and are kind of wondering what it is and they might think it’s something cool but they’re not quite sure, so I’m going to hopefully try and shed some light on that. A little bit about me, my name is Alex, I’m a consultant with DLM Consultants. I used to work for Redgate, so I spent about six years working for Redgate, helping people adopt database DevOps using various tools and around the middle of last year I started my own company, kind of going out as a consultant and helping people to bring the benefits of DevOps to their own environments, so please do get in touch if you want my help. My Twitter handle is @AlexYates the hashtag for the event is GroupBy.
Throughout this session, there are going to be various links and references and blog posts and things I’m going to be referring to. I know it’s really annoying when somebody puts a really long link up at the screen and then everyone’s kind of down on their notepad trying to jot it all down, so what I’ve done is I’ve automated a bunch of tweets to go out with the GroupBy hashtag. Any time I reference a blog post, if I’ve got my timing right, then it’ll be roughly there on the Twitter feed at about the same time, so hopefully you won’t need to do any kind of screen grabbing or anything like that. Now, I’d never seen Kahoot before until about an hour ago. It looked kind of fun, what Hugo was doing, and I thought I can use that. So what I’m going to do is I’m going to play this Kahoot game, so if everyone can go to Kahoot.it.
Brent Ozar: You guys all stand a much better chance of winning this one. I knew functions really well, I don’t know…
Alex Yates: So once I got a whole bunch of people in I’ll click start. How many people joined in last time? About 80?
Brent Ozar: I think it was like 80 or 100, somewhere in there.
Alex Yates: Okay, so let’s hold on until we get to about 80, 100 or so. I see Not Brent has just joined in.
Brent Ozar: Not Brent is going to win.
Alex Yates: SQLGrinder.
Brent Ozar: Wow. Alright, Nosantino, I see Anthony Nosantino in there. For those of you who weren’t in on the earlier sessions, you can use your phone if you want to join in with this, laptop, whatever. It’s like a speed game where you click quickly on the answer that’s most correct, then you get to see your name in lights.
Alex Yates: So I’m actually just using this for a very, very simple task. I just want to work out what sort of roles the audience have. Are they developers? DBAs? Which side of the DevOps wall do they sit on? Are they managers? So we’ve got a hundred players now, that feels like a good number, so I’m going to click start and if you all look at the main screen now, you’ll see a question. What is your role? And you should see a bunch of different options there. If you’re a developer, a DBA, manager or I don’t fit into your crazy stereotypes. Of course, it could just be an approximate, and this will just be interesting for me to work out kind of what my audience is, because I might judge the way I deliver the session. So got mostly DBAs, got a healthy number of developers as well, we’ve just got one manager in there and a bunch of people that don’t fit into my crazy stereotypes. So I’m surprised there are so few managers but the Dev DBA divider is about what I expected. What I normally do when I’m doing this session in a room, I normally get people to stick their hands up and I get all the DBAs on one side and all the developers on the other side and if we’re lucky, as many people leaves as went into it. So who’s read any software books in the last 20 years? Any of them.
I’m going to guess it had some of these words in, or at least if you’re read any kind of developer kind of business-y books about software development, it’s probably had some of these words in and the point is that as an industry, we’ve worked out that it’s much more effective way to deliver working, reliable software if you deliver small amounts frequently. If you have a six-month release cycle, that means every time you try and release your product, you’re releasing six months’ worth of code. I don’t know about you, but whenever I’ve tried to write six months’ worth of code, there’s been at least one bug in there somewhere. It’s also a lot harder to release that much stuff. It’s a lot more complicated. If you only release once every six months, you’re not very good at doing it because you don’t practice it regularly. If it’s a manual process, you’re going to screw stuff up, you just are. So what all of these processes are about, they’re about increasing that frequency. Can we release more frequently and can we really, really, really optimize that process for getting my code from Dev into production? So that’s one of the core things that a lot of these concepts have worked out and we really want to kind of – we want to be deploying more frequently in a faster way. Obvious benefits to the business, and that’s the language that developers tend to use and of course, I’m going to start by saying that I’m going to be stereotyping massively during this session.
There are some core truths that may be under there somewhere, I am using massive stereotypes. Generally, that’s what a developer thinks about. Unfortunately, there are two sides to this – I say unfortunately; unfortunately, we have got a divided group of people that look after kind of the database and we have a separate group of people called DBAs and frankly, DBAs don’t care about that stuff because they’re not paid to deliver the next new feature. DBAs get paid to look after the data, and that’s totally right. If I wanted to hire a DBA, I would want to be hiring a paranoid, control freak with OCD because that person is probably going to dot all the I’s and cross all the T’s and make sure my data is safe, and I care about that because if I have a business, I want the data to be safe. I simultaneously want safe data and I want to be able to take my business forward. So we have these two different objectives that sometimes conflict with each other and we’ve got two different groups of people that are largely responsible for these two different objectives, and sometimes they do conflict with each other. Sometimes we do need to compromise and if the DBA is going to get fired if we lose the data, then he’s not in a position, or she’s not in a position to compromise. If the developer is going to get fired, if they can’t ship the new update, which is really, really important because otherwise our competitors will beat us, then they’re going to be frustrated whenever they’re held back. So we do get these kind of two warring silos and I don’t know, Brent, if you’ve experienced that in the past but I certainly experienced it an awful lot in places where I’ve worked.
Brent Ozar: Yes, and I’ve been on both sides of it too.
Alex Yates: Yes. So there is this recognized divide between Dev and Ops or Dev and DBA and there was a talk in 2009 at Velocity conference. 10+ deploys per day, Dev and Ops Cooperation at Flickr. This was by a guy called John Allspaw and a guy called Paul Hammond, and they told a story about how their developers and their DBAs have teamed up and they’d shared the collective responsibilities and as a result, they’d been able to build a really awesome automated process that allowed them to release so many times a day, and at the time, Flickr was just kind of taking off like that. So they were very, very easily able to demonstrate the relationship between successful cooperation, delivering frequently and business results, and at the end of the day, we all like getting paid and the easiest way to get paid is to make sure our company gets paid and so, if we can create a successful company by working together an awful lot more effectively, well, these guys certainly got the world’s attention. And that’s traditionally what people start talking about DevOps from this session. However, I’m going to go a little bit back before that and the structure of this session, I’m going to talk a little bit about the history of DevOps, where it came from, who the main people are that talk about it, what the main concepts behind it are, what it is and what it is not. I’m then going to talk a bit about the difficulties of applying traditional DevOps to databases, because databases are hard.
There’s a reason why a lot of people say it doesn’t work for databases because databases are harder. There are some problems that we need to think about. I’m then going to have a very, very brief demo. I’m not going to go into a huge amount of technical detail, I’m not going to show you how to set it up, but I’m just going to try and show you kind of some of the concepts working, just to try and help you understand, look, this is what I mean by source control for database and automated build and automated deployment, just to kind of give you an idea about some of the ways that we can put this together. I am going to talk about two fundamentally different ways of doing it. There’s kind of a migrations type approach and a model or declarative type approach and people have different opinions about which way is right and which way is wrong, and clearly anybody that says one is right and the other is wrong is stupid. It’s actually more complicated than that, so I’m going to talk a bit about that, and that’s more or less it. So hopefully by the end of this you’re going to have an introduction to what DevOps is, how it relates to the database and how we can begin to think about taking some of those benefits and applying them to our day-to-day development, whether we’re in a development role or a DBA role or heaven forbid that one manager who’s on Kahoot.
So where are we going to start? We’re going to start in 2008 at the Agile Toronto Conference, and there’s a guy called Andrew Schaffer. Now, the Agile conferences are a very kind of cutting edge group of conferences. Agile was all the rage and these guys, a bunch of blokes that had just gone on a ski holiday somewhere, and he proposed a topic, Agile Infrastructure. Like people had been talking about Agile in the context of software development for a while, but how can we apply that to kind of servers? Machines, physical pieces of equipment, how can we do that? And he proposed it and one person turned up. One person turned up. It was Patrick Debois, and when I say one person turned up, I mean one person turned up because Andrew didn’t even turn up to his own talk because everybody there had told him don’t bother, this is a stupid non-starter conversation, nobody is going to be interested in Agile Architecture, it just doesn’t make sense. Patrick went and found Andrew at that conference and had a conversation with him in the corridor afterwards and they had a really, really insightful conversation.
Now, where was Patrick coming from? Well, Patrick – he’s a Belgian guy, he lives in Ghent, and he had done various different roles. He had worn many hats in his time and now he was responsible for a data center migration and he had to talk to both development functions – to both software functions and also hardware functions. If you’re moving a data center, there’s a hardware element and there’s a software element, and whenever you talk to these two different groups of people, traditionally Dev and Ops, they talked completely different language and it made it very, very difficult for him. The Dev folk organize their work in one way, the Ops folk organize themselves in a completely different way, and so he had to be jumping in and out of it and it made it very, very difficult and inefficient for him to be able to manage this project. So he was trying to – he was thinking about a lot of the same problems. How can we take a more agile approach to hardware? And so after this conversation, they started the Agile system administrators group on Google. Remember when Google was a social network? Apparently it was really popular with IT geeks, anyway. So they started this Agile system administrators group and that was becoming really, really popular. A lot of people were saying, look, we understand these problems, we understand that people are working in different ways, we understand that actually we have this really slow waterfall way of working when it comes to hardware and the software developers are doing this cool agile stuff, we want a bit of that, we don’t really understand how to do it. And then, as this conversation was bubbling along, in 2009 we have that 10+ deploys a day session at Velocity and at that time, Patrick Debois tweeted – he wasn’t at the conference but he tweeted, wow – I can’t remember exactly what he said, but it was something to the effect of, “I really wish I was there, this is kind of inspirational, I really wish I was part of that”, and so somebody said to him, well, why don’t you organize your own conference? Why don’t you start your own conference to talk about this stuff? Why don’t you bring the conversation to where you are? So he did, and now look at the dates. It was in June 2009 there was that session, 10+ deploys a day.
In October was the first DevOps days in Ghent and I don’t know how many of you have organized a conference and no offence Brent, but I mean like a real one where you have to get a venue, and so yes, people from all over the world attended this event and the system administrators group kind of went viral over it. People were really, really excited about it and people came from all over the world to talk about this topic, Agile Systems Administration, Agile Hardware. In the days after the event, the DevOps days on Twitter takes up quite a lot of characters so it got shortened to DevOps and that’s where DevOps comes from. It’s a whole bunch of people that are really trying to apply Agile principles to hardware. Breaching the divide between developers and operations. Now, as with all movements, you need a book, there needs to be a book to define the movement. You need a bible for your movement. Continuous delivery has a book, Continuous Delivery, and so Gene Kim and a couple of others a few years later wrote a book, The Phoenix Project.
Now, if you’ve not read the Phoenix project, you should. Unlike a lot of software books that tend to be a little bit dry and they take kind of a pamphlet’s worth of information and make a whole book out of it, because you make more money that way. What The Phoenix Project does is really insightful. It gives you the pamphlet version of six or seven of the important books about Agile and software delivery, it gives you the pamphlet version of each of those books in the context of a novel, a satire about a software development project, and within that novel you’ve got all of the stereotypes. You’ve got the grumpy DBA, you’ve got the developers who just want to make changes and keep breaking stuff, you’ve got the managers that just don’t understand the importance of this, you’ve got the awful marketing person and I guarantee you’ll read this book and you will find yourself in it somewhere. I did. I read it and I was like, oh my god, I’m that person, which was a bit of a shock for me and it’s been a wakeup call because it really did a good job of explaining, look, this is the problems that you’re causing because you’re being that stereotype or that stereotype. The way that it contextualizes these books – kind of basically every chapter kind of starts with this Yoda type silicon valley investor coming along and saying, well, here’s a page worth of releases or here’s a page worth of the goal or whichever book it is and saying, look, these are the main lessons that you need to learn and then there’s a story about – contextualizes that and puts it into place, so it’s a really fun book. However, experience stories based on fiction tend to get a bit of criticism, which it did. So afterwards – actually, just very recently, Gene Kim teamed up with Jez Humble, Patrick Debois and one or two others and released a DevOps Handbook, which is much more technical.
So if you like a technical book, go and read the DevOps Handbook, if you like a fun book that does a really good job of describing kind of the DevOps process and the story of applying DevOps, go and read The Phoenix Project. There’s also a blog post that I’ve tweeted, which gives you a reading list for all the books in The Phoenix Project that are referenced. So there’s a good list of books that you can start reading to learn all about DevOps. So that’s kind of where DevOps comes from, but what is DevOps? I’ve not yet answered what it is. I mean, applying Agile to infrastructure, yes, but let’s put some more meat on that. Well, first of all, I’m going to say what it isn’t because there are a lot of misconceptions around DevOps.
DevOps is not a tool. Saying I’ve got Jenkins or I’ve got Git or I’ve got – or I’m branching or I’m using Octapus Deploy or VSTS, saying I’m using some tool doesn’t mean you do DevOps any more than saying I have a gun means I’m going to war. A gun may be used in wars; it may be used to do other things. You may be going hunting; you might not be going to war. Just the gun isn’t the war itself, okay? Just because you have the tool doesn’t mean you’re doing DevOps, okay?
Now, when we are doing DevOps, we are often going to use tools because like, one of the principles of object-oriented programming is build something that is reusable. Don’t reinvent the wheel all the time, right? So there’s a whole bunch of stuff out there that’s going to help us do the various things we want to do and that’s great but just because you’re using the tools doesn’t mean you’re doing DevOps. Another thing that’s not DevOps is people or teams or job roles. There’s no such thing as a DevOps – well, there kind of may be but just because you have a DevOps engineer doesn’t mean you’re doing DevOps. In actual fact, in many ways, if you have a DevOps team and you give DevOps to that person or that team, you’re doing exactly the opposite of DevOps. You’re creating another silo and one of the core principles of DevOps is we’re all about breaking down silos. DevOps is something that we are all responsible for. DevOps is something that we need to all adopt, we need to collaborate more. It’s not something that we can give to somebody else to worry about. Now, a lot of people will have a DevOps team and there are many good ways that that can work and there are many bad ways that can work.
Matt Skelton has produced a really cool webpage called DevOpsApologies.com which has all sorts of information about how you can structure your team to success. It’s a really good read so I like to spread the joy about that wherever possible. Kanban, or kind of, ways of organizing your team, that’s not DevOps, okay? Using a kanban board to do, doing, done, organizing your team using TFS work items or JIRA or – the way that you organize your work, that’s not DevOps, okay? Yes, we can organize a way that we do our work and we can prioritize things and we will do that as part of applying DevOps, but just because we’re doing that doesn’t mean we’re doing DevOps, and definitely released spreadsheets – have any of you ever had those big long Excel sheets with a hundred different items that you have to do one by one manually? That’s definitely not DevOps, okay? Also, floating masses of visible condensed water vapor? Also known as somebody else’s computer, it really doesn’t matter whether the stuff is running on your machine or somebody else’s machine. The fact that the Cloud can help us to spin up and throw away environments, that can be useful. That can be a useful tool to have, but just because you’re using the Cloud does not mean you’re doing DevOps. It just means that your servers are running on somebody else’s computer, which may make it easier or harder for you to move stuff around, but it’s not DevOps. So there are a bunch of things that aren’t DevOps, and I’m sorry if you’re doing a few of those things and you think you’re doing DevOps because you’re doing those things, they’re not DevOps.
The best way to define what DevOps is, is to think about the phrase, Calms. Culture, automation, lean, measurements or metrics, and sharing. These are five principles that we want to take to our heart and we want to apply them wherever possible because the DevOps folk believe that if we start thinking about each one of these in turn and try to apply it in everything that we do, what we will end up with is a better way of working and all the stuff around kind of automated deployments and tools and all that sort of stuff, they will work themselves out in the wash. That’s a consequence of applying DevOps rather than DevOps itself. So let’s start with culture. Well that first one, that’s to do with silos, okay? We do not want to have different silo teams all responsible for different bits of the problem, okay? As an organization, your business needs to be able to release software frequently and without breaking stuff. You all own that, okay? Your business owns all of that, okay? You might be responsible for one particular part because you have various specialisms, and specialists are wonderful.
I love DBAs because they understand the database better than anybody. I love developers because they understand how to kind of actually make changes – they understand how to actually make changes and manage their changes and think about how to actually take the business forward, how to build the next thing that the business needs, okay? I love all of your specialisms and you know what, when you bring all of your specialisms together and work as a team, that’s wonderful. That’s what we want to be doing. We want to be avoiding these different silos of thinking about me versus you and all of that stuff. But the next bit is automation. One of the reasons we have such a slow laborious process is because we do it all by hand, we do it all manually. If you have a hundred steps to go through, you’re going to make a mistake with one of them. It’s about using computers for what computers are good at and humans for what humans are good at. We don’t do long division by hand anymore because a calculator is more likely to get it right.
Computers are really, really good at repetitive, simple tasks that you need to do a lot of. Things that need to be done in the same way every single time, consistently and reliably, okay? If you’ve got tasks like that, you should be automating it. There’s no sense having a human being there because frankly, we can get better use out of a human being. Human beings have got a brain, they can make decisions made on judgment calls and experience, okay? So we should be using human beings to be making those decisions, to be doing that exploratory stuff, to be coming up with the next big idea, okay? Computers aren’t yet at a place where they can do that although it’s worrying how close they’re getting. So we’re going to be embracing automation for all the tasks that are suitable for automation.
Now lean. Lean is a big one and there’s a lot packed into the concept of lean for just four letters. So I’m going to fly through a bunch of kind of points here, but really what you need to do is do a bit of research on lean if you’ve not – I mean, I could do a whole presentation on lean. I could do a whole day or a whole week’s worth of training on lean. What is lean? It’s – well, it’s the reason for this picture here. It’s kind of a methodology that originally came out of the 1980s Japanese car industry. I’m sorry Brent and anybody else that’s in…
Brent Ozar: No, big fan.
Alex Yates: In Unites States right now, but basically in the 1980s, Toyota and many other Japanese car manufacturers came up with this concept of lean and actually more recently, Eric Ries has written a book called The Lean Startup, and what they’re talking about there is embracing a whole bunch of ideas, eliminating waste, trying to become much more efficient with limited means, trying to build quality in, deliver frequently, deliver fast, think about your throughput, your bottlenecks, thinking about flow, stopping the line if you see a problem, stop the line on development, work out why that problem occurred, and then put into place some sort of test or some sort of automated process to make sure it can never happen again and then carry on. It feels disruptive to stop the – like literally, Toyota would stop the manufacturing line of cars until they had worked out how to avoid that defect happening again in the future, rather than thinking about the economies of scale, they thought about the economies of efficiency and making sure that that they just didn’t make the mistake in the first place.
Respecting people, empowering them to innovate and coming up with our own ideas, and one of the most important ones, optimizing the hole, which comes back to that silo thing. We don’t want to be optimizing for myself as a developer or a DBA, I want to be optimizing for the whole team, which means that sometimes, I need to make decisions about the way I work that benefit everybody, rather than just benefitting myself, and Eric Ries kind of put all this together and he came up with this idea of a minimal viable product, a different use of the term MVP. The idea is that – it’s all about prototypes basically. Whatever the thing is that you want to release, rather than building the whole thing and throwing it out there and 90% of ideas fail, so you’ve just wasted a whole bunch of time, what you want to do is you want to come up – pair it down to the smallest, cheapest, most efficient thing that you can build as quickly as you possibly can, throw it out there, get some feedback on it, either people don’t want it, in which case you just saved yourself a whole bunch of money with that failed idea, or people do like it, you can get some feedback and you can refine it gradually. That’s kind of the idea of minimum viable product.
Now, seeing – do be careful with this idea, because I’ve seen a lot of people use the idea of minimum viable product and they’ve used it as an excuse to throw quality out the window and they end up with a really shoddy product. So the key word for me is viable. Define viable, and part of that is defining what viable quality means, okay? What does it mean for you? If it is just a test throwaway thing, then fine, it doesn’t really matter very much. It doesn’t matter if it’s buggy. However, once you get to real production things, probably quality does matter a bit, so make sure you define viable very carefully. So once you’ve built this culture where you’re all working together, you’re all working collaboratively, you’re trying to work very, very efficiently, we then want to be gathering as much measurements as we can. We’re data folk, right? We love data, and that’s not just data about the performance of SQL server, but it’s also data about whether my users actually use this stuff. Is it useful? Do they like it? What do they like? What don’t they like? Are they paying money for it? Where is the money coming from? All of those really important questions to the business, let’s get some data on that. Let’s gather that, and also the sharing point, let’s make it available to everybody, which then, that empowers everybody to see kind of what version is where, what changes are where, what code is working, what code is not working, what’s being used by my users? If everybody can see that, we’re less likely to have issues where it works on my machine because my machine doesn’t look like your machine but I didn’t realize that, and we’re also more likely to empower people to come up with great ideas.
If everybody can see, well, clearly the data is pointing that people like that and they don’t like that, then we should do more of that and we should drop this thing over here and it means that we can much more efficiently come up with a better product for our customers. So that’s Calms. These are the core things that we think about when we’re applying DevOps and the result of that often is things like automated deployment pipelines and using Jenkins or whatever. That’s the consequence, it’s not the reason we work the way that we work. So what does this look like for your company? What does your road map for your company look like? And Gene Kim in this blog post here, and he mentions it in both The Phoenix Project and also in the DevOps Handbook, they talk about the three ways. The first thing that you need to do is think about that system thinking. Getting from dev through to production, automating that process, getting that as slick as possible, get Dev and ops talking together, working together on this process because you know what, it takes some skills that Dev people have and some skills the ops people have. By the way, side point, it really annoys me whenever I submit a DevOps session and people ask me to put it either in the dev or in the DBA tract. That really annoys me because that completely misses the entire point.
Anyway, so the first step is to think about systems thinking. The next step is to think about feedback loops, and here we start thinking about cycle time. I don’t know if you’ve heard the phrase cycle time before, but it’s one of the most important measurements of how well you’re doing with DevOps. How long does it take to go from an idea to dev code to production to feedback from production to inform your next idea? The quicker we can do that, the quicker we can refine our ideas, the quicker we can deliver those ideas and the quicker we can get really great products out to our users and the better chance we’ve got of beating our competitors. So we want to be amplifying those feedback loops. We want to be having it as quickly as possible, we want to be measuring what our cycle time is. If you don’t know what your cycle time is, think about what your cycle time is. Put targets around the business, say we want to get our cycle time down from six months to two weeks to a day to ten times a day, like they had a Flickr in 2009. So you want to be thinking about amplifying feedback loops and then the next step is this cultural thing that happens afterwards. Once we’ve got that quick feedback loop, which just changes the entire way that we think.
Everybody in the organization is now thinking about, right, I want to do this experiment and that experiment and well, I’ve got an idea, not just I’ve got an idea I think it’s great, I’ve got an idea, let me test it out, get some data on it, actually it turns out it’s rubbish, I’ll throw it away and come up with another idea, and that one’s a good one and I can prove it’s a good one because here’s the data, let’s do more of that please, okay? That’s kind of the journey that DevOps is going to take you on and the best organizations – I mean, if you look at any of the DevOps surveys that have been done recently, kind of the organizations that have gone down this path fantastically successful at building much higher quality software, releasing it much more frequently, getting their time to market is reduced significantly, the quality of their code has gone up, so it’s just a virtuous circle of benefits. If you go in the opposite direction, taking longer and longer between releases, your releases get more harder, your business gets much slower, you end up with these big legacy monolithic databases that we’ve all been there. They’re horrible to maintain, it’s almost impossible to get any updates out the door and at that point, you’re kind of stuck. You’ve reached what I call the technical debt singularity, where you are spending 100% of your hours fixing issues rather than actually building the next new thing, and it’s only a matter of time until somebody comes along and does whatever you do better than you.
So that’s why it’s really, really, really important to be adopting this stuff, because if you’re not, your competitors are. So it is really important for all of us because actually, it’s not so much about skilling up ourselves, it’s about making sure the companies that we work for remain successful and as long as the companies we work for remain successful, it means they’re probably able to pay us, and if we’ve contributed to that success, then we’re going to be rewarded. If we’ve not contributed to it, then we might not be rewarded in the same way. So that’s the three ways, so what I’m going to do now is I’m going to kind of mainly talk about this first part, systems syncing because I’ll be the first to admit that as an industry, there are very few of us that have got here as far as databases are concerned, where most of us are still struggling just to get our dev code into production.
So what I’ve done is I’ve probably just thrown you a road map of two or three years’ worth of investment and thinking about how to do this, to really make your organization a lot more agile in the future. What we need to do first is think about how do we go from dev code and get that into production, and this is why so many people are talking about automation and automated deployment at the moment because that can help a lot. So we’ve probably seen diagrams that look like this before, and typically we’ve seen them in the context of applications. I’ve got some .NET app, I can build it, I can compile the source code, I can run my unit tests on it, yay it works, and then I can deploy it to production, and that’s really easy for apps because you can delete the old one, throw it away and put a new one there instead. If I do that with the data – the database, well, I’ve just deleted my data. So doing it for databases is an awful lot harder. There are things that we need to think about. First of all, you can define the state that you want to get to, but you also need to define how you get there.
So you have this conflict between kind of the model that you want to end up with and the way that you want to get there. So you have these two kind of competing ideas about how you think about changes, which as these two get out of sync it’s just complicated, and I’m going to talk about that in quite a bit of detail. Actually, a small amount of detail because this is only a one hour session. We’ve also got to think about where the data is moving. There’s some sort of data in production and actually we need that in dev to do proper testing but how do we get production data to dev? Can you even do that? Because of compliance reasons, if you are going to do that then we need to think about the amount of data, where are we going to put all that data, we need to think about sensitive data, we need to mask that data, we need to think about compliance. So there are all sorts of complicated, thorny issues there and there are a lot of difficult problems and there are a bunch of people trying to solve those problems at the moment. I’m not saying that anybody has the best answer right now.
So databases are hard. You’ve got data that wants to move from production back to dev so you can do proper testing, but you’ve also get data that wants to move the other way. Your lookup tables, your reference tables, your list of country codes or product codes. Any table that you have that’s something type, contact type, address type, person type, any of those sorts of tables, you probably want to be keeping that in source code as well and deploying that from dev up to production. So you’ve got data moving in different directions, which is difficult and then we get onto all the thorny issues like replication or dependencies between databases and then it all starts to get really complicated.
So generally, databases are hard. Doesn’t mean we should give up though. It does possible mean we need to think about how we’re building our databases and also this is a key one, we need to think about building our databases for deployability and testability, okay? For too long, the main thing that we’ve all thought about with databases is security, scalability and performance. They’re like the main things that DBAs care about. I want to know that my stuff is backed up, I want to know that I can grow it and I want to know that it is going to run fast. But we also need to think about deployability and testability because if our stuff isn’t deployable because we’re using some features that don’t marry well with deployment frameworks and things, then yes, our feature gives us a great benefit, but actually the cost to the organization of reducing that cycle time is way more significant than a lot of people give it credit for.
Testability, how many people have written unit tests for their databases? How many people have written automated tests for their databases? In application code, people have worked out that you know what, if I’m going to release some code, the only real effective way to test whether that still works is to go through every single one of my use cases and work out whether they still work, okay? Now, we can either do that manually every single time I make a change, because how often do we make a change here and we change something – and something unexpected over there breaks. It happens a lot, right? So the only effective way to do that is to start doing some sort of automated level of testing.
Now, people will talk about testing – people will have lots of different opinions about the right way to do testing and the wrong way to do testing, and they all have valid opinions and there’s a debate, but I guarantee you one wrong way to do testing is not to do testing. That’s a very, very bad testing strategy. So we need to think about how we can automate some tests. There are unit test frameworks out there for SQL Server. People are used to using N unit and J unit and X unit in their .NET code, you wouldn’t hire a .NET developer who turned around to you and said, I don’t believe in writing tests, but somehow database developers get away with it and I don’t understand why. Even though there are unit test frameworks out there, T-SQL, TST, DB unit, there are all sorts of unit tests out there for SQL Server, just people don’t know what they are and people don’t use them. Some of them are open source, some people use something like T-SQL, they’ll say, well, that doesn’t solve my problem. It’s open source, go fix it, contribute, make it solve your problem. But the fact is, as an industry, we’ve not valued unit testing enough.
So databases are hard for a bunch of different reasons.
I’m going to tell a little bit of a story about Farm Credit Services of America, which is an organization that I visited a couple of years ago. You might recognize some of the faces on there. Some of them are quite vocal in the SQL community. When I visited Farm Credit Services of America, when I did this I was working for Redgate rather than DLM Consultants. They had a hundred people in their IT team, 14 different sub-teams all working on the same set of databases on various different projects, various different work flows of changes. Some of them were using source control, some of them weren’t, the ones that were using source control were using different types of version control system, TFS, GIT, version. The deployment process was each individual team would send John Morehouse, he’s the guy on the left, who was the production DBA – they would send him, “By the way, can you deploy this live? You’ve got two hours, here’s a week’s worth of work” and of course that didn’t go very well for John because it would just land on his desk and also, they were doing it in different ways. Some people would give an upgrade script, some people would give him a SSTD Dacpac, some people would just give him a link to the source code and there would be bits that are missing. Everybody did it in a different way, which made it very, very difficult for John to actually reliably put this deployment together, which as a result, deployments were slow and unreliable. Well, what we did when we went in is we drew this diagram on the board behind him.
Now, that looks a bit familiar, remember this diagram here? The logical consequence of thinking about these problems is to make sure all your development happens in a consistent fashion. Make sure that everybody is working in the same way so you can understand when I’m doing – when these changes are working through the system, everybody can understand, right, I understand what that code means because I understand your source control system or I understand the way that you write code. We have some sort of CI process.
Now, CI is a whole other big, broad category, but basically, it means testing your code to make sure it works and doing it every single time a change happens and reporting back to your about whether or not that code works or not. Once you’ve validated that the code works and you need some sort of automated release process to go out into the various different environments and initially yes, there will be due diligence checks in there, there will probably still be a DBA who stands in front of the production server with a bat and takes a look at the code and says is that a good idea, is that a bad idea, absolutely. We want DBAs to be involved in this. Just because we’re talking about automated deployment, it doesn’t mean we’re cutting you out the loop. We want you to be involved, we want you to be giving your knowledge to the process to make sure we don’t screw stuff up. We want you to be part of this process.
So this is kind of the process that we put together. The result was standardized process, they’re able to deliver much more frequently, the number of failed deployments went down, how much did the failed deployment cost you? I mean, it’s a fairly horrible day in the office when a deployment goes wrong and following night and the rest of the weekend before you eventually get to go home to your loved ones. The deployments were much easier to review because they’re all a standard format, they’ve got a standard kind of report. They actually implemented some sort of testing and as a result, that kind of became a blueprint for them and various other sister companies and the guy who’s smiling in the bottom left of this picture, he’s a guy called Bob Walker. He wrote a blog post all about the experience. If you would like to have a real world experience, tweet him, read his blog, codeapeture.io. Really great blog posts. He’s put so much content out there about how they manage to go through this process. So that’s broadly speaking what we did but I’ve not yet given you any technical details about how we did it and we’re not 40 minutes into the presentation, I’ve not even started that, so let’s try and address that. Basically, there’s more than one way to skin a cat.
A bit of context here, when I originally put this slide together, I used a picture of my own cat and my wife did not like that so she made me change it to a stock picture of a cat so this is not my own cat. The one that’s gone out in the Twitter feed is the original version of the slide, which does have my cat. She also made me correct the wording because there’s more than one way to do this. Broadly speaking, you’ve got this migrations and state way of doing things. Now, if we’re talking about .NET code, we don’t care about migrations, we don’t care about how the upgrade happens, you just delete the old thing and put the new thing there. It’s fine, it’s just code. Databases, we do need to worry about that because we need to worry about how we massage the data from the old schema into the new schema. If we’ve got for example, a column split, if I’m talking full name column and splitting it into first name and last name, kind of database administration 101, how do you make that change? What do you do about middle names? What do you do about prefixes? That’s not a trivial task and that’s not something that’s some sort of automated process can necessarily spot. It requires you to actually probably write your own upgrade script. So how the migrations approach to this works is basically each developer, or anybody making changes writes a migration script and another one and another one and another one and we run this automated deployment by running the script in sequence. Simple, straight forward, easy to understand. A lot of reliability in that, great.
The other way of working is the state way of working, and what we’re doing here is we’re finding the model that we want to get to and we’re going to let software work out the upgrade script. Now, while you’re probably going to start thinking, “Well, how is it going to deal with that problem split?” Well actually, yes that’s a problem but actually it makes for a much easier development experience. Now, I’ll explain that in a bit of detail. People have strong opinions, especially Hippos, famously strong advocates of database DevOps. People have really strong opinions about which is the right way to do this and which is the wrong way to do this, and anybody thinking about it in terms of right and wrong is stupid. But let me try and put in context some of the arguments. So somebody very smart said there’s nothing more reliable than keeping track of exactly the scripts you intend to run and running them without trying to compare state and guess. Absolutely, that was Paul Stovell who wrote the tool Octopus Deploy, which is a deployment tool for .NET. Absolutely fair, it’s on this documentation page for Octopus Deploy and he’s kind of – they’ve very clearly expressed why a lot of people favor the migrations way of working. A lot of the automation DevOps folk think about this is the best way to automate your deployments because it’s the most reliable thing. You’re just running the same scripts in the same order, if it worked in dev, if it worked on the production like staging environment, then there’s very little that can go wrong when I run that in production. Great.
You can also automate rollback scripts so if it goes up you don’t get quite what you want, you can come back. There are various different advantages to this approach. However, somebody else smart said that as soon as you have multiple changes on a single aspect of an object, ordering and the ability to detect which change needs to be made gets very complicated. Anybody who’s put together a deployment script knows that you need to do stuff in a particular order. Tables and views – you need to think about the dependencies between different objects. That can be quite a hard task and if you’ve got a hundred different developers all putting together upgrade scripts and they give them to you all at once, working out the order is difficult. Also, let’s think about programmable objects, view stored procedures. These things are just code. There’s no data there, so why do we need to think about that in terms of alter statements? If I’m going to put sequential update scripts every time somebody changes that stored procedure, it’s an alter procedure… okay?
Now, if three people edit the same stored procedure at once and I run all of those alter scripts, the last one wins and we’ve lost the stuff that went beforehand. That was Gert Drapers, by the way. He’s a Microsoft guy, he’s the guy that built Data Dude, which is what later became SSDT and visual studio database projects, which is a declarative way – a model based way of thinking about the problem. I went into a lot more detail about the migrations versus state conversation in a blog post a little while ago, it’s gone out on the Twitter feed. A few people felt the need to respond to it. As I said, it’s a hot topic but at the end of the day, you need to adopt one of these approaches, so there are various hybrid options out there as well, there are all sorts of tools on both sides and various different providers from Microsoft, open source communities, from third party tools vendors, that are going to be able to help you do this but you do need to think about them and you do need to pick the one that is better suited to you. In general, the conclusion that I’ve come to is migrations is really, really good if you’ve got to do a lot of table updates, you don’t have very many stored procedures and you’ve got a relatively small team, so you don’t have as many upgrade scripts to have to deal with the ordering. The declarative model driver approach works really, really well with large teams where you’ve got to handle the problems associated with large distributed development teams, lots of people making changes all the time and also dealing with stored procedures, the use of programmable objects because frankly, they should be treated like code, like a model driven approach, treat some right code, so pick the team that is more like you. If you’re interested in this debate, go and read the blog post.
So what I’m going to do now is I’m going to give you a bit of a demo using both a state based and a migrations based approach to how you can put all this stuff together, and I’m not – this isn’t a how to type of demo, it’s just to kind of put some context on the stuff I’ve been talking about. So far, this presentation has been an awful lot of slides and it’s been a very touchy feely presentation. I would quite like to actually jump into management studio and actually show you how this can work.
So what I’m going to do now – where’s my mouse? Jump into VMware. Right, so I’m in management studio here, I’ve got a bit of code. Very, very, very straightforward piece of code. I have a database, it has a table called kitten trainers and a view called kitten trainers near London, and one of my big clients has suggested it’s a really important feature relevant to I’m sure, hundreds of people, but definitely to me, I want to know whether the kitten trainers in London accept tiger cubs. So I want you to add a column to the kitten trainers table and I want you to also after the view, kitten trainers in London so I can grab that information out as well. I’ve tried to dumb down this demo to the very, very simplest database I can think of with a demo of a table with a view just to try and focus on the point of the demo. So I’ve got a development database for the awesome traders state database, I’ve also got an awesome traders migration database. Identical databases, I’m going to execute the same change against both databases. So I’m going to execute that against the state database, I’m also going to execute that against the migrations database.
Okay, so I’ve made my changes against the migrations and state database and now I’m going to try and put those in source control. So the source control is the definition of what this version of the database looks like. Now, I could use any tools and many tools are available. I have to pick some tools for this demo, that’s the way it is. I have the most experience with Redgate tools, you can do this with other tools if you want. I’m not necessarily promoting Redgate over anything else at this particular point in time because I have to have a much bigger conversation with you to work out which tool is going to be right for you. But I’m going to use the Redgate stuff because it was already on my machine. So Redgate SQL source control is showing me the difference between what was there before and what was there afterwards. If you note, you get the stiff paint here is telling you on the database we’ve got this create table and in source control we have this create table. I can actually go and look at where – this is in source control if I just open up my GIT repository here. You can see how the table is defined in source control. You can see it’s a create statement. That’s the entire point. It’s a create statement. I am defining what the table should look like, not how it should change.
So I’m going to commit that stuff into source control, demo, don’t type in demos, I’m going to commit that to source control, I’m also – under migrations database, I’m not going to use Redgate SQL source control because Redgate SQL source control is a kind of – it starts out as a model based approach there, kind of some hybrid features that allow you to do migrations later but I’m not going to worry about that right now. This is a separate tool, again also by Redgate because that’s just stuff that I personally like best. What we’re doing now is again we’re comparing the state of my development database to the state of my project that I can source control, and this time what I’ve come up with is an additional migration script. You can see on the top right hand corner here, we’re got all of these update scripts one by one that kind of apply my database updates in sequence and this time you can see I have not a create table, but I’ve got an alter table. I am adding the column and then I’m – got an alter statement on the view as well.
I can right click on that, I can commit that to source control right, some comment and I can pop that into – in this case GIT again, but you can use TFS or again, use whatever tool you like. It really doesn’t matter. As a result of that, now we come to the testing phase. Now, you can see here that actually, we’ve got various things happening here. This is a tool called Teamcity.
Again, you can use Jenkins, you can use the stuff that comes in with your studio, and what is happening here is an automated task has been kicked off. It spotted that the code is being committed into source control. It’s not going to take that code and try and build a real database from that code. It’s going to say, “Can I take your code and can I build a real database from a real Dev integration server on local DB or something? Can I build that database for real and does it work? Have you got the syntax right? Have you broken any dependencies?” Does the thing actually compile first task? Then if I’d had unit test or something I could be writing those unit tests and it’s going to give me a list of kind of this version failed, this version didn’t, this version passed, this version failed, and here’s everything that was wrong with it. I’ve also asked Teamcity to talk to my deployment tool.
This is a tool called Ocotpus Deploy, again, you can use the stuff that comes out of the box of visual studio if you like. Any moment you’re going to see the integration database here start deploying, you can see that awesome traders deployed a moment ago on January 20th, the migrations database last deployed on 19th, now it’s deploying on the 20th, that’s happening automatically. So what’s happening here is in – with the state based model, it’s running software in the background to compare the version in source control to the version on the target database and generate an upgrade script and run it, and the result is that the result of both of these is that the integration database, which I have an integration database here for the state demo, we’ve got the accept tiger cubs in there now, and in the migrations demo we’re going to have it there as well, okay?
Now, I forgot to show you the before, so I can’t prove to you that I didn’t cheat there but what I can do this time is I can show you the production version, so in this case we have kitten trainers, no accepts tiger cubs column, I will refresh that just to prove it, no accepts tiger cubs column on migrations and on the state table – let me refresh this, is that even being deployed? Yes it has. No accepts tiger cubs there, so now what I can do is I can go into Octopus Deploy, again remember what I said about that kind of sharing and visibility thing, everybody can log into this, everybody can see what’s deployed where at any point in time and they can click on any version and they can promote that to production.
You also get standardized reports on what’s actually changed, so if I was to look at this particular release, I can see my migrations I’ve got to change to this view, I’ve got to change to this table and there are various reports that are generates drill into more detail, and equally, if I was to deploy the state version to production, it’s going to do exactly the same thing. It’s going to take that packaged up version of code and it’s going to deploy it.
Now, in this case it doesn’t know what the differences are yet because it’s not seen what’s on production but in a minute it’s going to pause and it’s going to give me an opportunity to review and it’s going to kind of generate a nice like diff report and stuff, which is really nice and cool. So when it gets out I’ll be able to show you that.
Good, I’ve waffled for enough time so I can see the diff report now, I can take a look, what’s changed, well there’s one change, there’s another change, there’s a script that’s going to be executed, yes, I’m happy with that. As a DBA, I’m going to come in here, I’m going to say, “Okay to deploy and proceed” and right there in the log submitted by Redgate.
Have you ever had to ask who approved that deployment? Well, there it is, in the logs night and day. So using these sorts of tools – I don’t know about you but that’s an awful lot easier a deployment process than what I’ve seen in organizations before I’ve been in – after I’ve been in this rather. Of course, there are all sorts of problems, there are all sorts of things you’re going to learn along the way. There’s a saying that to make a mistake is human, to deploy that mistake to a thousand servers is DevOps, okay?
There are some nasty issues that you need to be aware of and there are some caveats and there are some gotchas and that’s why the DBAs need to be involved in this conversation. Like, if you’re afraid that people are going to set up a DevOps on your database and they’re going to break everything, be involved in the conversation, learn what they’re doing, make sure that they’re carrying out all the checks that you normally do manually, but make sure they’re carrying it out on an automated process so that you know that these mistakes aren’t going to happen. Be involved in the process.
Right, so I’ve got some final slides that I’m going to come back to now. That was my I’m doing a demo now slide. I’ve done that. The database administrator is dead. Probably should have given you a little bit of warning before I get into this slide. I don’t believe this, for the record. I’ve been saying throughout this presentation that I think that there’s a bit role for database administrators in DevOps, however, there’s certainly a group of people that are saying, “Right, I don’t need a relational database anymore because I can go no SQL and that will solve all my problems, right? I don’t need to manage my own premise servers anymore because I’ve got the Cloud and that will take all my problems away, right? I don’t need a DBA to deploy stuff anymore because I can automate it myself, right?”
So a lot of people are looking at these things and coming to the conclusion that they don’t need a DBA anymore.
Now, I think that they’re wrong, they’re misguided, they don’t understand actually the skills of the DBAs and actually all database professionals. Not just DBAs but database developers as well. Database folk understand the issues, they understand the problems that you’re going to run into and as the amount of data we need to look after grows, then we do need specialists in data. The reason why people come to this conclusion that they don’t need a DBA is because of the word no. It’s because they see DBA as the person who is blocking this process. They understand that we need to move faster, they understand that we need to be agile, they understand that we need to do DevOps, but there’s that DBA that just keeps getting in the way and he keeps telling me no and that’s really frustrating because I don’t really understand the reason why he says no, he’s not doing a very good job or she’s not doing a very good job of explaining it to me, they’re just a problem, we need to get rid of them so that we can move forward. That’s why people are coming to that viewpoint. I don’t believe they’re right, but the fact is there are a lot of people that are coming to that way of thinking and that’s not good for data folk.
One of the core principles of lean and as a result DevOps, is think about your bottleneck. There is no point investing anything in anything that isn’t your bottleneck, okay? If you’ve got a six lane highway and there’s roadworks over here that’s bringing it down to one lane, there is no point building a seventh lane over here to make it go faster because you need to think about the bottleneck. What is the thing that’s holding you up? And I’m sorry guys, for a lot of people it’s the database. How many deployments have been held up because their database change needs to be made or – that’s hard, okay? We are the bottleneck, okay? That means the most important thing for an awful lot of our organizations is that we can do DevOps on the database. That’s my case for it anyway, and I believe it’s true.
I don’t think I’m just saying that. I think that that’s genuinely true. It’s really important that we can apply DevOps to databases. This is a really good blog post by the way from Grant Richie, DevOps DBAs and the Word No where he basically just makes exactly the same case that I made, after looking at that DevOps reactions gif. That’s a depressing note to end on, so the note I’d like to end on is a friend of mine, called Sjors Takes. I can never pronounce his name, I’m sorry, Sjors, if you’re out there if I’ve pronounced it wrong, I’m terribly sorry but you do have a complicated Dutch name wouldn’t you. So he started a blog a while ago called DevOpsDBA.com. It’s a great blog, he posts an awful lot of stuff about how to do all of this stuff and he had a conversation with one of this colleagues about DevOps DBA and that conversation is very much like the conversation we’ve just had. His colleague told him there’s no such things as a DevOps DBA, those two things don’t go together. DevOps is one thing, DBA is a different thing, they don’t go together and Sjors makes a really good case about actually, there are a lot of really important fundamental technical problems associated with holding on to data and it’s a DBA that’s going to be good at solving those problems.
Now, for you to be that DevOps DBA, for you to remain relevant as we go into – down a DevOps road, you need to be thinking about how you can help automate these deployments. You need to be thinking about how you can be part of the solution rather than part of the problem, and there’s a whole bunch of great resources out there that can help you with that. So that’s my rallying call to everybody that’s watching. Be part of the DevOps revolution. Let’s be the people that are responsible for fixing that bottleneck. Let’s be the people that can take the release cycle for our database from six months down to two weeks. Let’s work out what those problems are, let’s work together to solve those problems because if we can do that, there’s a good chance our businesses are going to do an awful lot better and they’re going to be able to employ us for an awful lot longer. So in summary, do not be the bottleneck, be the fix.
Do not be the person that says no, be very careful with the word no. Sometimes people want to do stupid stuff, fine, say no. But try and say, “Right, what’s the problem you’re trying to solve? Let’s talk about that because the way you were going to go do it is a bad idea, but let’s have a conversation where you bring your experience, I bring my experience, we try and work together to solve that problem.” So embrace that cooperation. Apart from that embrace the other aspects of DevOps. Automation, database life cycle management, the tools that go along with that. Make sure – in this day and age, we should all know how to use GIT, we should all be learning PowerShell, we should all be trying to think about how we can test our – it’s crazy that it’s 2017 and I’m trying to suggest wouldn’t it be a good idea if we did some testing? We should be thinking about that, it should be crazy that that’s not something that we think is really important. We need to be careful about how we adopt it, we need to think carefully about the strategy that we’re going to use to set up this automation, we need to pick something that’s right for us.
If you ever listen to anybody that says, “This is the way you do it, that way’s bad, that way’s right”, ignore everything they say for the rest of the night and go and talk to somebody else who can give you genuine advice about which way is right for you and can have a conversation about your environment and give you some advice about that. Do design for deployability and testability. For too long we focused on security, we focused on scalability and performance but we also need to think about testability and deployability. When we’re thinking about how we’re going to design or architecture our databases, we have to consider those. I’m not saying they will always win, I’m not saying that you need to do that at the expense of one of the other three things that you already think about. Of course not, but it’s a balancing act. We need to consider them in all of our design and planning. We need to be thinking about that. We also need to be aware of the fact that Microsoft SQL server is not just Microsoft SQL server anymore. It is data platform.
There is the Cloud that we can play with, there are other forms of databases that may or may not be better solutions to the problems that we have. Now, I actually think SQL server is fantastic and it’s the best solution in an awful lot of cases but not 100% of cases and it will be naïve to pretend that it is and do be careful with the word no. I’m not saying don’t say no, I’m just saying be careful with it. Use it in moderation and try and use no but let’s talk about how we can achieve the same thing that you want to achieve. All of the references that I have tweeted, you can find them all if you go to dlmconsultants.com/devops-101. You’ll also see – once it’s uploaded, a video of this session there, all of my slides are there so everything that I’ve done in this session is available to you there along with my contact details. The bit about come and work with me, I work for DLM Consultants, we can help you with training and implementation services, we can come and help you understand how to do this.
If you want some help do give me a call. I’m going to be in the States in Q2, so I know there are a lot of you in the US and so if any of you are in the States and you would like my help then I’m going to be there in Q2 so let me know.
This is my final slide, all of my links are at dlmconsultants.com.devops-101. You can rate my session if you go to groupby.org, go session seven. Again, we’ve gone nine, eight, seven this morning so I’m not quite sure about that. That’s my face so please do rate me. I would ask, please do rate me honestly, but if you want to give me either a five or two or lower, I would appreciate it if you just send me a message to say why because I want to do more of the things that you give me fives for, and less of the things that you give me twos or lower for because I want to be continuously improving, I want to be gathering metrics from my users and I want to be able to continuously improve this as well. If you do want to get in touch with me, you’ve got my email address there as well. I’ve not been tracking questions because I thought that would be far too distracting while I’m trying to…
Brent Ozar: No, you’re good.
Alex Yates: Alright Brent, do we have any…
Brent Ozar: Yes I’ll start – we do, yes. Now, and before we do questions, I would actually put a plug in for Alex’s consulting as well because if you go down the route of trying to implement this stuff, any of the stuff involves changes to behavior, which lead – they have a long lead time and they’re super expensive for companies to go implement. Talk to people who’ve done it before and can introduce you to other clients, who can tell stories about what their successes or failures were looking like because Alex is just showing you tools fairly quickly but choices on tools are still – because it’s a burgeoning industry, it’s huge. You have so many decisions you have to make along the way and the decisions that you make and the behavioral changes that you put in early on have such a huge impact later as you’re going through the development life cycle. So let’s see here. Lee Markem says, “DevOps scares me because of columns like the DBA is dead. Let’s be honest, developers have been writing that for years and I always hear DBAs writing the same thing about developers.”
Wes says, “What a great session. Prior to my DBA role I was implementing an automated testing using Jenkins and a third party testing tool. I’m really happy to see inside and imagining the database using CI and other DevOps methodologies.” But the first question comes in from Lee who says he wants to be part of the solution but it’s a challenge for him because his developers won’t let him review the T-SQL code before it gets deployed. How do you start building that bridge and becoming a part of that team?
Alex Yates: Yes, that’s always the most difficult part. The most difficult part is always the people. Like, there are technical solutions to these things, we’re all engineers, we can read about them, we can implement them, we can test them, we can try them out. We’ll come up with something that’s half decent because that’s what we do. Where – I want to say this in the politest possible terms, where sometimes our skill-set is a bit lacking is getting on with people. Because actually, we’re not paid to get on with people, we’re paid to write great code. Well, actually we should be paid to get on with people. So when was the last time you had lunch with them? When was the last time you did anything social with them? When was the last time you went to the – I’m British so I’m going to show my British words here, but when was the last time you went to the pub with them after work? When was the last time you talked about their kids’ football tournament or whatever else it is?
These things start with the personal relationships. When did you last ask them what they were struggling with and offer to help them with something they’re struggling with? So there are all sorts of processes, there are all sorts of ways you can attack this but it has to start at a human level. Understanding them, understanding who they are, understanding what they care about, understanding what they don’t care about. Once you begin to build that personal relationship, it becomes an awful lot easier. Failing that, take out the manager for a steak dinner.
Bret Ozar: Yes. I like the carrot in the stick analogy like you always want to have something to bring to them. You want to make them happy for something but often when DBAs approach developers, they see it as here he comes to tell me no I can’t deploy, no I can’t go live, and you want to look at what can we do to make it look awesome, make your code look awesome and help you avoid possibly more problems but…
Alex Yates: Exactly, I mean, a lot of it comes down to that word no, that great piece that Grant wrote, that word no. The reason they’re cutting you out of the loop is because you’re using the word no too much, a lot of the time. If you use the word no too much you get seen as part of the problem and they don’t come to you to try and find the solution. So we have to think about how we can make ourselves approachable and appear as part of the solution as well. But yes, it’s never easy. It never is easy. There’s a really good model that I’ve used in some scenarios called the Adkar model. Awareness, desire, knowledge, ability and reinforcement. So if you think about things – just Google Adkar and there’s a whole bunch of advice on how you bring about human change as well and recognizing where people are on the Adkar model and changing your behavior accordingly, so that’s quite useful as well.
Brent Ozar: Arestice points out that instead of using no, you should try using nope or nuh-uh.
Alex Yates: Well, I’ve not tried that strategy before but maybe I will do.
Brent Ozar: Never, over my dead body. JD says, “The problem is when they don’t want the DBA as part of the team, they want to impose the solution.” You know what I would do too, is go on BrentOzar.com, search for how to become a senior DBA and I break out what the different roles are. You might be a production DBA. You might be a DBA who’s responsible for keeping the server up and they may have a different role in their own team who’s responsible more for change deployment because not every DBA has that responsibility. It may not be your job. Slavos says, “It would be interesting to hear Alex’s favorites for unit testing and auto testing packages for SQL server.”
Alex Yates: I tend to like T-SQL T, it’s open source, it’s easy, there are some gnarly issues with it but you know what, there are with almost all the unit testing frameworks. I like using a unit testing framework that is written in the same language that you’re testing. Like there are a lot of unit testing frameworks that just kind of – you test from the application code or you test – you write a test in c# to test your SQL. I don’t like those approaches because – and who are you writing this unit test for? You’re writing it for yourself and you’re writing it for whoever’s going to come after you and whoever’s going to come after you, you don’t know what their skill-set is going be, other than the fact that they can probably write SQL. You don’t know whether they’re going to like writing c# or not. Not all of database folk do, so try and write your unit test in the same language as the language you’re testing, that’s one piece of advice I’d give you. I do like T-SQL T, I think in my experience it’s one of the most widely used and that’s a good thing because it means there’s a lot of support out there. It’s also open source so if you have any problems with it, well, contribute, so that’s my gut but as I say, I’m just one person and there are lots of unit testing tools and by far the most important thing is that you’re just doing something.
I mean, even if you’re not doing it in the way I approve of, at least if you’re doing it, that’s a good thing. I’ve got one thing that I’m going to say. Another quick plug. I’m just tweeting now a question. I would like to come back again next time and I’ve got a few other sessions that I deliver. One is going further and deeper into that model versus migration debate. I’ve got a much more – a much bigger example, a much clearer example where I actually go in to the problems that you’re going to have one way or the other, so that’s quite an interesting session. I can also draw more into the CI bit, kind of how do the build work, how do we do that, and I’ve got a nice little anti-patterns rant about all the things that I’ve seen gone wrong and that’s another piece of fun. So I’m going to tweet that now and I would appreciate it if you would vote on that and then I will make my submission for next Groupby and if you’ve enjoyed this session, I would really like to come back. If you haven’t then don’t vote for me.
Brent Ozar: Well thanks so much for volunteering your time today. We really appreciate it. It was absolutely wonderful having you here, I know there have been a lot of good comments about – already in the Slack room, Rushton says, “I would like all three topics please.” Well you know, you can always do all three and see which one people vote for.
Alex Yates: Well, Groupby is going to be a regular thing, right? I’m not going to be able to do three in one Groupby but I can do them one at a time.
Brent Ozar: Well, and you could submit all three and see which one gets the highest number for votes.
Alex Yates: Well, that’s the thing I don’t want to do because then first of all I’m competing with all – but also I read a blog post recently from Chris O’Dell which is how to get selected for NDC. It’s a really cool blog post. NDC London is on at the moment, that’s the context of this decision, and she said have some respect for the review panel, okay? If you got a hundred different people submitting sessions and each one of them has submitted five sessions, then that’s just mean to the submission panel. So I know I’ve been guilty of that in the past submitting like all of my abstracts to all of my events because I don’t know which one they’re going to like. I just want them to pick the one they like the most but I think I’m going to start trying to be a bit more selective and thinking more carefully about kind of which of my sessions is right for which event. So that’s why I thought I would just submit the one for the next Groupby, but I need to know which one people are most interested in, so that’s the reason for the tweet.
Brent Ozar: I’m going to say something really weird too. So you’re kind of at this point the DLM guy for the SQL server community. Normally, for conference organizers you want to be able to say I want to put together an agenda with what people want to talk about. With you being the DLM guy, it’s easier to let you talk about your favorite thing first and let other people fill in the gaps but you should kind of have first pick there on whatever the topic is. Alright, well thanks everybody for hanging out with us.
Alex Yates: All the best guys, thanks for having me.
Brent Ozar: Thanks for coming. Thanks Alex.
Latest posts by Alex Yates (see all)
- Getting CI right for SQL Server - March 21, 2017
- DevOps 101 for data professionals – how your jobs will change - November 25, 2016