Many years ago, I was working with a great DBA. Seriously, a very smart and capable guy. He told me, “We need to put the database into source control, just like app code.” And I just laughed. Not because I disagreed with him. I knew he was right, but I had tried, several times, to do just that. See, I’m not really a DBA. I’m a developer. I knew that code (and all the T-SQL that describes databases is code) needed to be versioned, sourced, tracked and audited. But great googly moogly, it was not an easy thing to do.
I first tried just exporting the entire database into a script and then occasionally checking that script into source control. Yay! Mission Accomplished… Well, I had a database in source control, yes, but I didn’t have any of the great stuff that went with it, most of all, a way to deploy from source control.
Next, I tried just storing the stuff that changed most, procedures. But, I had to store everything as an ALTER, or, I had to store it all as a DROP/CREATE and store the security settings and extended properties. I tried both. Neither satisfied and it was WAY too easy for someone else to modify a script the wrong way and bring the entire thing crashing down. And, not to mention the fact that any and all structural changes outside of stored procedures had to be built manually, or using a compare tool to generate them (but not the procs, cause we have those in source control, remember) by comparing prod & dev or qa & dev or something & something… Oh yeah, that was fun.
Man, it was painful back then. But now, there are several ways you can do this using Microsoft and/or 3rd party tools.
Why aren’t you?
Seriously, most of you aren’t. I’ve been going all over the country teaching a class on database deployments (next one is in Cleveland if you’re interested) and I know most people don’t put their databases into source control. Of course, I’m pretty sure most people don’t talk to their Dev teams if they can help it, and it does seem like most Dev teams seem to be on less than a perfectly chatty basis with their DBAs. is that the cause? The thing is, you are experiencing pain in terms of time spent, mistakes made, slower deployments, less frequent deployments, possibly even down time, all because you don’t do your deployments right. And deployments start from source control.
Developers have spent the last 30 years or so figuring out better and better ways to arrive at functional code in the hands of their customers (internal or external) as fast as possible, as accurately as possible. Over the same 30 years, DBAs have been figuring out how to better and better protect the information under our charge ensuring that it’s backed up, available, performing well, and always on (to use a phrase). My suggestion to you, data pro, talk to your developers. Figure out what they do and how they do it. Take advantage of their years and years of process improvement and apply what they’ve learned to your database development and deployment.
There’s a new concept growing out there. It’s fairly well established within the *nix communities, DevOps. It’s the realization that the world doesn’t begin and end with your database/server/application. Instead, your database/server/application is completely dependent on the database/server/application that it needs to run. Notice, no one is more important here. We’re talking about creating mechanisms for teams to deliver functionality to their clients (internal or external). And it all starts with the realization that there are parts of the process that some of us are better at than others. Developers know how to develop and deploy within teams. Let’s learn from them. And the start, well, that’s source control.
So, is your database under source control… for real. If not, get it there. The excuses I used to have are gone. That means the excuses you have now are probably gone too.
Fair warning, I may use the term DevOps more in the future.
I’ve seen a lot of segregation between infrastructure and development at various companies and it seems like most of the time the DBA is on the infrastructure side. I think your point of communication between the DBAs and Devs is right on (especially when quite a few DBAs end up on the infrastructure side of IT).
Me too. Heck, I’ve probably caused the problem at points in the past (I can be a real stinker when I’ve a mind to). But, the concept of DevOps is to recognize that, in fact, there really is not distinction. It’s a single team. Yes, different areas of responsibility and different skill sets and different priorities, but still, one team.
Oh, you guys are going to get sick of hearing from me about the DevOps concept (until you adopt it of course).
Totally agree on both getting DBs into source control and getting a real DevOps team going. We started using VS 2008’s DB Projects a while back and it definitely had a steep learning curve. We also had one fun incident where two of us started from scratch with DB Projects, resulting in the worst merge ever. (Start with one base set of projects – branch from there.) We’re now on SSDT because the price is right (free) and it works well. There’s still a bit of a learning curve, but we can live with that.
We started a DevOps concept a while ago and tackled the most painful parts of our system. The Ops guys pointed out areas that made our servers cry. The Dev guys either pointed out a real need for different hardware or addressed the issues in code as they could. Some legacy code had way too much overhead to tackle in a timely manner, but we addressed what we could.
The best thing that came out of it was automated releases, allowing continuous delivery. Before that time we had a dev build manager who built all of the code for a release and handed it off to the Ops team to release. DB code was off to the side somewhere. Now, we have a single main branch from which we release and can build/release automatically as things are ready. The Ops guys worked together with the Dev team to tackle all of those pieces.
Another major win was automatic environment setups. We used “Chef” to build and configure our Windows servers and services. A single script can provision the server, install the correct OS, SP, Services, configure them, add security, and be completely ready to use for code releases. That made our recent data center move much easier because we could almost push a button to set up our new environment and have it ready to run and test.
The only point some people miss is that you can’t (shouldn’t) bring in outside developers for your DevOps team. You should use your developers who know your system along with your Ops guys who know your system. Trying to bring in consultants or people who don’t know the system will result in much frustration.
Hey Peter,
Sounds really good.
You mean “never” bring in outside developers or not to use them to “build” the process/systems? Because I pretty much guarantee, most organizations past a certain size are bringing in consultants at some point. Just curious.
How about “include your own developers; don’t shut them out of the process”? I’ve seen some managers suggest that their in-house people are too busy building apps, so they should just bring in outside people to do all of the work and never talk to the in-house devs. (Seriously – defeats the purpose of DevOps if you don’t include the people who know your systems at all.)
Oh good gosh, yes. I agree with that 100%. You’re right. Totally defeats the entire purpose of going in that direction.
I’ve advocated this for as long as i can remember. As DBA, I’ve followed up on catastrophe after catastrophe resulting from deploys conducted by one dev group without talking to another dev group.
I placed a standing weekly meeting on the calendar for all dev groups and the data team to talk about what is coming or what questions or concerns they may have. More than once, a simple question placed before us made things much easier and saved me the grief of fixing a bad process or a bad deploy…
Awesome! Well done. And what about source control?
That looks like a fairly intense 3 sessions. Do you think you’ll be recording it? I wonder how it stacks up to the other redgate 2-part session that was recorded.
The two-parter is a mix of some of this material and more drill down on Deployment Manager. This includes a lot more time on source control and continuous integration.
What I’ve been doing is almost the reverse of this. I’m using Event Notifications to capture the changes that are actually made to the databases, then using some custom powershell (modified from Phil Factor and others) to script out the new version and check it in to a separate branch of TFS (along with the person making the change). That way you don’t just have the Dev’s view in Source Control, you have what what’s actually in prod. It also helps out the devs because they can now see what production looks like – I know I’ve had to tune things in the past, and notifying the dev wasn’t high on the list at that point.
Gratutous self-aggrandizement – I’m presenting on this next weekend at SQLSat271 in Albuquerque. But what got me to this point was basically the same frustrations.
Sounds great. Record the session.
[…] Database in Source Control – Flying the DevOps flag high, it’s Grant Fritchey (Blog|Twitter). Make sure you read the comments. […]
Grant, I managed to record it at SqlSat271. I’ve submitted to SqlSat300, but feedback has convinced me I need a different title. Now I just need to come up with one. Suggestions definitely welcome, http://www.youtube.com/watch?v=fuEGgdQRbIY
[…] is much more approachable than his nicknames and this blog post. Just look at his posts directly before this post where he’s acknowledges the issue saying “I’m pretty sure most people don’t talk to […]
[…] is much more approachable than his nicknames and this blog post. Just look at his posts directly before this post where he’s acknowledges the issue saying “I’m pretty sure most people don’t talk to […]
[…] https://www.scarydba.com/2014/01/15/database-in-source-control/ […]