Database administration and automation

Database administration is the function of managing and maintaining database management system

Database management system

A database management system is a software package with computer programs that control the creation, maintenance, and use of a database. It allows organizations to conveniently develop databases for various applications by database administrators and other specialists. A database is an integrated...

s (DBMS) software. Mainstream DBMS software such as Oracle

Oracle database

The Oracle Database is an object-relational database management system produced and marketed by Oracle Corporation....

, IBM DB2

IBM DB2

The IBM DB2 Enterprise Server Edition is a relational model database server developed by IBM. It primarily runs on Unix , Linux, IBM i , z/OS and Windows servers. DB2 also powers the different IBM InfoSphere Warehouse editions...

and Microsoft SQL Server

Microsoft SQL Server

Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...

need ongoing management. As such, corporations that use DBMS software often hire specialized IT (Information Technology

Information technology

Information technology is the acquisition, processing, storage and dissemination of vocal, pictorial, textual and numerical information by a microelectronics-based combination of computing and telecommunications...

) personnel called Database Administrators

Database administrator

A database administrator is a person responsible for the design, implementation, maintenance and repair of an organization's database. They are also known by the titles Database Coordinator or Database Programmer, and is closely related to the Database Analyst, Database Modeller, Programmer...

or DBAs.

DBA Responsibilities

Installation, configuration and upgrading of Database server software and related products.
Evaluate Database features and Database related products.
Establish and maintain sound backup and recovery policies and procedures.
Take care of the Database design
Database design
Database design is the process of producing a detailed data model of a database. This logical data model contains all the needed logical and physical design choices and physical storage parameters needed to generate a design in a Data Definition Language, which can then be used to create a database...

and implementation.
Implement and maintain database security (create and maintain users and roles, assign privileges).
Database tuning
Database tuning
Database tuning describes a group of activities used to optimize and homogenize the performance of a database. It usually overlaps with query tuning, but refers to design of the database files, selection of the database management system , operating system and CPU the DBMS runs on.The goal is to...

and performance monitoring.
Application tuning and performance monitoring.
Setup and maintain documentation and standards.
Plan growth and changes (capacity planning).
Work as part of a team and provide 24x7 support when required.
Do general technical troubleshooting and give cons.
Database recovery.

Types of database administration

There are three types of DBAs:

Systems DBAs (also referred to as Physical DBAs, Operations DBAs or Production Support DBAs): focus on the physical aspects of database administration such as DBMS installation, configuration, patching, upgrades, backups, restores, refreshes, performance optimization, maintenance and disaster recovery.
Development DBAs: focus on the logical and development aspects of database administration such as data model
Data model
A data model in software engineering is an abstract model, that documents and organizes the business data for communication between team members and is used as a plan for developing applications, specifically how data is stored and accessed....

design and maintenance, DDL (data definition language
Data Definition Language
A data definition language or data description language is a syntax similar to a computer programming language for defining data structures, especially database schemas.-History:...

) generation, SQL writing and tuning, coding stored procedure
Stored procedure
A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure is actually stored in the database data dictionary.Typical uses for stored procedures include data validation or access control mechanisms...

s, collaborating with developers to help choose the most appropriate DBMS feature/functionality and other pre-production activities.
Application DBAs: usually found in organizations that have purchased 3rd party application software
Application software
Application software, also known as an application or an "app", is computer software designed to help the user to perform specific tasks. Examples include enterprise software, accounting software, office suites, graphics software and media players. Many application programs deal principally with...

such as ERP (enterprise resource planning
Enterprise resource planning
Enterprise resource planning systems integrate internal and external management information across an entire organization, embracing finance/accounting, manufacturing, sales and service, customer relationship management, etc. ERP systems automate this activity with an integrated software application...

) and CRM (customer relationship management
Customer relationship management
Customer relationship management is a widely implemented strategy for managing a company’s interactions with customers, clients and sales prospects. It involves using technology to organize, automate, and synchronize business processes—principally sales activities, but also those for marketing,...

) systems. Examples of such application software includes Oracle Applications
Oracle Applications
Oracle Applications comprise the applications software or business software of Oracle Corporation. The term refers to the non-database parts of Oracle's software portfolio....

, Siebel and PeopleSoft
PeopleSoft
PeopleSoft, Inc. was a company that provided Human Resource Management Systems , Financial Management Solutions , Supply Chain and customer relationship management software, as well as software solutions for manufacturing, enterprise performance management, and student administration to large...

(both now part of Oracle Corp.) and SAP. Application DBAs straddle the fence between the DBMS and the application software and are responsible for ensuring that the application is fully optimized for the database and vice versa. They usually manage all the application components that interact with the database and carry out activities such as application installation and patching, application upgrades, database cloning, building and running data cleanup routines, data load process management
Process management
Process management is the ensemble of activities of planning and monitoring the performance of a process. The term usually refers to the management of business processes and manufacturing processes...

