A Wandering Mind

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 fifth 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 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?

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.

This post is the third 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.

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.

This post is the second 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.

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!