Databases are integral part of any Information Technology (IT) system, with advancement in technology these IT systems are getting more and more involved in people everyday life. IT is involved in every aspect of life such as food such as online food delivery, travel online booking, hospitals system, social media and so on and so forth. Every IT system uses databases directly or indirectly, management data base is very crucial in keeping all systems up and running.

Database administration plays very important role in technical aspect of any software system as it runs on top of database and data is by far the most critical piece of information in business software application. This paper sheds light on this important topic for database administration and entails how various database administration activities are performed during various situations and lifecycle of business system.

The core feature of database administration is securing database from unauthorized access this paper is going to enlist details about database security, fine-tuning, backup and recovery in case of any disaster. Another key area worth mentioning is database software upgrades and security patches, depending upon Database Management System (DBMS) and operating system it is very critical for database system to be updated at regular interval in case of any security update or patches available.

Database administration in general is fairly complicated depending upon system, it involves various factors such as relational or non-relational database, cloud or on-premises database system etc. administration steps for each type vary especially in terms of achieving database security.

Overview

Database administration is very important aspect of a database system, and involves various steps need to achieve better and effective database administration.  Data is by far the most important resource of any business, major part of data is stored and processed through databases using some database management system such as Microsoft’s SQL Server, Oracle and MySQL etc.

Database administration is sort of art rather than science, and varies between use cases, complexities in achieving effective database administration depends upon factors such as cloud databases or on-premises databases and relational or non-relational databases.

Apart from aforementioned major factors there are other factors that can affect the level of complexity in database administration these include, the scale of database of application; large scale applications for instance required extra steps of database administrator to achieve high performance and high arability and queries tuning etc.

Database management activities are done by various roles known as Database Administrators (DBAs). Although database administration is not silver bullet, but it can be abstracted into following set of generic activities regardless of context of system being used in. Following figure show general overview of typical database system.

Typical database system
Typical database system

Building blocks of Database Administration

Database Security

Database security is one of the key areas of database administration which refers to steps and processes needed to security data, database and database managed system from unauthorized access.  Database security has four key parts, first security data sitting in database, this includes granting and denying access to users based on their roles.

Secondly securing the database server, this includes database server access permissions which is done using access management on database server operating system and physical security of server which is done using physically securing server room with locks, and security cameras, and steps needs to be taken to prevent in case of natural disaster.

The database security goes hand in hand with information security, overall database security in terms of process depends upon three key features Confidentiality Integrity and Availability (CIA).

Confidentiality, Integrity & availability
Confidentiality, Integrity & availability

Confidentiality is term refers to securing data in database using encryption. The data in database is always encrypted which makes it more security in case of breach because even if data is exposed it is not usable until decrypted. Integrity on the other hand ensures that only authorized users access to data, by enforcing access control mechanism.

Availability is term refers to make database available all the time whenever is needed, this also makes sure little or no downtime even in case of system upgrades. Maintaining all aforementioned principles in database security is one of main challenges.

Schema modification and data updates

Database modification and update is another key step in the line of database administration, which deals with any schema modification, updates and migration for application deployments and releases. This is important set of processes and procedures done often as part of application release and maintenance lifecyle, these include changes such as changes done in database schema to support new feature in application.

The changes in database are documented and done in form of script batches, in typical application that is divided into two categories Data Definition Language (DDL) scripts and Data Modification Language (DML) script, DDL scripts enable database schema changes such as adding or updating triggers, columns, store procedures; DML scrips include any changes to data sitting in the database.

DDL scripts executed first followed by DML scripts, each batch of script is executed under transaction which mean if anything goes wrong during execution it can easily be rollbacked to prevent any schema or data corruption or inconsistency.

Performance Optimization

This Performance optimization is another crucial phase in database administration, which involves optimizing database and SQL statements to reduce response or execution time. The most popular technique in performance is to create efficient indexes, index can be of two types clustered-index and non-clustered index. A clustered-index based on physical storage of data in a table that is reason it is very efficient in parity of searches for data.

Primary key in table is clustered-index by default, searching on primary key is always efficient as compared to any other columns. A non-clustered index on the other hand is based on logical ordering of data, mean physical order of data is different than the data indexed in non-clustered index. There can be only one clustered-index on table and one or more non-clustered indexes. Common performance hotspots in database are listed in following figure.

Optimizing performance for queries also requires statistical data to determine when and where and how many indexes needs to be applied; there are three factors needs to be considered in this regard Density, Cardinality and Selectivity. Density is uniqueness of values within dataset, density can be calculated from dividing total rows in table by values in given key of dataset. Cardinality is measurement of unique records in given dataset.

Selectivity on the other hand measures row count rented as result of given query. Every database management system (DMBS) has internal query optimizer which uses these statistics to find optimal way to execute query. When data changes these indexes becomes invalid and automatically updated by DBMS. Most of DBMS has various database optimization tools, MS SQL Server for example has query optimizer tool which helps to optimizing indexes and queries.

