Share via

SQL Server Zero downtime/Blue Green deployment

BIFlake 1 Reputation point
2020-11-11T18:04:43.09+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,954 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. David Browne - msft 3,771 Reputation points
    2020-11-11T18:53:28.083+00:00

    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.

    0 comments
  2. Tom Phillips 17,716 Reputation points
    2020-11-11T19:35:08.5+00:00

    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

    0 comments
  3. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2020-11-12T03:13:57.463+00:00

    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