Then it hit me: it’s only by writing all this crap (and believe me, most of it really is steaming piles of crap), trying out different routes and ideas, and ultimately letting them fall off that I’ve been able to improve my skills as much as I have. Picture the proverbial writer sitting at the typewriter with a pile of crumpled papers next to them, head in hands. But then, one day, something clicks, and out comes a masterpiece.

It’s not important that all we produce is wonderful, glittery, and perfect. No, what’s really crucial is that we keep going and pushing ourselves, especially when it seems like all we churn out is junk. That junk is gold, because it’s what teaches us to do better. As long as we keep learning from our mistakes and bad ideas, then we grow as professionals and human beings. And sooner or later, you might just produce that golden egg.

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?

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.

Why I Hate Writing Documentation

The answers to this question are pretty straightforward, but as we’ll see, there’s counter-arguments in most cases.

It Makes The Work Take Longer

It’s true that documenting a process means taking longer to actually complete the work. Consider the added time to take screenshots, write down steps, and (of course) test to ensure completeness, and it could well double the amount of time required.

On the other hand, this increase in time should be pretty much a one-time occurence, with occasional tune-ups of the documentation required as procedures change and evolve. So really, it’s not that much extra work.

The Logic Is Too Complex To Document

STOP!

On this one I have to call my own bullshit. There is no such thing as a process that is too complex to document. It may take pages upon pages of screenshots and steps, and turn into a forty page monstrosity, but it’s still quantifiable.

So let’s be honest here: what we’re really saying is that we’re too lazy to take the time to document our process, because it would take a long time. Everyone agreed? Let’s just suck it up then and move on, because laziness is never an excuse for bad practice.

It’s Really Boring

Well that one is easy to answer. You see, all you have to do is… um… well…

*thinks*

Okay, you’ve got me. I can’t really think of a way to make writing documentation fun. Oh well, there are some things in our jobs that just aren’t going to be fun. All we can do is minimize them, perhaps by automating things as much as possible?

Why I Love Writing Documentation

It Lets Me Outsource

If I have a process thoroughly documented, it makes it easier to hand off to a junior DBA or other staff. This then frees me up to do more interesting or important things. Why spend time doing things that someone else could do just as well, when I could be doing things that provide real value to my customers?

It Ensures Consistent Service

To me this is the big one. If my documentation is complete, I can leave for vacation knowing that my customers will be well taken care of while I’m gone. Good customer service means consistency: they provide the same input (a request to restore a database), and receive the same output (the database is restored and permissions re-granted). Without documentation there’s no guarantee that someone else on my team would handle the request the same way I would; with it, the customer won’t even notice a difference.

So now I’ll pose a question to the community: How do you motivate yourself to write documentation? How do you ensure it is kept up to date?

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.

What Makes A Process Simple?

sim·ple   [sim-puhl] adjective

1. easy to understand, deal with, use, etc.: a simple matter; simple tools.
2. not elaborate or artificial; plain: a simple style
3. not ornate or luxurious; unadorned: a simple gown
4. unaffected; unassuming; modest: a simple manner
5. not complicated: a simple design

When I design a process for my team to follow, I always do my best to follow the definition above, especially the first and last lines. Processes should be easy to follow and only as complex as they need to be, no more. Programmers can sometimes get carried away with complexity, because a) it’s fun, b) it lets us stretch our muscles a little. That’s all fine and good in your lab, but when you’re trying to design something to be used in the real world by actual people (other than yourself), complexity can be a huge problem.

Take this example: your team needs a way to locate information about a particular server, including what it’s used for, make and model, etc. Here’s two choices for how you fulfill this requirement.

Option A – Command line / SQL scripts and a SQL Database

Sounds easy enough, right? And a relational database seems like the perfect place to store this kind of structured information.

Well, that’s great, for someone who is comfortable writing T-SQL code to interrogate a database. And who understands the structure of the database. And, oh by the way, can even find where the database is.

I’m guilty as charged on this one. When I started on my team there was no repository for server inventory, so I made one. I’d call it a pretty well designed database, normalization and structure wise, but there’s one glaring flaw: no interface. Every operation, such as adding a server, removing one, looking up information about one, etc, is done via T-SQL scripts (hand-written mostly). Fine for me, but my teammates are going to quickly get lost and just call me when they need information.

