How to Get Started With Database Automation

Database automation helps make complex and time consuming tasks simple and fast. The tasks most commonly and easily identified for automation are those that are time-consuming yet repetitive. These often consume productivity, and can affect company finances because you have to pay the people working on these tasks. However, the processes in which time and effort are needlessly consumed can be converted to virtual automation, thereby avoiding often dull, exhausting work.

Database automation has been a common practice of database administrators and server administrators, who together are more commonly known now as DevOps. DevOps also refers to the combination of DBAs’ and server admin tasks. In the old fashioned way, traditional and common automated tasks are written as a series of SQL statements or .sql files, which deploy and provision servers via scripts, setting up encryption/decryption, or harnessing security for the environment in which your automation is supposed to run. Here, automation is not an example of a company replacing people with scripts. It is there as an assistant to bring things up to speed, and finish tasks faster with fewer errors. Automation cannot replace the way DBAs perform their tasks or the value they can deliver to the entire company or organization.

Sophisticated tools for Infrastructure as Code (IaC) such as Puppet, Chef, Ansible, SaltStack, and Terraform help DBAs complete those tasks that are easily replicated, such as backup and restore, failing over, deployment of new clusters, adjusting security settings, OS Kernel and database performance tuning, and a lot more. With the help of automation, a lot of DBAs have also improved or shifted their skills from focusing on data-domain specific tasks to also cover how to code in order to utilize these IaC tools that make things easier than using the traditional approach. There are also tools at-present that make it easier to manage your assets in the cloud,  such as managing your company user accounts, logs, deploying instances, or managing your servers. Tools for the cloud from the big-three cloud providers include AWS CloudFormation, Azure Resource Manager, and Google Cloud Deployment Manager and allow DBAs or DevOps to leverage the power of automation and make things faster. This not only impresses your organization or company’s executives, but also the customers relying on your service.

What Needs to be Automated?

As mentioned above, database automation is not new for DBAs, server administrators or even DevOps. There’s no reason to hesitate or question whether to automate. As stated earlier, common cases that are easily identified for automation are tasks that are repetitive in nature. 

Below, we enumerate things that are axiomatic from the DBA’s perspective.

• Provisioning of your servers (e.g., initiate VM instances such as using vagrant, initiate docker, or initiate your Kubernetes platform) and setup SSH access or setup VPN access

• Deployment of a new database cluster

  • Identify what type of database provider, the type of setup (primary/standby, master-master replication, synchronous replication)

• Import existing database cluster

• Deploy/import existing databases to your current database cluster

• Auto-failover or switchover

• Automatic node or cluster recovery

• Replica/Slave promotion or Demoting a master

• Deployment of load balancers (e.g. ProxySQL, HaProxy, pgpool, pgbouncer, MaxScale, Keepalived)

• Backup and Restore

• Setup your database monitoring environment (e.g., deploy agent-based monitoring such as Prometheus)

• Enable security adjustments

• Perform automatic optimizations and tuning in accordance with the type of environment

• Enable alerting systems to other third-party integrations

• Generate alerts or alarms and notifications

• Generate reports such as graphs

• Process query logs (slow logs) for query analysis

• Generate query analysis

• Database archival or clean up

There are of course a lot of cases that you could automate, but this lists the most common tasks and automating them is unquestionable. These are the types of tasks that are repetitive in nature and the majority are error-prone, especially when they have to be done quickly due to time constraints. 

What Are Things That Shouldn’t be Automated?

These areas are where your DBAs or SysAdmins do most of the work. Automation cannot replace the skill set and intelligence of the DBA when it comes to things that cannot be automated. 

It is understood that a DBA has to be skilled, with a profound understanding of:  the database they are using and the databases that will be deployed; the data that are being processed and stored; and whether the way they are being processed is secure, or if it complies with company security standards. DBAs also review and mostly are considered DevOps, as well the automation architect. They dictate what has to be done, and what won’t be done. Common things that should not be automated are the following:

• Setting your scheduled backups. Scheduled backups are of course automated and have to run accordingly, but the scheduled dates or period of time required should  be based on the low-peak times the server will perform. For example, you cannot take a backup if the cluster is busy during the daytime. There are common cases as well when servers are still busy at night depending on the type of application you are serving, and where it is geographically located.

• Auto-failover failed to promote a new master. This is one of the most important cases and has to be well understood. If you have automated scripts designed for failover, it should not be designed to forcibly pursue a failover in case it happens to fail.. You might never know what is the main problem, and if there is a failure, there can be transactions that have to be recovered before anything else should be done. For example, it could be a financial transaction that was stored on the failed master, and you forcibly wanted to promote a slave, but the candidate slave had failed to replicate the latest transaction. In that case, you might end up with corrupted data.

• Data Recovery. Of course, when you encounter data corruption or a cluster fails to recover from your automatic node/server recovery, you might have to investigate the primary cause. You have to document this for your RCA (Root Cause Analysis) to avoid it in the future. However, there are instances when the failure is a bug of the database software you are using, or it can be a VM corruption.

