Creating a SQL Server DBA's Library

By: Sadequl Hussain | Comments (12) | Related: More > Database Administration

Problem

In a previous MSSQLTips article I discussed the concept of a DBA Configuration Repository which can be a container for all your SQL Server related metadata. This time, I would like to talk about another best-practice area: the DBA's Library.

Solution

DBAs - like any other information worker - creates, collects, shares and interacts with all different types of information to help him/her do their job. Some of these pieces of information may be needed repeatedly, some of the knowledge gathered over time may be better saved in a secure location. Some of the applications used should perhaps be available for everyone else in the team. This is the concept of a "DBA Library": it's not the library in traditional terms where you have books and journals stacked on your desk; rather it's a centralized and integrated repository of knowledge, information and applications for the DBA. You interact with the library every day in the course of your duties.

DBA Knowledge, Containers and Library

Before we begin, here are some definitions. Let's start with the concept of "DBA Knowledge". I like to call any organization-specific information and applications a database administrator creates, uses or inherits as part of his role as "DBA Knowledge". This is different from the established and traditional meaning of corporate knowledge. This "knowledge" helps the database administrator do his/her day-to-day work. Typically the knowledge is accumulated over a period of time and can come from a number of sources. Think about the following:

Server build guides
Program installers and third-party tools, customized DLLs and SQL Scripts
Audits and governance reports
DBA rosters, schedules and work responsibilities, contact details for after-hours support
Diagrams, flow-charts and topologies from system architectures
Process instructions
E-books, presentations and white-papers
etc...

As you can see, the list can be wide and varied. Some of these information can reside in the DBAs local computer or indeed his head, some can reside within network shares and drives, some will come from other people's computers, while some may be buried within e-mail attachments or version control systems. Corporate SharePoint sites and document management systems may also host other parts of the puzzle. I like to call these distinct sources of knowledge as "Containers".

To keep things simple and make life simpler, a database administrator should adopt one or more standardized containers and make sure all organization-specific DBA knowledge is categorized and collected in those containers. A well maintained and well stocked container or collection of containers is what I call a "DBA Library". Within this library you have all the information and resources you need to do your job. Effectively, this is the DBA's arsenal.

The concept that I present here applies to any database admin shop. You could be the sole DBA in your small company, or you could be the team leader of ten DBAs in a large corporation. You could be a consultant working in one client site today and going to the next tomorrow, or you could be a trainee database admin starting out for a cloud hosting provider. Whatever your background, it would be much easier for you if you have a consolidated library available at hand.

Building the Library

Okay, so now you know why you should keep all your DBA knowledge in one place, how do you go about implementing it? And more importantly, what type of information do you create, collect, or categories?

To answer the first question, use whatever means you have at your disposal to build the library. Most people would use network shares and drives to store their files, typically under different folders. Others can be using a document management or version control system. You could also use a mixture of storage options: your Word documents and Visio diagrams could reside in a version control system while the program installers can come from a network share.

Whatever route you take, make sure you (and others within your team) follow a standard of knowing where to look for the relevant information.

To answer what type of information you should include in the library, here are my suggestions:

Documents

Documents will make the bulk of your library assets. These are files you can attach with an e-mail, print out or edit with a program any time.

Build Guides: Create a server build guide for every database server you roll out. Specify the name of the project, date of installation, stakeholders involved in the project, etc... Use screenshots of every option chosen during the installation, make sure all server configuration options selected have been listed here.

Why would you need it? Well, this document will be helpful if you have to refer to the system's history at any point in future. A performance troubleshooting exercise may come up with a question of why the system was rolled out with a MAXDOP setting of 1. Having a build guide at hand will help you answer that sort of question. Again, the server may be due for replacement or upgrade, and you may want to refer to the original setup configurations so the new system conforms to the same standards.

Build guides can come from other DBAs within the team, from partners, vendors or even customers. Keep all of them. Categories these build guides according to the systems they represent, name them meaningfully like "Accounting_Production_SQL_Server2012_Build_Guide_v1.0.docx".
Policies and Standards: These documents define how you manage your database environment. They are DBA standards you follow for every project and every task. Your organization may already have strict database management rules in place. In that case you don't need to do anything; they will already be available for you. If you are the only DBA (or DBA team leader) in your organization and your company does not have any such standards, work with your managers, senior app owners and team leaders to create them. It's worth it. The standards would not only tell others what you or your team follows, it will also make your work look professional. Here are some examples:

Server Build Standard: What OS should you install in your database box? What version and edition of SQL will be installed by default? Do you use any SOE image? Where can you find it? What collation settings to be used unless otherwise specified? What accounts will be made system admins by default? What will be the layout of the disks? How do you request and provision storage for those disks?
Server Maintenance Standard: What types of maintenance strategies would you use (database shrink, rebuilding the indexes etc.)? When do you run them and how often? Who monitors them? What's the standard for naming them? How often do you shrink the database transaction logs? How do you monitor the CPU, RAM and disk space usage?
Backup and Restore Policy: How do you backup your database servers? What's the RTO (Recovery Time Objective) and RPO (Recovery Point Objective) for your databases? Do you use any third party solution? What's the common, vanilla backup configuration for your typical server? How long backups should be available before moved to offline media? What's the restore policy? How do you initiate a restore activity? Is it restored on a separate server or on the same server? Do you have access to the off-site storage facility? If not who do you contact? What's the policy when you have to provide backups to someone outside your company?
Database Retirement Policy: How do you start a DB server's retirement process? What pre-requisites need to be satisfied (final backup, application shutdown) and what questions need to be answered?
Database Server Patching Standard: What's the process of patching DB servers? How often do you conduct a review of service packs installed? How often do you patch them? Which servers you leave alone? Which servers are patched first? How do you test the patching? Who do you contact for system outage? How do you rollback?
Server Health Check Policy: How do you check the health of a server? What areas do you look at first? How do you measure performance? How often do you measure performance, create baselines and make comparisons? What metrics do you include in your performance monitoring? How do you check capacity?
Coding Standards: How do the developers in your team write code? What templates do they follow? How do they create in-line documentation and how do they use version control? What basic SQL testing should they perform during development?
Database Release Management Policy: How do you release objects into a production environment? Your organization may have existing rules and procedures for change and deployment management, but within this framework how do your DBAs rollout objects during production deployments? What's the QA policy for code checking? What channels are in place to raise deployment requests and what's the authorization process?

Work Instructions and How-tos: These are like build guides. These documents provide detailed step-by-step instructions of installing, configuring, maintaining or decommissioning a component in your database infrastructure. In most cases you, your predecessor or other DBAs in the team would be creating these documents. If you are writing the work instructions, make sure they are as detailed as possible. Include screenshots, flow diagrams, use cases and exception rules. If you have inherited such documents, check for their accuracy, see if you need to add or modify something.

Work instructions can cover areas like business processes where more than one functional team is involved: like a month-end billing cycle. You or your team could be responsible for performing one or more functions within the process (refresh the UAT environment for data quality testing). The work instruction document will then cover topics like timing of the operation, who do the DBAs get signal from, what server would need to be refreshed, what scripts would need to be run and how the next-in-line person is notified, etc...

The longer you stay with a company, the more you get familiarized with these business processes. The more you document them, the easier it becomes for someone else in the team to follow them.

Sometimes the work instructions can come from a vendor or third-party. An example can be the uploading of multiple flat files to a vendor's FTP site. They could be providing you with custom instructions for accessing their site and uploading the data. Make sure you have collected all such custom instructions. If it is in someone's head, get that documented. It's much better to have a one-page instruction than to have nothing.
Workarounds and Troubleshooting: These documents are typically created when a DBA has spent considerable amount of time in troubleshooting an issue and found a solution. The response is to create the document so the knowledge is not lost. There may be a wide variety in writing style too. Some workaround documents will have two screenshots and few words, others will have multiple pages with detailed problem descriptions, reference to KB articles or other Internet links. If you are creating these documents, make sure they are as detailed as possible. Follow a certain style in dividing your article (problem statement, background, workaround method, testing the functionality). If you have inherited a cache of these documents from other DBAs before you, check their validity and relevance. If relevant, include them in the document container. If your team already has existing processes for writing up workarounds and troubleshooting guides, follow those standards and make sure your documents are saved in appropriate locations.
Presentations: These are short and compact sources of information highlighting one particular topic. You could be creating PowerPoint documents for your management team for a business case; a vendor could be sending you a slideshow highlighting the features of the latest version of their product. Your team's service level performance could be highlighted in one of these documents.