Let’s assume I’ve even saved scripts in our code library for common operations, such as looking up a server’s information by its name (not always a valid assumption, certainly). Even with that shortcut, so the team doesn’t have to write JOIN statements, consider the list of steps required to look up a server:

1. locate the correct script in the repository
2. Open the script in SSMS (do they have that installed?)
3. Connect to the server where the management database resides (do they know where that is?)
4. Look through the script and find the “find/replace” tags (I would at least use SSMS template parameters, though it’s debatable if that is easier)
5. Execute the script and interpret the resulting text output

Hmm… five steps and several questions. Not very simple.

Option B – A Small Web Application With A SQL Back End

With this choice, we still get the benefits of storing the data in relational format, but with a cleaner interface (even my crappy UI design skills can manage this one). Most competent admins can understand a web interface with fields and buttons, so they should have little problem using it once trained (and of course, its use would be documented).

Consider the steps required to perform the same operation here:

1. Open a web browser and browse to the web site (do they know where it is?)
2. Select the “Find Server By Name” option presented on the home page.
3. Enter the server name and click the “Find” button.

We’re down to three steps and only one question (do they know what the site address / URL is). Now we’re getting into the realm of simple.

One could certainly argue that there’s an even easier solution, along the lines of a folder on a searchable network drive with a set of Word documents, one per server, in standard format. I wouldn’t discount that argument, but I just really had folders full of Word documents, especially when you have to go through all of them one at a time to change some common field.

You could also argue that the process of writing a web application (and supporting it) would outweigh the simplicity gained by having the information presented in that manner. After all, can anyone else on your team understand C# code? What happens when it breaks and you’re on vacation? That may be true, but I honestly believe that if you write your code properly (and, drumroll please, simply) then the application should be pretty much self-sufficient. Good code hums along and doesn’t require babysitting.

So the next time you’re designing a process for your team, remember to keep things as simple as you can. Your teammates will thank you.

One of the more common scenarios I’ve run into is that the distribution agent encounters an error applying replicated transactions, causing the flow to stop dead in its tracks. From some research, it seems like in general this symptom indicates that the publisher and subscriber are out of sync, and usually the best option is to re-initialize the subscription. For various reasons, however, that is not an option in this case, so I’m left with resolving the issue causing the replication to fail. That means finding the actual command causing the failure, which was a little tricky.

I’ve found that the Replication Monitor GUI is not very helpful in this regard. What I tend to see is that it reports the transaction sequence number just fine, but the command text is less reliable. Mostly I’ve seen the last command in the batch, which (with my application) usually ends up being a simple “IF @@TRANCOUNT > 0 COMMIT” statement. Not very useful.

So instead, I’ve taken to using some good old T-SQL commands to get the information I want. The first is the sp_helpsubscriptionerrors stored procedure. This is executed at the Distributor and will return a list of errors for a specified subscription. The columns error_text, xact_seqno, and command_id are very useful, as they tell you the specific error message being encountered, as well as the transaction and command that are causing the problem.

Once we have this information, we can use the sp_browsereplcmds stored procedure to actually view the command(s) causing the issue. While you could feed it both the transaction sequence number (usually the same for @xact_seqno_start and @xact_seqno_end) and the command ID (@command_id), I find that it’s most helpful leaving the command ID out. This way you can see the entire transaction’s worth of commands (including the offender), giving you some good context to evaluate how to resolve the problem.

As for actually resolving the problem, well that of course depends upon what the root cause of the issue is. In our case, it seems that most times the problem lies with some developer deleting or adding a row at the subscriber, causing data validation errors. It’s a painful process, which is why our response is usually “Hey Mr. Developer, you deleted this row and now it’s causing problems. Please let us know when you’ve recreated the row and we’ll restart replication.” Let people fix their own problems.

I’m sure I’ll blog more on the subject as I encounter other problems and learn more about supporting replication.

Design for next time, not this time

When you get a request to do something, say, restore a database and give a team some access, do you just click through the GUI quickly to get the job done, then move on? Or do you consider how you can design the process so that it can be repeated over and over again with the same result?

Before you answer that question, let’s consider why we’d want a process to be repeatable, especially as a lone DBA:

