I’m excited to announce that I’ll be presenting at SQL Saturday 121 in Philadelphia on June 9th! My session is titled “Avoiding Monkey At The Monitor By Delegating“; I’ll be showing some ways to securely delegate menial DBA work so that you can focus on more important (less urgent) work. While the session is really geared towards DBAs, it could be useful for some developers as well, since we’ll be talking about things like permission chaining and certificate security as well.

I’m obviously psyched to be part of a great group of speakers. I’d be lying if I wasn’t a little nervous too, since this is my first time presenting at this level (or really any outside of my company, for that matter). So, I’ll be practicing quite a bit between now and then.

Hope to see you there!

A short while ago, I read a great post by Kendra Little (@Kendra_Little on Twitter) on how using some common consulting tricks can be useful even for those not in a consulting role. One item that resonated in particular was the idea of tracking your time, for the purposes of finding places where your time is probably not being used wisely (Kendra uses the example of not planning for disaster recovery, a clear “must-do” for a good DBA). I’ve tried various methods of tracking my time before, including everything from home-grown applications (I suck at GUI programming on a side note) to simple notepad paper. Everything failed miserably for one of two reasons.

It Was Too Hard To Use

My cruddy GUI programming skills aside, I had never found something lightweight enough that I could really use it consistantly without slowing down. In my work I am constantly switching tasks, so whatever method I used needed to not require ten clicks to switch from one task to another. If it was anything less than really easy, I would simply give up after a few days.

It Didn’t Give Useful Data

Recording time is one thing, but of equal importance is the metadata associated with that slice of time. What project was I working on (if there was a defined project, not something that’s always true)? For what client? What “tags” or other little flags were associated with a piece of work (for instance, was my time spent dealing with a “walk-up”, or was it unplanned troubleshooting)? Without this enriched data, simply knowing that I spent from 8 AM to 9:45AM working on task ‘XYZ’ really isn’t very useful.

Well, thanks to Kendra’s suggestion, I’ve finally found a tool that I’m sticking with. Toggl is nothing short of fantastic! It’s easy to use, quick, and allows me to easily tag and classify work without a lot of extra effort. It’s been downright fascinating seeing how my time is really spent, and I’m finding it’s often quite different than what I perceive. I might feel like I’ve spent every waking moment working on a particular effort, only to find that my judgement is skewed simply because I find that particular work unpleasant (nothing to throw off your gut feeling like hating what you’re doing).

Most importantly, it’s enabled true, accurate (at least mostly so) transparency with my superiors and customers. When I report weekly to my boss, I can say with integrity what I have (and conversely, have not) been spending my time (and therefore the company’s money) on. It’s been invaluable in tweaking my workload to better suit our client’s needs, but also in identifying easy automation / delegation opportunities. Clearly a win for both sides!

I was just sitting here looking over some old code projects of mine, and thinking “Man this stuff is junk. How’d I write this?! And why’d I waste my time on it?” These included a Java library for parsing tweets for data mining (boy that was going to be a great open source project), my first .NET application that was for work and used Microsoft Access as a back-end, and various other half-started works that never really took hold. At first this was rather discouraging, since it reminded me of my genetic pre-disposition to not following through on my projects (a topic for another day for sure).

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.

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.

Yes, you heard me right. I have committed the ultimate DBA sin: I used Microsoft Access. And dammit, I enjoyed it. Somewhere, Brent Ozar, the king of Access, is plotting my violent and painful death.

Here’s the story: I’ve been using SharePoint extensively for tracking project work in my new role. It’s critical that I’m methodical and completely transparent here, because demand for my time is high and people aren’t going to take “I don’t have time to work on this until March” at face value unless I can prove it.

Overall it’s worked really well, but one frustration is the lack of things like sub-forms in SharePoint. That is, it’s really hard to be looking at an item in the Project list and add a related item in the Task list without a lot of clicking and copy / pasting.

