Click here to monitor SSC
  • Av rating:
  • Total votes: 33
  • Total comments: 5
Joshua Feierman

On Adopting the Mindset of an Enterprise DBA

19 May 2014

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.

▬►  On Adopting the Mindset of an Enterprise DBA
  The Mindset of the Enterprise DBA: Creating and Applying Standards to Our Work
  The Mindset of the Enterprise DBA: 8 Ways To Centralize Your Work
  The Mindset of the Enterprise DBA: Harnessing the Power of Automation
  "The Mindset of the Enterprise DBA: Delegating Work

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.

  1. 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.
  2. 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.
  3. 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.
  4. 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.

  1. 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.
  2. 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.
  3. 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.

Joshua Feierman

Author profile:

After completing a degree in neuro-psychology in college, Josh started his career in IT almost by accident. In what was originally a temporary job creating reports for clients of a financial company, he decided we would rather write some VBA macro code than repeatedly push the same buttons on a keyboard . After working in low level development and support roles, he transitioned to the role of a full time SQL Server DBA, supporting both production and development systems, and has never looked back. He currently works as a senior DBA for Gateway Ticketing Systems advising customers on maximizing SQL Server performance and availability. He also recently co-founded the independent software startup Do It Simply Software. He loves learning about what makes SQL Server tick, and how to make it go faster. For more information, follow him at

Search for other articles by Joshua Feierman

Rate this article:   Avg rating: from a total of 33 votes.





Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.

Subject: Great advice
Posted by: Anonymous (not signed in)
Posted on: Friday, June 6, 2014 at 5:21 AM
Message: Good solid advice, which I'd recommend to anyone. And I'd add that's it's amazing how taking small steps in the right direction can help. So if you can occasionally spend an extra ten minutes scripting something instead of doing it manually, it's worth it as it all adds up!

Subject: Great advice.
Posted by: Anonymous (not signed in)
Posted on: Friday, June 6, 2014 at 8:00 AM
Message: One thing I see in some small companies that grew up into large companies is the guy who's been there for 20 years and still does things manually. He fears automation because he thinks that will automate himself out of a job.

I work with a guy like that, management loves him and I still have to go back and clean up after his installs, because he forgets things and servers crash because of mis-configuration.

They still spend a lot o time doing busywork, put in 60 hours every week and complain how busy they are. Some have even gone so far as to discourage the new guys away from automation for fear of being shown up and replaced by the younger and less expensive junior employee.

I'd love to see an article on how to handle the lifer who makes more work for himself and everyone else.

Subject: Should be Second Nature
Posted by: Anonymous (not signed in)
Posted on: Friday, June 6, 2014 at 8:39 AM
Message: Well....doesn't EVERY IT professional try to automate as much as possible all the time. I've been in the business for 17 years and NEVER had enough resources to meet deadlines or budgets. Automation was the only way to ever even come close to meeting any requirements. It still didn't leaders want all off-the-shelf components run by power users. Developers and admins are a dying breed.

Subject: On Adopting the Mindset of an Enterprise DBA
Posted by: BISL_KENNY (view profile)
Posted on: Wednesday, June 11, 2014 at 4:02 AM
Message: Thanks Josh for your write up. It may sound critical to those who detest proactivity but i think having an enterprise mindset as a dba is the key to productivity and professionalism which in long term will help to grow one career.
As an upcoming dba, i am challenged by your suggestions and therefore very keen to see follow ups implemetation on these enterprise / proactivity ideas.

Once again, well done for this nice stuff.

Subject: Automate yourself out of a job
Posted by: saleyoun (view profile)
Posted on: Sunday, September 7, 2014 at 4:25 PM
Message: This is a very good article Josh. I'm a strong believer of automation and writing my own scripts to monitor the environments I manage. I always keep these scripts and solutions with me wherever I end up working.

I have worked with DBAs who do everything manually and off the cuff. Executing via the GUI or writing somewhat the same queries over and over to configure and monitor their environments –wasting time. I have also known some DBAs that will not be able to function without a specific third party solution to monitor their environments. Currently, I’m working in a shop that encourages automation and centralization of processes to monitor our database environments.

For those who believe that automation processes have gotten you out of your job, it was time to go anyways.

Simple-Talk Database Delivery

Patterns & Practices Library

Visit our patterns and practices library to learn more about database lifecycle management.

Find out how to automate the process of building, testing and deploying your database changes to reduce risk and make rapid releases possible.

Get started

Phil Factor
Routine SQL DML Testing for the Unenthusiastic Tester

There are more exciting things in life than unit testing SQL Statements, checking the results, timings, and... Read more...

 View the blog

Top Rated

Jodie Beay and the Production Database Drift
 You make an example database, like NorthWind or WidgetDev in order to test out your deployment system... Read more...

SQL Server Data Tools (SSDT) and Database References
 SQL Server Data Tools (SSDT) provides, via the DacPac, interesting support for verifying not only... Read more...

Writing Build vNext tasks for Visual Studio Online
 Hosted TFS, now called Visual Studio Online (VSO), has a new way of writing build processes called... Read more...

Microsoft and Database Lifecycle Management (DLM): The DacPac
 The Data-Tier Application Package (DacPac), together with the Data-Tier Application Framework (DacFx),... Read more...

A Start with Automating Database Configuration Management
 For a number of reasons, it pays to have the up-to-date source of all the databases and servers that... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

Temporary Tables in SQL Server
 Temporary tables are used by every DB developer, but they're not likely to be too adventurous with... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.