Continuous Integration for SQL Server using Red Gate Tools, Atlassian Bamboo and GitHubPublished 21 March 2013 1:46 pm
Update 31st March 2015
This blog post is now out of date, as there have been some changes to the Red Gate products for database lifecycle management (DLM) since the post was first published. In particular, we now have a new version of Red Gate SQL CI, part of the DLM Automation Suite, which makes it easier to set up continuous integration to run through four different tasks: Build, Test, Sync and Publish.
The SQL Automation Pack, referenced in the article, is replaced by the DLM Automation Suite, offering the tools you need to automate database changes, including continuous integration and release management.
We’re working on an updated version of our documentation. Meanwhile, the Red Gate team would be really happy to walk through an online demo with you of how to set up Red Gate products for CI and using Bamboo and Github. Drop an email to firstname.lastname@example.org to find out more and arrange a time.
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.
- 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
You will need:
- Access to a SQL Server instance and SQL Server Management Studio (SSMS)
- SQL Source Control
- A GitHub account and Git set up on your local machine
- The Red Gate Continuous Integration build scripts Click on this page and save the sqlCI.zip file to your machine
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:
- The Java Runtime Environment (see the end of this article for further information) Click 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:
2. Expand the database, right-click Tables and click New Table:
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:
4. Save the table.
5. Create a new blank project called RedGateApp on GitHub using the Windows client:
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:
7. Click Link Database to Source Control…. The Link to Source Control dialog is displayed:
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:
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
ALTER DATABASE RedGateAppCI SET TRUSTWORTHY ON
This creates the RedGateAppCI database.
2. Now run the following query:
This allows tSQLt tests to run.
1. Open sqlCI.zip which you downloaded earlier.
2. Copy all of these files into the local source control folder that you created for RedGateApp:
3. Commit these new files to your source control repository and sync:
After syncing, GitHub should display no uncommitted changes.
Installing an evaluation of Atlassian Bamboo
We need to install Atlassian Bamboo. If you already have Bamboo installed, then you can skip ahead to Setting up the Build.
2. Click Try it for Free.
3. Under the Download Column, click Start My Free Trial:
4. On the following page, click Download and save the file to your machine:
The following page is displayed:
Note the link to the installation guide which may be useful during the install.
1 Run the Bamboo installer:
2 Go through the installer using the default options.
3 Click Finish when you get to the end:
4 In the Start menu, find the Bamboo directory:
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:
2. The login page is displayed:
3. The Sign up page is displayed:
Fill in your details and click Create Account. The Evaluate Atlassian Product page is displayed:
The form is already filled in with details from your installation.
4. Click Generate License. The following survey appears:
5. Fill in the survey and click Submit. The evaluation license is displayed:
6. Copy the evaluation license key to your clipboard.
8. Click Express Installation >>. Wait while the following page is displayed:
9. When the installation is complete, the following page is displayed:
10. Type in your admin details and click Finish.
You will receive an email from Atlassian during the install confirming your Atlassian ID:
After this, you will receive an email with some helpful information about your evaluation of Bamboo:
Setting up the build
1. Go to the Bamboo setup page:
Click Create my first Plan. The following page is displayed:
2. Click Create a New Plan. The following page is displayed:
3. Fill in all the fields and set the Source Repository to GitHub.
4. Click Configure Tasks. The following page is displayed:
5. Click Add Task. The following page is displayed:
Click Add Task. The following page is displayed:
6. Click on the MSBuild task. The following page is displayed:
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:
Testing that it all Works
1. Add a column to the Email table in RedGateApp called address with data type varchar(50):
2. Save the table.
3. Right-click the database and select Commit Changes to Source Control:
4. Enter a comment and click Commit:
5. Next, sync these changes in GitHub:
6. This will then start a build of the database in Bamboo*:
* 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:
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:
3. The following page appears:
The Java Runtime Environment is installed.