Configuring TeamCity with SQL Server

1. April 2009

This is the second post in my two post mini series where I try to describe my development environment. I have already mentioned that I use TeamCity as my continuous integration and build system. In this post I will describe how to configure TeamCity to use SQL Server as a main configuration repository.

Installation of TeamCity

Run TeamCity installation to install the build server and build agent on your server. This will give you fully functional build system that is integrated with your development components. It will automatically (when configured appropriately) trigger build when a change is submitted into your VisualSVN Server repository. The build will use MSBuild to build all the artifacts of your project including running unit tests, generating source code documentation, etc. When finished the system will automatically label the source code after a successful build.

Currently all configuration is written into HSQLDB database.

 

Microsoft SQL Server as a configuration repository

Assumtions: You have 64-bit operating system and you have installed TeamCity into C:\Program Files (x86)\JetBrains\TeamCity and your build server configuration is at C:\BuildServer folder.

 

JDBC driver

TeamCity is written in Java and therefore we need Java drivers for Microsoft SQL Server. Download and install JDBC driver from Microsoft SQL Server JDBC Driver page. Copy sqljdbc.jar and auth/x64/sqljdbc_auth.dll into C:\Program Files (x86)\JetBrains\TeamCity\webapps\ROOT\WEB-INF\lib.

 

Configure SQL Server for JDBC driver

Open SQL Server Configuration Manager and make sure that SQL Server (MSSQLSERVER) is up and running and that TCP/IP protocol is enabled (using port 1433).

 

TeamCity_SqlProtocol

Create SQL Server database

Open SQL Server Management Studio and add a new Login named teamcity. In this example I will use SQL Server authentication with password teamcity (because I chose such a simple password I need to uncheck Enforce password policy).

Create a new database named TeamCity and add teamcity user to the newly created database as db_owner.

 

Configure TeamCity migration tool

Edit C:\Program Files (x86)\JetBrains\TeamCity\bin\dbMigration.properties file and make sure that sourceURL is set correctly:

sourceURL=jdbc:hsqldb:file:C:/BuildServer/system/buildserver

Now we need to add target configuration for the migration. This is where we add information about our newly created SQL Server database:

# MSSQL 2008 via JDBC 
targetDriver=com.microsoft.sqlserver.jdbc.SQLServerDriver targetURL=jdbc:sqlserver://dpokluda:1433;database=TeamCity; 
targetUser=teamcity 
targetPassword=teamcity

 

Configuration migration

To execute the migration follow these steps. Start PowerShell (or any other shell but you will need to change the bellow mentioned steps).

Add Java to your Path variable so that migration tool can run it. This step is only necessary when you don’t have Java installed on your system and you want to use the one that comes with TeamCity.

$env:Path += ";C:\Program Files (x86)\JetBrains\TeamCity\jre\bin"

Stop all TeamCity services.

stop-service TCBuildAgent 
stop-service TeamCity

Navigate to TeamCity bin folder and start the migration.

cd "C:\Program Files\JetBrains\TeamCity\bin"
migrateDB.bat migrate

(It is fine to press just [Enter] key here since we have entered the full path into the dbMigration.properties file - otherwise enter C:/BuildServer path.)

Start TeamCity services.

start-service TCBuildAgent 
start-service TeamCity

 

Verify functionality of the new repository

Navigate to your TeamCity web page and add a new user in TeamCity administration named Test.

Open TeamCity database in SQL Server Management Studio and you should see that there is a new user named Test in users table:

SELECT TOP 10 * FROM [TeamCity].[dbo].[users]

 

TeamCity_VerifyUsers

For more information see the following resources:

Coding , , ,

Configuring Development Environment

31. March 2009

As a .NET developer I have a set of development tools I frequently use. As a test-driven developer I prefer to have some kind of a continuous integration environment available. This is the first part of the two post series. This first post will list components I use. The second post will describe how to configure TeamCity to use SQL Server as a main configuration repository.

Source control system

