A phase of my Agile Blue/Green deployment project is now complete, as I have a Proof of Concept which has been tested successfully. As you may recall from prior posts, the Blue/Green technique features two databases, the “green” live one that users connect to (I’ve made an arbitrary assignment of colours), and the offline “blue” one which has a different version of the schema (a previous or future release).
The idea is to upgrade or downgrade a schema version very quickly by cutting over from Green to Blue. Fine – but often a database is coupled to multiple applications and data feeds. It’s too big for the upgrade to be done from a backup file in normal downtime. This situation is reminiscent of “Achilles and the Tortoise” from Zeno’s Paradoxes. While the Blue database is trying to catch up, the Green receives new data, which will also have to be loaded into Blue before it goes live… and so on. The same applies in the other direction. Once you’ve promoted the Blue database, there’s no going back.
In my Proof of Concept, each top-level Transact-Sql command issued against the Green database is recorded in a third database named “Journal”. This captures the DML operation type, the target table, the date/time, and also preserves the sequence in which the statements executed. With this information a stored procedure in the Blue database can “play back” the changes and catch up with the Green one. Performance testing has barely started but I can at least say that inserts of 20M rows completed with no obvious problems.
I have used triggers for this. Any other approach could miss queries run directly against tables. Yes, all updates should be done through some kind of API, and no doubt they generally will be. When is that rule most likely to be broken? This will probably be when the application is broken and needs fixing as soon as possible. Isn’t that also the time when we most need a reliable back out option if the fix doesn’t work? That’s why we can’t afford to miss any changes. Could we use log shipping or a restore from the transaction log instead? This is much more appealing than using triggers; it would be simpler and would save a lot of coding. The problem is that by definition the Blue and Green schemas will be different, which makes these techniques impossible to use.
With a major architectural change it’s very important to widen out the discussion early on. Any seasoned developer will want a second opinion on performance and on whether the more obscure Transact-Sql statements will work as advertised. This is something the DBAs can help with.
More subtly, developers may realise that some of the new components could be used for more than one purpose. In this case, the data capture tables could also serve as a change log for auditors. They would also provide a very realistic set of inputs for application testing, and for troubleshooting in Production. Is any of this an duplication of something we already have? Is it an opportunity that should be exploited – or just scope creep? An early discussion with the DBAs (and of course with the people commissioning a commercial project) can help avoid these problems. This is what I’m intending to do in the next stage of the project.