Yep, you heard me right. I am going to automate everything I do. Or, at least, try to.

I’m not going to talk in detail about why I’m doing this, other than to say:

1. I have better things to do with my time than perform mundane, repeatable tasks.
2. I recently re-read John Sansom’s excellent blog post on the subject, and, as is usually the case with John’s work, found it rather inspiring. Bravo John!

Instead, I’m going to talk a little about two kinds of “automation”, and why I generally pick one that might not, at first glance, be the right choice.

Full versus Partial Automation

I will define something that is fully automated as a task or operation which requires absolutely zero manual effort on my part. That is, I don’t have to push a button, flip a switch, type a command, or even know that it is happening, unless something goes wrong. An example of something that is already fully automated in my world is the weekly and daily database maintenance that I have installed, using Ola Hallengren’s excellent (and freely available) scripts. These run every night and ensure that my databases are backed up, corruption free, and optimized. Unless something goes *bump* and the jobs fail, I don’t get so much as an e-mail upon completion.

By contrast, a process that is partially automated requires some manual effort, but is still optimized such as to require as little work on my part as possible. I may need to open a script, enter a few parameters, or run a Powershell command, but outside of that, all the logic and processing is done behind the scenes, with perhaps a pretty little progress bar showing that work is, well, progressing. An example of this might be setting up of a server side SQL trace; I have a series of scripts ready that, given a database name and a path at which to place the trace files, will setup a standardized trace, grant rights on some signed stored procedures to allow non-DBAs to read the trace data, and even generate a README file to be sent to the requesting team with a series of instructions.

In an ideal world, I suppose that all of the mundane daily work I have to take care of would be in the former (fully automated) category. But in reality, I find that there’s a bit of a diminishing return on putting more and more effort into fully automating tasks. Consider this scenario / requirement: the request and creation of new databases.

At a basic level, the process we follow is something like this:

1. A developer requests a new database via our in-house ticketing system.
2. We (my team) ensure that the request at least contains the following details:
1. The name of the database.
2. The server on which to create it (or at least a version of SQL Server).
3. The initial size of the database.
4. The domain users or group that requires access to the database.
3. Assuming we’ve got all the information required, we will go out to the database server where the databases will be placed, and determine a suitable location to place the data files. This is usually determined by simple the simple rule of “whichever volume has the most space”, with a few exceptions. If there is not enough space on the server, we’ll inform the requestor that there will be a delay, and start the process of ordering more storage.
4. Once we know where the data and log files will be placed, we will create the database, take a full backup (to initialize the differential backup chain), and grant the access requested.

Now, I could certainly envision writing some kind of application that would allow developers to submit requests for new databases and process everything straight through based on the rules outlines above, similar to what I see on my web host’s control panel for MySQL databases. But, at the same time, constructing such as system would take a lot of time and effort. Contrast that with a partial automation solution, such as:

• Provide an InfoPath form for the requests, forcing people to enter the necessary details in before submitting the request, thereby eliminating back-and-forth chatter.
• Use a Powershell script that, when given a server name, database, and log file size, will connect via WMI and determine a list of suitable locations for placing the database files, then prompt the user to select one. When selections are made, the database is created and a full backup taken. This eliminates logging into the computer (to look at drives) and stepping through a bunch of GUI screens to create the database.
• Have a second Powershell script that can accept a text file containing domain groups / users and role names, which will then grant the access required on a specified database. The file could be generated from the InfoPath form. Again, this eliminates some GUI clicks, and minimizes the chance of fat-fingering group / user names.

While this will not prevent someone from having to manually process the request, it will significantly cut down on the pain to do so, without a great deal of effort / complexity. Especially considering that these requests are fairly infrequent (perhaps one to three a month), I see no reason to spend additional time on providing a fully automated solution, when the partial one provides almost as much value.

Over the course of the year, I’ll be blogging about the various processes / means I use to accomplish this goal.

How do you automate your processes, and how do you determine a “break-even” point, if you will?

