Some time ago I read an article on the importance of version control for databases by Jeff Atwood. I knew that in our team we had to start using something like this because updating different databases to a new release took a lot of time. We always used sql compare to look for the difference between the two databases and to update the database. Sometimes, with small changes, this wasn’t that bad, but with big differences and with data that also needed to be inserted, it was a nightmare. And if you had done one database, you needed to do it all over on another… very bad, very bad, but hey, we learn, we learn. Now I’ve created a C#.NET Console Application that handles most of the things described in the blog of Jeff Atwood. It’s certainly not done and lots of things can be improved, but due to a lack of time right now, this will currently have to do it. It does its job and that’s all we needed. This is the source code and this is the release build. In order to follow this serie of posts, you’d have to read the article by Jeff Atwood because I will not repeat everything he has said. In the source code zip there’s a folder lib with libraries I used and a folder files with the files I will explain further on.

What’s not implemented?

• Setting up a database from scratch using the baseline scripts is not implemented.
• Configuring more things like folder structure
• Support for databases other than mssql
• Views are not implemented as we do not use them yet in our application. Implementing them however is pretty easy and you should be able to do it yourself with the source code.
• …

How does it work?

First you have to have a certain folderstructure because I haven’t had time to configure that. Here’s a screenshot of how it should look:

Screenshot Folder Structure

I quickly wrote the program so that we could use Database Version Control as quick as possible. Because of that default it works with this folder structure. Under Program is the Release build of the C# Console Application. Under the root of Scripts are the update scripts along with some files I’ll discuss later. Under the functions and storedprocedures are the create (no alter scripts because the sp’s and functions are removed when running the exe!) scripts for functions and stored procedures.

How does the root of Scripts look like:

Screenshot Files under Scripts folder

• DropStoredProceduresAndFunctions.sql: Script that will be used to delete all sp’s and functions out of the database. This will be necessary before creating all sp’s and functions.
• excludeList.csv: This is a text file with comma seperated values. If one of the values appears in the name of a sp or function, then this sp or function will be ignored (will not be deleted and not be created).
• sc.xx.xx.xxxx.sql: The update scripts. The name is important as the exe depends on that.
• sc.template.sql: This is a template script that is best used to create an update script

Explanation of scripts

DropStoredProceduresAndFunctions.sql script:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

-- variable to object name
DECLARE @name VARCHAR(1000)
-- variable to hold object type
DECLARE @xtype VARCHAR(20)
-- variable to hold sql string
DECLARE @sqlstring nvarchar(4000)
DECLARE SPViews_cursor cursor FOR
SELECT QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME)
AS name, ROUTINE_TYPE AS xtype
FROM
INFORMATION_SCHEMA.ROUTINES
UNION
SELECT QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) AS
name, 'VIEW' AS xtype
FROM
INFORMATION_SCHEMA.VIEWS
 
OPEN SPViews_cursor
fetch NEXT FROM SPViews_cursor INTO @name, @xtype
while @@fetch_status = 0
BEGIN
-- test object type if it is a stored procedure
IF @xtype = 'PROCEDURE' ###
BEGIN
SET @sqlstring = 'drop procedure ' + @name
EXEC sp_executesql @sqlstring
SET @sqlstring = ' '
END
-- test object type if it is a function
IF @xtype = 'FUNCTION' ###
BEGIN
SET @sqlstring = 'drop FUNCTION ' + @name
EXEC sp_executesql @sqlstring
SET @sqlstring = ' '
END
-- test object type if it is a view
--if @xtype = 'VIEW'
-- begin
-- set @sqlstring = 'drop view ' + @name
-- exec sp_executesql @sqlstring
-- set @sqlstring = ' '
-- end
-- get next record
fetch NEXT FROM SPViews_cursor INTO @name, @xtype
END
close SPViews_cursor
deallocate SPViews_cursor

I’ve got this from here. The ### tokens will be replace by transact-sql by the program to exclude sp’s and funtions with certain keywords (excludeList.csv).

Example of an update script:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

