Blue Green Deployments Using a Single Database

By | November 6, 2013

One of the things I’ve been hearing a lot about lately is “blue-green” deployments.  Martin Fowler’s explanation is the one I hear about most often.  You can read his post here.  This graphic is a basic depiction of how the environment is setup and it comes from his website:

blue_green_deploymentsOn the surface, it looks like the basic idea is that you have two sets of production servers.  One is dedicated to running the old application and the other, to the new.  A router sits in front of both environments and basically, the URL the clients access is directed to whichever environment you want on the backend.  For the purposes of this discussion, assume the majority of users start on the blue slice for regular production use.  This enables you to deploy and test the green slice at the same time the blue slice is up and in use.  Then whenever you are ready to cutover, you just make a change in the router, and the slices essentially swap.  But now, all the users are on the new software.  Then you start all over with development on the green slice.

Strictly following Fowler’s architecture, I would need to implement two databases on two seperate servers.  You could very easily have green users working against a set of gold data in a green database.  Sometime relatively close to when you would want to flip the router, you would run whatever ETL processes would be necessary to populate the green database with data from the blue database.  Then, just before you actually flip the router, you would stop any further changes to the blue database data.  Then load any remaining data changes from the blue to the green, that may have occurred since you initially transferred the data (which should be a relatively small, and much quicker operation in theory).  While you wouldn’t have a “zero-downtime” scenario doing it this way, it could be very close to one.

However, in the situation I’m going to describe, the following caveats are required to be dealt with;

  1. Green Users will be using the green slice as a “pilot” environment to do actual production workloads.  As such, all transactions must be recorded.
  2. Updates to data done by Green Users must be available to Blue Users, and vice versa.
  3. All CRUD operations are done through stored procedures.

If I disregard the portion of Fowler’s diagram that specifies a logical topology using two distinct databases, I can conceive of a separation between the blue and green slices within the same database.  The assumption here being that the two slices will share a majority of the database objects, schema, and data.  The actual differences in the database between the two slices should be minimal, at least in quantity (if you’re doing rapid, small, incremental changes).  So now the logical topology looks like the following diagram;

blue_green_shared_db_woschemaNow let’s assume a breaking change like adding a non-nullable column to an existing table needs to occur in the green slice.  A change like this means that I need to maintain the original blue table and add a new green table to the database.  Let’s assume a table structure that ends up as follows:

--Blue Table:
CREATE TABLE [Person].[tbPerson]
(
[SSN] CHAR(11) NOT NULL PRIMARY KEY,
[FirstName] CHAR(20) NOT NULL,
[LastName] CHAR(40) NOT NULL

--Green Table:
CREATE TABLE [Person].[tbPerson_1]
(
[SSN] CHAR(11) NOT NULL PRIMARY KEY,
[BirthDate] DATE NOT NULL,
[FirstName] CHAR(20) NOT NULL,
[LastName] CHAR(40) NOT NULL
)

 

But I still have the same problem in that I need to be able to have the same data available to users of both slices, and changes made to that data, must be reflected in both.  How do I accomplish this without changing the applications in the blue slice?  Since all CRUD operations are done through stored procedures, I could create stored procedures that will insert, update, and delete data from both tables for both slices, and read data from the like color table for each individual slice.  To keep management simple, I will keep all the stored procedures in an interface schema named iPerson.  So now the topology looks like this:

blue_green_shared_database

Now let’s assume that I had a basic stored procedure in the blue slice to add persons to tbPerson.  The code looks like this:

USE [BlueGreenDB]
GO   CREATE PROCEDURE [iPerson].[usp_AddPerson]
     @ssn char(11),
  @firstname char(20),
  @lastname char(40)
AS  
DECLARE @ROWS int = 0
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Person].[tbPerson]') AND type in (N'U'))
    BEGIN
INSERT INTO [Person].[tbPerson]
([SSN], [FirstName], [LastName])
VALUES
(@ssn, @firstname, @lastname)
SET @ROWS = @ROWS + @@ROWCOUNT
END
RETURN
 @ROWS
GO

This won’t work for inserting a person into green tbPerson_1 table because of the additional non-nullable BirthDate column.  At first glance, you might think you could simply create a new stored procedure for tbPerson_1 and name it something like [iPerson].[usp_AddPerson_1].  But that doesn’t solve the requirement that data that is inserted into either table is inserted into the other at the same time.  If you did that, the application that is using the green side would be aware of the new stored procedure, and so would be able to update both the blue and green tables.  But the application running on the blue slice, would not know about that stored procedure.    The way I will solve this is by altering the existing stored procedure so that both the green and blue slices use it.  To work, it needs to update both tables, and since the applications in the blue slice will not be trying to insert a birthdate, which is a non-nullable column, the stored procedure will need to accommodate that.  Here is one way of writing this:

CREATE PROCEDURE [iPerson].[usp_AddPerson]
@ssn char(11),
@birthdate date,
@firstname char(20),
@lastname char(40)
AS
DECLARE
 @ROWS int = 0
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Person].[tbPerson]') AND type in (N'U'))
BEGIN
INSERT INTO [Person].[tbPerson]
([SSN], [FirstName], [LastName])
VALUES
(@ssn, @firstname, @lastname)
SET @ROWS = @ROWS + @@ROWCOUNT
END

-- If no birthdate was specified, set it to the default value before inserting
SET @birthdate = COALESCE(@birthdate, '1800-01-01')
INSERT INTO [Person].[tbPerson_1]
([SSN], [BirthDate], [FirstName], [LastName])
VALUES
(@ssn, @birthdate, @firstname, @lastname)
SET @ROWS = @ROWS + @@ROWCOUNT

RETURN 
@ROWS

FIrst thing the stored procedure does, is insert into tbPerson, if it exists.  I’ll explain that in a moment.  But since all the data that is in tbPerson will also be in tbPerson_1, it makes sense to insert it there first.  Next, it checks to see if the birthdate was specified.  If not, it sets it to a default value, and then inserts all of that into tbPerson_1.  The reason it checks for the existence of tbPerson is that eventually, when we no longer need that table, we’re going to drop it.  With this existence check, if someone misses altering the stored procedure to reflect that the table is gone, it will continue to function.  You’ll also note that the @ROWS returned is a cumulative value.  I would expect if both tables are still in use that any execution of this stored procedure would return 2.  If tbPerson has been dropped, it should return only 1.

Go on to Part 2.

One thought on “Blue Green Deployments Using a Single Database

  1. Pingback: Blue-Green Deployment With a Single Database | Bozho's tech blog

Leave a Reply

Your email address will not be published. Required fields are marked *