How to synchronize deployments (especially of database object changes) on multiple environments [duplicate]

Question

I am the DevOps engineer and a software engineer in a team where months back, the developers moved from having a central Oracle DB to having the DB on a CentOS VM on their individual laptops. The move from a central DB was to reduce dependency on the DBAs and also to eliminate issues that stemmed from inconsistent data.

The plan for sharing and ensuring synchronization of the Database with everyone on the team was that each person will share change scripts with everyone. The problem is that we use Skype for communication (we just setup slack but are yet to start using it fully), and although people sometimes post the text of DB change scripts, it could be missed by some. The other problem is that some developers miss posting the changes. Further, new releases are deployed in Production without being deployed on the Test and Demo environments.

This has posed a serious challenge for us, especially myself who of recent, became responsible for ensuring that our Demo deployments were in sync with the Production deployments. Most of the synchronization issues border on the lack of sync of the Database due to missing change scripts or missing DB objects. Oracle is our DB of preference.

A typical deployment in the Demo environment is a very painful process that involves testing an application and as issues occur due to missing DB table columns, functions, stored procs, we have to look for the missing DB objects, apply them to the DB and then continue until all issues are resolved.

How can I solve this problem to ensure smooth, painless and less time-consuming deployments? Can migrating our applications to Docker help with the DB synchronization issues and the associated lack of discipline of the developers? What process can we put into place to improve in this area?

Answer 1

I would integrate the schema management into the application itself (or along with it).

Any change to the schema should be committed along the application code (and hence tagged also).

There's already a bunch of possibilities listed in this question: What practices or tools enable Continuous Deployment of Databases

With this kind of tools, using an in memory database like h2 or a central database and having the application check the schema version (stored in the DB) when started allow to get an updated database schema at application startup.

This will not solve your discipline problem and may create new ones, as this usually comes with a strict versioning of the schema, enforcing the version can be done with pre-commit hooks but that's a hard work.

Answer 2

In our company we manage our application code within a VCS (Git) and most of the applications we work with install their core database from within the setup scripts that ship with the application.

If we would have to extend or customize an application for one of our customers which involves having to extend the database or do database customizations, then we will accompany our code with a setup script that will perform the database operations through the application's database layer/wrapper/driver.

This way, once the code is merged into the VCS repository, every developer that will check out this code, will retrieve these database changes. Also after the code is merged, these database changes will end up on every environment (test, UAT and production nodes) and this way all database changes will roll out smoothly.

This is an easy to implement method. You could also dive into source control tools specific for databases if you need more advanced management possibilities.