Database Automation Guide

Guide

Table of contents

What is database automation?

Database automation is leveraging processes and tools to make admin tasks for a database simpler and safer. By using database automation for your database management, you not only have fewer deployment errors but also higher reliability and speed on change implementations. Additionally, your DBAs will use less time manually checking and updating code and have more time for patching, scaling, provisioning, and other tasks.

The empty seat in DevOps

DevOps is changing the culture of IT. This movement is bringing application development and operations teams to the same table. Together, they are working to overcome all of the challenges facing the entire application delivery process.

However, while speed to market is important, businesses can’t forget about the internal implications of accelerated release cycles. With that in mind, there is one empty seat within the “DevOps Inner Circle”: the database administrator (DBA).

DBAs hold the keys to the database and the data within it, a business’s most important asset. But DB change innovation has not kept pace with agile development methodologies and database DevOps tooling that enable continuous delivery of software updates and changes. Many enterprises are still updating databases the same way they always have:

  • Manual script review
  • Manual validation
  • Manual execution
  • Individual specialized initiatives

As a result, application release cycles always slow down at the database release process and businesses struggle to keep up with customer demand. Because of this, it’s time databases and DBAs become part of DevOps and start leveraging database continuous delivery to meet demands and deliver more features. Including DBAs and database automation in DevOps can create better, more efficient teams and save time and money. DBAs can even help companies create better solutions for their customers’ needs or their own. And, by augmenting processes with database automation, DBAs and development teams can work better (and faster) than ever before.

When database release processes aren’t automated, they are slower than application code releases, slowing everything down.

Bringing DBAs into DevOps

Before database automation, developers needed to bring their database scripts to DBAs for review and validation to ensure compliance with technical, business, and regulatory standards. DBAs were buried in change requests necessitated by accelerated application release cycles. Inviting DBAs into the DevOps inner circle allows teams to consider the implications of new application changes and updates across the entire technology stack.

One of the founding principles behind DevOps is the breakdown of silos between development and operations teams. Product managers can easily translate customer needs. They can then give feedback to developers in a way they understand—using epics and themes, for example. This puts everyone on the same page so that application updates or changes can be quickly coded, tested, and delivered.

Before application updates can be released to users, they need to be translated into the changes that will update the database. This is where DevOps breakdowns occur. DBAs have to rely on manually intensive, timeworn, error-prone processes and methods to make sure the right changes are made to the database.

By inviting DBAs into DevOps, database updates that could “break the build” (like inserting a new column with a default value when the row count exceeds a million) can be found and fixed before the change reaches the database.

Having DBAs involved in release cycles sooner can also help ensure developers are coding with corporate compliance in mind. This makes changes to the database smoother while adhering to guidelines that DBAs have typically policed.

Breaking down silos and making teams more aware of bottlenecks in the release process lets them expect and avoid database issues. This means faster releases and fewer headaches for everyone.

Database automation and DevOps

Faster release cycles are good for business, and not just because they get applications in front of end-users sooner. Continuous delivery cycles are designed to cut down application development costs and man-hours. But, while development timelines get shorter, the workload and time for database deployments get longer.

Enterprises can have many database configurations, so each change needs to be checked for compatibility. As the complexity of data repositories grows, so too does the time it takes to review and approve changes. In a recent IOUG survey on database manageability, 41% of respondents said changes took a week or more to approve. With multiple change requests coming to DBAs each day, it’s clear that sheer manpower can’t keep up with continuous delivery forever. Relief for DBAs is nonexistent without database automation.

Database automation software can protect against any unwanted outcomes. DB automation can help forecast the impact application changes will have on the database. With this information, DBAs can use those forecast reports to quickly discover issues instead of taking days to find them.

As part of a DevOps team, the DBA equipped with reasons for database automation can do more than save time and headaches for themselves. Protecting against errors that could break the build saves teams from spending time to find and fix the source of a breakdown, and saves a business from losing revenue while the fix is made. Plus, automating tasks for DBAs in the DevOps inner circle gives them more time and space for innovation.

