Blue/Green Deployments in SQL Server

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.



Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?