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.

As Agile goes into a full court press, many seem to downplay the importance of environmental management, which is also sometimes known as configuration management. This is usually to the detriment of all concerned.

The focus of Agile is generally on the various aspects of development: Defining what to develop, how to develop, when to develop. But ask yourself this: How many times has anyone talked about environmental management? Automated testing? Production support? I heard nothing about these until my co-worker Keron and I started asking very sharp questions about migration times, data refreshes, and build verifications. Even the instructors for the SCRUM class we attended were befuddled by our questions. It was like it never occurred to anyone that configuration management was important to a successful Agile implementation.

So why is it important? Any derivative of an Agile methodology must include a framework of continuous integration into target environments. This puts the product (process, code, intellectual property, etc.) into a place where you get the most benefit from it in the least amount of time. From a graphical point of view, our ideal SCRUM process flow looks something like this….

Bob Johnson called this diagram the Left Brain/Right Brain Scrum explanation. The actual start and end points are not important; what is important is the understanding of the two very different functions that promote fast turn-around times for any development process. Also, that the ‘team’ goes beyond those in the SCRUM.

The Right Brain is responsible for creativity and pattern recognition: This is the domain of the Development Team. They work the backlog at the discretion of the Product Owner. They get feedback from the Production Support team and make changes as assigned.

The Left Brain is responsible for process and sequencing: This is the domain of the Environment Team. They manage the output from the SCRUM team(s) in a proven, repeatable manner that is easily automated. They gather data about the process to:

• Implement continuous process improvement.
• Provide feedback to the Production Owner and SCRUM team(s).

Case in point, one of the repeating tasks that occurred in the environment was a data refresh. The refresh was problematic on several fronts:

1. Data had to drop down from the PROD environment to the lower environment.
2. Code had to be promoted up an environment.
3. The environment specific metadata had to stay in the same environment.

Sounds easy enough, but when you’re talking about 5 databases, >1000 tables, and a ~1.5TB footprint nothing is easy. And, ohh, by the way…you have to do it between Sprints and not slow down development. Without getting in the weeds, we set out a plan (which I’ll write about later in detail) that allowed continuous process improvement to evolve the process naturally and incrementally. The outcome?

A little planning, a little automation and, BAM!, a process that takes a couple of hours. Don’t know about you, but could you imagine the savings of time and resources if you did this with just 10% of your company’s IT processes? (Sniff, sniff) I smell shareholder value!

When I start the process of understanding a system I’m about to automate, I do three things:

1. I write documentation.
2. I draw diagrams.
3. I get other’s feedback.

Not to make this overly simplistic, but that is all that is required to get a detailed understanding of any system you need to automate. The written artifacts become the basis for system understanding. The feedback provides clarity and insight of system idiosyncrasies. Through discussions with team members, owners, and users, I will revise these artifacts many times until I have documents that allow me to fully understand and automate the process.

In a phrase, you have to practice chalk talk. Just as a coach will draw the tactics of a play on a chalkboard, you and your team must sit around the whiteboard and postulate what is possible, what is probable, and what the final product will look like. This usually is not done in a single session, but is a continuous process of research, discovery, and experimentation (even if it is just thought experimentation).

For example, when I took over as the Application DBA for the unit it was apparent that I had no idea what my predecessors did and how they controlled the chaos. We had five environments, not all of which were consistent and may or may not have the latest code base. Each code fragment (SQL object) was migrated separately; it wasn’t always put on the official build list (didn’t make sense to me at all). Each change required another script which in turn caused the same object to be modified in the same build, usually to detrimental effect. I could go on (and on…and on ….and on….).

Our goal was to proceduralize the entire database environment management process, automating as much as we could, and initiate process improvement. When I started initially documenting the loose process they used, I started to formulate the below conceptual automate environments process.

It took multiple iterations and many discussions with my team mates in the Environment Team to reach this point, but it provided a solid picture from which we could plan the automated build framework. We also had detailed discussion with the development groups and management. From there we:

• Investigated corporate and department policy and procedural issues.
• Talked to other groups to see how they manage database environments.
• Researched available tools and started procurement processes when necessary.
• Initiated experiments to see what worked and what didn’t.

After a period of several weeks, a picture emerged that looks like this…

From this picture we targeted three main items that, when done, would significantly reduce the time/resources necessary and lower the incidence of defects.

1. Automate SQL Builds
2. Automate TCW Script Preparation
3. Automate Data Refreshes

Next, we’ll detail each one of these items and discuss how they were automated and review actual results.