Deploying database changes in Azure independently from application changes

Question

Hi

We have a cloud native application in Azure, the stack is python back and and react front end. At the moment the application changes are coupled with the database changes on the same pipeline, a separate tool is being used to push database changes and the tool is closely tied onto application build and release, I am trying to achieve 2 things, be able to rollback database code independently and also implement a blue/green release process.

In the scenario where application build 103 is deployed onto an environment, the tool updates the database version to version/build 103, let us assume there are issues with the deployment of build version 103 onto that environment and a rollback needs to happen (rollback to version 102), in this situation the database code for version 103 needs to be rolled back and so is the application code for version 103, the issue we have is that it can sometimes take a long time to ascertain that there is an issue with a release, at which point rolling back can cause data loss.

What I would like to implement is an independent release process for databases using Azure pipelines, here the database code can have its on separate repository where database changes can be applied independently on specific environments. I would like to understand how others deal with database releases where its closely tied to the application.

Thanks in advance.

Answer 1

True blue/green deployments with databases is nearly impossible. Usually application code is tightly coupled to the database. So it is not possible to deploy or rollback separately.

The main method of accomplishing this in databases is by making sure your changes are backwards compatible. It take a lot more work from devs to make sure this happens.

I suggest you read this:
https://learn.microsoft.com/en-us/answers/questions/159546/sql-server-zero-downtimeblue-green-deployment.html

Answer 2

I agree with Tom. Trying to have application and database independent from each other is very difficult.

I have actually partly implemented this in package that I've written. That is, I permit the client to be ahead of the database, but not vice versa. The code is littered with things like "call this stored procedure if it exists, else use this fallback". There is a very simple reason I written it this way: it is a tool for deployment which needs to be able to upgrade itself. And it may take quite sometime after I made a change, until all databases are updated.

But, no, you don't want this in a normal application.

I would suggest that if you have come that far of Rubicon that you cannot turn back, rather than doing a rollback, you will need to roll forward. That is, make a check in that fixes the bug, and makes the DB changes you need, without data loss.