SQL Server Zero downtime/Blue Green deployment

Question

Hi,
I'm looking for some recommendation for zero downtime deployment or Blue green type option for SQL Server running on Azure VM. Azure SQL Managed Instance could be also be open for another option and I'm looking for a solution design for that.

The use case is for Muti POD SQL Server with AG and minimize downtime (~to zero). Currently, the deployment is done using maintenance window and this is not a desired option.

I could not find any recommendation/best practice for zero downtime deployment for MS yet.

Thanks.

Answer 1

Blue-Green deployment is generally not applicable to database servers.

For zero downtime deployments you can deploy schema and data changes in a transaction so the database changes atomically from one version to the next from the client's POV. This can cause a brief delay in both the deployment process and in the application workload as one or the other waits on locks.

But the real reason to perform deployments in a downtime window is to coordinate the deployment across all the application components.

So if you want to deploy changes to a database without downtime, you must ensure that the running version of the application supports both the old and new version of the database. This sometimes requires non-trivial orchestration of application and database changes.

Answer 2

Doing blue/green on a database is more of a mandate of "no breaking changes", than a technical solution. A database is generally too big to copy to a new server and swap out for every deployment, and not really necessary if you follow the rules of no breaking changes.

I suggest you read this:
https://spring.io/blog/2016/05/31/zero-downtime-deployment-with-a-database

Answer 3

Hi @BIFlake ,

Please refer to the following articles which might help:
Automated blue/green database deployments
Zero-Downtime Deployment with a Database – Introduction

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet--Hot issues October
What can I do if my transaction log is full?--- Hot issues November
How to convert Profiler trace into a SQL Server table -- Hot issues November