What Kind of DBA Are You?

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 performing.

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 Server machine.

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

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 Standard Compliance
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.

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.

Index Design
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 query performance.

Backup/recovery planning
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 working.

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.

Mentoring/Training
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.

Performance Monitoring
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.

Capacity Planning
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.

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 are you?

This article has been taken from databasejournal.com.