Automatic DBA

OK, I have a serious pet peeve: Views calling views. Who the heck started that? Why would we bury simple code so deep that you can’t immediately see an issue? How can you properly optimize the thing?!???

I’ve had developers give me quite a few reasons why they stack views (views calling views). The reasons generally fall into these categories:

• It reduces the code we have to write.
• Easier to maintain.
• Provides a layer of abstraction.

Views are not an arbitrary feature that databases have; they serve a purpose. Views exist to:

• Simplify security.
• Simplify coding.
• Simplify presentation.

The key word here is SIMPLIFY….

sim·pli·fy (sîm′plê-fì) tr.v. sim·pli·fied, sim·pli·fy·ing, sim·pli·fies

To make simple or simpler, as:

a. To reduce in complexity or extent.

b. To reduce to fundamental parts.

c. To make easier to understand.

So, in what way is an architecture where views are calling views simplifying?

Reasons why stacking views are misguided:

SQL is not OO. Having stacks of views does not make the code more efficient or faster. Any measurement thus would be coincidental.

• Views are compiled base on position. Changing a referenced view could possibly affect referencing views. To prevent this you have to refresh the views in the order of referencing.
• Dependency verification is not straightforward. You might not see all the dependencies using the standard system views that detail dependencies.
• Code obfuscation: Views abstract complexity (i.e., simplification), so when you use an abstraction to create another abstraction you start to lose sight of what is really going on (i.e., add complexity).
• Duplicate references: Once the complexity is hidden in the code, references to the same table or column are used multiple times without it being readily acknowledgeable.
• It is difficult to properly optimize a view embedded in another view; ultimately you end up trying to optimize multiple views w/o understanding the effect on other referencing objects.

This is by no means an exhaustive list and each situation is a little different, but in general this covers most of the issues. Views, as with any feature, should not be abused. Just because something is possible doesn’t make it a good idea.

We’ve all been there: A project that has gone south and needs to be put back on track quickly. Resources get thrown into the breach to bolster customer and management confidence. When this situation occurs the manager dealing with the issue can get myopic pretty quickly. How can he do anything but focus like a laser on the one thing that everyone is looking at?

But focus comes at a cost. Other issues can quickly rise out of the turmoil and out flank a manager’s ability to effectively see and deal with it. Some of the available resources do not get fully utilized. There is dead space in the feedback loop. This is where Battlespace Management comes into play:

Battlespace Management (BM) – The adaptive means and measures that enable the dynamic synchronization of activities and resources to provide the desired outcome. BM has always been important but the nature of modern operations requires ever lower levels of command to plan and execute increasingly complex BM.

Yes, it is a military definition but can easily translate to business. In fact many consulting firms use military comparisons regularly. Whole books are written on the subject.

Battlespace Management has four main principles:

• Universal Application – BM is applied at all levels, the only difference being the means and measures. All applied activities require coordination, synchronization and prioritization.
  • Strategic Management – Focuses on resources acquisition and management.
  • Tactical Management – Focuses on resource assignment and task measurement.
• Interaction – Resources interact; therefore activities will (eventually) impact each other.
• Coordination and Control – As resources interact, control must be asserted to maximize benefit and minimize interruptions.
• Collaboration – Seldom can activities remain compartmentalized. Collaboration occurs both up and down the command chain, as well as across environmental boundaries.

So how could this understanding benefit an IT unit?

• Standard Operating Procedures: Clear cut procedures that exist in a process improvement environment that allows any member of a team to easily initiate required work.
• Task Integration vs. Responsibility Silos: Each member of a team should be familiar (but maybe not an expert) with all aspects of a unit’s area or responsibility.
• Liaisons: Have members of the team assigned to interact with support units. These liaisons are used to reduce the ‘friction’ that can naturally occur when coordinating tasks and resources w/o the need for supervisorial involvement.
• Training and Preparation: Each member should be competent in all platforms the unit operates in and a training regime should exist to support this.

If a unit followed these ideas it would be a lot harder to be surprised (flanked) by new developments within their area of responsibility. Has anybody seen a related case-study in one of their MBA classes?

In the past I’ve been called technical…Usually in the phrase, “Look, I don’t want you to get to technical in this meeting.” Heck, if I didn’t know any better I would think that was an insult.

But I don’t think I’m that technical. There are many more people a lot more technical than I. I think of myself as procedural….as in, “A standard procedure could remove the complexity of what we are trying to do and make it simpler…and more efficient!”