I currently use Subversion as my source control system. I like the fact that I can work on my files (see diffs for example) even when I’m not connected to my source control server. This is a huge advantage for notebook users in my opinion.

I currently use VisualSVN Server. It comes with a VisualSVN Server Manager (snap-in to the Microsoft Management Console) where you can very easily configure your source server. You don’t need to tweak authz file any more.

 

Development environment

I don’t want to spend too much time in this section because it would make this post way too long. Let me just note that my primary IDE is Microsoft Visual Studio with JetBrains ReSharper plugin.

As a unit test framework I use xUnit.net with xUnit.net Contrib integration to ReSharper. Why do I use xUnit.net and not NUnit or MSTest? The answer is best described by Jim Newkirk at Why did we build xUnit. I like the fact that the framework is very simple. It has a limited number of custom attributes and it’s reasonably fast. Assert methods are generic based which makes the code more precise. I also like the fact that each test is run in it’s own test class instance and therefore it improves individual test isolation.

Note: My biggest complaint about MSTest was the fact that it was only available in a special suite of Visual Studio. With Visual Studio 2008, MSTest is now part of Professional version of Visual Studio. I think that it is still not enough. Unit testing is such a basic requirement that it should be part of the platform (.NET Framework) or it’s SDK. I should be forced to buy Visual Studio to get MSTest binaries!

 

Build scripts

I am a long time NAnt user. I have a library of snippets I use for my build scripts. I have even created a set of CodeSmith templates that help me generate the basic NAnt scripts.

Even though I like NAnt a lot, I am slowly moving away toward MSBuild based scripts. Most of my new development is based on MSBuild scripts. Why? I like the fact that Visual Studio shares the same project configuration as my build system.

 

Continuous integration & Build system

I prefer spending my time coding rather than tweaking XML files to configure my environment. That’s why I decided to use JetBrains TeamCity. TeamCity is continuous integration and build management system that is available for free for small teams (details are available at JetBrains web).

 

Configuration repository

As a configuration repository I prefer Microsoft SQL Server database. For very small systems like stand-alone development on a notebook I would stay with HSQLDB which is the default configuration repository in TeamCity.

 

Installation and Configuration

First I install Microsoft .NET Framework, Microsoft SQL Server 2008 and Microsoft Visual Studio 2008. After that I install JetBrains ReSharper and xUnit.net. The last component to install is JetBrains TeamCity. Read my next blog post about configuring TeamCity with SQL Server.

Coding , ,

Automating installations with PowerShell script

18. March 2009

I am currently testing Windows 7 and therefore I am deploying various builds quite frequently. This is nice because you have a chance to see the product evolving and at the same time you have a chance to send feedback to the product team. On the other hand installing OS frequently means to install all the other applications very often. I have created various PowerShell scripts to simplify the deployment process. Today I will try to describe my install-folder script.

The basic idea of the script is this. I want to put all my installations into a folder and then run the script against this folder. The script will enumerate over all the files in that folder and “run the installation” for each of them. The problem here is that some installations are delivered as MSI files, some are EXE files (not build using Windows Installer) and some are ISO files (those downloaded from MSDN usually).

MSI File Installation

To install an MSI file you run msiexec with /i msiFileName. If you want to run the installation automatically then add also /passive. If you want to prevent Windows Installer from automatically restarting your machine then use /norestart.

Note: When you need to troubleshoot MSI installation, it might be a good idea to turn on verbose logging in Windows Installer. To turn the logging on use /l*v logFilePath parameter. More information about this topic can be found at:

EXE File Installation

This one is tricky. If it is EXE created using Windows Installer then you might be able to use some of the Windows Installer parameters. On the other hand many other installers are available (like InnoSetup, Nullsoft Install System, etc.) and the parameters for these are different. That’s why when installing EXE I usually just invoke the EXE file.

ISO File Installation

