Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Introduction to Team Foundation Server (TFS) Build for Business Intelligence (BI) Projects


By:   |   Last Updated: 2015-04-23   |   Comments (13)   |   Related Tips: More > DevOps

Problem

I am new to TFS and I have heard that I can use it to publish a SQL Server Database project. I would like to be able to publish in multiple environments (e.g. development, staging and production) as well as on a schedule and on demand. Can you help to get me started?

Solution

TFS is Microsoft's application lifecycle management (ALM) product that provides the tools you need to effectively manage software development projects throughout the IT lifecycle. If you are familiar with the term continuous integration, TFS is the Microsoft product that you could use. I use the version control and build tools in all of my projects.

TFS Build is a great feature that allows on demand and scheduled builds of BI projects as well as just about any other kind of project. The basics of TFS Build are that you create a Build Definition specifying the project(s) to be built, where they are in TFS version control, and how the build will run; e.g. on demand or on a schedule. When the TFS Build runs, a TFS build server retrieves the code from TFS version control and executes the build. In the case of a SQL Server Database project, the build will create a DACPAC file which represents the schema of a SQL Server database. In other cases such as a .NET application, the TFS Build will compile Visual Studio project(s) that create a DLL, EXE, etc. TFS Build can also execute the Publish on a SQL Server Database project which can create a database (and all of its schema objects) or update a database to match the schema of the SQL Server Database project.

The secret to TFS Build is that it just requires an MSBUILD project file. The SQL Server Database project file is in fact an MSBUILD project file so setting up the TFS Build is pretty easy.

MSBUILD is Microsoft's command line executable that Visual Studio leverages to build projects. MSBUILD reads MSBUILD project files (i.e. an XML file) and performs the build steps per the project file. MSBUILD can also be used from the command line.

In this tip I will walk through setting up and executing a TFS Build for a simple SQL Server Database project. I will assume that you already have the SQL Server Database project stored in TFS version control. I'm using the SQL Server Data Tools version for Visual Studio 2012.

SQL Server Database Project

The SQL Server Database project allows you to store the T-SQL scripts for the objects (e.g. tables, views, stored procedures, etc.) in your database in a Visual Studio project. You can use the project's Publish capability to create a SQL Server database and populate it with the objects defined in the project. You can also synchronize a database to the project; i.e. update a target database schema so that all of the database objects are the same as defined in the project. You store the SQL Server Database project in TFS version control to keep track of the current state of the database objects as well as the history of changes.

The SQL Server Database project allows you to create one or more publishing profiles. A publishing profile contains the parameters that you need to publish the project to a particular target database. To create a publishing profile, right click on the project in the Visual Studio Solution Explorer and select Publish from the context menu. You will fill in the following dialog:

Publish Profile Dialog

The following are the main points about the above dialog:

  • The Target database connection identifies the target SQL Server
  • The Database name is the database on the target SQL Server
  • Publish script name is the filename for the generated T-SQL script that creates / updates the database objects
  • Click Advanced to specify various database properties and settings (there are a lot of them; you definitely need to review these and update as necessary)
  • Click Save Profile As to save the publishing profile in the project
  • Click Generate Script to see the T-SQL script that will be executed when you publish; you need to review this before publishing
  • Click Publish to manually publish the database

I saved my publishing profile as DEV; here is the Solution Explorer showing my DEV profile:

Solution Explorer DEV Profile

Note that you can create multiple publishing profiles; e.g. DEV, STAGING, PROD, etc.

The above screen shot shows a very simple SQL Server Database project that only has two tables and the DEV publishing profile. It is a subset of a database project that I use to dynamically create measure group partitions in a SQL Server Analysis Services cube. This will be sufficient for demonstrating how to setup and run the TFS Build.

Before we move on to setting up the TFS Build, we need to check the Target platform property of our SQL Server Database project. Right click on the project in the Solution Explorer and select Properties from the context menu. Make sure the Target platform has the appropriate SQL Server database version:

Project Properties

TFS Build Setup

We use the Visual Studio Team Explorer to setup the TFS Build. Click the top-level View menu in Visual Studio and select Team Explorer. By default the Team Explorer will share a window with the Solution Explorer as shown below:

Team Explorer

Click Builds in the Team Explorer to create a new TFS Build Definition.

Team Explorer Builds

