I was reading a white paper from a Sql Server tools vendor about how to integrate their tools with SSDT and while it was very interesting there were a couple of inaccuracies and I wanted to clear one of them up and also to help people understand that although SSDT is integrated to Visual Studio it is not tied to it in any way to TFS. You can use SSDT with any source code provider such as TFS, Git, Mercurial, Svn or Source Safe etc although you really should not use source safe for anything as it is the least likely tool to keep your source safe!

To help descibe why and how SSDT can work with any source control system, it helps to understand how it works:

Step 1

If we start at the beginning, we have .sql source files on a file system - these files can be anywhere that is accessible by the machine, either in a folder on the machine running the build or a network share. SSDT uses Visual Studio project files, .sqlproj files to group together and include .sql files in the project. If the files are either not in the project, folder or sub-folder then they are not included in the build process.

Step 2

Msbuild reads the project file and all of the source files and compiles them into a dacpac. If there are any errors then you do not get a dacpac - this is like developing a program in c++ or c#, it has to compile to be useable.

A dacpac is a zip file of the contents of the .sql files that made up the project and some additional information, you can rename the file to a .zip and extract the xml files or use my dacpac explorer sample (https://sqlserverfunctions.wordpress.com/2014/09/26/dacpac-explorer/) to see what is in the dacpac.

Msbuild can be called from Visual Studio by doing a build, which is what happens as you are developing or it can be called independently of Visual Studio, normally by a build server or script.

Step 3

To deploy the dacpac either msbuild calls an internal task to publish the dacpac, if you are in Visual Studio or more common sqlpackage.exe compares the contents of the dacpac with a live Sql Server database. The output is then either a deployment script, if you choose to create a script or optionally sqlpackage.exe can run the deployment script against the Sql Database it just used to create the deployment script.

Regarding the deploy.sql I have in the diagram, although there is actually a script created, it is hidden from the user and to get a script you need to explicitly ask sqlpackage.exe for one.