And this is where (in the tradition of the great Emeril Lagasse), I said to myself, “Self! Remember when you were a fledgling programmer and used Access? Remember those nifty and very easily created subforms? You could use them here!” And use them I did. I set up a couple forms and nested subforms for easy addition and querying of related items, with some filtered drop-downs as well (another lacking feature in SharePoint). All these were bound to SharePoint linked lists.

And here’s why I feel mostly OK about it: it’s a tool for me to use, and it makes entering and tracking my time a lot simpler. That is going to benefit me and my clients. Yes, it’s not perfectly architect-ed, super robust, and well documented. But for now, since I’m pretty much a team of one, I’m good with that.

But still, like the shrimp in Finding Nemo says: “I am ashamed.” And I need to go take a shower now. Because I am a dirty, dirty boy.

I’ve blogged a bit over the life of this site about my use of a life-management framework called GTD and how profoundly it’s affected my life. Sometimes when I talk to people about it, a common response is “but why would I want to spend so much time keeping track of what I have to do instead of just doing it?”

The answer is that having everything in those magic lists gives you a kind of ultimate peace of mind that’s, well, hard to describe for those who haven’t experienced it. Now I know that makes it sounds like some kind of hippy-ish drug-induced trip; but really, the simple confidence of knowing all your responsibilities (“agreements” as David Allen, author of GTD calls them) will be there waiting for you when you come back is nothing short of priceless.

As I was reading my blogs today I found a great entry from fellow GTD’er and SQL community member Brent Ozar (blog | twitter). I think the passage below beautifully describes this “mind like water” (another David Allen phrase) state:

At around 5PM, when I’m not on the road, i leave my home office and my tasks behind.  I walk Ernie (our dog), get the house ready for Erika’s return from work, and leave the workday problems behind.  I’ll still check email from my ozone when we’re not doing anything, and I’ll respond to quick questions, but I won’t do work.

And I won’t care.

I won’t stress out about things I have coming tomorrow, won’t get worried about what a client’s server is doing, won’t work late trying to “get ahead” – because there’s no such thing.  As a knowledge worker, I’m going to be behind for the rest of my life.  The better I am at accomplishing stuff, the more work people will give me.  At 5PM, I have to change contexts because I won’t ever be caught up in my home life either.

via GTD: Why Things Have Been Quiet Around Here | Brent Ozar – Too Much Information.

Thanks for helping spread the word Brent. It’s always great to see others having the same experience and speaking eloquently about it to the world.

Now if you’ll excuse me, I’ve been neglecting that @home list of mine.

More often than I’d like, I get e-mails from various folks late in the evening (the record is about 11pm) somewhere along the line of: “This is really urgent and needs to be done EOD today.” Now, I have to assume that either one of two things is going on here:

1. Their End Of Day is different than mine. Not in the time zone sense (though I do occasionally deal with folks offshore), but just in the sense of they work hours beyond the normal workday.
2. They’re being clever and trying to be the first mail in my inbox the next morning.

Now in the case of #1, obviously they’re not readers of my blog. Otherwise, they’d see my prior post where I was pretty clear about my policy with regards to working after hours: high value and emergency work only. That’s not to say there have not been some cases where the work met one or both of those criteria; just merely that most of the time it doesn’t.

In the case of #2, for one I’d laugh because with the sheer volume of e-mail I get, no matter where you end up it’s most likely not at the top of anything. That, and even if you do somehow manage to be the first thing I read in the morning, that in no way affects where in the queue of actions you’ll end up. Guess they’re not subscribers to the GTD style of organization, huh?

Recently I was chatting with my sister-in-law, who happens to be a product owner at a rapidly growing startup company. We were talking about the nature of “flow” and how important it was for people to be able to focus on doing their work without being interrupted. Partway through the conversation she had a great quote (paraphrased roughly here):

Google has a great description for the role of managers: they should be sh*t umbrellas for their team. Their most important role is to protect their team from all the sh*t that comes flying at them so they can focus on doing their job: writing code. That’s my job in a nutshell and I take it very seriously.

