Click here to monitor SSC
  • Av rating:
  • Total votes: 18
  • Total comments: 6
Joshua Feierman

The Mindset of the Enterprise DBA: Creating and Applying Standards to Our Work

25 June 2014

Although many professions, such as pilots, surgeons and IT administrators, require judgement and skill, they also require the ability to do many repeated standard procedures in a consistent and methodical manner. These procedures leave little room for creativity since they must be done right, and in the right order. For DBAs, standardization involves providing and following checklists, notes and instructions so that the results are predictable, correct  and easy to maintain.

   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

Previously I wrote about the idea of adopting the mindset of an enterprise DBA, and how this might help us in our daily work as database professionals. I laid out a set of four core practices or skills that chart our course towards fully embracing this way of thinking: Standardization, Centralization, Automation, and Delegation.

In this and future articles, we'll dive into each in more detail. This first article looks at the skills and patterns that comprise the practice of standardization.

Standardization

If you have only enough time to work on developing just one of the four skills, focus on this one, and focus on it first. It’s difficult to say this strongly enough or repeat it sufficiently.

Why might I say this? Standardization is all about ensuring that we do tasks consistently. It lays the foundation for the way we go about the rest of our work. If we don't do our daily work methodically, and don't have that work clearly defined, how can we write a script to do it for us (automation), or teach someone else to do it instead of us (delegation)?

Here’s a simple example: let’s say that there’s a series of steps that need to be done whenever you set up a new instance of SQL Server. These steps would include such things as acquiring a license, installing SQL itself, and running some post-installation scripts. If we don’t list these steps at the time we tackle the task (a checklist will do, there’s no need for a novel) and then subsequently follow these notes, we risk forgetting a step, human memory being as transient as it is. Then, once we discover our mistake (if we’re fortunate enough to), we must go back and correct it after the fact, which is always more expensive than doing it the first time around. We also can document certain kinds of known problems (“Hey, anyone remember how we got Kerberos working that one time?”), which saves us the pain and time of looking up obscure answers buried in internet forums.

Unfortunately, standardizing things is one of the least enjoyable of the steps. Why? Probably because it involves a lot of documentation, something which no one in IT enjoys. Heck, I love writing as much as anything, but sitting down and trying to pound out a good set of instructions gives me the willies. The problem is, without clear and concise documentation to refer to, any attempt at standardization is going to fail, and fail miserably. There's simply no way that we could consistently do our work without some kind of written guideline. If you doubt the truth of this, just watch the pre-flight rituals of airline captains, or the set of steps a doctor must go through before entering the surgical suite. In both cases (and many more), you'll see extensive use of checklists and documentation, because it's been shown repeatedly how this simple act greatly reduces the chance of errors of omission.

The following list gives some examples of what I’d add to the general bucket I’ll refer to as “standards”:

  • Checklists – usually good for simple step-wise operations.
  • Rules – descriptions of standards or guidelines about how things should / must be done.
  • Knowledge bases (KBs) – a store of knowledge gained through experience, good for things like documenting known problems and solutions.

Although it is likely that some tasks need to be documented in more detail than others, it’s difficult to argue that there are parts of the role that need not be documented at all. So, it's probably best to err on the side of doing too much. After all, is there a downside to documenting something?

Take something as simple as restoring a database. Sure, this seems easy to most DBAs, who do it on a daily basis. But what happens when you’re not around? If you’ve got a checklist with all the steps listed, you’ve at least a chance of not getting a phone call and seeing just how bad the internet connection is from your cruise ship. You can also leverage this checklist in training your junior staffers.

The examples I'll give below are by no means an exhaustive list. They're merely something to get your brain warmed up, so that you might have a good chance of obtaining reasonably wide coverage. By all means, give suggestions for additional things in the comments section, so that we can improve as we go.

To keep this article relatively brief, I'm going to focus on two main areas: server setup and standard operating procedures.

Server Setups / Installations

We need to standardize the setup of all the systems we own. By doing this in a consistent manner, not only does it save us work down the road, but it also makes it easier to do our current work. Let's take an easy example: the name of the Active Directory account which runs the SQL Server service. If we always name this in a consistent way, place them in the same OU in Active Directory, and put them in the same security groups, we will always know where to find them and can leverage technologies such as delegation (i.e. the domain admins can let us do things ourselves) or group policy (which I'll argue later is a form of centralization). It's also something that we (hopefully) don't have to do that often, so it’s probably not a good idea to trust our memory.

