{"id":42953,"date":"2021-04-14T18:17:23","date_gmt":"2021-04-14T15:17:23","guid":{"rendered":"http:\/\/datalabsua.com\/ua\/?p=42953"},"modified":"2024-05-24T16:46:33","modified_gmt":"2024-05-24T13:46:33","slug":"dba-proactivity-or-fighting-fires","status":"publish","type":"post","link":"https:\/\/datalabsua.com\/en\/dba-proactivity-or-fighting-fires\/","title":{"rendered":"DBA: proactivity or “fighting fires”?"},"content":{"rendered":"

One of the most popular requirements for a specialist and it means one of the most valuable characteristics is proactivity.<\/em> 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\u2019t \u00abput out the fire\u00bb but tries his best to prevent its appearing.<\/p>\n

Is it necessary for DBA to be a proactive specialist? The answer is a definite \u2013 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.<\/p>\n

Below we\u2019ll talk about DBAs\u2019 routine work and some recommendations for database performance improving.<\/p>\n

Database maintenance <\/strong><\/p>\n

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 \u2013 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).<\/p>\n

Backup<\/strong><\/p>\n

No executive is willing to lose data. That\u2019s why it is too important to define a metric like RPO (recovery point objective) before a backup planning and database recovery.<\/p>\n

RPO <\/strong>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\u2019t 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.<\/p>\n

Ensuring successful backup is one of the most important DBA\u2019s tasks. In the process of a backup creating administrator should use the CHECKSUM<\/strong> parameter, with the help of which it is possible to check defects. Another one variant to check a backup is the RESTORE VERIFYONLY<\/strong> function.<\/p>\n

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).<\/p>\n

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.<\/p>\n

Recovery <\/strong><\/p>\n

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\u2019s question will be about recovery time. That\u2019s 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.<\/p>\n

Integrity check<\/strong><\/p>\n

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<\/strong> before a backup. The main disadvantage of this function is resource intensive \u2013 the work with database could take days.<\/p>\n

Agent Alerts<\/strong><\/p>\n

For quickly respond to database failures DBA should set up alerts.<\/p>\n

Microsoft<\/em> 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.<\/p>\n

PostgreSQL<\/em> offers its products: pgAnalyze \u0438 pgwatch2. PgAnalyze <\/strong>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 <\/strong>is a flexible monitoring solution using Grafana dashboards.<\/p>\n

Indexing<\/strong><\/p>\n

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.<\/p>\n","protected":false},"excerpt":{"rendered":"

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\u2019t \u00abput out the fire\u00bb but tries his best to prevent its appearing.<\/p>\n

Is it necessary for DBA to be a proactive specialist? The answer is a definite \u2013 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.<\/p>\n","protected":false},"author":2,"featured_media":44990,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[97,96],"class_list":["post-42953","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-database","tag-dba"],"_links":{"self":[{"href":"https:\/\/datalabsua.com\/en\/wp-json\/wp\/v2\/posts\/42953","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/datalabsua.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datalabsua.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datalabsua.com\/en\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/datalabsua.com\/en\/wp-json\/wp\/v2\/comments?post=42953"}],"version-history":[{"count":10,"href":"https:\/\/datalabsua.com\/en\/wp-json\/wp\/v2\/posts\/42953\/revisions"}],"predecessor-version":[{"id":44991,"href":"https:\/\/datalabsua.com\/en\/wp-json\/wp\/v2\/posts\/42953\/revisions\/44991"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/datalabsua.com\/en\/wp-json\/wp\/v2\/media\/44990"}],"wp:attachment":[{"href":"https:\/\/datalabsua.com\/en\/wp-json\/wp\/v2\/media?parent=42953"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datalabsua.com\/en\/wp-json\/wp\/v2\/categories?post=42953"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datalabsua.com\/en\/wp-json\/wp\/v2\/tags?post=42953"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}