• It minimizes the work needed to take care of similar requests in the future. Instead of starting from scratch, you can re-use existing code or knowledge. You therefore have more time to work on other, more important (but perhaps less urgent) items.
• It results in consistent service to the client. The same input (“Please restore this database and grant me access.”) should always result in the same output (the database is restored to the right server, and the right permissions are given). The client always gets what they want, and it is delivered in the same manner.
• It allows us to delegate effectively.It’s much easier for a junior team member to walk through a well thought process than to have a request thrown at them and told to figure it out (which usually results in a stream of questions, followed by a gruff “Oh jeez I’ll just do it myself, it’ll be faster”). Now you can focus on the work that really requires a DBA toolset, instead of boring administrative tasks.

Still think the processes you put in place don’t need to be repeatable? Perhaps you should read the first post in this series, and remind yourself of the ramifications of blindly doing things your way.

Moving on, let’s examine the characteristics of a good, repeatable process:

• Simple – the process should accomplish what the intended result is, with a minimum of fuss and effort, with no unecessary complexity in terms of how the process works.
• Automated – the process should require as little manual intervention as possible. If there are no steps requiring human decision making, then it should only require our help when something goes wrong.
• Documented – the process should be clearly described, both in terms of how to complete it, as well as how it accomplishes our goals (from a technical perspective).
• Generic – the process should be designed such that it can be tweaked at run time (to use programming lingo) to meet varying needs, as opposed to requiring lots of manual intervention.

In the next few posts in this series we’ll examine each of these in more detail. Please let me know in the comments if you can think of any other required traits of a repeatable process!

We don’t need no stinkin’ team…

Sure, who needs teammates? I mean, we don’t need to take vacations, have sick days, or even take breaks to go workout at the gym or go for a run. We can just work all day, handling everything ourselves. And if we’re on vacation, we can always take our laptops with us, and tether our Crackberry to dial in to the network when things need to get done.

If you’re not laughing out loud, or at least cringing at what the last paragraph describes, I’d like to recommend a website for you to browse.

Hopefully we can all agree that we all need time off now and then. And perhaps the occasional unplanned absence will happen, despite our best attempts to be the Cal Ripken Jr. of our office and keep our no-sick-day streak alive. And when that time comes, would you rather have your customers needs smoothly handled without pause, or would you like them passed around from team to team like the proverbial hot potato? Or even better, given that wonderful “Well, can it wait until so-and-so is back in the office?” question. I don’t know about you, but I hate it when I hear that question.

You can be the man, without being the man all the time

We all like to feel important and desired; after all, it’s just a natural human need. Early in my career I craved the spotlight and guarded my knowledge carefully, lest someone else take my spot as “go to guy”. It felt great to always be the one to solve issues and get things done. Well, at least it felt great until I came home three hours late and faced a cold dinner plate, or spent the day after Christmas troubleshooting over a dialup connection, while my wife and relatives opened presents in the other room.

At some point in our careers, I believe strongly we have to choose one of two paths:

1. We can keep being the lone wolf who does things our way all the time, however we please, because we’re the only one who ever knows how to do them.
2. We can compromise our rigid principles, stop being stubborn, and learn that sometimes we have to consider if our teammates could do things how we’d do them.

Allow me to clarify that last option a bit: Just because you’re a whiz with T-SQL and could whip up a one-time script to update ten thousandmillion records for that cute girl / guy in Operations doesn’t mean that you should. Because like any reasonable customer, if it’s done once, they’ll expect it can be done again, whether you’re there or not. And if this person / group is an important consumer of your services, chances are you’ll be getting a phone call from your teammate (or your boss) when you’re at your kid’s Scout camp, asking how to modify your query (because you at least saved that and let the team know where it is, right?).

If your whole team is full of crack T-SQL programmers, good for you, go wild (and you are one lucky SOB); otherwise, don’t do something unless you’ve made sure your backup can do it just as well. Maybe you feel comfortable running a series of scripts to setup replication every time you restore a database, but how about the guy sitting next to you who spent most of his career as a Windows sysadmin? I’m not suggesting that you can’t do complex things that can’t be repeated with a few clicks, but do consider how to make the processes easier for those without your experience. Talk to your teammates and make sure you build a process they’ll be able to support. Maybe they’re not comfortable running scripts in Management Studio, but perhaps a wrapper Posh script that prompts them for the necessary information and takes care of things under the covers would be fine.

