Managing database schema’s and keeping stored procedures up to date can become quite complex during long term development. We have ways to version control source code but how do we version control the database schema and other stuff like stored procedures?

How can we cover this in our automatic deployment setup?

The goal is to never use SQL Management Studio during development and instead rely on updating version controlled .sql files and let these make updates to the database during deployment.

This post covers how to export Microsoft SQL Server stored procedures, adding them to our solution, managing schema updates and deploying changes using Phantom with MSDeploy

1. Add to version control

First we needed to export all store procedures from our database and add them as .sql script files to our solution project. This way they can be version controlled together with our project and later be used to update any target database when we make a deploy.

1. Right click the database
2. Choose Tasks
3. Generate Scripts
4. Click Next
5. Select specific database objects
6. Select all stored procedures
7. Click Next
8. Select “Single file per object”
9. Advanced > select “Include if NOT EXISTS”
10. Advanced > select “Script DROP and CREATE”
11. Advanced > deselect “Script USE DATABASE”
12. Choose where to dump the .sql files
13. Click Next
14. Click Next
15. Done!

Add the .sql files to your solution. The sync.sql is where we keep all the schema updates.

-- Example sync.sql

-- 2011-05-03 : Added author to Article
--
if not exists (select 1 from INFORMATION_SCHEMA.columns where table_name = 'Articles' and column_name = 'Author')
	alter table [Articles] add [Author] [nvarchar](255) NULL

-- 2011-03-26 : Added Date to Source
--
if not exists (select 1 from INFORMATION_SCHEMA.columns where table_name = 'Sources' and column_name = 'Date')
begin
	alter table [Sources] add [Date] [datetime] NOT NULL
        alter table [Sources] add constraint [DF_Sources_Date]  DEFAULT (getdate()) for [Date]
end

2. Modify deploy procedure

We need to add steps to our deploy process where these script files are executed towards our deploy targets. Since we are using Phantom I added something like the following to our build.boo script. The reason why I chose to combine all .sql files into one big one was because it takes a very long time to execute 50+ such files one by one using MSDeploy.

//file: build.boo

import System.IO

solution_file = "./example.sln"
configuration = "release"
output_path = "./output"
sql_file_path = "c:/tmp/bigfile.sql"

target default, (build, clean):
  pass

target build:
  msbuild(file: solution_file, configuration: configuration, properties: {"OutputPath": output_path})

target deploy, (build, deploy_codebase_to_some_machine, update_sql, clean):
  pass

target deploy_codebase_to_some_machine:
  ..deploy stuff <snip>

desc "combine all .sql files into one big one"
target build_sql_script:
	file = File.Open(sql_file_path,FileMode.Create)
	writer = StreamWriter(file)
	with FileList("./.sql"):
		.Flatten(true)
		.Include("**/*.sql")
		.ForEach def(file):
			writer.Write("\n\n")
			writer.Write(File.ReadAllText(file.FullName))
	writer.Close()

target update_sql, (build_sql_script):
	msdeploy(verb:"sync", source: {"dbFullSql":sql_file_path}, dest: {"dbFullSql":"data source=127.0.0.2;initial catalog=Hermes;password=god!secure;persist security info=True;user id=admin;packet size=4096"})

target clean:
  rm(output_path)
  rm(sql_file_path)

3. Invoke

This script can be used to perform a deploy, complete with schema updates and stored procedures by invoking:

phantom.exe deploy

- or to only update schema and stored procedures:

phantom.exe update_sql

All that is left is to add this to the build server, add some triggers and you are all set. good luck!