• Data Drift or Data Inconsistency. This is definitely not an ideal situation for automation. You do not want your automaton to generalize or stereotype your data to a practice that would apply this concept: “if data is corrupted, let’s automatically fix it”. It’s definitely not a good practice. There are a lot of cases that first have to be understood and investigated before you can decide. In MySQL, for example, there’s a Percona tool called pt-table-checksum, then pt-table-sync for which both are correlative to each other on fixing data inconsistencies. You definitely won’t want to automate this unless you know your data very well, or your data is not extensive, or the data can be regenerated.

• Kernel tuning and database tuning. Of course, this can be seen as contradictory to what we have stated above. However, there are auto-tunable variables known for specific types of environments, such as memory, buffer pool, HugePages, or virtual memory parameters. However, there are definitely a lot of parameters that need understanding, investigation, testing, benchmarking before you decide to apply the changes or not.

Definitely, there are a lot of things you shouldn’t  automate that we did not mention. In the database world, there is an extensive number of situations that depends on the type of data and application you are serving. Keep that in mind, and be sensitive to the things that  can be automated. Otherwise, automation can lead to destruction.

Tools for Automation

This is where you can get started with your automation scripts. The most important component of automation is speed! When it comes to speed, it is not measured by how quickly a tool is able to finish the tasks, but how comfortable the developers or maintainers of the scripts or IaC are with the tool. Definitely, there are pros and cons for these automation tools available. What’s more important is to determine the specifications of these automation tools, as there are more to offer aside from being just automation. More commonly, they provide configuration management and deployment mechanisms.

Automation is all about speed, that is, how fast it is in contrast to using a traditional approach, or using your own preferred language scripts. Of course, using your own scripts can be perfect, but if your organization or company is for technological advancement, then using third party tools such as Ansible, Puppet, Chef, SaltStack, or Terraform is more ideal. Why is it more ideal? These third-party tools are designed to defeat long and lengthy tasks to perform, and can be done with few lines of code. 

For example, Terraform is known for its portability benefits. Just Imagine, with Terraform, you have one tool and one language for describing infrastructure for Google Cloud, AWS, OpenStack and ANY other cloud. If you switch to another provider, you don’t need to modify or redo your scripts. It also allows you to have full-stack deployment, and that includes managing your Kubernetes containers. Imagine that, from one tool, you can do a lot of things.

When starting your database automation, do not start from scratch because the goal of automation is speed! Again, speed is not measured here in how fast it is to finish the job, but how fast it is in comparison to a traditional approach or manual tasks. Of course, the speed of how quickly it’s  able to finish the job all depends, e.g., part of your scripts may cause long delays due to a bunch of processed data and long job executions.

Always Choose Based on Your Requirements

When choosing tools, do not rely on hype, or what’s the most popular that you’ve heard of. Though the mainstream tools that were mentioned earlier are embraced largely by the community, they do introduce complexity as well.  For example, when using Ansible, you have to be familiar with YAML, while with Puppet or Chef, you have to be familiar with Ruby and its underlying domain-specific language.

Take Advantage of Available Enterprise Tools

There are a lot of promising database automation tools to get started with. If you feel it’s uncomfortable and time-consuming to hire DBAs, SysAdmins, or DevOps to extend your team, there are tools available that offer help when it comes to database management, backup management, and observability.

Severalnines ClusterControl for Database Automation

ClusterControl offers a lot of automated tasks that eliminate  the need for manual approaches. ClusterControl is designed to make database operations easy for organizations, companies, DBAs, SysAdmins, DevOps, and even developers. Its goal is to automate long-running and repetitious tasks. The great advantage of ClusterControl is that it is a mature database management tool and has extensive features that are very powerful to manage your database servers. It also applies the most up-to-date, industry-standard best practices for managing your databases. We listen to the demands of our customers, then we implement capabilities to meet them.

Some of the most feature-rich ClusterControl automation functionality that you can take advantage of are:

• Deployment of your database servers. Choose the provider, specify the right version, determine what type of cluster, specify the server’s hostname/IP such as username, password, etc.

• Importing of existing servers to ClusterControl

• Deployment in the cloud

• Database health monitoring and reporting

• Alerts and notifications

• Backup and Restore

• Backup Verification

• Auto-failover, switchover

• High-availability setup

• Promote a slave or demote a master

• Add new/existing replica to your cluster

• Extend another cluster as a slave of another cluster (perfect for geographical setup for your disaster recovery)

• Node and Cluster Recovery

• LDAP integration

• Third-party alert notifications

• Deployment of any of an extensive list of load balancers  (pgbouncer, ProxySQL, MaxScale, HAProxy, Keepalived, garbd)

• Deployment of agent-based monitoring using Prometheus exporters

• Query analytics

• Security adjustments

• Auto tuning for OS kernel and database parameters

In addition to  all of these, ClusterControl also has built-in advisors that enable DBAs or DevOps to create their own scripts and integrate into ClusterControl Performance Advisors.

Summary

Database automation helps bring complex yet repetitive tasks up to speed. It helps DBAs to move quickly ahead on different tasks and improve their skills depending on the scope of work involved. Database automation frees DBAs to be more innovative while also comfortably managing the database. Database automation does not replace the role of the DBA. There will always be a need for skilled and smart people to manage your databases, especially when disaster strikes. Always rely on the tools that your DBAs recommend, while trusting their DBAs skills to manage the health and life of your databases.