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?

Plucky

I had hoped Microsoft would de-couple SSDT Data Projects from its other software so we could use them in a way that suits us. However I'm starting to worry that they've instead tightly bound it to Visual Studio, TFS and Azure - which is the opposite of what we want! – Plucky

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.

Krone

Thanks for the info. I had seen that blog entry before (which seems suspicious in its intricate level of detail and no credited author I can see) but was hoping for a simpler method. MS seems to have merged SSDT into VS2013: blogs.msdn.com/b/ssdt/archive/2014/01/31/… I might try my luck with the SSDT2012 install and see if by some miracle it just works. – Plucky

Oh there it is - @datadude - that's a bit of MS history that totally escaped me! – Plucky

Unless you're using SQL 2014 CTPs, you should be good with the stand-alone bits. I haven't tried to do any SSDT work in VS 2013 yet. You should be able to test it before hitting your release box, though. Just install the 2012 SSDT bits locally and try to open the sqlproj file and build/publish. If it works without issue, you're likely good. There should still be some standalone SSDT w/ the VS2013 IDE coming even if it's not out yet. Check the official SSDT blog for those details: blogs.msdn.com/b/ssdt – Krone

Good news. I installed SSDT 2012 on a fresh VM, and could then successfully build a VS2013 sqlproj from the commandline using MSBuild. It must have brought all the necessary pre-reqs with it. Not wild about having the Visual Studio integrated shell on there too, but at least it was only one install. I have just successfully repeated this on the build server. – Plucky

The link to sqlproj.com doesn't work any more, or at least, it doesn't work right now. – Tumescent