Believe me, you’ll still be appreciated when you return from that much-needed vacation, perhaps even more so because you made sure your customer would experience the same great service when you’re not there. And there will always be new challenges to tackle where you can flex your muscles to remind everyone just how good you are. Just remember to bring your teammates along too.

T-SQL Wednesday

A long time ago, in a building far far away…

…there was a young man who had just joined the ranks of an operations group at a financial firm. He had quit his job as a retail store manager (a job he only took because nothing was available in his chosen field of social services) and taken a temp assignment as a means to pay the bills. His job was simple: run reports from various systems (a mainframe green-screen system, a SQL database), carry some checks around, and various other menial tasks. But still, it was nice to be out of the 12 hour workdays of retail.

Soon after beginning work, the man noticed that a great deal of his work amounted to punching in a bunch of repeat keystrokes and waiting for things to print. As a result, he quickly learned the ways of ExtraBASIC, and wrote macros to tab around the terminal screens. He also learned how to use the query designer in Access, so that he could quickly get data out of a database when requested by various teams. Within a few months, word had spread of the reporting wizardry, and he was offered a permanent job within the group.

The First Fail

While not officially a programmer, he was given several tasks around building utilities for the group he worked with. Abandoning Access for greener pastures, he downloaded Visual Basic Express and began whipping together tools. Requirements shifted on a daily basis; after all, the ops groups weren’t used to having their own private coder at their beck and call. The young man enjoyed his work, if only because it kept him out of the daily grind of operational processing. And what with the simple drag-and-drop designer, the actual coding was minimal. A click here, a quick line or two of code there, and voila, a functioning application. Naturally Access / Jet was still used as the back end database, since that was what was available.

When it came time to roll out the application, the team was gathered and a demo given. Everything worked fine, and the decision was made to start using the new app right away. Everything was in place, and the go-ahead given. But soon after the use started, problems began appearing. Data wasn’t being consistantly updated, operations took a long time to complete, results weren’t as expected. After several hours, the decision was made to go back to the previous method of operation.

When at first you don’t succeed, try, and fail, again…

The young man was discouraged by this early setback, but he learned from his mistakes. At least until the next release, which included such wonderful upgrades as using MySQL as the back-end (on a dusty desktop box) and optimized in-line SQL (as opposed to GUI generated code). It fared much better, lasting several days before major issues were uncovered.

Within several months, he was offered a job as a full time programmer. He quickly churned out code, and his teammates loved his work ethic. What they didn’t love was his propensity for writing overly complex, convoluted code, which also conveniently was bereft of any comments. He also had a knack for finding and leveraging new technologies, such as an open source job scheduler, SQL Server Express (installed on that same dusty little box), and random .NET classes pulled off the internet. This was great when it came to functionality, and the business units were pleased. That is, until he was on vacation and something went bump, and they were forced into manual processing while the man’s teammates frantically dug through .vb files.

Since then…

I’ve learned quite a bit about writing better code since that first debacle, with many, many more missteps along the way. I’ve written code that was far, far too complex for what it needed to be (think a whole class for a simple calculation), scaled terribly (a linked server as a source for an 8+ hour ETL process that involved three layers of nested cursors), and was completely undocumented. But I’ve also grown tremendously as a programmer, learning lessons such as:

1. Write simple, reusable code. Don’t try and be clever; just write what is needed and nothing more.
2. Don’t rely on GUI generated code. Use it as a basis, but understand what it does and tweak it to better suit your needs. I yell at developers constantly because they try and do something via the Management Studio interface instead of writing the code. Writing encourages understanding, which in turn encourages better code.
3. Have solid test plans in place, and be methodical about following them.
4. Code for future scale, not current. I still refer people to the paper written by the Google founders years ago, where they explicitly acknowledge and plan for greatly increasing activity.
5. Document your code to the teeth. Some day in the future, long after you’ve moved on and requirements documents are long lost, the code will break, or need to be updated, and your comments will be the only information available about why things were done the way they were. I don’t know about you but I’d rather have people singing my praises after I’m gone, as opposed to throwing strings of four letter words (as well as various hard objects) at my picture.

I still cringe when I look back on those early days. I’m actually good friends with the guy who was my first development manager, and every time he mentions the name of some application I wrote, I scowl and apologize for having given birth to such junk. Everyone writes code that makes their skin curl years later, but what’s really important is that we strive to learn and improve our skills on a continuous basis.