Friday, December 28, 2007

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
    • 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

    • 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


        Friday, December 21, 2007

        sql, source control, and continuous integration

        Well the new gig has given me a chance to set up something form scratch...

        The pieces?
        Suberverion - code repository
        Cruise Control .Net - continuous integration
        MSBuild - scripted builds

        The hitch? A good portion of code will be in SQL because of the nature of the product. (Working with comparing large amounts of data, stuff like that.) So I want to make sure the team can actually track edits to the variou stored procs in such.

        Step one:
        In Visual Studio set up a dbproj and import the needed schema.
        Next on the project properties set up two types of build. One as a local "dev" build so that the developer can test locally. The other as a build targetting the "test" server. Make sure that the two types of build have different names for thier script files.

        Step two:
        Test the builds from the command line using MSBuild. Make sure everything is going where it is supposed to go.

        Step three:
        Add source control. Set up Subversion where ever you like an then check in the Db Project with all it's files. Now you have source controlled sql.

        Step four:
        Cruise Control .Net... I'm still setting this up but the goal would be something like...

        Developer Tasks:
        1. Performs "get latest"
        2. Rebuilds the system locally
        3. Edit/add what ever is needed
        4. Creates/performs unit tests
        5. Once happy with code, checks the code into Subversion.

        Cruise Control Tasks:
        6. Sees that Subversion has changed
        7. Gets the latest code
        8. Builds the code
        9. Deploys the code to the test server

        From that point I'm still working out how and where to automate the test process.

        But if this works I can bring the db development side in line with the regular coding side. Then all the team will have to worry about is doing actual work as opposed to the repetitive side of developing...

        Friday, December 14, 2007

        Starting up the tech blog...

        Thought I try to give it a go and post on more nerd/geek topics here.

        So, due to another conversation, in another blog, I thought I start off with the following age old debate.
        Contractor vs. Consultant.
        (o.k. not a tech thing but, it does matter.)

        I'm a consultant... specifically I work for an IT Consulting company (Magenic) and my title says "Senior Consultant". But... in another post somebody alluded to the idea that when they were out of work they became a "consultant" for a while...

        Well here's where I have an issue... I don't agree with that. So here are my definitions...

        Consultant - an individual, independent or part of a firm, that is hired to aid and/or lead project based work. In other words, work that has a beginning, middle and end. In addition this person is brought on due to a specific set of skills to aid in the project. Analysis, design, project management, QA Testing. They have a skill set that makes them an asset to the project.

        Contractor - an individual, independent or part of a firm, that is hired for the sake of getting work done. In other words staff augmentation. You have a project and you only have three developers and need three more due to the size of the project. The only qualifications, for example, are that they know how to program in the target language and have 3 to 5 years of proficiency. In other words warm bodies to fill seats to get stuff done. But... you do not expect them to add value to the project beyond that.

        So that's my thoughts on the two roles... yes they can blur together. But I generally see a consultant as having either a better skill set, or a targeted skill set vs. a contractor.

        So want to be a consultant? Want to work for a company that actually views it's employees as investments? Like to work in the world of Microsoft and .Net?

        Then check out www.magenic.com

        Labels: