Database Automation Tips and Tricks
Automation is the process of using automatic unattended procedures to manage repetitive tasks. Automation when applied to databases and data warehouses can improve reliability, reduce errors, increase uptime, increase speed and save you time and money. In fact, there are several tasks that may traditionally have been performed by DBAs, that are now becoming more and more automated.
Database automation is often overlooked by individual developers and DevOps professionals. However, there is a push in organizations to include database tasks in DevOps processes and infrastructure management. DevOps professionals apply a combination of tools and techniques to deliver information technology services reliably and efficiently. Since databases can be a core service in many organizations, they should not be overlooked by DevOps.
However, the primary challenge with some database automation tasks is that data needs to persist and database state needs to be preserved. You can’t simply erase a database and start over when you make changes. This means an automated database operation has to ensure that all business data stays consistent and reliable. This can be especially difficult because database engines have different management mechanisms. A technique that works in one database environment may not work in another.
Database Tasks Ripe for Automation
Updating, changing and deploying databases can be very tedious, and can interrupt otherwise smooth workflows. This is especially the case if your database is tightly coupled with your application (a practice that should be avoided if possible!) or extensively patched with emergency ad-hoc fixes. However, regardless of your system design, the tasks involved are often very repetitive, and are ripe for change automation. It doesn’t matter if you are using relational database system like SQL or a document database like MongoDB – an automation opportunity may present itself whenever you can think of a way to eliminate manual effort with code. Some examples of tasks that can easily automated include:
- Health checks ensuring your database system is accessible and operational.
- Server maintenance like rebooting the system for software updates.
- Database maintenance including scheduled backups, data encryption, restores, index clean ups and consistency checks.
- Storage defragmentation
- Database synchronization between QA and production databases.
- Database log cleanup and removal.
- Automatic documentation generation.
- Automated data entry from imports or external web services.
- Database consistency checks.
- Loading test data into test and sandbox environments.
- Ensure regulatory compliance of stored data for mandates like HIPAA, PCI and SOX.
- Creation of new user accounts with corresponding role assignments.
- Moving your database from one system to another.
- Machine learning training algorithms that constantly improve based on new data inputs.
- Automated failover – if your database goes down, automatically pass requests to a replica.
- Database growth monitoring helping you scale as needed.
Automating the above types of activities can offer huge reward with minimal risk. In fact, these types of repetitive tasks are much better handled by machines then people, as a computer will be more efficient and less likely to make errors.
Database Automation Tools
There is a good chance your database system already includes automation features. For example, Oracle provides various maintenance windows that can automate database cleanup, gather database statistics and monitor resources. And there are other open source and commercial products like “Rundeck”, “Ansible”, “Redgate”, “Liquibase”, “Delphix”, “DBMaestro” and “Jenkins” that help users streamline deployment steps including scripts and commands. These types of systems can be configured to work will types of specialized database systems including PostgreSQL, MySQL, MongoDB and more.
And in pretty much any database system you can write custom shell scripts to make backups, analyze logs and summarize usage statistics. And sometimes you can even implement database automation features using complete programming languages like Python, Java, Ruby on Rails, and C#.
Whatever tool you use there is a key principle to keep in mind: Scripts that change database structure like migrations, should be idempotent. This means that even if you run a script multiple times, changes only happen once. For example, if you had an automatic script that added a new phone number field to a table, you only want a single phone number field added to the table. no matter how many times the script runs. This requirement typically means that you need to maintain version numbers that can be auto incremented as database structure changes.
Also, all tools you select for database automation should support “rollbacks”. This means that you can easily restore a database to a previous state if a script or a migration puts your database into an unstable state.
Finally, cloud-based systems like Microsoft Azure and Amazon Web Services (AWS) offer automated mechanisms that let you scale resources up and down as system load changes.
Database Source Control
Databases can benefit from automated source control and versioning systems. Any schemas, SQL scripts, stored procedures and functions can be checked in or out similar to regular programming code. Database migrations can also be managed by source control. In fact, some databases systems offer built in source control features. For example, SQL Server Management Studio (SSMS) has the “SQL Source Control” plug-in. After you have selected and configured a database source control system, with just a few tweaks, you can add it to your deployment pipeline. This will allow database changes to trigger continuous integration and continuous deployment procedures, including automated testing, which in turn will further automate system rollouts.
Also, if you are software developer, your IDE (integrated development environment) may include plug-ins that assist with automated database migrations. For example, Visual Studio has plug ins that can automate migrations, and you can also use PowerShell scripts, various .NET libraries and other similar tools.
What not to Automate
As attractive as automation is, there are still some tasks that are better left to humans. These tasks may include valuing data in your database and finding solutions to business problems that require intuition and strategic decision making. These are the types of tasks that still require human input.
Tracker Ten
Our Tracker Ten windows desktop application automates many data management tasks in the background including index creation, data entry from web APIs (when applicable), external file management and more.