Click New Build Definition in the above dialog. You will now fill in the dialogs as shown below.

General

  • Name the build; I append DEV to the name to specify which environment; e.g. DEV, STAGING or PROD
  • Fill in the optional description
  • Click Enabled so the Build can be executed
TFS Build General

Trigger

The Trigger allows you to specify when the Build is executed. I generally use Manual as this lets me execute the Build on demand. There are a variety of options to execute the build on a schedule.

TFS Build Trigger

Source Settings

Source Settings allow you to specify the folder(s) in TFS version control that contain the project(s) that you want to Build.

TFS Build Source Settings

You can browse for the folder(s) by clicking the button in the Source Control Folder column:

TFS Build Source Settings Browse

Build Defaults

Select the TFS Build Controller and whether the build creates output files that need to be copied somewhere; e.g. a DLL or EXE.

TFS Build Build Defaults

Process

There are quite a few parameters on the Process dialog; at a minimum you will need to specify the following:

  • Select the Project(s) to build
  • Specify the MSBuild Arguments (TFS Build leverages MSBUILD)

Click in the Projects to Build text box and you can select the project(s) from a popup window. The MSBuild Arguments requires a little bit of explanation. I found this post on the SQL Server Data Tools Team Blog which had the details in the section "Publish Option 1: Setting MSBuild arguments in the Build Definition. Here are the details:

  • /t is the MSBUILD command line parameter where you specify the target(s) to build. The Build target "builds" the SQL Server Database project (i.e. creates the DACPAC file) and the Publish target performs the publish operation (i.e. deploys the database to the target server specified in the publishing profile).
  • /p is the MSBUILD command line argument where you specify the value for a property. Deep inside the SQL Server Database project collection of MSBUILD project files, you will find the SqlPublishProfilePath property that specifies the publishing profile name. The publishing profile name is one that is contained in the SQL Server Database project.
TFS Build Process

Note that you will generally create a TFS Build for each of your publishing profiles; e.g. DEV, STAGING, PROD, etc.

Execute the TFS Build

To execute a TFS Build, return to the Team Explorer and click on Builds. Right click on the build you want to execute and select Queue New Build.

TFS Build Definitions

The Queue Build dialog will be displayed; review the settings and click Queue to launch the build.

Queue Build

Return to the Team Explorer, look under My Builds, and locate the build you just launched.

New Build

Double click the build (e.g. 251 just started) and the build log will be displayed showing the progress of the build. Below is the build log after the build has completed:

Build Log

If there are any errors, they will be noted in the build log. In this case the build was successful. You can open SQL Server Management Studio, connect to the SQL Server specified in the publishing profile and see that the database has been created or updated.

Troubleshooting

My experience with TFS Build over the last three years has been good. I haven't had many problems but there is one that pops up occasionally so I thought I would mention it here. I've gotten this error a few times:

TFS Build Error

It's a pretty simple one to fix; someone has logged in to the Build server, opened Windows Explorer, and navigated to a folder that the TFS Build needs exclusive access. The solution is to close Windows Explorer or navigate out of the folders that the TFS Build needs.

Next Steps
  • If you haven't taken advantage of TFS Build you should definitely give it a try. It can perform a build / deploy with just a few clicks.
  • You can download the sample SQL Server Database project here. The sample code does not include the build definition; I do not know how to extract that from TFS.


Last Updated: 2015-04-23


get scripts

next tip button



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, October 25, 2016 - 8:36:14 PM - Ray Barley Back To Top

 

 

The source code control capability in TFS can be used with any project type in Visual Studio; e.g. SQL Server Database, SQL Server Reporting Services, SQL Server Analysis Services, SQL Server Integration Services, etc.  The TFS BUILD capability in this tip can be used for most of the Visual Studio project types but not all of them.  It handles the project types that are MSBUID project files.  Last I checked, SQL Server Reporting Services, SQL Server Analysis Services, SQL Server Integration Services project files are not MSBUILD project files so they don't work but you can create your own MSBUILD project files to leverage the BUILD capability.


Tuesday, October 25, 2016 - 3:54:41 PM - Pierre Gagnon Back To Top

The title of this article specifies Business Intelligence (BI) projects. You are describing the Database layer only. Where is the BI Layer? Can we really use TFS for the BI components such as BI Models, BI Reports?


