Automatic DBA

Once I took over from the previous App DBA, it became apparent rather rapidly that builds were an exercise in patience…and they took a lot of time. Developers requested scripts be run in multiple environments in what seemed to at random intervals. The master turn list was informal and updated manually. Some of the scripts ran out of order, sometimes with devastating effect. Some of the scripts were environment specific without a way to manage their operation. In a word…CHAOS!

So I told my boss that I was going to automate the SQL builds…”You can do that?!??” My response: “With the correct tools and a documented process you can automate anything.”

Based on what we discovered doing the Process Inventory we know that we can get a big payoff from automating the SQL Build. And from what we covered in my series on documentation, you can’t automate what you don’t understand. So, it looks like we need to breakdown and document a process that will do the following:

• Keep track of changes to the SQL source code.
• Ensure that a script is written so that it can run in every environment.
• Ensure that a scripts runs is every environment.
• Verify that the scripts after they were run.

So with that in mind, he’s the starting scenario: Two development teams in an Agile framework using Microsoft TFS. After several meetings and some chalk talk with the key developers and managers, we came up with the following initial outline.

So this procedure was documented and put into place (with the exception of the FinalBuilder automation). One of the first things we noticed was that we could actually plan a build without worrying about losing some dependent SQL code. The second thing we noticed was that the process was cumbersome. So we got rid of some of the quality gates (How many gates do you really need?) and started coding some customized tools. In this case FinalBuilder.

Seque: If you are doing builds of any kind, YOU NEED FINALBUILDER! Why?

• It’s quick to setup and use.
• It integrates with most open-source platforms and source-code control systems.
• It’s completely customizable, including the ability to use in-house DLL’s. (I’ve done this and it works great!)
• A license is cheap.

OK….So I get back together with the development teams and we revise the process, which turns into this:

Based on this I write the process documentation, paying particular attention to the detail needed to providing the necessary information for:

• Code Promotion Requests
• Production DBA Support
• Sprint Team Feedback
• Build List Verification

Once we the team felt comfortable with what I had, we started the automation process in earnest. Until the automation process was completed, the existing process would stay in place. Once completed with testing the FinalBuilder SQL Build, we cut over and never looked back.

There is quite a bit of the unsaid in this article: I apologize for that. I, unfortunately, have limited time that I can dedicate to writing and this subject is quite detailed and extensive. If you would like to have a fuller explanation of any items, please contact me.