To wit the situation I’ve seen with turns in the Enterprise Data Warehouse: They are complex…very complex with many rules and exceptions. At least that is how it appears. And, in general, appearance (perception) is reality. This is further complicated by having each developer do their own turn requests, which in turn is complicated by the turnover, especially with the contracted/offshored resources.

So what is the cost of all this complexity?

• Miscommunications
• Delays
• Defects
• Rework/Resubmissions

It’s like throwing sand in a transfer case! So what is the answer? My proposed solution would be twofold:

1. Create a living document that consolidates all the turn request processes/knowledge/lessons learned into a single, cohesive standard operating procedure.
   1. Create a logic-branched procedure so that someone could literally ‘walk through’ the process…step by step.
   2. Cover all aspects of a turn: Sign-offs, security requests, document transfers, code file staging, job templates, etc.
   3. Create examples for the most common cases.
2. Have a turn Subject Matter Expert (and a backup) who is responsible for all turns within a unit.
   1. They would serve as the liaison between the DBA group and the unit doing the work.
   2. They would advise/facilitate all turns.
   3. They would provide quality control and feedback for the procedure outlined above.
   4. They would automate what pieces of the process that can be automated.

And the best part? This is not theory. This is how my old unit operates day in and day out; regular turn or emergency turn. It works, it works well, and has allowed the unit to split out some of their resources to work on other projects (like Mobile Computing!). Gotta love simplicity!

Data refreshes are one of the hardest things to accomplish in an Agile environment: The downtime between sprints is miniscule and most modern systems have quite a bit of metadata that must be contained within specific environment such that ‘unwanted’ interactions do not occur. “Like what?” I can here you say. How about sending account cancellation to active members during a test. Yeah…I can hear the gasps of horror from here.

This is the Redgate Tools Team, a small software company out of Cambridge, England. How small? Here’s the entire group…..

Ahh, the English, their Easter Egg and their dry sense of humor. Anyway, Redgate builds the best SQL management tools I’ve ever seen: Fast, reliable, and easy to use. They have quite a tool selection, but my favorites are SQLCompare and SQLDataCompare.

SQLCompare is a schema level comparison tool that can either report on the differences between two databases or it can alter the target database to match the sourcedatabase…VERY POWERFUL. There are a few gotcha’s here and there (like circular relationships…has a hard time with those), but when you are in a hurry it’s a great time saver.

While SQLCompare is centered on structure, SQLDataCompare is all about what is in those structures, namely data. Given two databases, it can compare every row on every table with a corresponding row on a corresponding table. Quick…real quick. The configuration can be intimidating, but once you have a handle on it you can get a lot of work done quickly and reliably.

And the best part of Redgate products are that once a project is created and configured it can be run from the command line, which in turn is easily integrated into a schedule, SSIS, or FinalBuilder automation.

So here’s the scenario:

• Four environments must have refreshed data from PROD: DEV, TEST, QA, and PROOF.
• Each environment has 5 major databases with a footprint of about 500GB.
• > 100 tables hold environmentally sensitive data spread out over 5 databases.
• There can be NO ENVIRONMENTALLY SENSITIVE DATA LEAKAGE! (e.g., Data from PROD can’t exist in TEST if it could cause unwanted results or damage.)
• Any one environment can be down no longer than 24 hours.
• All the refreshes have to be done in a calendar week.

So…Here’s the game plan:

We created a new environment (What’s that? Number 7?) that will be used as a staging area where the data refresh preparations will be conducted. This environment will hold an extra database that will gather and hold all environmentally sensitive data from all the environments using SQLDataCompare.

1. Each Saturday a FinalBuilder project is scheduled to gather all the environmentally sensitive data from each environment and store it in the hold area.
2. Each Sunday a SQL job restores a production copy of all databases down onto this refresh preparation environment.
3. A FinalBuilder project is scheduled for 8AM each day:
   1. It recovered non-essential space from the databases in the form of audit, deprecated, and staging tables.
   2. Environmental sensitive data was pulled from the holding area and overlaid on the PROD data depending on the day:
      1. Monday = DEV
      2. Tuesday = TEST
      3. Wednesday = QA
      4. Thursday = PROOF
   3. The data is scrubbed of personal/sensitive data.

On Monday, which is the first time the space recovery and cleansing routines run, it takes about 6 to 7 hours. Each subsequent day takes about 5 minutes.

1. The Production DBA’s backup the refresh environment and restore it to the target environment.
2. The Production DBA’s resync the security on the target environment. (So that it works the way it did before the refresh.)
3. Conduct any necessary code promotions (either though automation or a request, depending on your process).

