For those of you not already aware, VMware and PowerCLI guru Alan Renouf (blog | twitter) recently upgraded his excellent vCheck Powershell script framework to have a plugin friendly approach. (The framework was originally written to provide a daily report of issues identified throughout a VMWare environment.) As a result, numerous forks have been popping up for getting daily reports on all sorts of systems, from Exchange to System Center. I noticed that there didn’t appear to be anything for SQL Server, and pinged Alan to confirm if anyone had starting working on one. When he responded that no one had, I volunteered to take a stab at it. The framework is an impressive piece of work, with some robust HTML reporting features and extensibility. If you’ve got a need to do any kind of centralized, scheduled reporting on some infrastructure, I highly recommend you check out his work.

And here, dear reader, is where I need your help. I want to make this as useful and complete a daily health check as possible for all of us DBAs out there. I have my own list of items that I want to check on, but I want to get more input to make sure I’m covering all the bases.

The scripts will connect to a CMS server, and iterate over all the servers contained within while performing various health checks. At the end, a nicely formatted HTML report is delivered listing all the problems identified. Thanks to Alan’s work, all the thresholds will be completely configurable.

Here is a list of the checks I’ve thought of so far:

1. Ping test (is the server responding to a ping)
2. Last backup date (full, differential, log)
3. Last DBCC date
4. Disk space free percent
5. Services running (SQL Server, SQL Agent)
6. Database file space free percent
7. High severity errors (17+)
8. Failed logins (over a certain threshold of counts)
9. Failed agent jobs

This is a short list, which is why I’d love to hear from you. What would you like to see in your mailbox every morning that will give you the best view of your SQL Server infrastructure?

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!

It’s T-SQL Tuesday again! This month is being hosted by Argenis Fernandez, and the topic is specialization. I am late to the game because, go figure, I was held up at work troubleshooting issues. Shocking, I know, that a DBA would be kept late at work!

In my time working with SQL Server I’ve done my best to keep my focus fairly small (I would say I’m a performance / admin specialist – maybe that’s a future blog post to describe what that means). What with all the various features that are contained within the sphere of the overall SQL Server product offering, it’d be fairly easy for my ADD-riddled brain to jump completely off the deep end. The problem is, as we know, trying to be an expert in everything ends up causing you to be an expert at nothing.

But even with keeping my SQL focus narrow, my job has expanded greatly in the last year or so after I left the production DBA group. Being the only full-time DBA on the current team means that I’ve also had to pick up other skills, such as VMWare and AD domain administration to name a few. Has this hurt my SQL Server skills? I’d have to say yes, as it’s taken away time and brainpower I could have devoted to learning / fine-tuning my SQL Server skillset. But at the same time, it is a necessary evil in today’s “do more with less” world, and I need to accept that.

So how do I try and balance out the lost time? In my spare time at home, of course. Naturally things like family time take precedence, but I do make a point to spend a few hours every week playing around in my home lab setup. This has helped keep me pretty sharp, though I certainly wish I could do more. Especially with SQL 2012 coming out, the “To Learn / Play” list just keeps growing and growing.

I’d love to hear from other folks who’ve found their roles at work shifting and expanding, and how you have tried to keep some relative priority on SQL Server as your “specialty”.

Recently I had a requirement to determine how a certain Windows user gained access to an instance of SQL. Let’s say, for example, you want to audit certain DDL code executions for some users, but not for others. We could do this at the individual login level, but that would be tedious to keep up. Instead, we want to use Windows domain groups to selectively enable the audit process. The trouble is, when a user is logged in all we see is their individual user name, not the name of the groups that, but virtue of them being a member, give them access to the server. Or do we?

I was reading a post by Erland Sommarskog on the subject of permission granting through stored procedures, and noticed his use of a system view called sys.user_tokens. This view (according to BOL) “Returns one row for every database principal that is part of the user token.” In essence, it shows one row per role or group that the user is part of. For example, if a user is a member of a Windows group that has access to the database, a row will be present for that.

This got me thinking, was there an equivalent for the server level? Sure enough, there is the sys.login_tokens view. This shows one row per server level authentication token. For some reason it seems to return duplicate rows at times, but when joined on the sys.server_principals table it does indeed seem to work as I hoped.

This way I can tell what domain groups a user is a member of that gives them access to the server.

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.

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.