What You’ll Learn

  • How to select the best tools for automated database deployment.
  • The advantages and challenges of script-based vs. state-based schema migrations.
  • Best practices for standardizing database deployment across platforms.
  • The benefits of treating database code like application code for agile, secure releases.

In recent years, as more and more companies have adopted agile and DevOps tools for software development, businesses have realized that time-to-market is more crucial than ever. These new application development tools and tactics have enabled not only faster deployment, but also more responsive solutions for their customers. However, to ensure that releases are also safe and secure, database release automation necessarily had to catch up with application development. Yet, despite great gains in database deployment automation tools, the majority of enterprise level organizations today are still updating databases through manual SQL script execution, requiring a massive reliance on the skill (and loyalty) of overworked DBAs and database engineers.

The primary reason that database automation initially had a hard time keeping up with application automation was purely due to the need for data integrity. When data is involved, it’s not possible to simply copy over an older version of database code, as application code does (often to correct errors). So, database change management has been particularly challenging, given the need for secure and safe database changes.

How to choose the best automated database deployment tool?

When considering the best automated deployment tools for databases, the first step might be to seek out a database versioning tool (or database source control tool), which enables Devs, DevOps and DBAs to collaborate effectively and make changes efficiently. This significantly minimizes errors while also building and planning a release. Version control unfailingly helps avoid conflicts and prevents them from reaching the release automation process. This database source control tool tracks all changes made, which is invaluable for its ability to provide visibility into what, where and why changes were made, as well as who initiated that change. SQL script deployment automation, and the ability to automatically generate and test database change scripts, is extremely beneficial and a great time-saver.

Pro Tip: Prioritize database source control tools that allow you to track every change and provide complete visibility, reducing the risk of conflicts during releases.

Migrating schema as part of an automated database deployment

There are two ways to go about database migrations, and there are “pros” and “cons” to both of them. The first method is called script-based migration. This is when you take a single script and run it on every environment. The pro side of script-based migration is that it’s repeatable, because it’s exact. And one thing that developers and DBAs like is precision. So, every time you execute that script, you should get the same result. The con is that, although the script may work in one environment, it isn’t repeatable across several environments, due to differences between these environments – usually referred to as configuration drifts. This is why state-based migrations were created.

Pro Tip: To reduce risk, perform impact analysis before deploying any state-based migration scripts in production, ensuring no unexpected configuration issues.

State-based migrations are very good in recognizing the differences between two environments. So, for instance, you may diff QA to Prod in order to see what’s changed. You may have a table in the first and a procedure in another state that are not the same. So, from this diff it’s possible to generate code automatically, which is a great time saver. But state-based migration also has a con. It’s not repeatable.

If you diff Dev to QA, and then you diff Dev to Pre-prod, you’re likely to get different migration scripts. That means that your state is going to be the same, but the way you get to it, might differ. In this way, your migration is not really tested and you lose the ability to have a repeatable release. This is essentially the “con” with state-based migrations.

So, script-based migration is reliable for being repeatable, while state-based migrations are very good for generating scripts. If that state-based migration script hits a database that was changed, it’s likely to cause unexpected results. Furthermore, when a script hits an environment that is different than what’s expected, probably the worst outcome would be that it would break something, but it wouldn’t report a problem. That could create lasting damage if it goes unnoticed. Changing a procedure from one environment might mean overriding someone else’s hotfix. Without an error alert at that crucial time, the failure could go unnoticed for days or weeks. And that damage could accumulate. These are important concepts when evaluating database deployment automation tools.

With regard to schema migration, DBmaestro uses the best of both worlds, by employing state-based migrations to generate scripts early in the pipe. So, state-based migration tactics are used on the left side of the pipeline, moving to script-based going forward.

Once the script is created, DBmaestro makes it immutable. That makes it repeatable. So, we are moving from state-based on the left side of the pipeline to script-based on the right. And in order to ensure that a script-based migration does not hit a changed or drifted database, DBmaestro automatically runs an impact analysis on the objects that are about to be changed with a state-based validation to the expected configuration. At that point, it becomes a repeatable process. In other words, DBmaestro has figured out how to use all of the pros, and none of the cons – the best of all worlds.

Speaking of repeatability, database deployment automation and CI/CD processes should always be the same, no matter the size of the organization. Yet, larger organizations often seem to differ by what tools they employ in their tool chain, when contrasted against smaller organizations. For example, smaller organizations often use tools like Jenkins for CI as well as the CD process, while larger organizations regularly use different tools on the left side of the pipeline, compared to the right.

Larger companies may decide, for instance, that they prefer UrbanCode or Azure DevOps for the CD because they like the additional security and/or role management built in. That’s part of the reason DBmaestro realized it needed to work across all of these tools. So, whether it’s Jenkins, Jira, ADO, Gitlab or dozens of other CI/CD tools, DBmaestro is focused on providing end-to-end CI/CD processes for optimal and efficient deployment, no matter what tools are used.

DBmaestro also supports a multitude of databases, including: Oracle, MSSQL, MySQL, PostgreSQL, Snowflake, MongoDB, DB2, Redshift, Snowflake and others. The goal has always been to create a solution that would help standardize anything and everything.

Standardize everything

DBmaestro is committed to creating a solution that will help standardize everything in every tool stack. However, some have questioned the importance of having a CI/CD solution for ALL tools, like MongoDB for instance, as Mongo doesn’t use a schema. To standardize everything, everywhere, DBmaestro made the decision not to exclude any database. MongoDB may not use schemas, but it does require maintenance, as well as the creation of indexes and/or retrofitting a schema to various structures. When you want to have a traceable, audited and compliant process, you will want to have everything done in the same place. In other words, everyone should want to have standardization, consistency and control over everything, as well as compliance of everything in the same place.

Why can’t database code be like application code?

Developers like to move quickly while database administrators like to move safely, which has historically meant slowly. Database deployment automation brings repeatability and standardization to the application delivery process, enabling both speed and security. In essence, automated database deployment tools bring the same automated, agile process that dev teams use to release applications. Waiting until the production environment to find out if proposed database changes were satisfactory is not only risky but can also be terribly time-consuming. Moreover, antiquated manual database releases are completely avoidable today. By following the structured process inherent in automated database deployment, DBAs and developers will identify conflicts and issues early in the process, resolving the issues much more easily and at a much lower cost.

Key Takeaways

  • Automated database deployment is essential for secure, fast releases, particularly in agile and DevOps environments.
  • Combining script-based and state-based migrations optimizes for both repeatability and flexibility.
  • Standardization across databases improves compliance and simplifies complex deployments.
  • Treating database code like application code promotes collaboration, speed, and risk reduction in database changes.