Chapter 24. What does it mean to be a DBA?
If you ask ten database administrators (DBAs) what their job roles are, you’ll get ten different answers. For example, one organization may need a DBA to manage the day-to-day administration of SQL Server instances, whereas another organization may want a DBA to write both Transact-SQL (T-SQL) and C# code. Another organization might want a DBA who specializes in SQL Server Integration Services (SSIS) packages, whereas another organization may want a DBA who specializes in database design.
The job of DBA encompasses many different roles (and often job titles), and these vary widely from one organization to the next. In short, there is no standard job description for a DBA. This is partly because the job title doesn’t have a long history (relational databases were first used in the 1970s, and Microsoft SQL Server Version 4.21 was released in 1993) and partly because the DBA role is quickly evolving as new technologies are released.
Because there is no commonly-accepted definition of a DBA, the goal of this chapter is to describe the types of tasks and roles that DBAs commonly perform, with the aim of explaining to those new to the profession what DBAs do day-in and day-out.
The first section of this chapter describes many of the tasks DBAs commonly perform. The second section classifies these tasks into specialty roles, as you will rarely find a DBA who performs all of these tasks all the time. Throughout this chapter, I try to answer the question: “What does it mean to be a DBA?”
Typical DBA tasks: A to Z
DBAs perform so many different tasks that it is hard to categorize them all. The alphabetized list that follows outlines the common DBA tasks. This list is by no means comprehensive, and some of the tasks overlap.
Application Integration
Most organizations use some combination of in-house and third-party applications; and few of these applications work in isolation. Therefore, DBAs often have to make disparate applications talk to one another, using a database as the means of sharing data. DBAs commonly get involved in figuring out the best way to integrate applications, and often perform the integration themselves.
Archiving Data
Data grows over time and can become costly to store and difficult to manage. In addition, increased data tends to exacerbate performance problems. DBAs are often responsible for monitoring data size and growth, and determining the best way to store it. In some cases, this may include archiving seldom-used data in another database, or it might mean purging data that is no longer needed. Often, choices are limited, as company policy and government regulations can restrict how and where data is stored.
Attending Meetings
Most DBAs hate attending meetings because it detracts from work time. Meetings are a fact of working life and, if used wisely, aren’t necessarily a waste of time. With correct planning and preparation, meetings are great opportunities for improving communications. The responsibilities of the DBA are often unknown or misunderstood by coworkers, so DBAs should always take advantage of opportunities, such as meetings, to share with others what they do.
Auditing
An emerging task of the DBA is to identify which users are accessing, inserting, updating, or deleting data, and when. Auditing might only be necessary for limited time periods, for specific data, or it might be required 24/7 for all data. DBAs often work with people outside their organization, such as external auditors, to perform this task. And to further complicate the lives of DBAs, many industries are subject to specific regulations on how data is accessed and protected, which means that DBAs have to comply with some specific rules, with the potential for significant penalties, should they not fully comply.
Backup and Recovery
One of the most fundamental aspects of the DBA’s job is to protect the organization’s data. This includes making periodic backups of data and keeping it safe from accidental or intentional destruction. In addition, a well-developed recovery plan needs to be implemented and tested so that when problems arise, data and access to critical systems can be restored quickly.
Business Intelligence and Data Warehousing
Another emerging trend is for DBAs to be tasked with the creation and maintenance of data warehouses and business intelligence applications, so organizations can better use their data to make more effective business decisions. Increasingly, DBAs are specializing in this fast-growing area.
Capacity Planning
In most organizations, the number and size of databases grow rapidly. It is the responsibility of the DBA to monitor data growth, and plan how best to deal with it. This may include archiving it, compressing it, increasing the size of current hardware, or adding new hardware.
Change Management
SQL Server configurations, database schema, T-SQL code, and many other facets of the application ecosystem change over time. It is often the responsibility of the DBA to perform impact analysis before changes are made, implement changes, test changes, and document them.
Data Modeling and Database Design
The foundation of all efficient and scalable databases is good design. DBAs often create database designs by performing needs and requirements analysis, creating a logical model, and then implementing the physical model. Larger organizations may have DBAs who specialize in database design.
Database Application Development
Many DBAs are application developers who specialize in writing code to access data stored in SQL Server databases. Although this is most commonly done using T-SQL, it can include writing code that is used to access SQL Server data. Many DBAs decide to make this their area of specialty, because of the depth of knowledge required to be a good database applications developer.
Developing and Maintaining Best Practices
Successful DBAs are proactive in their work, and one of the best ways to be proactive is to develop sound database best practices, and to implement them in a consistent manner. The better organized and managed the database operations, the more efficient they will be. One of the many ways that DBAs can perform this task is to create a run-book that describes the organization’s best practices and internal procedures. This document is the DBA’s bible for performing all DBA-related functions.
Disaster Recovery
At some point in their careers, DBAs will have to recover lost data. This may be as simple as restoring a database and its log files, or it may be as complex as moving a weather-damaged data center from one city to another. To make dealing with disasters easier (small or big), it’s the DBA’s responsibility to plan, implement, and regularly test disaster recovery scenarios.
Hardware Setup and Configuration
In some organizations, hardware is handled by dedicated hardware technicians. In others, the DBA is responsible for building, installing, and configuring their own hardware, including servers and storage area networks (SANs). In addition, DBAs may also perform regular hardware troubleshooting and maintenance.
High Availability
A DBA needs to ensure that the databases are available to users when they need access to data. You can help ensure high availability in many different ways, including use of log shipping, clustering, database mirroring, and other technologies. Because of the specialized knowledge required for high availability techniques, many DBAs choose this as their main area of focus.
Installing, Configuring, and Upgrading SQL Server Software
One of the most time-consuming of all database tasks is installing, configuring, patching, and upgrading SQL Server instances. Although it might sound like an easy task, it usually isn’t. One of the difficulties is finding a good time to perform such work, especially in 24/7 shops. In addition, each time SQL Server is patched or upgraded, there is the risk that after the patch or upgrade the applications that access the database, or the databases themselves, might not work properly. On top of this, DBAs often have to battle third-party vendors who are unwilling to certify that their application will work after you have made the changes. This requires the completion of planning, testing, and backout preparation before implementing major changes.
Load Balancing
Over time, the load put on individual databases changes. DBAs are responsible for monitoring workloads and figuring out how to maximize hardware resources to get the best SQL Server performance. This may involve moving a database from a heavily loaded server to a server with a lighter load. SQL Server virtualization, and how it can benefit load balancing, is becoming a hot topic for many DBAs. For more information on SQL Server virtualization, see chapter 39, “Running SQL Server on Hyper-V,” by John Paul Cook.
Maintaining Documentation
Writing and maintaining documentation is probably the most boring and loathed task that a DBA will encounter. However boring, it is a critical part of the DBA’s job. If you don’t document, then there is no easy way to rebuild the current infrastructure should major problems arise.
Managing People
Many DBAs find themselves in management positions, such as a senior DBA in charge of junior DBAs. Some DBAs at large organizations do this full time, whereas others combine people management with other DBA duties. On the other hand, when some DBAs get a taste of management, they give it up to get back to more technical work. Managing people is not for everyone.
Managing SQL Server–Based Applications
A surprise for many new DBAs is the realization that they are responsible not only for managing SQL Server and its databases, but in many organizations, they are also responsible for managing any applications that access the database. In some organizations, the DBA spends more time managing applications than SQL Server itself.
Managing Test Environments
In many organizations, DBAs manage test environments that not only include test SQL Servers and test databases, but also test applications. The purpose of this is to allow applications (both in-house and third-party) to be tested before new versions of either the application or SQL Server are rolled out into production.
Mentoring
Oftentimes, DBAs have to share their knowledge with other DBAs, developers, or end users. This might be informal one-on-one tutoring, or it might include classroom training.
Monitoring
This is a wide-ranging task that includes multiple subtasks, such as performance monitoring, monitoring server disk space, viewing logs, ensuring jobs have run successfully, and scrutinizing for errors. Although a variety of third-party tools are available to perform these tasks, DBAs commonly spend a lot of time performing monitoring manually because they don’t have a budget for such tools, or they are not familiar with them.
Needs and Requirements Analysis
Whether DBAs are involved in development, or just supporting third-party applications, they often get involved in needs and requirements analysis. This can include talking to users, finding out their needs and requirements, and determining the best way to meet them. This often includes researching available third-party solutions and determining if it’s best to develop the application in-house, or to invest in a third-party application.
Negotiating Service Level Agreements
In many organizations, DBAs become involved in negotiating service level agreements (SLAs). An SLA is an agreement between parties in which one is the customer and the other is the service provider. The customer (the owner of the business application accessing SQL Server databases) and the DBA (the service provider), must agree on acceptable levels of service, such as defining what is an acceptable response time for a specific type of transaction. Part of the negotiating process often requires the DBA to set expectations and educate the customer on what is feasible given the resource constraints of the organization.
Operating System Setup, Configuration, and Administration
Just as many DBAs manage their own hardware, many DBAs also manage the operating system on their SQL Server instances. Not only must SQL Server be configured optimally, the operating system it is running on must also be configured optimally, and be properly maintained.
Performance Tuning
Everyone wants their data right now. It is the job of the DBA to monitor performance and to determine ways to optimize database performance. This can be complex, and many DBAs specialize in this area.
Project Management
Oftentimes, DBAs find themselves in charge of a large project involving many other people. This could entail writing a new in-house application, or managing the migration of a data center from one location to another. DBAs with good project management skills are in high demand.
Replication
Data is often moved from one server to another, and one of the most popular ways to perform this task is to use SQL Server replication. DBAs often research various ways data can be replicated from server to server, decide on the most appropriate method, implement replication, and then manage it after it is up and running.
Report Writing
With the advent of SQL Server Reporting Services, many DBAs find themselves writing reports against databases. This might include writing the T-SQL code to extract the data, or it could include the creation and formatting of physical reports. This is a new and growing area, and some DBAs are specializing in it.
Running Jobs
Virtually every SQL Server has jobs that run periodically. They might include backups, data imports or exports, index rebuild jobs, and data integrity checks. DBAs are responsible for determining what jobs are needed, creating the jobs, and scheduling them.
Scripting
DBAs often write their own T-SQL scripts to perform a wide range of tasks, including monitoring and maintenance. With the advent of PowerShell, many DBAs are writing PowerShell scripts to enhance their productivity.
Security
DBAs control who can access data and what they can do with it. This has many aspects, including creating SQL Server logins and users, assigning permissions, moving security between servers, and implementing data encryption.
SSIS and ETL
DBAs often move data in and out of databases, and at the same time, perform transformations on the data as it is moved. This is often done for data warehouses and application integration. This process is often referred to as extract, transform, and load (ETL), and in SQL Server this is commonly performed using SQL Server Integration Services (SSIS).
Testing
DBAs frequently perform all sorts of testing. This can include testing of the following: databases, applications, management tools, and disaster recovery plans. DBAs test because they want to ensure the high availability and integrity of their data.
Troubleshooting
Virtually every day, DBAs troubleshoot one problem or another. Often when a problem occurs, the DBA is expected to drop everything and focus on resolving the problem at hand. This can result in long hours and high levels of stress. As with many technical positions, effective DBAs are good problem solvers.
Working with Teammates
DBAs rarely work alone. In most cases, DBAs interact with many people, including other DBAs, developers, end users, product-knowledge specialists, vendors, accountants, hardware experts, and networking experts. Because of this, DBAs need good people skills.
DBA specialties
As you can see, DBAs perform many different tasks and take on a wide variety of roles. Usually, DBAs are generalists because they need to know a little about many different areas. On the other hand, you seldom find a DBA who is a master of all the previously described tasks. This is for two reasons. First, most people don’t have the time to master every DBA-related subject. Second, there is the added burden of having to keep up with rapidly changing technology. For most people, it’s impossible to carry out all aspects of the DBA role. To become a successful DBA, you don’t have to know everything there is to know about SQL Server. In fact, you have a much better chance of becoming a successful DBA if you narrow your focus and specialize in a handful of SQL Server subjects.
In this section, we take a look at some of the common areas in which DBAs specialize.
DBA System Administrator
The DBA system administrator is a generalist who knows a little about many different subjects. Also known as a production DBA, the DBA system administrator is generally in charge of setting up, configuring, and maintaining test and production SQL Server instances. This can include such routine tasks as monitoring, performance tuning, backups and restores, security, creating jobs, and so on. In medical terms, think of the DBA system administrator as the general practitioner. All of the other DBA specialists are more like medical specialists.
DBA Database Architect
Think of a DBA database architect as the employee who sees the big picture of data storage and access in an organization, and whose job it is to research the organization’s data needs, research the available options, and then recommend policies the organization should implement. In many organizations, the title of database architect is another name for the lead DBA, DBA manager, or DBA project manager.
DBA Database Designer
DBA database designers focus on creating new database schemas. They define user needs and requirements, develop logical database designs, create physical databases, and so on. In most cases, the DBA database designer works with DBA developers, or other developers, to develop and maintain applications.
DBA Developer
The DBA developer writes code, be it T-SQL scripts, stored procedures, functions, Common Language Runtime (CLR) objects, or any other kind of code that is used to access SQL Server data. Often, the DBA developer works with DBA system administrators, DBA database designers, project managers, and other developers to develop and maintain applications.
DBA High Availability and Disaster Recovery Specialist
For many of today’s online businesses, it is more critical than ever that SQL Server is available around the clock. Therefore, DBAs often need to specialize in high availability. Their job is to determine what high availability methods are best for their environment, and then to implement and maintain them, helping to prevent the loss of data in the first place. Other DBAs specialize in disaster recovery, after the fact. In other words, they are brought in to try to recover data from corrupted databases because existing high availability and disaster recovery procedures failed.
DBA Business Intelligence Specialist
DBA BI (business intelligence) specialists design, create, and maintain data warehouses and online analytical processing (OLAP) cubes, so that data can more easily be retrieved and analyzed by organizations. This often involves developing BI-based applications written using Multidimensional Expressions (MDX) queries. In most cases, they are also heavily involved in extract, transform, and load (ETL) and designing and implementing SSIS packages.
DBA Report Writer
In the past, the DBA report writer has often been lumped in with the business intelligence specialist. SQL Server Reporting Services has evolved to a point that many DBAs are now specializing in designing and creating reports to extract data from databases in meaningful ways.
Summary
Given that the DBA job description is so varied and constantly changing, it is hard to list all the possible DBA specialties on which you might want to focus, so don’t consider the list in this chapter to be anything more than an informal guide.
You may find that you want to develop your own specialty, one that is even more focused than the broad specializations I’ve listed. For example, you might want to focus on performance tuning, clustering, SSIS, or replication only. Nothing prevents you from specializing as much as you like, other than the fact that the more narrow your specialty, the fewer jobs available.
As we have seen, being a DBA means many different things. It is not a single job title with a single skill set. Instead, the job title of DBA encompasses a wide range of job tasks and roles that are tied together with a single common denominator—DBAs are the guardians of the organization’s data. As DBAs, it is our job not only to protect an organization’s data, but also to make it available to everyone in the organization who needs it to perform their jobs effectively. How well we fulfill this responsibility determines how successful we are as DBAs.
About the author
Brad M. McGehee is an MCSE+I, MCSD, and MCT (former) with a bachelor’s degree in Economics and a master’s in Business Administration. Involved in the industry since 1982, Brad is currently the Director of DBA Education for Red Gate Software, and is an accomplished Microsoft SQL Server MVP with over 14 years of experience with SQL Server and over 6 years of training experience. Brad is a frequent speaker at SQL PASS, European PASS, SQL Connections, SQLTeach, SQLBits, SQL Saturdays, TechFests, Code Camps, SQL Server user groups, and other industry seminars. He blogs at www.bradmcgehee.com.