Worked example: setting up continuous integration with TFS2012 - Automation with Red Gate tools

Overview

This worked example demonstrates how to set up Red Gate's SQL Automation Toolkit with Team Foundation Server 2012.

The end result is the WidgetShop example database configured for continuous integration. When changes to the WidgetShop database are checked in to SQL Source Control, a build is triggered in TFS and the current version of WidgetShop is deployed to the continuous integration database, WidgetShopCI. tSQLt tests also then run on a copy of the database.

Requirements

Make sure you have the following:

• Access to Team Foundation Server 2012 (TFS)
• Access to a SQL Server 2012 instance
• SQL Server Management Studio (SSMS)
• Red Gate SQL Source Control on the same machine as SSMS
• Visual Studio 2012
• The Red Gate continuous integration build scripts. You can download them from this web page.
• The WidgetShop demo files. You can download these in a zip here.

Creating the team project

1. Make sure Visual Studio is connected to TFS. Click Connect to Team Foundation Server:

   

2. Enter your server details.
3. Open the File menu, go to the New menu and click Team Project.... The Specify Team Project Settings page is displayed:

   

4. Name the project WidgetShop and click Next. The Select a Process Template page is displayed:

   

5. Select a process template, and click Next. The Team Site Settings page is displayed:

   

6. Select Do not configure a SharePoint site at this time and click Next. The Specify Source Control Settings page is displayed:

   

7. Make sure Create an empty source control folder is selected and click Next. The Confirm Team Project Settings page is displayed:

   

8. Click Finish to confirm the settings. The Team Project Created page is displayed:

   

9. Click Close.

The Team Project is now set up.

Setting up source control for the project

1. In Visual Sudio, go to Team Explorer and click Source Contor Explorer:

   

2. In the View menu, go to the Other Windows menu, and click Source Control Explorer:

   

3. Click Not mapped to create a workspace mapping. The Map window appears:

   

4. Select a local folder to map the $/WidgetShop root to, and click Map. The following window appears:

   

5. Click Yes to synchronize the files.
6. Open the local folder you mapped the source control location to and copy the WidgetShop demo folder to it:

   

7. Copy the Red Gate continuous integration build scripts files into the same folder:

   

8. Using Source Control Explorer in Visual Studio, add all the files you've just copied to source control. To do this, right click select Add Items to Folder.
9. Select all the folders and files, then click Next.
10. Click the Excluded items tab, select all the items in there, and click Include items(s). Then click Finish.
11. Right click again and select Check In Pending Changes.
12. Click Check In in Team Explorer.

    

You now have all the files you need in source control.

Setting up the build

1. In Team Explorer, go to Builds.

   

2. Click New Build Definition. The following page is displayed:

   

3. Name the build WidgetDevelopment. Set the Queue processing to Disabled for now. We will change this to Enabled when everything has been set up.
4. Click Trigger, the following page is displayed.

   

5. Select Continuous Integration.
6. Click Workspace, and click in the Source Control Folder field to browse for a folder. The following window is displayed:

   

7. Set the Source Control Folder to WidgetShop and click OK.
8. Click Build Defaults. The following page is displayed:

   

9. Select Copy build output to the following drop folder. Enter the location of the fileshare that you want to use as your drop folder.
10. Click Process. The following page is displayed:

    

11. Under Projects to Build, browse for sqlCI.proj in source control and add it.
12. Under Agent Settings, select a suitable build agent. In the example below, we have used the Name Filter to select one.
13. Save the build definition with Ctrl+S.

You have now set up the build definition.

Setting up the databases

1. Open SQL Server Management Studio (SSMS).
2. In the File menu, go to Open and click File....
3. Browse for the WidgetDevelopment.sql file from the demo folder and click Open.
4. Press F5 or click Execute to create the WidgetDevelopment database.
5. In Object Explorer in SSMS, go to the WidgetDevelopment Database. Right click it and click Link Database to Source Control.
6. On the Link to Source Control page that appears, select Team Foundation Server (TFS). The following dialog is displayed:

   

7. Enter the URL of your TFS server under Server URL. Set the Database folder and Migration scripts folder paths as shown above. These refer to locations in the WidgetDeveolpment folder that you added to source control.
8. Click Link.
9. Right Click the WidgetDevelopment database in SSMS and click Commit Changes to Source Control.

   

10. The following page appears:

    

11. Click Commit. This commits the database to source control.
12. Click New Query.
13. In the new query window, execute the following SQL to create the continuous integration database:

    CREATE DATABASE WidgetShopCI

    GO

    USE WidgetShopCI

    GO

    ALTER DATABASE WidgetShopCI SET TRUSTWORTHY ON

14. Next, run the following which is required for tSQLt tests:

    EXEC

    sp_configure 'clr_enabled',1

    RECONFIGURE

The databases are now configured.

Configuring the targets file

1. In Visual Studio, go to Source Control Explorer and open $\WidgetShop\sqlCI.targets
2. Fill in the following details:

   Database folder path: WidgetShop\Database\Scriptsfolder

   Database server name: your database server and instance for example, sql.example.com\sql2008

   Target database name: WidgetShopCI

   Serial key: your Automation License serial key, or leave it blank for a trial.

   To use a Windows account to access the SQL server, set Windows authentication to true. It will use the Windows account that the build agent runs under.

   If you want to use a SQL account to access the SQL server, set Windows authentication to false and fill in the SQL user name and SQL password options.

   Set tSQLt tests to true.

3. Save the sqlCI.targets file.
4. Right click sqlCI.targets file in Source Control Explorer and check it in.
5. Edit the Build Definition and set it to Enabled. (Similar to step 2 of Setting up the Build, above.)

You have now configured a build for continuous integration.

To see it in action, make a change to the schema of WidgetDevelopment in SSMS, for example, add a new table. Commit the changes in SQL Source Control.

A build will start automatically:

When it's complete, open the log file and view the results.

You should now have successfully configured continuous integration.