/*
Comment
*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error INT)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
 
-- sql code
 
GO
 
INSERT INTO [dbo].[SchemaChanges]
([MajorReleaseNumber]
,[MinorReleaseNumber]
,[PointReleaseNumber]
,[ScriptName]
,[DateApplied])
VALUES
('03'
,'01'
,'0011'
,'Comment'
,GETDATE())
 
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO

These sort of scripts will be executed by a SqlCommand (DbCommand from Enterprise Library). We are still experimenting and have seen that the Transactions do not work. Somehow there’s a difference between these kind of scripts being executed in Management Studio and being executed by a SqlCommand. I think we’ll have to use TransactionScrope in the C# Console Application to make the transactions work. These Transactions are important because if only a part of the script would be executed, then the build would be broken. You would then have to restore a previous backup, fix the script and run the exe again against the database. Later on I’ll talk about some best practices to use database version control. The GO statements are also a program and the program filters them out before executing the script (again with some other keywords that cause problems). In a couple of months, when we have been able to work things out a little better, I’ll post a new version. For now, this is what you get .

In the script there’s place to put your sql code and then there’s an important part, updating the SchemaChanges table. Each update script inserts a new record in the table SchemaChanges so that the update gets registered in the database. With this table, the program can check what the last update was and look for new scripts in the Script folder. It’s very important that the values you insert into the SchemaChanges table correspond to the name you gave to the file, otherwise the whole thing will not work correctly.

sc.template.sql script:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

/*
Commentaar
*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error INT)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
 
-- Plaats SQL Code hier
 
GO
 
INSERT INTO [dbo].[SchemaChanges]
([MajorReleaseNumber]
,[MinorReleaseNumber]
,[PointReleaseNumber]
,[ScriptName]
,[DateApplied])
VALUES
('00'
,'00'
,'0000'
,'Short Description'
,GETDATE(), 'Delete this parameter (is to make sure you change the version numbers')
 
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO

This is the template, which will surely change in the next months. The INSERT query will not work like it is written because there’s a value too much. I’ve done this so that my teammembers wouldn’t forget to edit the release numbers.

Explanation of the program and source code

Let’s start of with explaining how the exe (DatabaseVersionControl.exe) works:

Parameters that are required (one):

-connectionString=”Data Source=OMITTED;Persist Security Info=True;Integrated Security=SSPI;Initial Catalog=OMITTED;MultipleActiveResultSets=True”

Parameters that are optional (two):

-include “value1,value2,…” : If one of these values are in the excludeList.csv, then they will be overruled so that they will not be excluded.

-scriptsDirectory=”…” : Path to the directory which contains the scripts. Under this folder the two folders storedprocedures and functions must be existing, otherwise the program will not work. Maybe that in the future this can be configured, but for now it’ll do. If this parameter is left out, then the exe searches for the scripts folder at ../Scripts. Change this if you want in the soruce code.

The source code itself is explained in the code. You’ll need some 4.1 enterprise libraries, but I guess you’ll see that if you try to compile the code. I have put them in a lib folder, but the project uses the dll’s from the registry.

We use cruisecontrol to set up a part of continuous integration. One project of cruisecontrol is listening to the scripts svn repository and executes the exe with the proper connectionstring is something has changed.

Best practices that we use in our team

1. Everybody that wants to work at the database has a local database.
2. Before starting to write an update script or change a sp or function, update your svn repository and execute the exe against your local database (and make sure you use the right one if you also have maintenance branches). I create a local bat file for each branch and for the trunk.
3. If you want to change an sp or function, open the corresponding file, edit it, save it and run the exe. If everything works, commit the changed sp or function.
4. If you want to create an update script, copy the template and give it the right name. If the last update script is sc.03.01.0017.sql, then the new one should be sc.03.01.0008.sql or sc.03.02.0000.sql or sc.04.00.0000.sql, depending on what kind of change you are going to make. !Do not commit at this point!
5. Open the change script in an editor or in management studio and put in your scripts. Change the release numbers and put in some comment. Save the script and run the exe. If everything works, commit the change script.

Executing a change script, sp script or function script in management studio is a bad idea. It may work in management studio but not with the exe. In the future we’ll have to check some things that happen with the exe and not with executing them in management studio, but for now we use what we have. If you do want to test changes in management studio you should take a backup before you start doing that. If you think your script is done, then restore your db and run the exe. It’s not fun for other developers if things go wrong on the development database after you committed.