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 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.

I just wanted to post this quickly before I somehow lost the syntax I developed. What I was trying to do is gather some PerfMon counters for a set period of time and return some aggregated (averaged) results. Here’s what I ended up with:

$counters = (Get-Counter -ListSet "LogicalDisk");
$counterValues = ($counters.PathsWithInstances | Where {$_ -like "*Sec/*"} | get-counter -MaxSamples 10 | select -ExpandProperty CounterSamples);
$groupedValues = $counterValues | select Path,InstanceName,CookedValue,Timestamp | Group-Object Path;
$avgValues = $groupedValues | % {New-Object PSObject -Property @{CounterName = $_.Name; AvgValue = ($_.Group | Measure-Object CookedValue -Average).Average}};
$avgValues | Out-GridView;

This ends up working really well for what I needed. Now I just need to wrap it in a function, parameterize it, and enable it to interrogate remote machines via PowerShell remoting (less firewall hassles that way).

Well, almost. As of this coming Monday at least.

That’s right kids. Starting next week, I’ll be moving into a (gasp) Development centered role, acting as the database guru for a bunch of internal development teams.

What’s this mean in a nutshell? Well, among other things…

No More Pager

Yes, I know, as Tom LaRock (blog | twitter) very aptly puts in his book DBA Survivor, “A Development Server is A Production Server To A Developer.” But, at least for now, that only extends within business hours. No more wakeup calls at 3AM to deal with slow-moving servers.

Lots More Code Reviews

Up until now, code review was an activity that was handled by the production DBA team, including myself. And while I think all of us agreed it was a vital role to have, we were never really able to give it the kind of attention it needed. After all, as production DBAs, our main focus had to be on keeping production systems up and running.

“But Josh, doesn’t reviewing code help keep production systems running smoothly by preventing bad code from getting there in the first place?”

Right you are, and that’s why a dedicated part of my new job will be performing code reviews and ensuring everything follows standards. I’d be lying if I said I’m thrilled about the prospect of filtering through all those lines of code, but I do understand its importance and I’m honored to be given that responsibility.

It’s Cleanup Time

Another part of my role is taking ownership of a good number of Development owned database servers. This process includes auditing for configuration and setup, patch level, security (can you say “Powershell” people?), and planning for remediation to bring the servers into compliance with how our production systems are setup.

This to me is just as important as reviewing code in terms of helping our teams (both Development and Production) to have success in releasing code. Think about it: if you are running all your tests under a highly privileged account, on a server which is left wide open in terms of permissions, you could have a rock-solid test-case plan and still run into major issues when you try and move into a live system. While I’m sure that I’ll encounter some resistance (“What do you mean we can’t have the ‘sa’ password any more?!”), I’m confident that in the long run everyone will benefit from this inoculation of best practice methodology. Plus, the development teams can rest easy, knowing that their servers are being backed up and DBCC’d regularly, as opposed to hoping months of work doesn’t just go down the tubes courtesy of our friend Mr. Corruption.

Overall, I’m truly excited about the change. I will miss the adrenaline of dealing with production issues and the constant problem solving. But at the same time, I’m looking forward to attacking the kinds of problems we encounter as DBAs from a proactive approach, and building, dare I say, a strong rapport with our friends who pound out code on a daily basis.

On a closing note, I’d be remiss if I didn’t thank the members of my team for the wonderful ride over the last 6 months as a production DBA (has it really only been that long?). You’ve been (and will continue to be, since I’m still going to be working with you) exceptional teammates and I can’t wait to continue contributing to our team, just from a different angle.