DBAs (Database Administrators) perform many different tasks, and one way to explain what a DBA is, is to describe the kinds of tasks they perform on a regular basis. The following lists some of the most common tasks performed by the average DBA, from A to Z.
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 users, for very specific data, or it might be required 24/7 for all data. While performing this task, DBAs often have to work with both internal and external auditors.
While most organizations use third-party applications, very few of these applications work in isolation. In other words, disparate applications have to be made to talk to one another, often using the database as the means to share data. DBAs often get involved in figuring out the best way to integrate applications. This may include creating custom applications, Transact-SQL scripts, or SSIS packages.
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 do arise, data can be restored quickly.
Business Intelligence/Data Warehousing
One of the fastest growing areas for the DBA is Business Intelligence (BI) and data warehousing. This is because more and more organizations are seeking to mine all the information they can, in order to make better business decisions.
In most organizations, the number and size of databases grows rapidly. It is the responsibility of the DBA to watch data growth, and plan how best to deal with it. This may include archiving it, increasing the size of current hardware, or adding new hardware.
SQL Server configurations, database schema, Transact-SQL code, and many other facets of the application ecosystem, change over time. It is often the responsibility to the DBA to perform impact analysis before changes are made, implement changes, test changes, and document them.
Database Application Development
Many DBAs are really application developers who specialize in writing code to directly access SQL Server. While this is most commonly done using Transact-SQL and stored procedures, it can involve writing other code that is used to access SQL Server data.
Data Modeling and Database Design
The foundation of all efficient and scalable databases is good database design. DBAs often create database designs by performing needs / requirement analysis, creating a logical mode, and them implementing the physical model.
Developing and Maintaining Best Practices
DBAs should be proactive in their work, and one of the best ways to be proactive is to develop sound database best practices and to implement them. The better organized and managed the database operations, the more efficient they will be. Ideally, an organization’s best practices will be documented for all to read and follow.
A DBA needs to ensure that their databases are available to users when they need access to data. There are many different ways to help ensure high availability, including use of log shipping, clustering, database mirroring, and other technologies.
Installing, Configuring, Patching and Upgrading SQL Server Software
One of the most time-consuming of all database tasks is installing, configuring, patching and upgrading SQL Server instances. While installing and configuring new instances is relatively straight-forward, it can be time consuming.
Installing and Configuring Hardware
In some organizations, hardware (the server and I/O subsystem) is handled by dedicated hardware technicians. In others, the DBA is responsible for building, installing, and configuring their own hardware. In addition, DBAs may also perform regular hardware troubleshooting and maintenance.
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 busy server to a less busy server. It can also involve server consolidation or virtualization.
Writing and maintaining documentation is probably the most boring and loathed task that a DBA will encounter. However boring it is, it is still a critical part of the DBA’s job. If there is no documentation, then there is no easy way to rebuild the current infrastructure should major problems arise.
A manager needs to be a DBA’s ally, not an enemy. It is important for a DBA to develop and maintain good relationships with their manager, and with any other managers in the organization that they work with. Getting along with managers makes it easier for DBAs to get the resources they need to succeed at their job. The same is true for getting along with other managers, as many of them may control resources that DBA’s need in order to perform their tasks successfully.
Many DBAs find themselves in management positions, such as a senior DBA who is in charge of junior DBAs. Some DBAs at large organizations do this full time, while others combine people management with other DBA duties.
Managing SQL Server-based Applications
DBAs are often responsible not only for managing SQL Server and its databases, but also any applications that access the database. These varies by organization, but in some places the DBA ends up spending more time managing applications than SQL Server itself.
Maintaining Servers and Databases
SQL Server, and the databases running on them, need periodic maintenance in order to run efficiently. DBAs are responsible for ensuring all maintenance gets done on a timely basis.
Managing Test Environments
In most, larger organizations, DBAs manage test environments that include test SQL Servers and databases, as well as test database applications. The purpose of this is to allow databases and applications (both in-house and third-party) to be tested before new versions of SQL Server (including patches and service packs), operating systems, or applications are rolled out into production.
This is a wide-ranging task that includes many subtasks, such as monitoring performance, monitoring server disk space, monitoring logs, ensuring jobs have run successfully, checking for errors, and so on.
Whether a DBA is involved in development, or just supports third-party applications, they often perform needs/requirements analysis. This can include talking to users, finding out their needs and requirements, and determining the best way to meet them.
Negotiating Service Level Agreements
In many organizations, DBAs become involved in negotiating Service Level Agreements (SLAs). A SLA is an agreement between the customer (the owner of the business application accessing SQL Server databases) and the service provider (the DBA team managing the databases). This agreement sets out the criteria that define “acceptable service”.
It is the job of the DBA to monitor performance and to determine ways to optimize database performance.
Oftentimes, DBAs will 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.
Protector of the Data
While this is not a specific job task, I am including it here because it underlies so many of the DBA’s other tasks. DBAs are responsible for protecting the integrity of an organization’s data. This not only involves such obvious areas as backup, restores, and high availability, it also includes ensuring that applications don’t corrupt data, that hardware doesn’t corrupt data, or that user’s don’t corrupt data.
It is very common for data to be moved from one server to another on a regular basis. A DBA will often research various ways in which data can be replicated from server to server, decide upon the most appropriate method, implement the replication, and then manage it once it is up and running.
With SQL Server Reporting Services, many DBAs find themselves writing reports against databases. This might just mean writing the Transact-SQL code to extract the data, or it could include the creation and formatting of physical reports.
Virtually every SQL Server has jobs that run on it periodically. These jobs might include backups, data imports or exports, or rebuilding indexes. DBAs are responsible for determining what jobs are needed, creating the jobs, and managing them.
DBAs control who can access data and what they can do with it. This involves creating SQL Server login IDs, database IDs, assigning permissions, moving security between servers, and maybe even implementing data encryption.
DBAs often write their own Transact-SQL scripts to perform a wide range of tasks, including monitoring and maintenance tasks. In addition, with the advent of PowerShell, many DBAs are writing PowerShell scripts to enhance their productivity.
A very common task is to move data in and out of databases and at the same time perform some transformations on the data as it is moved. This is often done for BI applications, data warehouses, and application integration. SQL Server Integration Services (SSIS) is a popular tool DBAs use to implement Export/Transform/Load (ETL) operations in SQL Server.
DBAS perform all sorts of testing, all the time. This can include testing servers, testing databases, testing applications, testing management tools, and so on. DBAs test because they want to ensure that what they do will work, and that data integrity and high availability, is maintained at all times.
Oftentimes, DBAs need to share their knowledge with other DBAs, developers, or end-users.
Virtually every day, DBAs are troubleshooting one problem or another. In many cases, when a problem occurs the DBA is expected to “drop everything” and focus on resolving the problem at hand.
Many SQL Server-based applications are provided by third-parties, so the DBA often becomes involved with maintaining relationships with these third-party vendors. The DBA will generally perform the initial installation of the third-party software, troubleshoot problems, and update the application and its database when new versions are rolled out.
Working with Teammates
Rarely will a DBA work alone. In most cases, DBAs will be interacting with a very large group of people, including other DBAs, developers, end users, product-knowledge specialists, vendors, accountants, hardware experts, and networking experts.
While this may seem like a long list, it is only the tip of the iceberg when it comes to describing the many tasks of the Database Administrator. If you are a DBA and would like to add to this list, please do so in the comments section below.