Blue/Green Deployments in SQL Server

Published On: 2021-08-30By:

Blue/Green deployments are a common development practice. This allows you to deploy the application code for the next release and swap to it after testing. This gives you the ability to when you’ve got several servers to deploy the application to, allows you to push the code change to two of the servers, then swap those servers to live while leaving the other two servers with the older version of the code. If there’s a problem with the release the application is able to be failed back, all of this is done with no outage to the application.

In databases, this blue/green deployment technique is much, much harder. This is because making changes to databases takes time, and depending on the change that is being made the change may require blocking in the database while making the change.

Often times the idea that is used for a Blue/Green deployment process is to break the DR link and push the change to the DR server and then pushing the changes to this server. The problem them because that any data which is written to the production database server isn’t being pushed to the DR server. And you can’t write schema changes to one server without pushing the changes to the other server. Stateful systems like databases just don’t allow for this level of flexibility like stateless systems (like websites) do.

We’ve had a client get around these problems by still doing the blue/green release process on their application tier, but on the database tier taking a different approach. On the database tier, they design all of their application code and stored procedures to be able to run even if there are extra columns or extra parameters on stored procedures (they do this by assigning a null default value to the new variables) allowing the application to use either the n version of the n+1 version of the database. This way when the old version of the application code runs the stored procedure it runs without issue even though some parameters are “missing”.

Columns that are to be removed from the database are left in place for 1-2 versions past when the column is no longer needed. The same applies when stored procedure parameters are to be removed. They are left in the stored procedure simply with a so that as the application calls the procedures and uses the parameters their values are simply ignored as the parameter isn’t used.

While this doesn’t give the database true Blue/Green deployments it is probably as close as you can come from a database perspective.

Denny

Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Award 2021 Gold    American Business Award 2021 Bronze    FT Americas’ Fastest Growing Companies 2021   INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award
FT Americas’ Fastest Growing Companies 2020       Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award
Share via
Copy link