I am excited to say that over the next two weekends I am speaking at PASS SQL Saturday events in Southampton and Slovenia on Continuous Integration and the Data Warehouse.  Over the coming weeks I hope to provide some supporting posts outlining exactly how to set up CI in the data warehouse context.  However, I am taking a new contract next week, so my time may be sparse!

In my view, the key to implementing Continuous Integration is to move away from the old migration-based development of your SQL database to the declarative model approach offered by SQL Server Data Tools (SSDT).  In my opinion, without this transition you will be fighting an up-hill battle!

I recently attended a SQL Supper session by Alex Yates of RedGate where he discussed the pros and cons of state-based declarative model versus migration-based database development strategies.  Although he outlines the problems inherent in both approaches, he did not outline how SSDT and good source code management overcomes all these issues as his talk was tool-neutral.  He is also speaking at Southampton, so please go along.

With the declarative model approach, problems typically arise when you:

1. Add a non-nullable column to an existing table which contains data
2. Rename an existing column
3. Two developers change the same objects at the same time one adds a new column to a table and a related proc, and the other developer renames the same table. This is the example Alex uses in his presentation.

I am sure there are a few other scenarios, but none immediately come to mine on this a cold November morning.

SQL Server Data Tools overcomes these issues by the following specific functionalities:

1. Simply select “smart defaults” when you publish your database.  This will add a new nullable column to the table, then populate it with zeros and finally make it non-nullable.  All automatically!  You can then run your own data fix to update the zeros with the correct values. Clearly on a table containing a large amount of data, populating the new column with zeros will take a long time, so you may want to think about your deployment methodology. 
2. Use the Refactor-Rename option on the SQL menu; but remember to check in the newly created refactor file!
3. Of course, when the second developer checks the code into source control, he will be made aware of conflict and will have to merge his changes into the branch. If he does it wrong, SQL Server Data Tools will immediately identify that he broke the related stored proc, particularly when he compiles the project.  Even if he checks in the broken code, your continuous integration environment will pick up the flaw and highlight the broken code.  If you don’t have CI, come and find out how to implement it at my talk!

So please come along to one of my PASS SQL Saturday presentations or watch this space for more on Continuous Integration and the Data Warehouse

http://sqlblogcasts.com/blogs/drjohn/archive/2015/12/01/continuous-integration-and-the-data-warehouse.aspx