All about Business Intelligence

Yes, it is possible to automatically synchronize a SQL Server database with an SVN repository. Every change to a table, stored procedure or other database object will automatically be committed to the repository. New objects will get added, deleted objects deleted, etcetera. As a bonus, why not monitor the contents of (some) database tables ? You probably have a few tables that contain parameter values that influence the applied business logic ? It may be useful to put their contents under source control too!

The basic ingredients to the solution are:

• a vbs script that generates SQL create commands for every object in the database
• a sql script that generates SQL insert statements for selected tables
• a sql script that checks changes to the database since last run and creates the necessary SVN client commands

These scripts are scheduled to run on a regular basis via SQL Agent. In my case I run them nightly.

Step 1: Create SQL for database objects
A VBS script uses SQLDMO objects and their capacity to generate SQL. It loops over the most “interesting” SQL Server object types – tables, views, stored procedures, functions, rules and defaults – and creates a file for each. The file name is constructed as follows: database_name.object_name.object_type.sql. (It is prefixed with the database name so that different SQL databases holding objects with the same name can be uploaded to a common repository.)
The VBS script (download it here) is launched via a SQL Agent job step of the type Operating system (CmdExec). It takes 3 parameters:

• -w sql_output_dir: the location where the files will be saved. Use a dedicated directory for this: the script starts by deleting all files in here!
• -s server: the database server
• -d database: the database to script

Step 2: Create SQL for monitored database contents
This step may be optional for many. To put content from some tables under source control – I have parameter values and sql statements in tables for example – you will need the following two scripts:

• checkDBContent.bat: a simple bat file that launches a sql script and captures its output
• GenerateContents.sql: the sql script that actually outputs the sql insert statements.

GenerateContents.sql actually calls sp_CreateDataLoadScript, see my post here or have a look at the code directly here.

Step 3: Synchronize with SVN repository
This is the tricky part. There is no way to tell SVN to “just check this directory and update the repository with whatever changes that were made” where “whatever changes” can be modifications, new files or deleted files. Adding new files can be somewhat automated by issueing an SVN add * –force command: that will add all new files to the repository without enumerating each individually. Modified files will get updated via the SVN commit command. But deleted files will still remain in the repository.
Therefore it is necessary to keep track of database changes since last commit and create the necessary SVN commands accordingly.
To keep track of these changes you will need to create the following table:

CREATE TABLE L_Last_Sysversions (
    name varchar(128),
    id int,
    modify_date datetime,
    type char(2),
    filename varchar(255)
)

Each time the SQL Agent job runs, step 3 will save the current state of the SQL Server objects in this table.
Step 3 again consists of two scripts:

• checkDBChanges.bat: a bat file that launches a sql script, captures its output and then runs that output. The output being the necessary SVN commands!
• checkDBChanges.sql: an sql script that checks what was changed since it last ran, saves the new state of the SQL Server objects and outputs SVN commands.

Since checkDBChanges.sql outputs SVN commands it needs to know a few things about the SVN setup: where the client was installed, the user name and password to use and the location of the working copy (i.e. where steps 1 and 2 saved their output). I have these in a parameter table in SQL, but you could just as well “hard-code” these. Just have a look inside checkDBChanges.sql and change as needed, both options are there.

Getting it running on your server

0. Get all the scripts neatly zipped here and unzip in a dedicated directory on the database server, e.g. C:\DBVersioning\scripts. (*)
1. Prepare SVN: create a directory to hold the sources on your SVN repository either via svn mkdir or via the Tortoise repo-browser or whatever you use to manage your SVN. Also, you may want to use a specific SVN user for this automated sync’ing.
2. Create an SVN working copy for the directory above somewhere accessible from the database server (*) and checkout the (empty) SVN directory there. Again, use whatever SVN tool available, e.g. svn checkout if using the command line tool.
3. Create the table L_Last_Sysversions in the database you want to monitor. The SQL for this is included in checkDBChanges.sql. It is commented-out at the top of the file.
4. Edit checkDBChanges.sql: @svn_command, @svn_user, @svn_pass and @svn_wc need a value. See the file for possible ways to set these values.
5. (optional) Edit GenerateContents.sql: if you are going to monitor the data inside some tables, then enter your database name and table names (comma seperated) here. Also, execute sp_createdataloadscript.sql to create the necessary stored procedure.
6. Create a SQL Server Agent job with 3 steps: 1) calls the script to create the output that will go into the repository, 2) (optional) creates the output for the data inside the monitored tables and 3) checks for changes and updates the SVN repository. See the image above for the actual commands to put in the job steps. Note that step 1 uses cscript to start the vbs, the other steps are merely bat files that run directly.

(*) if you cannot create these directories on the database server you will need to map drives and/or do other hocus pocus to be able to access the directory from the SQL Agent job.

To test your setup launch the job. You should see files appear in the SVN working directory. Also check the contents of L_Last_Sysversions. That should contain the current SQL Server objects. Last but not least, check your SVN repository: it should contain your first version of the SQL Server sources!

If you schedule the SQL Agent job your SQL Server database will then be versioned under SVN.

Enjoy!