, etc.

While individuals usually specialize in one type of database administration, in smaller organizations, it is not uncommon to find a single individual or group performing more than one type of database administration.

Nature of database administration

The degree to which the administration of a database is automated dictates the skills and personnel required to manage databases. On one end of the spectrum, a system with minimal automation will require significant experienced resources to manage; perhaps 5-10 databases per DBA. Alternatively an organization might choose to automate a significant amount of the work that could be done manually therefore reducing the skills required to perform tasks. As automation increases, the personnel needs of the organization splits into highly skilled worker

Skilled worker

A skilled worker is any worker who has some special skill, knowledge, or ability in their work. A skilled worker may have attended a college, university or technical school. Or, a skilled worker may have learned their skills on the job...

s to create and manage the automation and a group of lower skilled "line" DBAs who simply execute the automation.

Database administration work is complex, repetitive, time-consuming and requires significant training. Since databases hold valuable and mission-critical data, companies usually look for candidates with multiple years of experience. Database administration often requires DBAs to put in work during off-hours (for example, for planned after hours

After Hours

- Television and film :* After Hours , a 1985 movie directed by Martin Scorsese* After Hours , a 1953 Canadian television series* After Hours , a 2007 television drama broadcasted in Singapore...

downtime, in the event of a database-related outage or if performance has been severely degraded). DBAs are commonly well compensated for the long hours

One key skill required and often overlooked when selecting a DBA is database recovery (under disaster recovery). It is not a case of “if” but a case of “when” a database suffers a failure, ranging from a simple failure to a full catastrophic failure. The failure may be data corruption, media failure, or user induced errors. In either situation the DBA must have the skills to recover the database to a given point in time to prevent a loss of data. A highly skilled DBA can spend a few minutes or exceedingly long hours to get the database back to the operational point.

Database administration tools

Often, the DBMS software comes with certain tools to help DBAs manage the DBMS. Such tools are called native tools. For example, Microsoft SQL Server comes with SQL Server Enterprise Manager and Oracle has tools such as SQL*Plus and Oracle Enterprise Manager/Grid Control. In addition, 3rd parties such as BMC, Quest Software

Quest Software

Quest Software is a computer software manufacturer headquartered in Aliso Viejo, California. Founded in 1987, Quest develops, manufactures and supports software used by Information Technology professionals in a variety of industries...

, Embarcadero Technologies

Embarcadero Technologies

Embarcadero Technologies is an American computer software company that develops, manufactures, licenses, and supports a wide range of products and services related to software through its various dynamic product divisions...

, EMS Database Management Solutions and SQL Maestro Group offer GUI tools to monitor the DBMS and help DBAs carry out certain functions inside the database more easily.

Another kind of database software exists to manage the provisioning of new databases and the management of existing databases and their related resources. The process of creating a new database can consist of hundreds or thousands of unique steps from satisfying prerequisites to configuring backups where each step must be successful before the next can start. A human cannot be expected to complete this procedure in the same exact way time after time - exactly the goal when multiple databases exist. As the number of DBAs grows, without automation the number of unique configurations frequently grows to be costly/difficult to support. All of these complicated procedures can be modeled by the best DBAs into database automation software and executed by the standard DBAs. Software has been created specifically to improve the reliability and repeatability of these procedures such as Stratavia

Stratavia

Stratavia, formerly known as ExtraQuest, was a software company that specialized in enterprise Database and Data Center Automation, and private cloud computing enablement...

's Data Palette

Data Palette

Data Palette is a patented IT process and decision automation platform developed by Stratavia. It is intended as a highly scalable and flexible means for automating repetitive data center tasks, such as software upgrade and patch management, database and application provisioning, database...

and GridApp Systems

GridApp Systems

GridApp Systems, Inc. was a database automation software company. It was purchased by BMC Software in December, 2010.Founded in 2002 and headquartered in New York City, GridApp Systems was the brainchild of five former employees of Register.com, Rob Gardos, Shamoun Murtza, Matthew Zito, Dan Cohen,...

Clarity.

The impact of IT automation on database administration

Recently, automation has begun to impact this area significantly. Newer technologies such as Stratavia

Stratavia

Stratavia, formerly known as ExtraQuest, was a software company that specialized in enterprise Database and Data Center Automation, and private cloud computing enablement...

's Data Palette

Data Palette

suite and GridApp Systems

GridApp Systems

Clarity have begun to increase the automation of databases causing the reduction of database related tasks. However at best this only reduces the amount of mundane, repetitive activities and does not eliminate the need for DBAs. The intention of DBA automation is to enable DBAs to focus on more proactive activities around database architecture, deployment, performance and service level management.

Learning database administration