Creating a SQL Server DBA's Library
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.
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
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 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.
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
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".
These documents define how you manage
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
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:
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
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?
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
- Backup and Restore Policy:
How do you backup your
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?
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?
Server Patching Standard: What's the process of patching DB
servers? How often do you conduct a review of service
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?
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?
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
the authorization process?
- Work Instructions and How-tos: These are like build
documents provide detailed step-by-step instructions of
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
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
- 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.
These are software packages DBAs install in local machines and servers. Broadly, the DBA's library should include the following:
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
For larger organizations, there could be a dedicated software library that hosts these installers. For smaller companies, you can use a network folder.
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.
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:
Typically large PDF documents with hundreds of
Articles, Whitepapers and Best Practices:
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
Webinars and online presentations
Guides, Quick Start Guides, Administrator's Guide and A-Z References:
Comes with software packages. Often downloaded
- 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
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
Systems and SLAs: A list of top tier systems and their
Service Level Agreements. Detailed procedures for incident and problem
management. This could also be available from a corporate document
- 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?
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.
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.
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.
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 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.
- 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
View all my tips