Régis Baccaro

Continuous integration with SQL Server Data Tools and Team Foundation Server

October 1, 2014 by

SQL Server Data Tools – a free standalone download or and add-in to visual studio comes in different flavors and versions. Although this blog post uses Visual Studio 2012 and SSDT stand-alone the principles are example are also valid on Visual Studio 2013.

As you know SSDT is Microsoft developer tools for working with SQL Server databases on premises and in the cloud. It supersedes Management Studio in development functionality adding the powerful Visual Studio IDE to the rescue of developers that – for too many years – were stuck with SQL Server Management Studio.

When SQL Server 2012 was released, SSDT was also released and since then many versions have been released, not necessary following the release pattern of SQL Server.

SSDT version number Figure 1 SSDT version number

For each release, some new features are added. Some are still missing but they are probably coming in an upcoming release.

Team Foundation Server on the other hand has been around for quite a while. Team Foundation Server is the collaboration platform at the core of Microsoft’s application lifecycle management (ALM) solution. TFS supports agile development practices, multiple IDEs and platforms locally or in the cloud and gives you the tools you need to effectively manage software development projects throughout the IT lifecycle.

Remember : For SSDT in Visual Studio 2012 you will need TFS Express 2012. If you have SSDT 2013 you will need TFS Express 2013. You can’t mix versions!

In this post, we are going to focus on the interaction between SSDT and TFS. Developing locally and publishing the changes to an on-premises instance of SQL Server. We will look at how we can do continuous integration between development and other environments pushing the schema changes and upgrading the version numbers in a seamless manner upon successful build of the solution.

For more info about TFS, please read following: TFS overview

I am using the free version of TFS called TFS Express that can be installed locally. It is suitable for small projects only and is limited to five concurrent users. Download it here.

For creating a Visual Studio Online solution and more info about Visual Studio Online click here

On and Off-premises

At the time of writing, it is not possible to have Visual Studio Online (VSO) deploying to a local instance of SQL Server as VSO cannot “see” your on-premises SQL Servers. Even with a Hosted Build Controller.

The scenario

I am a database developer and I am working on an existing Azure SQL database that need some changes. I have some simple tables and I need to add a stored procedure to create customers ranking and a table to get hold of the customer ranking. The ranking should be calculated according to how much the customer has bought.

Rank Name Description
Inactive No orders
Bronze Orders under $100
Silver Orders $100 to $999
Gold Orders $1,000 to $9,999
Platinum Orders $10,000 and more

Stored Procedure:

Listing 1: Stored procedure for ranking customers

Creating the project

Creating a project from an existing database in SSDT is straightforward.

In another blog post I will have a detailed look at the different ways to work with new and existing databases but for now let’s import an existing database in our project.

  1. Create a new SQL Server Database project
  2. Right click on the project name -> Import -> Import database
  3. Create a new connection to the server (or use an existing one, if one exists)
  4. Choose the database to import
  5. Leave the other settings as they are.
  6. After a few seconds the import should be complete

    Database import complete Figure 2: Database import complete

The first thing I always do when working on an existing database is taking a baseline for the solution. So I have an initial state of the database I eventually can return to when working on it. This is done by right-clicking on the database and choosing snapshot project. It will generate a dacpac file holding the database schema of the newly imported database. I usually name the file “baseline” and leave the timestamp so I now when it was created.

Adding the solution to Visual Studio Online

Now I need to add the database to the Source Control provider, in this case Visual Studio Online.

  1. Choose Team -> Connect to Team foundation Server and connect to my Visual Studio online project folder
  2. Now I can Choose File -> Source Control -> Add to source control
  3. And all my items should be added (but not checked-in)
  4. Check in now to save all items in the source control repository.

Implementing the changes

Now that I am done I can implement the changes necessary to database, adding the ranking table, the stored procedure and making changes to the customer table so it references the ranking table.

Listing 2: adding Foreign Key to Customer table

I also add data to the CustomerRanking table as described in the scenario above

There is also another table that I think it is important to add: Deployment. The idea behind this table is to keep track of all the deployments I’ve made, using a post deployment script that is always executed each I perform my continuous integration

Listing 3: Adding Deployment table

Now that I’ve done my changes I can build my project (necessary before taking a snapshot), take a snapshot project of my database, call it “change_complete”, and leave the timestamp as is.

Create a deployment profile

Before I can deploy I need to create a profile telling Visual Studio where to deploy upon successful build

  1. Right click the project and choose Publish
  2. Write the server name and the name of the database where it should be deployed to (in my example ContinuousIntegrationINT), but it can also be on Azure or other places
  3. Don’t publish but save the profile and give it a meaningful name
  4. Close the dialog without publishing
  5. If you are connecting using a user name and password you need to manually edit the publish profile file afterwards as the password is not saved.

    Create a deployment profile Figure 3: Create a deployment profile

Implementing the continuous integration

The idea is to have the database deployed each time I check the source code in. At the same time I want the deployment table to keep track of the deployments.

To achieve that I start by creating a post-deployment script:

  1. Right click on the project -> Add -> New item -> Post-deployment script (in the User script category)
  2. Add some SQL to increment the deployment version and also to populate the CustomerRanking table and every other table that needs data.

    Listing 4: Post deployment script

For modifying the deployment options go to the Source Control explorer tab and add a new build definition.

Adding a build definition
Figure 4: Adding a build definition

This is the page where you can control what happens during a build.

What we want here is to tell Visual Studio to use msbuild to deploy the database on any successful builds. This is done by:

  1. On the Trigger page, choose Continuous Integration

    Continuous integration option Figure 5: Continuous integration option

  2. On the process page make sure that the project is listed on the item to build property
  3. On the process page I add the necessary build arguments

    Process page Figure 6: Process page

The arguments necessary for the example are:

  1. /t:build (builds the source code)
  2. /t:publish (published the code using the publish file)
  3. /p:SqlPublishProfilePath (the path of the publish file – relative to the project)

So in my case the arguments (separated by space) would be:

/t:build /t:publish /p:SqlPublishProfilePath=ContinuousIntegrationINT.publish.xml

You’re done!

Save the New build configuration. Every future check in should trigger a build and a publish to the database specified in the publish file.

Next time you check in it, your build definition should kick in and build the project:

Team Explorer - Build definition shown

The Build menu of the Team Explorer should show the current build:

Team Explorer - Build menu

And finally the Build log should show the progress and status:

Build log showing the progress and the status

Finally the database specified in your Publish file (ContinuousIntegrationINT) should be created where you specified it and the schema copied and your post deployment script executed so the Deployment table should show the data of the deployment

The Deployment table

In the next post I will show you how to do unit testing of your SSDT database project. In the meantime: Happy deploying!


Régis Baccaro
Continuous integration (CI), Data Tools (SSDT), DevOps

About Régis Baccaro

Régis is a Principal Consultant at Rehfeld a/s working with databases and administrations since 1999. He has been working with Business Intelligence since SQL Server 2000 and with SharePoint since 2003. He specializes in mentoring, performance tuning and scalable architecture of datawarehouses and bi-solutions. His background and work experience mixes technology and business, providing him a unique edge to contribute to datawarehouse and SharePoint projects, being able to effectively interact with technical and business decision makers. Régis is a frequent speaker at SQL Server conferences and is a board member of the Danish SQL Server User Group and the founder of the SQL Saturday Denmark community event. In april 2014 Régis received the MVP award for SQL Server for his involvment in the product and the community. View all posts by Régis Baccaro

168 Views