What is database release automation?

Database release automation (DRA) is the process of packaging and deploying database changes, across environments throughout the software delivery pipeline and ultimately to production.

Best practices dictate that DRA solutions combine deployment automation, environment modeling, validation, testing, and release coordination.

Implementing a fully automated, continuous application delivery process requires that ALL code, including database code, be checked into version control and deployed as part of the application release process.

If your company isn’t using a database release automation process yet, you’re not alone. Many companies that implement continuous delivery for their application code aren’t doing the same for their database code. Not yet. But this is going to change very quickly as the importance and advantages of CD for the database become more apparent.

Recent research is shining a light on a huge problem that’s slowing application releases down. The State of Database Deployment Survey Results found:

  • 84% of application stakeholders have serious production issues due to database change errors
  • 57% of all application changes require a corresponding database schema change
  • 88% report taking more than an hour to resolve these schema change issues

CIOs are not going to let this trend continue for long. Especially now that there are tools and proven processes in place to eliminate this bottleneck.

Benefits of database release automation

There are a lot of benefits to adopting DRA into your current DevOps process, especially if you choose the right partner.

  1. Faster application releases
    Benchmarks from our customers show that using Liquibase Enterprise helps users deploy database changes 80% faster than they did before. When used as part of your CI/CD pipeline, releases take minutes, not days.
  2. Fewer errors
    Database script errors put companies at risk. Finding and then fixing these errors is incredibly time-consuming and tedious. By using database automation tools that simulate deployments for each environment in your pipeline, you can eliminate errors before they’re deployed. Our customer benchmarks show that Datical reduces database errors by 90%.
  3. Painless audits
    Enjoy on-demand access to detailed information on every database deployment across the enterprise. Liquibase logs detailed information on deployments into a centralized database, allowing users to retrieve, validate and label all database changes and trace them back to the corresponding application task or business requirement. Automatically.
  4. Happier, more productive teams
    Eliminate tedious manual tasks and prevent friction between developers and DBAs.

More and more companies are discovering that database release automation eliminates bottlenecks and speeds up application deliveries. By streamlining database release processes organizations can have fewer internal issues and focus on providing the best customer experience possible.

Database source control vs. database release automation

Source control is your single source of the truth. Using a continuous build server with source code control is the first step in Agile Development, Continuous Integration, and Continuous Delivery. When a build happens, source code is transformed from human-readable text to machine instructions that can be deployed. The source is tagged so that the code is forever tied to a specific application release. You can recreate it at any future time.

Database source control is missing from our database change process today. Sure, maybe we are checking in our DB change scripts. Maybe we use Toad Team Coding to check-in and check-out DB objects for us during development. Once that build is performed, you have an atomic application binary ready for automated deployment and a bunch of SQL scripts. But that will still not speed your deployment process. You are still tied to a manual database deployment process.

The big challenge is that you cannot determine if a change has been persisted to a database without a manual review. If you need manual DBA intervention for every change to a database, you will fail.

The best way to guarantee safety is by using the same source control repository for both application code and DB code changes. This creates database source control: a single source of the truth for what database changes are part of a release. This helps teams know what application version goes with what database schema version. But this can be a lofty goal for many organizations.

Database release automation

Database Release Automation (DRA) solves this problem. Just like Application Release Automation orchestrates and automates previously manual tasks, database automation enables full stack automation by changing our database schemas safely and quickly without the need for human intervention. Of course, databases are different than applications or web servers. We cannot simply blow away our database server and recreate it with a new version of the schema. We would lose our data. Databases have state and evolve over time.

Balancing safety and speed is key; you must have both with Database Release Automation. As part of every Database Release Automation tool evaluation, you should have use cases that test if you can perform a completely automated database deployment. Review your past year’s schema changes and choose the really gnarly, nasty ones to automate. Renaming tables is always a good one. Also, attempt to enforce naming conventions and the like with changes. Look for ones that you know should fail and make sure your DRA tool will catch them.

