Build SQL Server Data Tools (for Visual Studio 2013) project on build server without Visual Studio

Question

We have a C# web project authored in Visual Studio 2013 which incorporates a Database Project, and I've set up a number of TeamCity build configurations to do Continuous Integration.

Team City is working well for us at the moment with all of our code checkins going automatically into the integration environment.

SSDT is also working well for us, we are easily able to share and publish database changes within the development team.

What I want to do now is get TeamCity to automatically build the Database Project and deploy it into the integration environment.

We do not have VS2013 on the build server, and would rather not install it there. We are all running SQL Server 2012.

I have read a few questions and articles about "headless" builds of *.sqlproj projects e.g:

How to build .sqlproj projects on a build server?

These approaches seem to be quite manual and fiddly and related to SSDT 2012 - has anyone got a reliable configuration for the versions we're using?

Answer 1

We've got SSDT working on our build server without requiring a copy of Visual Studio. In our case, we did a full install of SSDT because that will give you the necessary components, including the Visual Studio IDE shell. It was also a bit less fiddly than trying to make the admin install point and install just those bits. We added the folder containing sqlpackage to the path and made sure that we could get to the latest MSBuild executable (part of the .NET framework). There's no need to install a full copy of Visual Studio unless you happen to need it for some other reason on that machine.

This article goes into a bit more detail: http://sqlproj.com/index.php/2012/03/headless-msbuild-support-for-ssdt-sqlproj-projects/

Summarizing that article, here's what you need:

1. Install the Microsoft® SQL Server® 2012 Data-Tier Application Framework
2. Install the Microsoft® SQL Server® 2012 Transact-SQL ScriptDom
3. Install the Microsoft® SQL Server® 2012 Transact-SQL Compiler Service
4. Install the Microsoft® System CLR Types for Microsoft® SQL Server® 2012
5. Install the SQL Server Data Tools Build Utilities from the Administrative install point. (this is where we used the full install)

It's been a while, but you might be able to just make sure that machine is connected to the Internet and run the latest SSDT installer to install SSDT and all of the pre-reqs. At that point, you've got the necessary bits to build SQL Projects.