Database Automation Best Practices in FinTech

Automation equals speed – there is no discussion about it. Automation also increases reliability and reduces the error rate. Those feats are important in pretty much every kind of industry, but they are crucial for some. The FinTech industry is among those. Operating in a highly competitive market, you want to be able to deploy fast and reliably and control your database environment to the maximum level. Relying on external cloud providers is very easy, but it comes with a high cost. What you may want to do instead, is to build an environment tailored for your particular needs, which can be used to achieve velocity and reliability that you require. Let’s take a look at how ClusterControl can help you to accomplish this goal.

Database Deployment Speed

One way to increase the velocity of your internal processes related to the database tier is to let users do what they want whenever they need it and whenever they like. There are many internal projects running at the same time, distributed across numerous teams, sometimes even across them as multiple teams may be assigned to the same project. Single operations team won’t be able to reply to all of the requests, take care of all of the needs, or help to design all of the environments that are required at the given moment. Instead, what can be done is to build an infrastructure that will allow the other teams to take care of their needs related to the database environment.

ClusterControl is a platform that can be used to handle the whole lifecycle of open source databases. This actually fits very nicely into the FinTech industry, given that we see significant trends of immigrating from preparatory databases into the open source ones. PostgreSQL, for example, is one of the common picks among financial organizations. ClusterControl can be used, among others, to deploy open source databases and, as such, it is a good core around which you can build the self-service solution for your company. There are a couple of ways in which you can integrate ClusterControl with your tools. Outside of the UI, ClusterControl comes with an API that can be used to execute RPC calls and perform required tasks. It is also possible to use the command line interface to ClusterControl to perform actions required at the given moment.

ClusterControl comes with an extensive set of features, and it could be that not all of them are needed for people, who just want to have their databases up and running and will not be performing management of such databases. It could be wise to build an application on top of the ClusterControl, an application that will expose only the most important features of the platform, smoothing the learning curve and letting your teams get their databases as quick as possible. In the background, your operations team will be working tirelessly with ClusterControl to ensure that the databases are properly managed and maintained, backups are being executed. Should a node fails, ClusterControl performs the recovery.

FinTech Data Security

FinTech is all about security – whenever Personal Identifiable Information is stored or processed, especially if it is related to financial accounts, security is paramount. Having a standardized way of dealing with databases may be of a significant benefit. You want to ensure that the deployments you perform are done in the same way. You want to be able to change the configuration across your databases easily should you need to make any changes. Automation can be of great help here – having a set of scripts and tools to deal with the deployments and life cycle management of the databases can be crucial in complying with the security standards and requirements that are related to the FinTech industry or PII in general. What would also be very useful is to prepare a piece of code that would be plugged into the deployment process for staging or test servers. Such code should be able to obfuscate any vulnerable data into the form that can be used outside of production, in less strictly secured environments. Please keep in mind that staging setups, typically, have reduced security measures, at least related to access – you may want to have developers accessing staging servers while you don’t want them to access production systems. If that’s the case, you probably don’t want to let developers access sensitive data thus the need of obfuscating it.

Another important aspect is the access itself. You may want to automate security audits. Among things to look at would be:

1. Are the users defined in the database ones who should be defined? Compare what you see in the database with what you should see there.

2. Do the users defined in the database have the proper access privileges? Are the privileges limited to what is required for a given user?

3. Do the users have defined access from a proper set of hosts? We want them to access the database only from the hosts that make sense (application hosts, loadbalancers and so on) instead of allowing access from everywhere.

4. Are the security settings in the database configured properly? In some cases, you can configure your database to enforce password rotation, define specific requirements for the password string and so on.

Those are some suggestions related to the database configuration. Aside from this, you probably want to include security checks on the operating system, network setting check (example firewall check) and analyze contents of audit logs to detect malicious activity.

Database Backups

Backups are crucial for any kind of serious business, not just FinTech; it is also the most commonly automated activity. Write a shell script that does the job, set it up in the crontab for scheduled execution, and that’s it. The commonly missed step in such a setup is that non-tested backup is as good as none – backup can only be marked as working if you have tested it. Backups can be corrupted, can be unrestorable. If you won’t catch this before you actually need the backup, you can run into a serious situation. This is why the backup automation has to involve a test restore – restore the backup on a separate host, make sure the database can be started and then see if you can set it up as a part of the replication topology and catch up with the rest of the cluster.

When designing an automated backup verification, please make sure you test all the steps you have to execute during the regular restore process. If you use encryption and the key to decrypt backups is stored in a safe location, make sure it is actually involved in the backup verification. See if you can encrypt the backup, decompress it. It is paramount to test the backup after you implement any kind of change in the backup process. If you add a new step to your backup process or change the backup tool’s configuration, make sure you tested at least several backups before you decide that the backup process is properly working.

High Availability

As you can imagine, FinTech industry, even more than your average online industry, requires databases to stay available even if there are issues. We, as users, prefer to see financial organizations as stable and available. We, after all, trust them with our money. It wouldn’t be nice to experience downtime that would affect our ability to use our credit cards or access bank accounts. This is why the high availability of databases is so important in the financial industry.

We want to see in the high availability department the automated failover for the asynchronous replication topologies. The idea is simple – in an asynchronous replication environment, you have one writer (master) and one or more readers (replicas). Should the master crash, one of the replicas should be promoted to take over its role. Several safety checks should be performed on the master candidate; once all is clear, failover should be triggered.

Let’s take a look at the process:

We have here a master and two replicas ready to be promoted. Let’s assume that the master crashes and 

The moment the master becomes unavailable, automated scripts will ensure that the replicas in the topology will catch up and replay any missing transactions. As a next step, replicas will undergo sanity checks to determine the best master candidate. Finally, a new master should be promoted and take over writes.

Database automation is a large topic, and this blog is just scratching the surface of it. If you are interested in more details, please look at these case studies on how ClusterControl helps Fintech Industry. If you would like to share your experience, we’d love to hear from you in the comments below.