If your current DB change process cannot support lights-out automation, you need a solution with something more than just source control for the database.

Database automation best practices

The most time-consuming tasks a DBA must deal with include reviewing and testing changes to the database. Database automation can take care of many of these tasks. However, DBAs need to follow database automation and testing best practices. Some of these best practices include:

Decide which processes to automate

Determine which processes should be automated first. Ask yourself which tests require a lot of data to perform the same task and take a lot of time.

Test early and often

Start testing as early in the cycle as you can. The earlier and more often you can test, the more bugs you can find early on. This can also help you identify more candidates for automation.

Select the database automation tool that best suits your needs

There are a lot of tools for database automation in the market. You need to consider which ones will work with your platform and existing technologies as well as provide flexibility for your testers.

Logging and error tracking

While not exciting work, logging, and error tracking can help you track and analyze errors as your database automation system grows.

Manage dependencies for your automated processes

As your database automation system develops, it will have more moving parts. By setting up and managing dependencies for processes to complete, you can ensure the safety of your system.

6 reasons to get started with database release automation

Problems, specifically ones dealing with software releases, rarely have a single root cause. You might have to use multiple iterations of the software to find them. And, once fixed, you may find that your software releases are still not happening as quickly as you would like (even after adopting DevOps and Application Release Automation).

Database Release Automation (DRA) can help fix these problems. Database Release Automation is tooling that enables speed and safety for database changes. Think DevOps for the Database. This is not provisioning the database server; this is DDL and DML change to your database internals.

Releases via database automation mirror what we have already accomplished with application release automation but do not have to be done after adopting ARA. In fact, many of our DRA customers at Datical have adopted DRA prior to ARA or have stated that they regret doing ARA prior to DRA. They have identified the database change process as the biggest impediment to fast releases.

The value of applying DevOps to the database is off the charts. We’ve had customers take a week-long manual process down to an automatic process that takes seconds with self-service.

Here are 6 great reasons to get started with database release automation:

  1. Spend less time on database change tasks – This is the key value proposition of DRA. As you evaluate DRA tools, you absolutely must time the process and attempt to get to a time frame that is only comprised of automated machine time, not a human’s time.
  2. Spend less time on script reviews – Any DRA tool that still requires you to 3-way compare your changes is just asking for trouble. You’re trying to get your data professionals on higher value activities, not staff a help desk.
  3. Decrease deployment errors to production – As you have the same process for change from Dev to Production, you will have high consistency and decrease environment deviation leading to fewer errors in production. You will simply catch errors earlier when they are faster and cheaper to correct.
  4. Spend less time on audits – 3-way compare is for suckers. When audit time comes, you should be able to capture and email a report in less than 30 seconds. Spending time verifying change manually is unacceptable.
  5. Better adherence to corporate governance – Automating standards leads to 100% enforcement. Think Robocop for your database changes.
  6. Visibility to database change status – All DRA tools should come with a dashboard that shows who changed what, where, and when to aid in transparency and communication. (No more spreadsheets!)

Strategy for database release automation

The strategy for adopting Database Release Automation (or creating the DevOps Database) is simple, yet amazingly impactful. Here’s what you should look for.

  • Process standardization – Database Release Automation allows you to enforce your release process across all environments, from Dev to Test to Production, and all points in between. DRA enforces the same release mechanism and process independent of a human.
  • Rules automation – DRA provides enforcement of corporate, regulatory, and technical standards. An example of a technical standard would be that indexes cannot be comprised of more than 3 columns. So, when a developer submits an index that comprises of 4 columns, that change should be automatically rejected, just like a failed used case will break the build.
  • Change simulation – Knowing the impact of the change prior to being persisted to the database allows for environment transparency and allows for code reviews of proposed database changes.
  • Enhanced auditing – All persisted changes for all environments, including who requested what change and where, are invaluable for many audit use cases, such as SOX compliance.
  • Version control for the database – Just like you can recreate your application from source for deployment to newly created environments, you should have the same ability with your database. Taking database dumps from another environment and restoring is not the way to do this. It provides no insight into changes and it doesn’t allow you to recreate an environment at a specific release level.