Applications

These are software packages DBAs install in local machines and servers. Broadly, the DBA's library should include the following:

Database Software Installers: keep all your SQL Server installers in one place. These could be categorized under different versions (2005, 2008, 2008 R2, 2012 and so on), editions (Standard, Enterprise or Developer) or bit level (x64 and x86). Keeping a copy of the ISO image or uncompressed installers in a storage device is more reliable than depending on DVD media. Make sure you keep all the relevant Service Packs, Cumulative Updates and Hotfixes in the same location.

For larger organizations, there could be a dedicated software library that hosts these installers. For smaller companies, you can use a network folder.
Third-party Tools: These are software applications DBAs use to better manage their servers. There is a whole industry out there that produces tools that go beyond what's available from Management Studio. Some of these apps are commercially available, others are free and open source. Here are some examples:

A tool to analyze free disk space
A tool to migrate logins, jobs and permissions
A tool that can make database schema and data comparisons and generate reconciliation scripts
A tool that reports on system performance, service availability, possible bottlenecks
A tool that can scramble sensitive data
and so on...

Some of these third-party tools will be run from individual computers, so if you have more than one DBA in the team, it would make sense to keep the installers ready in another network share. That way new DBAs can get the tools from this location.

If the tools are to be run from a central location then it would make sense to create a DBA monitoring box. This will be a purely DBA accessible central server that will host one or more of the apps.

With commercial applications, be careful about licensing restrictions for server or desktop installations. Your company may have paid for a certain number of desktops licenses. There could be restrictions on electronic storage of software media.

Virtual Server Images: These images could be used as SOE (Standard Operating Environment) for certain systems like dev or test boxes for developers. Once again, it would make sense to keep the images in one place and classify them according to application, platform, version, purpose etc.

Scripts

Like software applications, scripts can be used for a number of reasons. You can have a whole gamut of PowerShell command files, T-SQL scripts and Windows batch files lying around in the network. They can be used for rolling out new servers, copying user data, capturing performance baselines, generate test data or automate one or more processes. As long as they are related to a DBA activity, review them, consolidate them in one place.

Where do you keep these files? Use a source code version control System like Team Foundation Server (TFS) or Subversion. That way, if the files are changed, they will at least have to be checked-out and checked-in and have an audit trail attached.

Learning & Development

In their own time and when searching for solutions, DBAs often stumble upon excellent resources on the Internet or in the network. When they leave the organization, they often take it with them to the next company. Typically these resources are documents created by other people or other companies and talk about their own experience in the field. Consolidate these resources if you find them lying around, keep them in one place. These may form the basis of your best practices, guidelines and standards documents. Here are some categories for these resources:

Presentations: PowerPoint presentations
E-Books: Typically large PDF documents with hundreds of pages
Knowledge-base Articles, Whitepapers and Best Practices: Small to medium sized documents about any one particular topic (e.g. best practices for running SQL Server in virtual machines). You would want to print them out and read them on your way back home
Videos: Webinars and online presentations
User Guides, Quick Start Guides, Administrator's Guide and A-Z References: Comes with software packages. Often downloaded separately.
Links: Consolidate and save all bookmark links in one place

As with storing and using software packages, be careful about any intellectual property restrictions attached with these resources. A document could be freely downloadable from a website, but you may not have the rights to send it as an e-mail attachment (you would probably be breaching distribution restrictions). Similarly a training manual provided to you as a personal copy by the training provider would not qualify for electronic storage and distribution.

Management and Governance

This container in your library will host management related information rather than technology related ones. Some of the files may need to be password protected too. Here is what qualifies to go in this container:

DBA On-call Roster: Who is going to be on-call next week or next month?
DBA Contact Information: This would be useful if a vendor / system integrator is managing your database infrastructure. If it is an off-site operations center, what's their contact number? What's the after-hours support call procedure? Who do they liaise with from your side?
Vendor Contact Information: If your team is supported by a vendor, who is the account manager for your company? How do you contact her?
DBA Training and Initiation Guidelines: How do you start with a fresh face in the team? What does he do the first week, the first month and the first six months? Which systems is he first given access to? What software would a DBA workstation need to be installed?
DBA Interview Questions: A pool of questions you would ask new DBAs to test their knowledge.
DBA Task List and Availability: What are the daily, weekly or monthly tasks for the team? Who is working on what project or task at the moment? Are they booked for a weekend rollout in coming weekends? What's the availability of DBAs over the next three months (leave and training etc.)?
Critical Systems and SLAs: A list of top tier systems and their associated Service Level Agreements. Detailed procedures for incident and problem management. This could also be available from a corporate document repository.
Reports on Service Level Performance: How many P1 calls were resolved last month within agreed SLAs? How many changes were successful? How many calls are still open? What's the trend graph looking like?

Journaling

What are your DBAs doing at the moment? What requirements did you agree to in an e-mail with the client six months ago? How are you keeping track of daily status updates from a DBA resource currently assigned to project XYZ?

There is so much that happens in an operations DBA center that it's easy to forget what happened last week and what tasks are still pending. To keep of track of everything that happening around, there are two types of files / documents you can put under the journaling container.

DBA Work Logs: These are daily or weekly summary reports from one or more DBAs. Each DBA will need to fill out a DBA work log every day or every week. This will show what work was done that day or that week. Bear in mind that DBAs will also probably fill-out regular timesheets. There is a difference here though. Timesheets would typically show what time was spent for a particular project whereas DBA work logs do not show that. It's more technical in nature: it does not say how long the DBA spent for a particular task, rather what tasks were actually performed in that period. A typical DBA log may look like the following:

Rolling out a new SQL Server 2012 production system for client XYZ
Capturing performance counter logs for data warehouse reporting server ABC and analyzing the data. Provided output and recommendation to client
Unlocking three user accounts and creating new user accounts

If you think about it, a DBA work log does tell you what your team is doing, who is doing what, how work is progressing in any project and how workload is distributed in the team. If you are the sole DBA with your organization or department, a DBA work log can help you tell your story to your managers. This shows what you have been up to.

Now filling out a DBA work log at the end of a day or on a Friday afternoon could especially seem to be a tiresome job, and it may be tempting to finish it with only one line saying "database maintenance", but its value is realized when questions are raised about individual or team performance or work in the pipeline. It can help in measuring progress of projects and distributing workloads. Plus it can help in a possible rollback scenario where you need to understand exactly when something was performed.

Archived E-mails: The are E-mails residing in people's computers or in mail servers. Sometimes they contain valuable insight into a system's history, agreement between stakeholders, scope of work or audit logs of actions taken. DBAs receive support requests through service management tools as well as direct e-mails and it would make sense to save their contents as well. Your company could already be archiving e-mails in compliance with regulatory requirements. In such cases you don't have to do anything. In other cases there could be a quota that prevents you from having a mailbox bigger than a pre-defined size. If the latter is true, you can archive your DBA e-mails in .PST files and save them in a secure location.

Once again, check your company policy about user-initiated electronic storage of e-mails. Make sure you are on the right side of the policies.

Audits and Baselines

DBAs often perform a variety of audits across their database systems. Usually these are in response to ad-hoc management queries, periodic reporting requirements or for legal compliance. The scale and nature of audits can also vary. There may be security risk assessment audits, licensing audits or environment consolidation audits to name just a few. As a DBA, you should conduct all your audits in a consistent manner and collate your findings using standard document templates. The audits should be classified according to their type and the cycle or period when they were conducted.

Just like audits, DBAs could be creating performance measurement baselines. These can include storage capacity reports, query execution times, disk I/O performance etc. All the performance baselines should be saved under relevant categories along with any subsequent readings for comparison.

If you have a policy document for creating audits and performance reports, it should point to this location in the DBA library.

Architecture

This container will host everything related to various system architectures. The artifacts within this container can come from both the design phase and from existing solutions. Here are some examples of architecture components:

Diagrams showing systems and their functions and their inter-relationships (web server, app server, DB server in a deployed farm)
Process flow-charts (high level diagram showing the decision points and actions when a new web outlet is added to the e-commerce site)
Proposed solutions from third-party vendors (automated antivirus rollout for desktop computers in the company - the overall design document will have a database section)
Application specification: design documents to show what tables and stored procedures are being changed for the new Unicode-enabled feature