ISO file installation is the most difficult because in the OS there is no direct support for these files. There are various utilities available for this purpose (SlySoft Virtual CloneDrive, Daemon Tools, Microsoft Virtual CD-ROM, etc.). I have decided to use SlySoft solution because I have had a really good experience with their other tools like AnyDVD (this is an awesome tool if you are dealing with DVDs from different regions for example). After you install the tool you can mount an ISO image by calling VCDMount.exe isoFilePath. To unmount the current ISO image execute VCDMount.exe /u.

InstallFile function

So to install the files from a folder we need to enumerate over all files in that folder and for each of these files execute InstallFile function:

    function InstallFile($file)
    {
        if ($file.EndsWith(".msi"))
        {
            execute-command -wait -command msiexec -parameters "/i $file /passive /norestart"
        }
        elseif ($file.EndsWith(".exe"))
        {
            execute-command -wait -command "$file"
        }
        elseif ($file.EndsWith(".iso"))
        {
            execute-command -command "$programFiles32\Elaborate Bytes\VirtualCloneDrive\VCDMount.exe" -parameters "/u"
            execute-command -command "$programFiles32\Elaborate Bytes\VirtualCloneDrive\VCDMount.exe" -parameters "$file"
            Pause "`tPress any key when finished with this installation."
            execute-command -command "$programFiles32\Elaborate Bytes\VirtualCloneDrive\VCDMount.exe" -parameters "/u"
        }
    }

 

This function references some of my other scripts/functions. First of all it uses execute-command script. The core of that script is the following:

param([string] $command = $(throw "Missing: command parameter"), [string[]] $parameters, [switch] $wait, [switch] $cmd)

#Main
    if ($cmd.IsPresent)
    {
        cmd /C $command $parameters
    }
    else
    {
        $process = [Diagnostics.Process]::Start($command, $parameters); 
        if ($wait.IsPresent)
        {
            $process.WaitForExit();
        }
    }

Save this script as execute-command.ps1.

The only extra thing we have in our InstallFile folder is reference to $programFiles32 folder. This is initialized in my profile script (which I will describe in one of the latter posts). For now I will just define the variables:

    #Define ProgramFiles* variables
    $is64 = $(if([IntPtr]::Size -eq 8) { $true } else { $false })
    if ($is64)
    {
        $programFiles32 =  $(get-item "env:ProgramFiles(x86)").Value
        $programFiles64 = $env:ProgramFiles
    }
    else
    {
        $programFiles32 = $env:ProgramFiles
        $programFiles64 = $env:ProgramFiles
    }

InstallFolder function

Now we have all we need to implement our main InstallFolder function:

    function InstallFolder($folder)
    {
        $files = get-childitem $folder -recurse -force
        foreach($file in $files)
        {
            if ($file -eq $null)
            {
                continue
            }
            $answer = Ask "Do you want to install $($file.FullName)? (y or n)?" "y"
            if ($answer -ne "y") { continue } 
            set-color "yellow"
            write-host "`tInstalling $file..."
            set-color
            InstallFile $($file.FullName)
        }
    }

In this function I am using two additional functions. Function set-color (is defined in profile script) and Ask function.

    function Ask($text, $default)
    {
        $answer = $(read-host "$text [default: $default]")
        if ($answer -eq "") { $answer = $default }
        return $answer
    }

    function Set-Color([string] $color)
    {
        if ($color -eq "")
        {
            # $myDefaultColor initialized in profile script to [Console]::ForeGroundColor
            $color = $myDefaultColor
        }
        $host.UI.RawUI.ForeGroundColor = $color
    }

Final Install-Folder script code

With all these we can now create the install-folder.ps1 script:

param([string] $path = $(throw "Missing: path parameter"), )

