By: Ray Barley | Comments (13) | Related: > 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:
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:
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:
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:
Click Builds in the Team Explorer to create a new TFS Build Definition.
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
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.
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.
You can browse for the folder(s) by clicking the button in the Source Control Folder column:
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.
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.
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.
The Queue Build dialog will be displayed; review the settings and click Queue to launch the build.
Return to the Team Explorer, look under My Builds, and locate the build you just launched.
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:
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:
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.
About the author
Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