So you see. You may not be directly responsible for designing each and every application architecture from the database side, but you will most probably be consulted or asked for review or at least be informed. In my opinion, if you have been provided with architecture components related to your databases, save them in one place.

The architecture repository will also hold deployment plans, checklists and rollback mechanisms. The approved version of a deployment plan may very well be embedded within a change management application; your library should have a copy too.

Housekeeping Rules

Now we have seen what we can include in the library, it's time to understand how we can keep it tidy. You need to be mindful of some basic rules when creating your DBA library.

Keep the Latest Information

In your DBA Library, always look for sources of information that are obsolete, old and have no practical use for present or future. You would come across these types of information artifacts when you have been in the organization for a few years and have seen systems come and go. You may also inherit a large cache of documents from your predecessor. Inevitably as databases and applications are migrated to newer versions or platforms, some of these documentation will become irrelevant. Perhaps the data flow diagrams no longer represent the current system, table structure may have changed radically since version 1.0 and maybe a new backup system was installed six months ago has rendered the customized DR mechanism obsolete.

So what do you do with these sources of information? Delete them. After all, what's the point of keeping a document from 2003 that tells you what to do after a SQL Server 2000 has been installed? If you have the slightest doubt about any of the files, put them in an archive container.

Don't Duplicate

Keep only one version of the truth. If your development team is saving their design documents in TFS, ensure the DBA team has access to the relevant location. There is no point saving another copy of the same documents in a network share. In this case that particular location in TFS becomes part of your DBA library.

Use Version Control

This is particularly important for scripts and database components. Knowing what version is the latest will save you a lot of time and headaches later. The same goes for design documents. If multiple people are working on the same design document, save the progressive versions in one place. When finalized, delete the older versions.

Create an Archive Container

If you are in doubt about a particular information source and its validity but don't want to delete it straightaway, put it in an archived section.

Use Templates

Use a document template whenever possible. This is for standardizing your information resources. Your company or team may use certain templates for documentation. See if your documents are better represented by adopting those templates. Use separate templates for separate classes of artifacts. To give an example, your deployment plans should follow one particular style while your build guides should follow another. Work instructions and troubleshooting guides may use their own template while system architecture documents may use something different.

Use Iterative Approach

Like the DBA Configuration Repository I discussed before, building the DBA library will also be an iterative process. With so much information available all around, managing it may seem like a full time job. In practical cases, the bulk of the initial phase will be spent in identifying the containers of information and deciding on which ones to use. Once a skeletal frame is in place, the rest can be done with small iterations. Ultimately as everyone becomes aware of the library, it becomes a team effort where everyone follows a standard approach.

Next Steps

Make a note of all DBA resources, artifacts and documents available in your organization and accessible to you
Consult with your colleagues, peers, managers to find out which ones are relevant in your current database environment
Cross check with your existing systems and see which ones are needed
Start categorizing the resources and move/save/copy in a central location
As you work with your resources, start designing the structure of your own DBA library
Create documentations as necessary
Implement the library with one or more containers
Move your resources in the new containers
Start version controlling, reviewing and updating the resources as necessary.

About the author

Sadequl Hussain has been working with SQL Server since version 6.5 and his life as a DBA has seen him managing mission critical systems.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Friday, January 17, 2014 - 4:57:04 PM - Golam	Back To Top (28129)
Very well explained - keep posting

Thursday, January 9, 2014 - 10:49:23 AM - Vasyl Shyshka	Back To Top (28004)
Thank. Very useful in this moment for me. Especially with continuing reading from 1 part.

Wednesday, January 8, 2014 - 11:22:12 PM - Gopalakrishnan	Back To Top (27999)
Excellent and Chanceless. You are covered everything from A-Z in this two articles 1. DBA Configuration Repository & 2. DBA Library. Hats Off to you Sadequl.

Wednesday, January 8, 2014 - 5:06:01 PM - Russ Thomas (@SQLJudo)	Back To Top (27995)
Excellent article Sadequl. For the reasons you outline a couple folks and I are working on a codeplex project called the DBA Toolbox http://dbatoolbox.codeplex.com. You bring up some interesting things I hadn't considered for that project - like versioning. A lot of points to consider adding as a feature set. Open source project if anyone else interested wants to collaborate.