CI and DB development... part I

Continuous Integration and DB Projects in MS SQL and VS 2008
The start...
I'm using a vpc and my main machine to act as build and dev boxes along with the ability to deploy from one to the other. essentially, for testing, you will need at least two machines. One as a dev enviroment, one as the source control and test enviroment.

The goal is to have developers use SQLExpress on thier local machines to develop and test db procedures, etc. Then upload those to a test db and have a suite of tests (e.g. unit, regression, etc.) run and make sure nothing has broken.

Here's what I have so far:

1. Dev Box Install
• VS 2008 (C# default)
• SQL Express
• Subversion Client
2. Test/Source Control Box
   • VS 2008 (just what is need to support MSBuild, and test abilities
   • Subversion server
   • Cruise Control .Net
3. Initial Test
   
   • Set up repository on server (with preferred diretory structure, e.g. root or trunk, etc.)
   • Install Subversion client on box and test to make sure you can get to Subversion server
   • Create DBProj and check it into Subversion
4. MSBuild Test
   
   • Create two configurations in the Project properties, one for the dev build and one for the test build
   • Build for Dev - example -
     MSBuild /t:Build /p:Configuration=devServerBuild SandBoxDb.dbproj
   • Build for Test - example -
     MSBuild /t:Build /p:Configuration=testServerBuild SandBoxDb.dbproj
   • Deploy test To Test - example
     MSBuild /t:Deploy /p:targetDatabase=SandBoxDb /p:BuildScriptName=TestSandBoxDb.sql /p:TargetConnectionString="Data Source=TestBox\SQLEXPRESS;Persist Security Info=True;User ID=admin;Pooling=False" SandBoxDb.dbproj
5. CruiseControl .Net
   Download and install as recommended.


• Make sure ThoughtWorks.CruiseControl.MSBuild.dll is in the .\CruiseControl.NET\server directory
• Edit the ccnet.config file, replace with your values where appropriate....
  

  <cruisecontrol>
  <project name="SandBoxDb">
  <sourcecontrol type="svn">
  <username>admin</username>
  <password>password</password>
  <trunkUrl>svn://localhost/sandBoxDb/root</trunkUrl>
  <workingDirectory>C:\ccProjects\dbSandbox\build</workingDirectory>
  </sourcecontrol>
  <tasks>
  <msbuild>
  <executable>C:\WINDOWS\Microsoft.NET\Framework\v3.5\MSBuild.exe</executable>
  <workingDirectory>C:\ccProjects\dbSandbox\build</workingDirectory>
  <projectFile>SandBoxDb.sln</projectFile>
  <buildArgs>/noconsolelogger /p:Configuration=TestServerDeploy /v:diag</buildArgs>
  <timeout>60</timeout>
  <logger>C:\Program Files\CruiseControl.NET\server\ThoughtWorks.CruiseControl.MSBuild.dll</logger>
  </msbuild>
  </tasks>
  </project>
  </cruisecontrol>

• Edit the dashboard.config file, adding the msbuild logging info

  <buildPlugins>
  <buildReportBuildPlugin>
  <xslFileNames>
  <xslFile>xsl\compile-msbuild.xsl</xslFile>
  </xslFileNames>
  </buildReportBuildPlugin>
  <buildLogBuildPlugin />
  <xslReportBuildPlugin description="MSBuild Output" actionName="MSBuildOutputBuildPlugin" xslFileName="xsl\msbuild.xsl"/>
  </buildPlugins>

• finally correct a bug in the .\CruiseControl.NET\webdashboard\xsl\msbuild.xsl file

• delete the DOCTYPE and ENTITY section at the top
• Replace all nbsp occurences with #160
• start up ccnet on the test box
• edit your project and check it in, watch to see if ccnet updates and rebuilds it


6. Left to do...Automate deploy of the db to test servercreate a "standard" database for testing (e.g. scripts to repoputlate with test data etc.)
• create way to run and report on tests, integrating with ccnet