Kate Duggan

13 April 2017

How Redgate helped define a truly automated database deployment process

At the end of 2016, we interviewed Bob Walker, a Lead Application Developer, to find out how he went about setting up automated database deployments using Redgate tools and what lessons he’d learned. The highlights make interesting reading.

You describe the time before Redgate database automation as ‘truly the Wild West of database development’. Tell us more about the way you were working before you started using the Redgate toolchain.

I’ll start by explaining a little bit about our environment. We have four database environments: development, test, pre-production, and production, and every developer is a SysAdmin on the dev and test environments. But almost all the data that we have for testing is in the test environment, so developers were making changes in the test environment that they actually wanted to make in development.

We had no idea what was going on and we didn’t have any change history, so we didn’t know who made a table change, a stored procedure change or anything like that. When promoting to the upper environments, like pre-production or production, we were guessing a lot of the time and trying to keep track of all the files or stored procedures that had changed.

You also talked about the process as being full of different points of failure. Could you give some examples?

Our projects ranged anywhere from a couple weeks to a couple months and we’d have to make our Database Developers remember they were going to have to change the following ten tables and the following ten procedures.

As we went up to, say, our pre-production environment, it would most often fail because, inevitably, we would forget a table or stored procedure or view or something. And then we would deploy out, and hope it worked. The same if we did any data scripts or anything like that – you couldn’t really test those consistently through the environments. So it was ‘Man, I hope this works’ the first time we ran every script.

There will be others out there in the same position you were in. What prompted you to reach out to Redgate and get some help to look at this differently?

We’d been using the SQL Compare and SQL Data Compare tools that Redgate provides for quite a while and we always thought that Redgate makes the best in breed tools. We thought they would probably have a solution to do more automation, so let’s see what we can do to make this more automated. Our biggest goal was that we wanted to be able to deploy multiple times a day. That’s the true end-goal, but we figured why not ask the people who make the tools since they probably know how it works?

What led you to make that initial determination – was there pressure from the business to keep going faster? What actually got you to pick up the phone and call Redgate?

You actually hit the nail on the head. It was mostly pressure from the business to keep going faster. Any time you have work that’s pending, its unrealized money that you’ve spent where you’re not giving any value to the user. It was really tough to tell users, ‘We have that bug fix ready to go, but it won’t go out until next month or the month after that, so you’re just going to have to live with it’.

How was the process of getting buy-in from the rest of the company to move to a more automated process?

A lot of times the desire is there to be able to do automation. Everyone could see we were delivering a substandard product at that point and we wanted to make sure we could deliver a great product. That’s actually our application development team’s motto: ‘Building stuff that rocks together’.

So the buy-in for a lot of the stuff was there, but there was still some pushback. We had to make sure we could justify the cost because you’re looking at not just tooling but people too. We also had to make sure we were dealing with any concerns like ‘Will you really need me here any more because I’m the one who generates the scripts, or the one who handles this work’. So mostly dealing with that fear of now we’re automating, we didn’t need people any more.

We hear that from various different organizations we work with, and it’s a culture change. From people we talk with, it’s not so much their job becoming obsolete, it’s more about what they can do now with their expertise that’s more value-add to the business as opposed to taking care of a crummy process. Would you say that’s accurate for you too?

Yes, without question. It really helped that we piloted this process on the team that I’m the lead for and we have a Database Developer on my team. Instead of focusing on that crummy process, as you put it, they could actually work on more important things like performance tuning, the database structure, and checking the system was working how it should. They weren’t spending the entire day writing scripts or making sure everything was up to date.

How did you justify to the business that it was worth it? How did you quantify what the benefits were going to be down the road?

It really helped that our team was the pilot team that drove this forward, so we could show after we did a deployment that we no longer had to do an emergency deployment or fix. We didn’t have to do any more urgent fixes because we forgot some database script or let something slip by.

Every time we had one of those in the past, we had to bring down the system and that cost money, or users were affected. One time we forgot a very important view and none of the users could work for a half hour or 45 minutes, and that involved about 800 to 1,000 people.

How did you set up a proof of concept? Give me a little bit of background to what it looked like.

On the project I was working on at the time, we were using TeamCity as our build and deployment tool, and Redgate makes a great plugin that helped us out. Like I said, we were SysAdmins on the lower environments so to prove it out, the first thing we did was to put our database into source control. That was obviously the first step in the process.

From there, we tested it and made sure that we could get to dev first, that we weren’t going to break anything in dev or in system tests. We tried to get it all the way through to our testing environment to see what would happen. When we first started doing that, we just had it generating the scripts to make sure we’d make it. I think the first time I tried to run it, I dropped all the tables so I think that was missing a permission or two! We proved it out, making sure everything was working correctly and then, once it was, gave it to the rest of the team and let them play with it before rolling it out to the entire floor.

If you had to compare your world now to what it was before getting this in place, what are some of the key differences that you see?

One of the biggest differences we made was to move to local database development or a dedicated database model so every developer, be it an Application Developer or a Database Developer, has their own instance of SQL Server. That really helped with our branching policy. Before, we had one development database so if you made a breaking change you’d mess everyone up until a code fix went out to handle it. That was a big change, so we can now get a little more creative with some of our changes.

What’s the culture shift been – do you see any marked changes?

The biggest one was getting everyone working in a consistent way, because each team was doing database deployments very differently. Some would generate multiple scripts that they would give to the Database Architects to run in production, some would do one massive script, and others would give the scripts on the fly to the Database Architect. So getting everyone working consistently was a huge win. That’s probably the biggest change because now, when we have our Lead Developer meetings, everyone’s on the same page.

There will be people out there with a similar mindset to the one you had a couple of years ago, which is, I think we need to change but I have no idea how to begin that process because it’s going to involve tons of people. You guys found success with this in a large organization, so what would be the best advice you could give?

Think about your end state, where you want to really get to, and then start thinking about the steps to get there. You’re not going to start out of the gate automatically pushing to production – there’s a series of steps to reach that end-goal.

Another big piece of advice is to set up a pilot project with a pilot team. And read: there’s a lot of good material out there and Redgate has some great documentation. You can try out a lot of the tooling that’s out there too.

And just get started on something, because that’s when you’ll find out where you need to make some changes. Look at your own process but also consider where you need to make some changes around some of your architecture. One of the things I’m so surprised about is that, even after a couple years of doing this, the process is constantly evolving.

To find out more about getting a pilot up and running in your own team contact us.