Continuous integration (CI) is the practice in software engineering of merging all developer working copies of code to a shared mainline several times a day.  (source: Wikipedia). This is quite linear for software development. You should have a source control versioning system where all programmers commit their changes. Afterwards, an automated build should be executed to check if the code’s compilation is not broken. Preferably, you should also have unit tests triggered to pass/fail the new build. You can see the flow in the image below.

What about database development? In most projects, developers aren’t focused on database development and for proper CI, the database version should keep neck to neck with the application builds. 

Regarding Microsoft's SQL Server, the discussion is out there. Other than that discussion, a quick research on the Web will find you specialized tools and how to use them. It's all very well said, but: should I trust developers to keep an updated version of the database schema under the SVC?

• What if my project doesn't use SVC for the database?
• Should the database be recreated each time an integrated build is triggered, loosing test data? 
• Can I bring the database CI task over to the DBA team, assuring all changes are forwarded to the build? 

The answer to the last question is yes. And you can do it using just Microsoft free tools, the SSDT: SQL Server Data Tools.

The scenario I propose to you is as follows. You've got a single development database as a back end for all the programmers developing their code. That database will be the development baseline. You'll have another database for the build derived from the application's CI scripts. that will be the CI baseline. You'll have to trigger a process that:

• Extracts the development baseline;
• Extracts the current build baseline;
• Compares both baselines;
• Generates changes script;
• Runs changes on the build baseline.

You can keep the changes scripts, so you'll have a matching diff file for each build version.

First, download and install the SSDT from Microsoft. On this article, we'll use SSDT for SQL Server 2014. Second, configure the system path (in my case "C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin") so we'll be able to call the tools from the command line. For the sake of example, let's use the Northwind database. Download and restore it twice naming it:

• AdventureWorks2014_DSV: the development baseline
• AdventureWorks2014_BLD: the build baseline

Assume that we'll make some changes to the "MaritalStatus" table:

CREATE TABLE Person.MaritalStatus
 (
 MaritalStatusID smallint NOT NULL,
 Description varchar(15) NOT NULL
 )
GO
ALTER TABLE Person.MaritalStatus ADD CONSTRAINT
 PK_MaritalStatus PRIMARY KEY CLUSTERED 
 (
 MaritalStatusID
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON)
GO
ALTER TABLE Person.Person ADD
 MaritalStatusID smallint 
GO
INSERT INTO [Person].[MaritalStatus]([MaritalStatusID],[Description]) 
VALUES (0,'Single');
INSERT INTO [Person].[MaritalStatus]([MaritalStatusID],[Description]) 
VALUES (1,'Married');
INSERT INTO [Person].[MaritalStatus]([MaritalStatusID],[Description]) 
VALUES (2,'Divorced');
GO
ALTER TABLE Person.Person ADD CONSTRAINT
 FK_Person_Marital_Status FOREIGN KEY
 (
 MaritalStatusID
 ) REFERENCES Person.MaritalStatus
 (
 MaritalStatusID
 ) ON UPDATE  NO ACTION 
  ON DELETE  NO ACTION 
GO

Now, let's start using the sqlpackage utility. We'll package the baselines using DACPAC files. A DAC is a self-contained unit of SQL Server database deployment that enables data-tier developers and database administrators to package SQL Server objects into a portable artifact. To extract the baseline from the development database, open a command prompt and run:

sqlpackage.exe 
 /a:Extract 
 /scs:Server=localhost\sqlexpress;Database=AdventureWorks2014_DSV;
  /tf:F:\SSDT\AdventureWorks2014_DSV.dacpac

To extract the baseline from the build database, run the same command but changing the target and dacpac name:

sqlpackage.exe 
 /a:Extract 
 /scs:Server=localhost\sqlexpress;Database=AdventureWorks2014_BLD; 
 /tf:F:\SSDT\AdventureWorks2014_BLD.dacpac

Having both baselines extracted, we'll compare them both to generate a script with the changes from development to build:

sqlpackage.exe 
 /a:Script 
 /sf:F:\SSDT\AdventureWorks2014_DSV.dacpac 
 /tf:F:\SSDT\AdventureWorks2014_BLD.dacpac 
 /tdn:AdventureWorks2014 
 /op:F:\SSDT\AdventureWorks2014_Update.sql

Finally, we can run the changes, updating the build to the same baseline as the development database:

  sqlcmd.exe 
 -S localhost\AdventureWorks2014_BLD 
 -i F:\SSDT\AdventureWorks2014_Update.sql

All thers left to do is to automate these steps on a single script and run it periodically or triggered by a build server such as TFS. I've built a simple PowerShell script that performs the steps and saves the changes files with a timestamp, keeping a history:

#Generate filename
$timestamp = get-date -f MM-dd-yyyy_HH_mm_ss;
$filename="C:\scripts\"+$timestamp+".sql";
#Extract baseline
sqlpackage 
"/a:Extract" 
"/scs:Server=localhost;Database=AdventureWorks2014_DSV;" 
"/tf:C:\scripts\AdventureWorks2014_DSV.dacpac" 
#Extract Target DB
sqlpackage 
"/a:Extract" 
"/scs:Server=localhost;Database=AdventureWorks2014_BLD;" 
"/tf:C:\scripts\AdventureWorks2014_BLD.dacpac"
#COMPARE & GENERATE the Delta script 
sqlpackage 
"/a:Script" 
"/sf:C:\scripts\AdventureWorks2014_DSV.dacpac" 
"/tf:C:\scripts\AdventureWorks2014_BLD.dacpac" 

This scenario has a limitation worth pointing out. It suitable for schema changes only, with no regard for data and, more importantly, constraints over the data. This limitation is referenced on MSDN: Database projects and .dacpac or .bacpac packages cannot be the source or target in a data comparison.

We've altered the Northwind database to have a new table called "MaritalStatus", added a column to the "Person" table and a foreign key constraint. However, the constraint can't be checked until we load the reference data. Hence the nullable value on the MaritalStatusID foreign key.

So, is anyone doing CI with SQL server yet?