The best reference I could find to that term is here, where GMail product manager Todd Jackson was quoted as saying “You can either be a sh*t funnel or a sh*t umbrella.” The article goes on to describe how a good manager protects their team from unnecessary garbage, in effect being an “umbrella” to the storm of things (requests, questions, stupid meetings) that rain down on their staff. But some managers take on more of the role of being a “funnel”, in that they simply filter and then pass on the things that come at their team.

I suppose one might say, “Well at least in the case of the funnel, the people aren’t directly exposed to the onslaught of sh*t.” That’s true, but I’d counter that depending upon how the manager passes on the stuff they deal with, the end result could be the same. If at once-weekly meetings everything that’s been triaged is laid out and assigned, that’s fine, and in fact I’d argue that’s really playing the umbrella role. But, on the other hand, if the manager simply sends everything off towards their team with the convenient “FW:” tag, well then what’s the point of the filter at all?

Personally at work I’d love to have some way of shielding my team from the daily barrage of (mostly) crap work that comes our way. I still believe the idea of a “handler” and holding designated office hours would provide immediate and noticeable relief. In effect, the handler becomes the umbrella, handling the collecting and triaging of incoming requests and only interrupting the rest of the team when absolutely necessary. Hopefully we can get some buy in from management on the idea, and time will prove our strategy to be one that greatly increases our ability to get work done.

So how do you shield your team from flying sh*t? Are you a funnel, an umbrella, or some combination thereof?

A Brief Recap

In part I of this two-part series we talked about the first form of what I call “Racing Brain Syndrome”, the “Stress Induced” variety. To briefly recite the main points:

• It is usually caused by underlying anxiety about some aspect of your life, be it work, home life, or just plain keeping stuff in your head about things you’ve committed to doing.
• This “stuff” can be either real or imagined (i.e. you’re going to get fired for writing a personal e-mail at work, once)
• It is best remedied by either:
  • writing things down and clarifying all the details of what you need to do to take care of whatever is bothering you (best for dealing with the former “real” source of anxiety)
  • practicing a little psychological technique called cognitive behavior therapy, which teaches you to examine and refute irrational thoughts using logical techniques.

Now we’ll talk about the second form of RBS, called “Excitement Based”.

It’s, well, due to excitement (duh)

Yeah, that does seem pretty obvious, doesn’t it?

In some ways this form of RBS can almost be seen as a positive thing, in that it means you have some good positive feelings about your life. Think about it: if you were bored and depressed, would you be waking up at three in the morning with some fascinating new take on that problem at work? At the time you may find yourself more annoyed by your brain’s utter refusal to quiet down, but if properly harnessed, these midnight revelations can actually greatly add to your productivity and creativity.

The trick to properly capturing these sudden bursts is to do just that: capture them

“Huh?”, you may say. Well, think about it this way: the very reason why your mind is refusing to let you drift off is because it’s saying “Hey, I have this great idea, but if you go back to sleep, you’ll forget me and I’ll be lost forever!” In a way it’s almost a built in self defense mechanism, designed to keep your late night brainstorms from slipping through the cracks.

To convince your brain not to concern itself with retaining this newfound wisdom, I suggest you follow the same basic tenet of GTD we used to combat the stress-induced variety of RBS: get it out of your head, now. Write it in the notebook you keep by your bedside table (you do keep one there, right?), send yourself a quick e-mail on the CrackBerry (yes, I’ve been known to do that in the wee hours of the morning), even scribble it on a spare Kleenex or other handy medium if you must.

You might think this behavior will just exacerbate the issue, since it will likely make you more fully awake. While that’s true, I would suggest that until the thoughts rolling around in your brain are properly captured and accounted for, you’ll likely find yourself tossing and turning restlessly anyway. This way, you’ll at least get things recorded so that you’re not battling that inner voice, nagging you about unfinished business. Trust me, that’s a battle you’ll come out on the losing end of every time.