Blog

One of the most popular requirements for a specialist and it means one of the most valuable characteristics is proactivity. A proactive employee is characterized by the independence, a responsible approach, the ability to influence particular situations, and the ability to calculate in advance possible events outcomes. So, a proactive specialist doesn’t «put out the fire» but tries his best to prevent its appearing.

Is it necessary for DBA to be a proactive specialist? The answer is a definite – YES. The proactive approach for a databases performance and maintenance helps DBA to avoid problems and prevent small bugs transformation into a full blown disaster.

Below we’ll talk about DBAs’ routine work and some recommendations for database performance improving.

Database maintenance

Every administrator knows that it is necessary to make a backup. But not all of them take this task responsibly and pay due attention to it. Making backup regularly many specialists forget to take into account the most important thing – consequences. It is necessary to remember that regular backup also determines a potential data loss as a result of the recovery (data that was created after a backup but before a recovery).

Backup

No executive is willing to lose data. That’s why it is too important to define a metric like RPO (recovery point objective) before a backup planning and database recovery.

RPO is a allowed time period over which data can be lost in case of failures. Time of the data recovery from a backup storage doesn’t have to exceed this metric. RPO has to dictate when (how often) and how (with the help of what technologies) it is necessary to make backups.

Ensuring successful backup is one of the most important DBA’s tasks. In the process of a backup creating administrator should use the CHECKSUM parameter, with the help of which it is possible to check defects. Another one variant to check a backup is the RESTORE VERIFYONLY function.

DBA can make different types of backups: full (all data), differential (only data that has been changed since the moment of the last full backup), incremental (only changed data since the previous backup).

Some administrators find it necessary to restore differential backups in chronological order to reach RPO, but it is wrong opinion. It is necessary to restore only the last full backup before the recovery point followed by the most recent differential backup.

Recovery

DBA has to be ready in advance for possible DB failures in which he needs to recover backups. Being in such situation the first manager’s question will be about recovery time. That’s why administrator should know how much time it takes to retrieve the backup files, to recover backups and give a manager the most accurate process estimation. For this it is necessary to test backups and possible recovery scenarios in advance.

Integrity check

Database integrity check is also an important part of DB maintenance. Some DB servers allow to back up corrupt databases, but without backup restore. In this case DBA can perform a full check for database corruptions with the help of DBCC CHECKDB before a backup. The main disadvantage of this function is resource intensive – the work with database could take days.

Agent Alerts

For quickly respond to database failures DBA should set up alerts.

Microsoft offers to solve this task by using The SQL Server Agent designed to notify about SQL server errors with a severity between 17 and 25 which include database engine and resource errors and 823, 824, 825, 829 errors.

PostgreSQL offers its products: pgAnalyze и pgwatch2. PgAnalyze is a software designed to improve the queries visibility. This tool can be used to define reasons of the slow queries performance and to monitor databases for receiving a current state imagine. pgwatch2 is a flexible monitoring solution using Grafana dashboards.

Indexing

A proper indexing is one of the best ways to improve database performance. Correctly created indexes promote quickly information finding and user queries performance. For the proper index building DBA has to own expertise in this field otherwise incorrect index can have the opposite effect and essentially reduce an inquiry processing.