#Internals
    function Ask($text, $default)
    {
        $answer = $(read-host "$text [default: $default]")
        if ($answer -eq "") { $answer = $default }
        return $answer
    }

    function Set-Color([string] $color)
    {
        if ($color -eq "")
        {
            # $myDefaultColor initialized in profile script to [Console]::ForeGroundColor
            $color = $myDefaultColor
        }
        $host.UI.RawUI.ForeGroundColor = $color
    }

    function InstallFolder($folder)
    {
        $files = get-childitem $folder -recurse -force
        foreach($file in $files)
        {
            if ($file -eq $null)
            {
                continue
            }
            $answer = Ask "Do you want to install $($file.FullName)? (y or n)?" "y"
            if ($answer -ne "y") { continue } 
            set-color "yellow"
            write-host "`tInstalling $file..."
            set-color
            InstallFile $($file.FullName)
        }
    }
    
    function InstallFile($file)
    {
        if ($file.EndsWith(".msi"))
        {
            execute-command -wait -command msiexec -parameters "/i $file /passive /norestart"
        }
        elseif ($file.EndsWith(".exe"))
        {
            execute-command -wait -command "$file"
        }
        elseif ($file.EndsWith(".iso"))
        {
            execute-command -command "$programFiles32\Elaborate Bytes\VirtualCloneDrive\VCDMount.exe" -parameters "/u"
            execute-command -command "$programFiles32\Elaborate Bytes\VirtualCloneDrive\VCDMount.exe" -parameters "$file"
            Pause "`tPress any key when finished with this installation."
            execute-command -command "$programFiles32\Elaborate Bytes\VirtualCloneDrive\VCDMount.exe" -parameters "/u"
        }
    }

#Main
    #Define ProgramFiles* variables
    $is64 = $(if([IntPtr]::Size -eq 8) { $true } else { $false })
    if ($is64)
    {
        $programFiles32 =  $(get-item "env:ProgramFiles(x86)").Value
        $programFiles64 = $env:ProgramFiles
    }
    else
    {
        $programFiles32 = $env:ProgramFiles
        $programFiles64 = $env:ProgramFiles
    }

    write-host "Installing $path..."
    InstallFolder $path

This is just one of my scripts that I use daily. I will describe more of them in later posts.

Attachments:

PowerShell

Connecting to SharePoint embedded database

10. March 2009

As a new member in Windows SharePoint team I am trying to learn as much as possible. I have installed SharePoint (WSS) version 3.0 on my home computer. I wanted to see what is the database schema in SharePoint. Since I had SQL Server 2008 installed on my machine I started SQL Server Management Studio.

 

Connecting to SharePoint SSEE database

 

But when I click Connect I only get the following error:

 

ConnectingToSharePointDb_Error

 

 

 

Bummer. After a while of searching on Internet I found the following Wikipedia article - Windows Internal Database. Let me quote some of the text from the article:

 

Windows Internal Database (codenamed WYukon, sometimes referred to as SQL Server Embedded Edition) is a variant of SQL Server Express 2005 that is included with Windows Server 2008, and is included with other free Microsoft products released after 2007 that require an SQL Server database backend. Windows SharePoint Services 3.0 and Windows Server Update Services 3.0 both include Windows Internal Database, which can be used as an alternative to using a retail edition of SQL Server.

 

Ok, now I know that I am not connecting to a SQL Server database but instead to Windows Internal database. How do I do that? Again Wikipedia has the answer:

 

SQL Server Management Studio Express can be used to connect to an instance of Windows Internal Database using \\.\pipe\mssql$microsoft##ssee\sql\query as server name.

 

That means, that I should be able to connect even from SQL Server 2008 Management Studio.

 

ConnectingToSharePointDb_EmbeddedPipeString

 

That did the trick. I have now SQL Server Management Studio fully connected to the content database and I can execute queries against the database (I can use the same connection with SQL Profiler).

 

ConnectingToSharePointDb_Connected

 

More information can be found at:

SharePoint ,

Windows SharePoint Services

6. March 2009

Ok, it’s not even a month since my first post on this blog and things are different already. I have changed teams and now I work in SharePoint Server organization, in particular in Windows SharePoint Services Storage and Perf team. Since I am new to the team I cannot say for sure what is my primary feature but I hope I will find out soon.

I’m now also on Twitter, you can follow me. I have also added a special side bar with my latest updates from Twitter.

Blog ,