Automating Database Deployments from Subversion

There was a bit of a learning curve going from manually deployed SQL statements and SSIS packages to automatic deployments, but this is what I did and it seems to work so far.

1. Create a database project in visual studio and setup projects to your liking making sure it builds and deploys correctly, a chore in itself.

2. Get SLIK Subversion command line tools for Windows (http://www.sliksvn.com/en/download) and install. The windows equivalent of $PATH is updated following a restart of your workstation so that you can execute the SVN command line commands without including the path to the executable. In short, restart your workstation!

3. Create a daily build directory to which you can check out from SVN.

4. Write batch script to checkout project from SVN. Here’s an example:
svn export –force https://[subversion url]/project “[build directory]”

TEST IT!

5. Write a batch script to build the database project using MSBUILD. Note that the executable is not in the environmental path variable, so the executable and its path must be fully qualified. Here’s an example:
C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\msbuild /target:BUILD ./[path]/[project file]

TEST IT!

6. Write a batch script to deploy the database project using OSQL. Here’s an example:
OSQL -E -S[server]\[instance] -i .\[path]\[SQL Script].sql

TEST IT!

7. If it both batch files test correctly, then merge them into a single batch file and make it nice by adding logging and parameterize paths and server names.

8. Run the process by manually until you are confident in it, then execute regularly through Windows Scheduler.

Extra credit: consider how more straight forward this would be on any *nix system.

References:

  • http://msdn.microsoft.com/en-us/library/aa833165(VS80).aspx
  • http://www.joelonsoftware.com/articles/fog0000000043.html
  • http://msdn.microsoft.com/en-us/library/aa214012(SQL.80).aspx
  • Leave a Reply

    Your email address will not be published.

    + 56 = 61