Here's some specific examples of things we might standardize about our servers and installations:

  • Naming Standards
    • Account names - Make them something meaningful as to what they are for.
      • SQL Server service account
      • SQL Agent service account
      • Application service accounts that connect to the database (may be outside our control, but worth trying)
    • Server and Instance names - In these cases, we should include things like environment identifiers (i.e. Dev, Test, QA, Prod) and versions (i.e. 2K5, 2K8, 2K12).
  • Layout, size, and labeling of disk drives:
    • Do we split up the volumes for the system databases from TEMPDB and the user databases?
    • Do we have dedicated drives for backups?
    • Do we use mount points or drive letters (or some combination)?
  • OS versions / patch levels:
    • What OS (i.e. Windows 2003, 2008, 2012, etc) do we install and is it related to what version of SQL Server we install (i.e. do we always match SQL 2012 with Windows Server 2012)?
    • Is there a standard service pack or set of patches we always install?
  • SQL Server versions / patch levels:
    • What versions of SQL do we allow / support?
    • What patch levels should they be at (i.e. "SQL 2008 is always patched to SP3 CU2")?
  • Post / Pre Installation Steps - are there actions that we take on all servers either before or after the installation, such as:
    • Restricting accounts from logging in locally or interactively.
    • Further reducing the security of standard roles like PUBLIC on SQL (I know this is sometimes frowned upon, but some like to do it), a.k.a "hardening".
    • Adding additional TEMPDB data files based on some formula.
    • Setup of standard agent jobs for maintenance or other purposes.
    • Adding the newly created server to repositories or third party tools such as SQL Monitor.
    • Installation of standard third party tools or software.

Procedures (a.k.a. SOP - Standard Operating Procedures)

We must carry out our daily work in a consistent manner. By doing so, it not only makes our jobs easier (do you really want to try and remember how to restart the TPS report at 3 in the morning after a night at the bar?), but also ensures good service to our customers.

Here are some areas to think about when we write our SOP:

  • What are some common questions we get and what are their answers?
  • What are the steps to do common tasks, such as:
    • Granting access to systems for users.
      • Do we use domain security groups? If so, which ones for what roles?
      • Are SQL accounts allowed, or must all access be via Windows credentials?
      • Are there any restrictions on what levels of privilege we give without some kind of senior approval?
    • Moving backups from production to test systems.
      • Is there any kind of approval required, and if so, by whom?
      • Must any sensitive data be scrubbed or otherwise removed?
      • Are there any scripts or other actions that must be done after the database is restored, either to any or specific application's databases?
        • Do we need to grant developers access?
        • Do we need to re-link orphaned SQL accounts?
        • Do we need to change any static configurations to point to development systems (don't miss this one!)?
    • Releasing new code into the production systems.
      • Is someone's approval or some kind of change management process required?
      • Who handles the release activities?
    • Responding to incidents (system down, slow, degraded)
      • Are there standard metrics to capture or baselines to compare against?
      • Are there any common problems for which known solutions are documented?
      • Is there an incident management process that needs to be triggered if certain things happen (i.e. we need to notify management if the system goes down between 8AM and 5PM EST)?
  • Are there any SLAs (Service Level Agreements) in place for things like:
    • Incident response times (i.e. "all Severity 1 incidents must be resolved within four hours" - and make sure you define what "Severity 1" means).
    • Off hours support or response times (and what defines "normal hours").
    • Completion of standard activities (i.e. when a request is received to provide a backup for testing, it must be done by the end of the next business day).
    • Standard down times / maintenance windows.
    • Backup and recovery, such as Recovery Time Objectives or levels of acceptable amounts of data loss.
  • Is there contact information for teams which we commonly interface with, such as infrastructure or system administrators, domain admins, or SAN admins? Do we need to follow any specific procedures when asking them to do something for us?

Phew! That's admittedly quite a list, and by no means complete. If you're feeling a little overwhelmed at the thought of documenting all this, don't worry, it's a completely natural response. Attempting to tackle all of this at once is a monstrous and intimidating task, which is why I wouldn't recommend trying to do that, especially while juggling your existing duties.

Instead, adopt the gradual "pay-down" approach that I've talked about before (and trust me, I will again). A good start might be to simply document things as you go, since it's a good way to capture your workload in the moment and it shouldn't be too invasive. For example, the next time you do a server installation, take note of the steps you do and liberally take screenshots as you go. You probably won't get it completely the first time, but as you keep doing it your documentation will get more accurate. And if you find yourself with some spare time (yes, a highly unlikely event I know) you can always go back and step through everything as well.

Keeping a "work journal" can also be useful, where you simply record what you do and how you did it. I like to use a text file for this, since I can easily paste in scripts or commands I use and search for them later. As a bonus, the next time Joe from accounting asks for "another copy of that report you sent me last year", you can just search your notes and find the query. I know it's saved me quite a lot of work on repeated occasions.

My next point, while hopefully obvious, bears repeating due to its importance: once you have your standards, make sure that you follow them. The more consistently you do this, the more value they will bring to your work. If you don't follow them, they're largely meaningless and you may as well not have them at all. This isn't to say they can't change (they will), or that there won't be exceptions (which you'll document, right?), but just that you should give as best an effort as possible to keep true to them. Consider trying to bring existing pieces into compliance as you touch them. For example, if you have to add some drives to an old server, at least make them follow your standards. It's likely not feasible to go back and correct everything you have, but fixing over time is a better option than simply allowing things to stay as they are.