This post is the sixth in an ongoing series about how to survive as the only DBA in your organization. Since October of last year, I’ve been assigned to a team that is responsible for owning and maintaining the development infrastructure. It’s a great team of seasoned professionals, but not a single other DBA. As a result, I’ve had to think very carefully about how I go about my daily work, so as to give our customers consistently good service, while still allowing those without a lot of SQL Server related knowledge to pick up my work when I’m not available.

Why is being generic a good thing?

When I think of the word “generic”, I usually picture those off-brand foods at most grocery stores, with their simplistic labels and lackluster colors. But in the case of processes, being “generic” really means “standardized, yet flexible”. This is a good thing, because it means your processes can answer many different (but ultimately) related needs.

Let’s take an example: a development team needs to trace activity in their database. We’re going to assume that just granting this group rights to run the trace is not an option, since, let’s say, they’ve taken the server down with a poorly done client side trace in the past (don’t laugh, it happened to me). In any case, let’s look at three options to answer this request:

Have the developers sit with you while you run a Profiler trace

I don’t like this for several reasons, not the least of which is that it is going to take a good chunk of my time. Because of course, the developer will probably have no idea what they are looking for, and may not be able to product the condition they are trying to capture on demand. It also still uses Profiler, which, as far as I’m concerned, should be banned.

Script out a one-time trace and have it run on the server

This is better, because it takes a lot less of my time to simply setup a server-side trace and let it run. I can then let the developer’s read in the trace files via something like a signed stored procedure (a topic for another day perhaps). But there’s still the one-off aspect: who’s to say that the next time the developers need this I’ll be around, have saved the trace definition, etc?

Write a templatized script that accepts a database name and a path for trace files, and use it going forward

This, to me at least, is the best option. After a slightly longer initial setup (one-time to write and document the script, plus test it), setting up subsequent traces will take very little time. In addition, the use of a template will mean a consistent experience / process for my customers, even when I’m not around. Even when I am around, it will also make it easier for DBA Junior to handle the request, leaving me to look at more interesting things. And by making the script flexible enough to handle different servers / databases, it becomes much more useful.

I go so far as to have a “no one-off” policy at work. That is, if I do something, I script it, put some parameters in, and save it off to source control. Then I publish it in our procedures manual, so that if a similar request comes in the team can handle it right away. It leads to a lot of scripts that are not highly used, but it also means less work in the long term, and a great bag of tricks in the process.

But, can something be too generic?

Sure it can. I’ve fallen into the trap many times of trying to have one process fit way too many needs, only to end up with a monstrous, un-followable mess. If you’ve got a ton of “if this is true, do this, otherwise do this” type of logic in your process, you might want to consider if you’re really answering related needs. This is kind of like the process equivalent of that awful stored procedure we’ve all seen; you know, the one that has twenty plus input parameters, and has every one in the WHERE clause as WHERE ((some_field = @some_parameter) OR (@some_parameter = NULL)). It looks good, but in the end the execution is piss poor.

This post is the fourth in an ongoing series about how to survive as the only DBA in your organization. Since October of last year, I’ve been assigned to a team that is responsible for owning and maintaining the development infrastructure. It’s a great team of seasoned professionals, but not a single other DBA. As a result, I’ve had to think very carefully about how I go about my daily work, so as to give our customers consistently good service, while still allowing those without a lot of SQL Server related knowledge to pick up my work when I’m not available.

Automation Is A (Lone) DBA’s Best Friend

I subscribe wholly to the sentiment expressed in John Sansom’s excellent post, titled “The Best Database Administrators Automate Everything”. Time is something we cannot make more of for ourselves, and as such is highly precious. If you are spending time doing repetitive work that could be either partially or fully automated, that is time wasted.

But there’s another benefit as well to automating processes: it makes them easier to hand-off. Let’s consider the example of restoring a copy of a production database to a test server. The typical steps I would complete would be:

