There are many types of DBAs. Some DBAs work in the
developer arena, while others are heavy into performance and tuning, and still
other DBAs fall into the operational aspects of managing SQL Server. There
are a number of different tasks a DBA might perform depending on the
environment in which they work. To help identify all the different kinds of
tasks a DBA might perform I have compiled a list.
My list covers many different types of tasks. I have
categorized these tasks into 7 different areas: Configuration, Database
Standards, Database Design, Development, Maintenance, Monitoring, and
Planning. For each task, I’ve listed a short description of the duties
associated with that task. In some environments, a single DBA might perform
each of these tasks but in other environments, these tasks might be shared by
many different individuals. If you are contemplating being a DBA, or are
currently a DBA then these are the kinds of tasks you might expect or should be
SQL Server Machine Configuration
DBAs should configure the physical machine for each new SQL Server
installation. The DBAs will work with application staff to identify fault
tolerance and performance requirements. These requirements will be used to
develop the physical setup requirements. In some environments, the DBA will
perform the actual installation of hardware and installing the operation system.
In other environments, they might just provide information to the systems
administration staff in how to configure the hardware associated with a SQL
Installing SQL Server Instances
The DBA will be responsible for installing SQL Server software and
additional instances on a SQL server machine. DBAs should consider how each
installation should be setup. When you have multiple instances and machines,
you should consider building a scriptable installation so all installations are
consistence across all instances. This consistency will help minimize the
maintenance aspects of managing all your instances
Tasks Related Database Standards:
Develop Databases Standards
In order to maintain consistency across an organization the DBA should develop
database standards. The DBA is responsible for developing and communicating
these standards across the organization. Standards should not be developed in
a vacuum, so the DBA should facilitate database standard discussions with
application developers in an organization. Standards are an evolving process,
once developed they need to be maintained. The DBA should ensure that as new
releases of application technology and database versions come out that the
standards are reviewed and modified appropriately to meet the needs of the ever
changing environment of information technology.
Review Database Designs for
Once standards are in place a DBA needs to make sure each new database, or
enhanced database is reviewed to make sure it meets the database standards. If
databases don’t meet the standards then the DBA should play the traffic cop
role, and make sure a database either meets the standards, or some form of
exception process is performed to document why a particular database does not
meet the standards.
Database Design Tasks:
Data Model Review
Data models are typically one of the first steps in designing a new database.
The DBA should review these models. This review process will acquaint the DBA
with the data that will be contained in the database. This review process stimulates
discussion about how the data in the database will be processed and loaded.
This information will be helpful with architectural decisions on how the data
will be stored, read, shared and managed. This review will help ensure data is
appropriately integrated into the enterprise database environment.
Physical Database Design
In some environments DBA’s will also be called upon to perform actual
databases design. In other environment applications, programmers/contractors
will propose the design of a new database. In either case, the person
developing the database will need to meet with the business analysts to help
define business data and processing requirements. From this discussion, DBAs
will develop or assist in the development of a physical database design. The
database design needs to meet the data requirements for inserting, update,
deleting, and exporting data.
Database Security Design
This task looks at applications and how they authenticate to SQL Server to
gain access to the data the application will be using. Here the DBA would
determine the most appropriate authentication method. SQL Server has two
different authentication methods: Windows Authentication and SQL Server
authentication. The DBA should work with the application developers and
business analyst to determine what kind of users will be using the database,
and where the applications will live. The DBA needs to determine if Windows
Groups can be used to simplify security and how SQL Server logins and database
roles should be used to facilitate securing SQL Server data. The DBA should
consider developing an approach that simplifies managing security and provides
the most flexible architecture for the long term.
One of the key things each application needs to achieve is optimum
performance. The DBA will work with the developers/business analyst to
determine how tables are joined, and queried. From these discussions,
appropriate indexes will be designed. The DBA needs to help refine the index
design decisions overtime. They can do this by monitoring index usage and
identifying missing indexes during the development lifecycle. This ongoing monitoring
will help ensure appropriate indexes have been designed to help maintain optimal
When databases are being designed and developed, the DBA needs to determine backup/recovery
requirements as well as disaster recovery requirements. From these requirements,
the DBAs will need to design a backup and disaster recovery plan to meet the requirements.
Once the database is actually created and backups are being taken, the DBA
should perform restore tests to ensure that the backup and recovery strategy is
Building Automated/Scheduled Database
Load and Data Transfer Routines
Not all data in a database is entered into the database through data entry
screens. DBAs need to be well versed in methods of loading and extracting data
from a database. They need to be able to design and build automated processes
that extract, load and transfer data. Therefore, they need to understand how
to use tools like SSIS, BCP, BULK INSERT, SQL AGENT jobs, FTP, etc.
Database Code Reviews
As applications are being built the DBAs should meet with application
programmers to have code reviews. These code reviews will help ensure code
meets standards and use best practices. Ideally, these code reviews should be
performed early on in the development lifecycle to minimize the effort required
to make any changes that the code review might identify.
Building and Managing Automated Data
Extraction, Transformation, and Load Routines
The DBA can be called upon to build data extraction, transformation and
load routines (ETL). To accomplish this, the DBA would work with business
analyst to define business requirements for the ETL process. From these
requirements, SSIS packages, scripts and SQL Agent jobs would be built to run
the required ETL process routinely.
Implementing Database Changes
The DBA should implement a change management process for database code and
schema changes. This change management process would allow you to capture and
track changes to stored procedures, views, trigger, table definitions, etc.
over time. Ideally, these changes would be implemented into a source code
repository. The change management process should consider not only how to
implement changes but also how to back out changes should any change adversely
affect stability of a database/application.
Routine Database Maintenance
The DBA needs to make sure all databases are being maintained and running
optimally. To ensure this the DBA will need to run routine maintenance tasks.
Here is a partial list of some of those tasks: reorg indexes, update
statistics, defrag databases, and/or shrink databases.
DBAs are typically the experts in querying data for a database. DBA staff
should provide training and mentoring as needed to junior DBAs and application
programmers. The goal of this task is to help staff gain the necessary skill set
required to build efficient T-SQL code. This training will ensure that
programmers write code that performs well. The DBA should also help ensure
that secure coding practices are deployed so SQL Injection attacks are
minimized. The DBA should educate developers in how to ensure steps are taken to
properly edit user entered data to minimize any possible SQL injection attacks.
DBAs should monitor the performance of the databases they manage. This
performance monitoring will encompass a number of different activities. First
monitoring should capture performance measurements (statistics) when
applications are first introduced into the environment. These statistics
should then be used to develop a performance benchmark for an application.
Each time enhancements are made to an application the DBA should gather new statistics
and compare the results against the baseline. Any difference in the newly
gathered performance statistics and the baseline statistics should be noted. Secondly,
the DBA should be keeping an eye on server wide performance related indicators
such as memory usage, buffer cache, object locks, application connections,
etc. When something looks out of place this should trigger some
analysis/investigation to determine what has caused these performance indicator
changes. Another type of performance monitoring task a DBA might take on would
be to help out with an application performance audit. This type of performance
works would assess the performance status of an established application. The
DBA would use different tools to gather performance information. This performance
information is then reviewed by the DBA and application staff to suggest
changes that will improve performance
Query Tuning and optimization
DBAs should be constantly looking for query tuning and optimization
opportunities in the production environment. When poorly performing queries
are identified, the DBA should work with the application programmers to
determine why a query is running slow. This process would then develop
strategies for improving the slow running queries.
Monitor Databases, Instances,
and Database Servers for Availability
This task routinely monitors databases, instance and databases services to
make sure applications can connect to them. This task promotes the idea of a
proactive monitoring method to ensure database services are available as
needed. Automated notification tools should be used to help the monitoring
effort. Hopefully by monitoring instance availability, a DBA can resolve any
issues prior to them being noticed by database users.
Monitor Error and Event Logs
SQL Server’s LOG directory and the Windows’ Event log should be scanned
routinely by the DBA for each SQL Server instance, and machine. The goal of
this monitoring activity is to identify abnormal error messages. When abnormalities
are found in any of the logs files then an investigation should be undertaken
to determine why these events occurred. All findings should be documented and
communicated to the appropriate staff.
SQL Agent Job Failure Monitoring
At least once a day, and possibly more often, the DBAs should review SQL
Agent jobs for failures. The monitoring can be done manually or automatically.
All jobs failures should be investigated so the appropriate actions can be
taken to resolve any failures found.
This task identifies the amount of space needed for new databases and the
growth rate for existing databases. Capacity planning takes into account the
amount of new data added daily, monthly and/or yearly to ensure there is enough
disk space available to handle the growth rate of a database. Grow rates are
calculated by gathering disk space statistics periodically for existing databases,
or best guess growth rates for new database.
Database Architecture Planning
The DBA needs to develop a database architecture for their environment. This
architecture needs to promote sharing and securing data, as well as connecting
to databases. One of the items of this effort is to make sure there are common,
flexible, and supported ways to connect to SQL Server databases. Another
aspect of this architecture is to make sure you design a security model that
supports your security requirements and is flexible enough to be adapted and
maintain over time. This item also ensures other management issues, such as
backups, maintenance, and troubleshooting are all done using common tools and
processes. Using consistent processes helps minimize effort when managing
multiple database servers and instances.
SQL Server Upgrade planning
Microsoft comes out with a new release of SQL Server every 3 years or so. The
DBAs need to consider this and then develop a plan for upgrading and/or
installing new versions of SQL Server as they are released by Microsoft. This
planning effort will help identify the requirements and steps/approaches
necessary to migrate from one version of SQL Server to another.
What Kind of DBA Are You?
This list of tasks covers the spectrum of DBA Tasks. Not
all DBAs will perform all of these tasks. If you are a developer/designer of
databases you probably only perform a subset of these tasks. If you are an
operational DBA that covers installing, backing up and disaster recovery then you
most likely perform a completely different set of tasks than developer/designer
types. If you work in a small organization then you might perform a lot of
these, but may not have time to perform all of these tasks. What kind of DBA