There…All done. A data refresh used to take all week without the ability to do anything else. Now…we could do all environments in a single week and still have time for additional tasks. Gotta love Redgate.

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.

Several recent events have brought associate engagement to the fore-front. Since the quite frank and refreshing conversation that out CIO had at the associate breakfast a little while back, I’ve looked inward to find where I was engaged and where I wasn’t and why. The classic definition of engagement doesn’t fit this conversation, however I did find this:

Employee Engagement – [Business Management Concept] An “engaged employee” is one who is fully involved in, and enthusiastic about their work, and thus will act in a way that furthers their organization’s interests.

At work I always act in a way that furthers my company’s interests, mainly because being an employee ties my future with the company’s. When the company benefits; I benefit. But I can say that there have been instances where I felt… less than enthusiastic. I worked hard non-the-less, but it felt like a weight on my shoulders. Did the work get done?…Yes. Was it a quality job?…Of course. Was it on-time?…Always. But, all the same, by the time I got home I was beat, mentally and physically. My wife would force me to “leave work out on the tree.” Which of course made it that much heavier.

Here are instances I’ve seen where a little recognition could provide quite a boost to associate engagement with very little cost:

• Being backed up – When your supervisor has your back and writes a wrong or injustice…That’s engaging!
• Recognition from your boss’s boss – A personal email from your director goes a long way: Let’s you know that your work makes a difference and was noticed.
• Role expansion – Being allowed to expand one’s role is very engaging.
• Idea investigation – If you have an associate that has an idea that could potentially benefit the company, create an environment where it could be explored by the associate. Make sure that when it works and is beneficial, that associate is in the front getting credit…engaging!
• Career coaching – Work with the associate to discover where their career is going and what it could become. Associates that can see their future are engaged.
• Comp time – If someone has worked extra hours, and/or through the weekend, giving a day or two off is a great way to allow the associate to recharge…..and get enhanced engagement.

There are many others…and the best part is all you have to do to find some is ask. I guess there is nothing more inspiring, at least in my mind, as getting a little recognition for your contributions and being involved in shaping your environment.

An observation if I may: Sometimes it only takes one act to disengage a highly engaged associate…Tread carefully.

So…When was the last time you were recognized for your contribution or allowed to make a difference? When was the last time you acknowledge someone for their contributions?

One of the first steps to initiating a maturity model program is to take an inventory of what processes exist. There are generally more processes than initially thought in any unit and it takes a bit of time to sit down and hash out what they are.

1. proc·ess/ˈpräˌses/

   Verb: Perform a series of mechanical or chemical operations on (something) in order to change or preserve it: “the stages in processing the wool”.

   Noun: A series of actions or steps taken to achieve an end. 

 With this definition in mind, how many processes (noun form of definition above) does your unit have? Probably more than you think. Anything can be automated as long as you understand the process steps and you have the necessary resources to accomplish the automation. Let’s take a look at a simple example; Spending Accounts (SPAA) Database Management. Keep in mind this is just an example and not an all-encompassing, cohesive list. Here are some of the processes that exist for managing the database in my old position:

 This is a short list of all the processes that are required, but I’d like to keep it simple for this exercise. Next, let’s categorize the processes into major and minor effort (i.e., how much resources does it take to accomplish – this could be considered resource savings or the REWARD), the dependency type of process (internal, external, mixed – i.e., will I need outside resources to finish the task?), and automation effort (i.e., how much resources would it take to automate – this could be considered the cost or the RISK). This is a much tougher task with a lot of variability. It is best done with a small group of knowledgeable associates; any group larger than 4 is going to lose focus and the discussion will breakdown pretty fast.

So let’s say this is the result (keep in mind this is hypothetical):

 So with these numbers we can start the evaluation/prioritization step. Remember, we are looking to get the biggest bang for the buck. There are many measures you could you, but the one I like to use is the Breakeven Point; at what point did the effort payoff? I understand that this is highly subjective and imprecise, but you have to start somewhere. Here are the results:

 So I can see that Data Refresh and Database Builds are taking the most of my time. They just happen to also be the fastest payoff for an automation investment, so I’ll target them first for automation. I also notice that while these tasks will require the same effort to automate, one has mixed (external) process dependencies so I might want to lower that tasks priority until I can arrange the necessary outside resources time.

Process improvement is a never ending endeavor: On a regular basis (e.g., once a quarter) this exercise should be conducted again to see what has changed and how it impacts existing maturity efforts. Business processes change, new technology becomes available, associates come up with new ideas for automation…These will impact what is possible and how they can be employed to improve the existing processes.

Next we’ll discuss breaking down a process targeted for automation.