1. Determine the location that the backup file was placed at by the production DBA group (I do not have access to production backups, so they must copy them down to my server).
2. Determine where the database files should be placed on the server using this logic:
1. If the database already exists on the server, the files should be placed at the same location as they currently reside.
2. If the database does not exist, then the files should be placed at the default data and log location as specified in the server configuration.
3. If the database exists, set the database to single user mode using the “WITH ROLLBACK IMMEDIATE” option to effectively kill any open connections to the database.
4. Issue the restore command, using native commands or the Litespeed equivalent if the database came from a server using Litespeed.
5. Execute the databases specific post restore script from the source code repository. These scripts reset permissions for developers, synchronize users, etc.

While none of these steps are particularly hard, documenting them and teaching them to others would not be a small amount of work. Consider that step two involves querying system tables (or stepping through some screens in SSMS), and steps three and four require knowledge of T-SQL commands for restoring databases.

Instead, I chose to write a Powershell script which handles as much of the logic as possible. All it requires is a server name and the name of a backup file, and it handles the rest (except step five, but I’m working on that). This way, instead of teaching all that logic, all I need to do is show someone how to find the backup file (further simplified by standardizing paths across all the servers) and run the script.

I don’t know about you, but I’d much rather write code to do a job than write a document about how to do the job manually.

When resources are tight, folks such as myself in the IT world are often asked to “do more with less”, to use a rather contrite phrase. With layoffs looming for many and already a reality for some, it goes without saying that in the coming year, so-called “knowledge workers” will be asked to stretch their capacity as far as humanly possible. So outside of working insane hours and sacrificing quality time with your family, how do we meet this goal and satisfy our clients?

Now, if you’re reading this blog, chances are you are already a productivity minded person, which puts you at an advantage above those around you. But beyond practicing our GTD skills of constant capture and ruthless review, an ability to single out and automate those routine, time-wasting tasks can prove equally as useful.

As a programmer / business analyst in my previous job (and even now as a product specialist / implementations manager), this mindset was constantly hammered into my way of thinking. There’s a saying that programmers are a lazy bunch, and I certainly won’t deny it; I will however argue that is a good thing. If you’re paying a programmer by the hour, would you rather they spend extra time re-inventing the wheel, or make efficient use of existing code? Similarly, if you’re working with a B.A., wouldn’t you appreciate it if they noticed some repeatable tasks you were paying staff to do that could be easily automated. Granted it’s not always quite that simple, since with automation often comes cost as well. It’s always a give-and-take situation, where the pros and cons must be carefully weighed. If it’s going to take 400 hours and $20,000 to automate a process that takes one person an hour a week to do, that’s probably not a good value.

But really, that’s not what I’m talking about here. The fact is, there are people paid lots of money with fancy initials like “P.M.P” after their name whose job it is to do analysis on larger business processes. No, what I’m suggesting is that you take a closer look at your own daily work, and see if there are any easy targets to be scripted or scheduled, so as to free up your time to take on higher value work. Here’s are three adjectives and phrases that describe good candidates for automation:

• Repeatable
  
  You perform the same task on a hourly, daily, weekly, or monthly basis, with very little change. It might be running a report and sending it to a client, doing some number crunching in an Excel sheet, or perhaps pouring over one of those dreadful “green bar” mainframe reports and pulling out some data.
• Rules Driven
  
  The task performed is based on hard business rules. For example, you open a report, and if a certain number is off by more than a given percentage, you have to send it to someone.
• Number Based
  
  Number crunching, by definition, is highly adaptable to automation. If you spend any amount of time manually punching in calculations or summary statistics, you could probably take care of all the work via something like macros (in MS Office, for example).

How far you go with this is totally up to you. I’m a pretty avid scripting geek, so I’ve been known to write VBScripts, AppleScripts, and even (for you hardcore geeks) bash scripts to do just about anything. Over the next week or so I’ll be looking for good resources on how to script / automate common tasks and tweeting them, so tune in! Here’s a few to get started:

• Recording And Customizing An Excel Macro – good for you Excel whiz kids.
• Beginners Guides: Understanding and Creating Batch Files – The do-it-all of Windows / DOS, batch files can be used to automate lots of simple tasks.