Wednesday, May 20, 2015 - 4:20:02 AM - yogita jadhav Back To Top

Thanks Ray!!


Wednesday, May 20, 2015 - 4:18:31 AM - yogita jadhav Back To Top

 

i need guidence for what configuration is necessary for 5  developers


Tuesday, May 19, 2015 - 10:32:59 AM - Ray Barley Back To Top

This is the starting point for information on TFS: https://msdn.microsoft.com/en-us/vstudio/ff637362.aspx


Tuesday, May 19, 2015 - 9:57:21 AM - yogita jadhav Back To Top

Hi all,

Actually we are 4-5developers want to use TFS is der any extra resource need to manage that TFs, how it works,and which configuration i need to purchase ,is der any idea about cost,i don't know how it works and how to use main thing is we need it for integration purpose.

if any one have a answer please mail me at [email protected],its some thing urgent


Friday, April 24, 2015 - 7:46:22 PM - Ian Back To Top

Interesting article but in the Problem section you mention the person is new to TFS and wants to know how to get started. In the solution you assume the person already has the database project in TFS. I feel like I need the part that explains the assumption.


Friday, April 24, 2015 - 3:06:13 PM - Bob Back To Top

Thanks, but that, like most everything else I've read talks about connecting to TFS from an older version of Visual Studio and managing SQL from VS.  I am looking to connect SQL SSMS to TFS.  I'm sure it can be done as there is a setting under tools / options / source control.. where a plug-in can be assigned.. Still looking, maybe I missed what you were refering too.


Friday, April 24, 2015 - 1:03:07 PM - Raymond Barley Back To Top

I think yes but I haven't done this myself.

 

Try this search and you will get a couple of hits that look promising:

vs2008 r2 tfs 2013

 


Friday, April 24, 2015 - 11:37:15 AM - Bob Back To Top

Can I connect SSMS 2008R2 to TFS 2013 without RedGate Source Control ?  HOW ??  I have spent far too much time chasing my tail in the "interweb" with no clear solution.  Can you or anyone reading this provide some clear insight ?


Friday, April 24, 2015 - 8:49:08 AM - Raymond Barley Back To Top

I have done some work with SSAS, SSRS and SSIS and TFS Build.  TFS Build uses MSBUILD project files to perform the build.  You have to create your own MSBUILD project files for SSAS, SSRS and SSIS in order to get them to work.  In most cases you use the MSBUILD built-in Exec task which is essentially a DOS command prompt which allows you to execute any command you want.  You will have to do things like execute Visual Studio to build or rebuild your project, execute the Analysis Services Deployment wizard, etc.  I have some tips planned for these.  Below are some pieces of MSBUILD project files.

 

For SSRS you can use the Exec MSBUILD task and run RS.EXE.

 

<!-- Tools paths -->

<PropertyGroup>

<VisualStudioEXE>$(MSBuildProgramFiles32)\Microsoft Visual Studio 11.0\Common7\IDE\DEVENV.COM</VisualStudioEXE>

<SSASDeployEXE>$(MSBuildProgramFiles32)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Microsoft.AnalysisServices.Deployment.EXE</SSASDeployEXE>

</PropertyGroup>

 

<Target Name="Build">

<Exec Command="&quot;$(VisualStudioEXE)&quot; &quot;$(ProjectFile)&quot; /Rebuild &quot;$(BuildConfiguration)&quot;" />

</Target>

 

<Target Name="Deploy" DependsOnTargets="UpdateConfigSettings">

<Exec Command="&quot;$(SSASDeployEXE)&quot; &quot;$(ASDatabaseFile)&quot; /o:&quot;$(MSBuildProjectDirectory)\Pandara.SSAS.xmla&quot;" />

<Exec Command="&quot;$(SSASDeployEXE)&quot; &quot;$(ASDatabaseFile)&quot; /s:&quot;$(MSBuildProjectDirectory)\Pandara.SSAS.log&quot;" />

</Target>

 

Friday, April 24, 2015 - 8:40:15 AM - Brian Back To Top

Do you have the same type of example for SSAS projects?


Thursday, April 23, 2015 - 4:03:08 PM - Erik Svensen Back To Top

Hi

Very interesting read.

Do you have any experience in using TFS for SSAS and SSRS ?

BR

Erik


Learn more about SQL Server tools