Although many of the important tasks a DBA has to perform should be done 'by hand', keying in commands or using SSMS, the canny DBA with a heavy workload will always have an eye to automating routine tasks wherever possible, or using a tool. Although the likely candidates for automation are often obvious, it is not always so. Time can often be saved in surprising ways.
Occasionally, in our careers as DBAs, we're likely to experience the discomfort of having less time in the week than the time we need to administer our server environment. Where that threshold lies will differ for each individual, as well as the signs of reaching it. For some of us who work alone, it may be that the small business we work for has expanded to the point that the one or two standalone servers no longer meet the needs of the company. For others, who work as part of a team, perhaps the number of databases has grown exponentially, and the team must now, unless they change the way they work, choose between expansion of staff numbers or regularly putting in extra hours (and we all know how management tends to respond to the former). For still others, it may simply be that they find themselves mired in the day to day busywork that can so easily consume time, at the notable expense of sharpening skills and keeping abreast of the latest trends.
But regardless of when or how that point arrives, the action is clear: we must stop acting like a small time DBA, and shift our methods and thinking to that of an enterprise DBA.
The best way of explaining what I mean is by using, as an illustration, some common stories from the daily life of the DBA. Let me say clearly that the examples I give below are from my own experience of growth as a database professional; I state that to show that I don’t pass judgment on those who have not yet adopted the enterprise mindset.
A server needs to be built from the ground up.
The DBA manually installs and configures software, mostly from memory or perhaps from a simple document they've written (if you're doing the last bit there, congratulations, you're already one step in the right direction). Total human interactive time: 4-8 hours.
The enterprise DBA navigates to the shared folder on their central repository server, runs a series of pre-configured scripts, enters in a few parameters (such as version and edition of SQL), and then moves on to other tasks while the installation and configuration completes in the background. When the process completes, a report of the installation and any final issues that need some manual intervention are e-mailed to the DBA's inbox. Total human interactive time: 20 minutes.
A new employee has joined the company and needs to be granted access across several different applications and servers.
The DBA connects to each server and, using the built-in SSMS GUI, adds the user to each. They then look at a user with similar rights, and script out the permissions before applying them. Total human interactive time: 1-2 hours.
The enterprise DBA lets the requester know the names of the pre-configured domain security groups that the new user must be made a member of. Once this is complete, no further action is required since those groups are members of defined roles in the databases. For a few legacy applications that require individual SQL logins, the application support team handles the request using a delegation framework the DBAs built to allow low-privileged users to complete administration tasks without extended rights. Total human interactive time: none, perhaps 5 minutes explaining this to the support group.
A requirement from the business group is that all the databases be backed up daily and that this must be checked and confirmed every morning.
The DBA looks at each server in the morning to make sure that the previous night's maintenance plan run completed on time and successfully. If problems are discovered they're corrected manually, including taking backups again using the SSMS GUI or re-running the maintenance plan. Total human interactive time: 30 minutes - 2 hours, depending upon how many problems cropped up.
The enterprise DBA sets up a centralized standard job that is pushed down to all their servers by means of the SQL Agent Master Server functionality. Standardized alerts and operators are automatically setup as part of the build process which notify the DBA group if anything fails. In addition, a morning report is generated from a central server which includes a section for servers with databases that have not been backed up within the defined interval. When problems do crop up, they're analyzed carefully for a root cause and proactive steps are taken to prevent recurrence, or the maintenance process is hardened to better handle them automatically. Because of this, actual manual intervention is very rare. Total human interactive time: 15 minutes, with occasional bursts to 30-60 if new problems come up.
The list could certainly go on, but these do a good job of illustrating some of the key differences in the thought process of an enterprise DBA. In fact, they're quite simple, though they can make a great difference to how we go about our daily work.
When we step back, we see that there are four important differences in the way that an enterprise DBA operates.
- An enterprise DBA creates and uses standards when building or maintaining their environment. If we take two servers at random from an enterprise environment, chances are that they will look extremely similar, right down to the naming structure of the accounts running services and the layouts of the folders and physical disk drives.
- An enterprise DBA centralizes operations and keeps things defined in as few places as possible. If the same agent job needs to run on all servers at the same time, why define it the same way in many places? Similarly, try and keep tools in one place and execute them remotely, rather than distribute them across all servers.
- An enterprise DBA automates tasks whenever possible. If a task needs to be done more than once, and is nothing more than a series of steps with inputs and outputs, we use whatever tools are at our disposal to ensure that the minimum (if any) amount of human interaction is required to complete the task.
- Where automation is not feasible, the enterprise DBA delegates work to other groups if the DBA group completing that work does not add value to the business. As part of IT operations there are always manual tasks that need to be done. To take our previous example, perhaps a vendor application requires that a single SQL user be setup for every user that will log into an application. While this can be scripted, it still must be done manually. But I doubt anyone could make the case that this is a high value task, so we take steps to safely grant the group that directly supports the application the ability to handle this themselves.
By adopting these four key strategies, the enterprise DBA is able to accomplish a great deal more work with far less effort, thus leaving them more time to focus on more important ,but less urgent, tasks. In many ways this becomes a virtuous cycle; by freeing up time from mundane yet laborious tasks, the DBA is able to spend more time on their toolset and automation, thereby lessening the manual labor involved in their daily work even more, which in turn leaves more time for automation: Lather, rinse, repeat.
Conversely, by not adopting the enterprise mindset, we find ourselves in a vicious cycle of increasing amounts of urgent, but unimportant, work as the size of our environments increase. As more groups expect the same (or increasing) levels of service on a larger set of servers, our time will become consumed with routine activity and leave less and less time for higher order tasks. If you're spending all day restoring backups in test environments or creating users, you're not going to have the time or the energy to think about a unified maintenance solution.
Unfortunately, there are probably many of you who are solidly in the weeds as you read this, and are wondering "But if I'm already working every minute of my work day doing busy work, how can I find time to do any of this proactive stuff?" If this is the case, don't waste time agonizing over what could be, if only you had thought about this before, or adopting a "woe is me" mindset. Many big time DBAs will gladly tell you how they were in the same boat once, so don't feel that you're somehow inferior in your skills, or that your predicament is unique. It’s human nature to focus on what's immediately in front of us, so it's incredibly easy to get so caught up in day-to-day activities and let your strategic goals wither.
Instead, direct your attention solidly on how you can get gradually get out of the situation you're in. Just like someone who finds themselves buried under a mountain of credit card debt, the key is slow, steady progress. Here are a few specific steps that you can try.
- Figure out what groups of work are consuming the largest amount of your effort. Just like when we troubleshoot performance problems in SQL Server, we want to identify the particular bottlenecks that are keeping us from being efficient. Nobody likes to track their time, but even if you do it for a limited period the resulting data can be incredibly useful. You might be shocked at what it shows you, in terms of just how much time is spent on a particular task every day. When I first did this, I was amazed to find that I spent an average of two to three hours per day doing nothing but restoring databases for development groups. The particular tool you use doesn't matter, as long as you use it consistently and can get useful data out of it. Personally, I settled on Toggl after playing with numerous different options, based on its speed (I simply can't stand apps that aren't responsive) and informative data presentation.
- Identify some specific, small action steps that you can take towards alleviating those pain points. Perhaps you want to read up on SQL Agent Master Server setups, or start learning Powershell. If you find yourself at a loss, a good starting point might be identifying the manual steps involved in whatever work you're finding is your bottleneck, which in turn will help determine a good automation strategy (point number three above). Regardless of what these actions are, write them down somewhere so that you can easily refer to them.
- Try and dedicate some portion of your workday towards the action steps you wrote down. Before you do this it would be wise to get your boss's approval and backing, so that if people complain about how you're not handling their work right away, you can redirect them. A minimum of an hour a day would be ideal. If your boss balks at this idea on the grounds of it delaying work to other groups, be ready to clearly show how this will help give better service over time. For example, you might show that by automating the process of installing and configuring SQL Server, you can reduce the turnaround time for new build requests from two weeks to three days. In most cases this should be enough, but if it's not, and your boss still doesn't want you working on anything but the daily tasks thrown at you, then you're going to have to make a tough decision. Either commit to working on paying down your debt on your own free time (or perhaps during your lunch break), or consider looking for employment elsewhere. Personally speaking, if a company's culture is to focus on churning out work without thinking about building in efficiency over time, that's not a place I would want to work at.
In future articles we'll go through each of the four key elements of the enterprise approach to being a DBA in more detail. We'll look at some of the different tools available for each, both from Microsoft as well as the vast array of free options given out by the amazing SQL Server community.
Before concluding I again want to state that the purpose of this article isn’t to judge or criticize those who choose to their daily work in a manual way. Perhaps you’re simply comfortable doing it that way, and that’s fine. But, if you find yourself with an ever increasing amount of work, then consider thinking hard about whether you want to begin lifting yourself above the daily grind, and get motivated to begin adopting the mindset of the enterprise DBA.