Click here to monitor SSC

Continuous Integration for SQL Server using Red Gate Tools, Atlassian Bamboo and GitHub

Published 21 March 2013 1:46 pm

This post explains how to install Red Gate’s Automation License for Continuous Integration on Atlassian’s Bamboo CI Server so that you can start running Continuous Integration builds for your SQL Server databases. This will allow you to automatically trigger unit tests and integration tests on your database whenever you check in changes to Red Gate’s SQL Source Control. It also makes the latest version of the database continually available for deployment.

The steps:

  • Source-controlling your database in GitHub with Red Gate SQL Source Control
  • Installing sqlCI.exe to build any database changes you make
  • Installing an evaluation of Atlassian Bamboo

A subsequent article will detail how to implement tests for those changes and how to deploy the updates to production, using Deployment Manager.

You will need:

UPDATE – Red Gate now offers a “SQL Automation Pack”, which provides a much simpler, easier way of getting up-and-running with our command line tools. For example, once downloaded, the following screen helps you through the process of getting installed:

SQL Automation Pack

  • The Java Runtime Environment (see the end of this article for further information) Click clip_image002 on the page and select the appropriate version.

Setting up the continuously integrated database in SQL Source Control

1. In SSMS, create a new database called RedGateApp:

clip_image004

2. Expand the database, right-click Tables and click New Table:

clip_image006

3. Name the table Email and name the columns id, email, and firstname. Set their data types as int, varchar(50), and varchar(50) respectively as shown below:

clip_image008

4. Save the table.

5. Create a new blank project called RedGateApp on GitHub using the Windows client:

clip_image010

Set the local directory to a location on your local machine and select the Push to github checkbox.

6. Right-click the RedGateApp database. The following menu is displayed:

clip_image012

7. Click Link Database to Source Control…. The Link to Source Control dialog is displayed:

0014

8. Select More on the menu at the left. Under Specify the location of your working folder, type or paste the path you set for the GitHub local repository. Under Select the config file for your source control system, Select Git. Click Link. The database icon changes to show it is in source control with pending changes to check in.

9. Right-click RedGateApp:

clip_image015

10. Click Commit Changes to Source Control. The following dialog appears: clip_image017

11. Type ‘Initial commit’ in the text box and click Commit. The database is committed to GitHub.

Setting up the target database

We need to create the target CI database. This is the database that the CI process is going to build based on the changes to RedGateApp that you check in.

1. Run the following query in SSMS:

CREATE DATABASE RedGateAppCI

GO

USE RedGateAppCI

GO

ALTER DATABASE RedGateAppCI SET TRUSTWORTHY ON

This creates the RedGateAppCI database.

2. Now run the following query:

EXEC

sp_configure 'clr_enabled',1

RECONFIGURE

This allows tSQLt tests to run.

Installing sqlCI.exe

1. Open sqlCI.zip which you downloaded earlier.

clip_image019

2. Copy all of these files into the local source control folder that you created for RedGateApp:

clip_image021

3. Commit these new files to your source control repository and sync:

clip_image023

After syncing, GitHub should display no uncommitted changes.

clip_image025

Installing an evaluation of Atlassian Bamboo

Downloading Bamboo

We need to install Atlassian Bamboo. If you already have Bamboo installed, then you can skip ahead to Setting up the Build.

1. Go to http://www.atlassian.com/software/bamboo/overview

2. Click Try it for Free.

3. Under the Download Column, click Start My Free Trial:

clip_image027

4. On the following page, click Download and save the file to your machine:

clip_image029

The following page is displayed:

clip_image031

Note the link to the installation guide which may be useful during the install.

Bamboo installation

1 Run the Bamboo installer:

clip_image032

2 Go through the installer using the default options.

3 Click Finish when you get to the end:

clip_image033

4 In the Start menu, find the Bamboo directory:

clip_image034

5 Right-click Install Service and click Run as Administrator.

6 Return to the Bamboo directory in the Start menu and click Start Service. Bamboo is now running.

