A solution for managing SQL schema changes and stored procedures in TFS featuring automated testing, gated check-ins and continuous integration.Design Goals
The schema change service was built to support the development of a complex web application where a significant portion of the complexity is in SQL Server and outside the control of our development process. We wanted versioning, history, branching & merging, continuous integration w/gated check-ins, automated testing and one-step deployments just like we have with our C# code. Peripheral goals include:
- Allow developers and testers to easily maintain local SQL Server instances for building and running any version of the application locally
- Enforce all schema changes to be scripted, no dependencies on magic sync programs
- Enable gated check-ins for schema change scripts, do not allow schema changesets that break stored procedures and vice versa
- Provide SQL updates for CI/CD environments
This project consists of a service and a TFS work item type. The work item type facilitates submitting schema changes for the service to test and check-in to source control. The service processes schema changes by executing them in a test database and maintaining consolidated schema update scripts.
Simplified flow diagram:
Each schema change is assigned a sequential unique id, copied to a file and checked into source control. The service combines all of the changes into a single script with some additional logic to update from any previous version to the latest version. The script works by stashing various metadata in a separate database and keeping track of the branch and version of each database it has updated.
Stored procedures are kept in individual files in source control and modified like any source file. The service merges them into a single script that drops and recreates them all. The service regenerates the two scripts (<schema>_structure.sql and <schema>_sprocs.sql) and updates them in source control every time a schema change is processed.
Optionally, the service will regenerate the DDL after each update and check the complete script into TFS as a changeset to a single file providing a clear history of schema changes over time.
Schema change work item:
Work item process flow:
Example update script:
-- SCHEMA CHANGE Version 2: [ Created by Brian Vaquilar on 5/3/2012 1:25:13 PM ] FOR TFS ITEM 15663
EXEC dm4main.SCH.RegisterSchemaChange @changeID=2, @targetID=1, @disable=0, @rerunfail=0, @clientID=NULL, @changeText='--test em yay '
-- SCHEMA CHANGE Version 12: [ Created by Jerry Riggle on 5/14/2012 1:20:09 PM ] FOR TFS ITEM 15776
@changeID=12, @targetID=1, @disable=1, @rerunfail=0, @clientID=NULL, @changeText='IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = Object_id(N''[EM].[DocumentTypeIndex]'')
AND type IN ( N''U'' ))
CREATE TABLE [EM].[DocumentTypeIndex]
[Id] INTEGER NOT NULL PRIMARY KEY,
[Enum] NVARCHAR(100) NOT NULL,
[Name] NVARCHAR(100) NOT NULL
(id, enum, name) SELECT 0, ''EMDT_Tactic'',