Databse performance hotspots

Data archival and Tuning

Data archival, tuning and purging is another important area in database administration. When database grows bigger, queries and indexes become slower, so data archival becomes essential to tune database, there are five main steps needs to be done to achieve effective data archival, these include data extraction, data validation, data transformation, data migration and data retrieval. Data extraction is the process of extracting data from source database and making it ready for target database archival, this also include considerations such as data type transformation, data encoding transformation etc.

Data and schema validation is another aspect to consider before archival, this include matching schema in source and target databases, matching privileges in source and target database, and any data integrity constraints needs to be satisfied by source data. Data cleansing may also be considered in selective cases where things like removing duplicate records or data minification is done. Data transformation is also consideration in some cases where certain parts of data base schema don’t match and required data transformation.

The moment of truth, data migration is process where data is transformed from source database to target database, this include various utilities and scripts needs to be executed to conduct data archival.

Data archival is not just dumping data from one database to another, it should be accessible and retrievable. After archiving data, it should also be retrievable, there are two data retrieval polices needs to be considered after data archival are hot-retrieval and cold-retrieval. In hot-retrieval data is searchable using keywords or queries while in cold-retrieval data is available through reports.

Database Monitoring

Database monitoring is another important part of database administration in which database is monitored for any performance, availability and security issue. Database monitoring is essential for any database as it ensure good database health and helps to determine any potential or current performance and security issues.

The main goal is to figure out how database server is performing, it also involves taking performance snapshot on regular interval, which helps identifying issue between time intervals. Most of database management system in cloud or on-premises has some sort of notification system which alerts administrators in case of any issue. In large business applications identifying and database related issues is fairly complicated and time consuming, database monitoring helps in identifying and troubleshooting less troublesome.

Backup and Recovery

Database backup and recovery which is by far the most aspect of any database system which include backup database on multiple physical locations and restoration in case of any disaster. Database backups can be either logical or physical, logical backup contain database logic such as store procedures, views and functions etc. Logical backups use to enhance backups in various saturations.

Physical backup on the other hand is backup for database including data and transaction logs etc. Database backups are really important to bail out in any disaster saturations such as server failure, disk failure, theft, fire, and case of natural disaster.

Software Updates and Patches

This covers the maintenance of Database Management System (DBMS) software applicator under discussion and includes software update or security patches related by vendor of DBMS. Security patches installation to database management system is important as fixes for new security flaws are released from software vendor.

Software updates installation is another aspect of database administration, these updates includes general updates, hotfixes and minor release updates for bug fixes, this helps running software smoothly and help reducing issues. This also includes management disk capacity in case of disk space outage and tacking any hardware issue and replacements for faulty devices.

Database Migrations and Merges

Database migration and merges also important feature need to be considered when updating or modifying large system. Database migration also is very important if you are migrating between database systems such as migrating SQL Server Database to Oracle, DB2 etc.

In some of the cases business required to migrate between different database systems due to some business of technology constraints, for example porting relational database to No-SQL database, this required through analysis and exhausted set of processes and steps to effective port database.

Another important use case of database migration is when business want to switch between different providers such as moving from SQL Server to Oracle, which also required deep knowledge and understanding of bother database management system, to achieve effective database migration.

Database Reporting

Database reporting and business intelligence (BI) is another important aspect of database and business system, this involves generating various report for different purposes. The database reporting can vary by application, some application needs report from within same database, and some dedicated report server.

Microsoft SQL Server Reporting Services (SSRS) and Power BI are some example of reporting tool and mechanisms. A database report in general is result of complex SQL query executed on large set of data to produce some business specific summary which can be present to different business role such as managers to drive certain business policies.

Database Management Roles

Last but not least various roles come under umbrella of database administration, these roles are divided by varying responsibilities and these roles include system DBAs, application DBAs, database architects and data analysts etc. System DBAs are focused on technical side of responsibilities of databases such as installation, updates, patches, configuring database tools etc.

Application DBAs are focused on application specific logic in database, such as writing queries, store procedures to fulfill desired application functionality, their responsibility also includes tuning and performance improvement for queries. Database architects design and architect databases such as identifying entities, relationships, normalizing tables, creating Entity Relationship Diagrams (ERDs) etc.

Summary

This paper covered database administration in general and enlisted various steps involved in achieving effective database administration. Although database administration itself can be complex depending upon system and scale of database, this paper did generalize top level database administration steps which every database system should adhere in order to achieve and sustain database systems.

Database security, performance, backup and recovery and major steps in database administration, done by various database roles known database administrators. In spite of having general steps for database administration, the database systems can quickly grow complex in real-world application, and hence poses more challenges to administration, such as scale, database security and performance.

Security and performance are by far the most crucial pillars of database admonition, achieving and maintaining effective security and performance has been key challenge for database administrators.