Agile Continuous Delivery of Databases (3)

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). Continue reading

Advertisements

Agile Continuous Delivery of Databases (2)

{Previous Article in Series}

Most applications include a database, and the goal of the project I’m working on is to make Agile continuous delivery possible in the database as well as in procedural code. The peculiar problem with databases is that when a change deployment fails we risk losing data that has been added since the release.

The “blue-green” deployment model is a way of dealing with this. We cut over to an “old version” database. The code and schema of this database pre-date the release, but it has received all the data changes since the release.

How can this be? Continue reading

“Test-First” Database Development (2)

I’ve made additions and changes to the GitHub repository for “Test First” development of the database schema. All procedure names will now start with “Test”, so that they’re easier to find in Object Explorer and Intellisense. I’ve added a “TestHeader” stored procedure. You can put this at the top of your TDD script and it’ll add the date/time, database user, Sql Server version etc., to your results.

The spObjectExists procedure has been replaced by TestObjectExists, with the following signature:
CREATE PROCEDURE [dbo].[TestObjectExists]
@i_ObjectType sysname
,@i_ObjectName sysname = Null
,@i_ParentSchemaName sysname = Null --not needed if testing schema's existence
,@i_DbName sysname = Null
AS
Continue reading

“Test-First” Database Development (1)

I’m looking at the practicalities of using Continuous Delivery/Agile techniques where the database is a “high scale” project in Agile terms, and therefore relatively difficult (for more detail about this Agile terminology refer to Scott Ambler’s writings).

As well as thinking about the big issues, I want to start putting together some of the pieces to make it work. I’m doing this in Sql Server 2014 Transact-Sql. I’ve started with automated testing of reporting procedures. There may be utilities that can do this; finding suitable tools is part of the research. It still seems worthwhile having alternatives in Transact-Sql if possible. There’s no purchase or installation to approve, and the code is easy to understand and to edit. Continue reading

Agile Continuous Delivery of Databases (1)

Agile and Continuous Delivery techniques have been applied to databases for some time, and it’s clear they offer big advantages. Some types of application are particularly suited. They’ll be well-represented among the successes and case studies. Others, such as those including a complex relational database, don’t fit the mould quite so easily. These would be in the “Agility at Scale” category, and often in use within large enterprises.

How does our database project fit in? If Agile wouldn’t be an easy win, can we still get some benefit? I’ve been thinking about that, because the data warehouses I work on are definitely not easy wins. I’m jotting down some notes here, not a formal document, and I hope to develop my thinking in future blogs. Continue reading

Continuous Integration and Delivery in Databases

As you’ll know if you’re a database developer, the whole “continual integration and delivery” philosophy tends to get much more complicated when you have to preserve data and aren’t simply deploying code. This problem will be solved of course; the two podcasts linked below give some clues as to where the solution might come from. Continue reading