A final note of caution: these documents, once written, need to be carefully guarded. I don’t mean in the sense of keeping them behind steel doors guarded by vicious dogs and laser security systems, but be prudent about who can access them. Often times we inadvertently put sensitive information in these, such as what our standard ‘sa’ password might be. In the wrong hands this could cause a lot of damage, so make sure they are reasonably secured (perhaps password protect them or put them on a locked down file share / web site).

It may be a slow process, but defining and following standards in our work as DBAs is as critical a piece of our journey as any. These procedures will become your requirements for automation, and your guides for those to whom you delegate your work. Capture them, keep them updated, and use them every day. Do these three things and you will be well on your way towards adopting the mindset of an 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, as well as developing independent software solutions to help IT users do their work more efficiently. He loves learning about what makes SQL Server tick, and how to make it go faster.

Search for other articles by Joshua Feierman

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


Poor

OK

Good

Great

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: ITIL anybody?
Posted by: jrbarnett (view profile)
Posted on: Thursday, June 26, 2014 at 2:17 AM
Message: For any organisation that has adopted the ITIL methodology, this would be standard fare as part of a DBA service. For those who don't know what it is, I would encourage you to look at http://www.itil-officialsite.com

John

Subject: Excellent article - standardization makes difference
Posted by: Rupertsland (not signed in)
Posted on: Monday, July 7, 2014 at 9:33 AM
Message: I write this as an biologist turned database architect/administrator who works for a medium-sized environmental consulting company.

After training myself in using SQL Server, I was able to install and operated the software on our server. The off-site network guys were on standby during the install process to help out with the network-related issues and entering user accounts.
As a scientist, I know that we must document the methods used during experimentation, so that the experiment can be repeated in the future by others in a consistent way. Problems encountered are also documented. I took the same approach during installation of SQL Server - every step was written down, and screenshots of each step was recorded. About 2 years later, when our GIS server was crashing and we had to switch to a new machine, I used my installation documentation to help me install SQL Server again. I knew in advance (using the notes) that the network guys had to intervene to enter the account info. The result was that SQL Server got installed without any problems, and I could then move all our databases from verified backups onto the new server. Standardization does make a difference – it saves time, cost, and headaches in the long run.

What also scares people away from standardization is the time needed to document the steps. I keep hearing the excuse that no one has time. It’s like building a new subway metro for the city – you build it once, and you maintain it thereafter. Sure, it takes time, but once you have that initial documentation, you can update it later, where necessary, without having to re-write it or start from scratch.

Standardization reminds me of quality control, quality assurance, and ISO 9000/14000. Procedures must be documented properly and audited (verified). Instructions must be clear. We need to look beyond the work involved and look at the overall benefits.

Thanks Joshua for your excellent article.

Subject: Excellent
Posted by: Martha A.N. Solarte (not signed in)
Posted on: Thursday, July 10, 2014 at 8:38 AM
Message: ISO standards and ITL guide IT personnel to do this, not just DBAs. The lesson is to document and use methodologies to apply repeatable processes. Sometimes management does not see the benefit of these, but we, as professionals need to always adhere by these methods. This is an excellent write up. I will be sure to keep it in my vault of useful reference material.
Thank you Joshua.

Subject: Yup
Posted by: Kyle (not signed in)
Posted on: Thursday, July 10, 2014 at 9:45 AM
Message: My anal-ness around doing the same things in the same way has saved my ass so many times. :)

Subject: AWESOME
Posted by: Rudy Komacsar (not signed in)
Posted on: Saturday, July 12, 2014 at 8:52 AM
Message: at our enterprise we have 95% of this in place already. It is truly refreshing to find this all in one place.

Subject: Checklist Manifesto
Posted by: YaHozna (view profile)
Posted on: Monday, September 8, 2014 at 2:28 AM
Message: For anyone still doubting the merits of a checklist, regardless of the context, I recommend reading The Checklist Manifesto by Atul Gawande.

http://atulgawande.com/book/the-checklist-manifesto/

 
Simple-Talk Database Delivery

DLM
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
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 you're... Read more...

 View the blog

Top Rated

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...

Archiving Hierarchical, Deleted Transactions Using XML
 When you delete a business transaction from the database, there are times when you might want to keep a... Read more...

The Mindset of the Enterprise DBA: Harnessing the Power of Automation
 After you have done the necessary groundwork of standardizing and centralizing your database... Read more...

Rollback and Recovery Troubleshooting; Challenges and Strategies
 What happens if your database deployment goes awry? Do you restore from a backup or snapshot and lose... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... 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...

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...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... 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.