Learn more about database release automation. Read our white paper, How to Get Started with Database Release Automation in 4 Easy Steps.

Top 4 reasons to combine database release automation with ARA

Software is reshaping industries, from healthcare to finance to retail and beyond. The pace of change is accelerating, and companies must master their applications to survive in the digital era. In the software economy, speed is the new standard.

To get a faster time-to-market, companies are adopting quicker, more iterative development methodologies. They have replaced age-old waterfall development methodologies with agile practices. From an infrastructure perspective, they have also invested in modern architectures and cloud technologies to achieve higher efficiencies. Some companies have even combined these 2 approaches by adopting DevOps—investing in new tools and processes such as infrastructure automation and continuous integration—to clock even faster speeds.

As companies have adopted application release automation (ARA) technologies to help increase software delivery rates, a new constraint has emerged: data. Data has long been the neglected discipline, the weakest link in the toolchain. In fact, most companies are still managing and deploying DB changes manually, anchoring development teams. Many organizations attempt to support modern, agile development environments with decades-old database management processes. The result is like mounting a modern Ferrari on Model T tires. It’s a great way to get nowhere fast.

The costs of ignoring the database release process accrue on many fronts. For your organization to survive, you must employ database automation. Let’s examine the impact upon four key areas.

1. Cost of adding DBAs

Are you relying on DBAs for all release-related upgrades? Then you’ll need more DBAs as you increase the rate of application releases. If 10 DBAs can handle 100 changes per week, then you’ll need at least 20 DBAs to handle 200 changes per week.

And, as you hire more DBAs to keep up with the work, you’ll also have to hire more managerial people. You’ll have to devote more resources to support infrastructure. You’ll have to invest more in training. And you’ll have to account for the months/years of on-the-job experience that the 10 new DBAs will need to become as efficient as the original 10. The costs add up fast, especially compared to the substantially lower cost of scaling through automation.

2. Mistakes and increased risk

No matter how large your staff of DBAs is, you’re always just one human error away from a mistake. Every DB schema change review and deployment relies upon the skill, reliability, and dedication of a human.

The result is the inevitability of mistakes and less-than-machine-like reliability. The carryover can be an application that significantly underperforms or mis-performs, or that crashes altogether.

The human touch also increases the risks to data integrity and security. For example, grants embedded in stored procedures offer a huge opportunity for human error. Either that or malicious intent will happen to derail an application and expose its data.

3. Diminished productivity

The DBA logjam can continue a backflow of delays that echo throughout the development process. It’s quite common for both development and QA teams to idle while waiting for DB changes to be reviewed and deployed. Our own customers tell us that, in the past, 5% to 10% of their developers’ time was spent waiting on schema changes. Database release automation, when compared to a manual database version control, can help increase your productivity.

4. Opportunity cost

All the above adds up to delays. Slowdowns in development, slowdowns in testing, and delayed releases. And applications simply can’t do what they’re meant to do until they’re released into production.

Whether an application is new, or it’s an upgrade of features and functionality to an existing application, it’s costly not to have that application in production.

If you think application release cycles are moving at a quicker pace, you’re correct. But the pace of application release is nothing compared to what it could be when combined with database automation.

Database deployment automation with application release automation helps companies break the logjam created by today’s manual DB deployment processes. These combined solutions can improve performance, allowing development, testing, and DBA staff to focus on more important projects. Database deployment automation also helps cut down human error while increasing data security and application performance and reliability. Put simply, database deployment automation helps speed up the delivery of better-performing applications into production.

The benefits of combining database deployment automation with ARA are massive. The typical organization can save millions by using Datical DB with existing ARA tools.

To learn more about the benefits of using database deployment automation and application release automation read our whitepaper, The Evolution of Application Release Automation.