7 Go to http://localhost:8085 to access the web console.

Getting an evaluation license for Bamboo

1. On the Welcome page at http://localhost:8085, click contact Atlassian:

clip_image036

2. The login page is displayed:

clip_image038 Click Need an account? Sign up.

3. The Sign up page is displayed:

clip_image040

Fill in your details and click Create Account. The Evaluate Atlassian Product page is displayed:

clip_image042

The form is already filled in with details from your installation.

4. Click Generate License. The following survey appears:

clip_image044

5. Fill in the survey and click Submit. The evaluation license is displayed:

clip_image046

 

6. Copy the evaluation license key to your clipboard.

7. Return to the Bamboo Setup Wizard. Paste the evaluation license key into the License Key box: clip_image048

8. Click Express Installation >>. Wait while the following page is displayed:

clip_image050

9. When the installation is complete, the following page is displayed:

clip_image052

 

10. Type in your admin details and click Finish.

You will receive an email from Atlassian during the install confirming your Atlassian ID:

clip_image054

After this, you will receive an email with some helpful information about your evaluation of Bamboo:

clip_image056

Setting up the build

1. Go to the Bamboo setup page:

clip_image058

 

Click Create my first Plan. The following page is displayed:

clip_image060

2. Click Create a New Plan. The following page is displayed:

clip_image062

 

3. Fill in all the fields and set the Source Repository to GitHub.

4. Click Configure Tasks. The following page is displayed:

clip_image064

5. Click Add Task. The following page is displayed:

clip_image066

Click Add Task. The following page is displayed:

clip_image068

6. Click on the MSBuild task. The following page is displayed:

Step_70

7. Configure the task to point at the sqlCI.proj file that you copied to your source control repository. Click Save.

8. In your local source control repository, open sqlCI.targets for editing.

9. Add the following details under the relevant entries in the file:

a. Database folder path: . [a full stop indicating to use the working directory]

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

c. Target database name: RedGateAppCI

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

e. 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.

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.

f. Set tSQLt tests to true.

10. Save the sqlCI.targets file.

11. Right-click sqlCI.targets file in Source Control Explorer and check it in in GitHub and sync:

clip_image072

 

Testing that it all Works

1. Add a column to the Email table in RedGateApp called address with data type varchar(50):

clip_image074

2. Save the table.

3. Right-click the database and select Commit Changes to Source Control:

4. Enter a comment and click Commit:

clip_image076

5. Next, sync these changes in GitHub:

clip_image078

6. This will then start a build of the database in Bamboo*:

clip_image080

* NB: To get the MSBuild task running correctly, I re-configured the Bamboo service to run on a new user account (called “Bamboo”) with Administrator privileges (necessary for creating the temporary files). For more info, see article: https://jira.atlassian.com/browse/BAM-11440

7. This will implement the changes on RedGateAppCI:

clip_image082

Success! From making changes in your source-controlled repository, the RedGateAppCI database is being automatically updated to the latest version of the database, reflecting the source-controlled master.

At this point, you’re not running any tests on these changes (so we’re not adding a great deal of value yet!), nor are we deploying these changes in a controlled manner to a live production system. These actions will be outlined in a subsequent article.

Appendix: Java Runtime Environment installation

You need the Java Runtime Environment (JRE), if it’s not already installed. You can skip this if it’s installed.

1. Go to the Java download page. Click Download on the latest version of the JRE: clip_image083

2. Select either the x86 or the x64 version, as appropriate to your environment: clip_image084

3. The following page appears:

clip_image086

Click Install.

The Java Runtime Environment is installed.

5 Responses to “Continuous Integration for SQL Server using Red Gate Tools, Atlassian Bamboo and GitHub”

  1. jsauni says:

    Great post! Look forward to a followup article re: deploying these changes in a controlled manner to a live production system.

  2. nportelli says:

    This says you only need SQL Source Control, don’t you need SQL Compare as well to update the target database? We use SQL Source Control V2 and I feel it is about useless since it can’t create merge scripts.

Leave a Reply