The goal here is to use SSDT SqlPackage to deploy the output of a Visual Studio 2012 Database project… a bit in the same fashion that was detailed here : http://geekswithblogs.net/80n/archive/2012/09/12/vsdb-to-ssdt-part-3--command-line-deployment-with-sqlpackage.exe.aspx

 

The difference is we want to do it on an environment where Visual Studio 2012 and SSDT are not installed. This might be the case of your Production server.

 

Package structure

So, to get started you need to create a folder named “DeploymentSSDTRedistributable”. This folder will have the following structure :

 

   

 

• The dacpac and dll files are the outputs of your Visual Studio 2012 Database project. If your database project references another database project, you need to put their dacpac and dll here too, otherwise deployment will not work.
• The publish.xml file is the publish configuration suitable for your target environment. It holds connexion strings, SQLVARS parameters and deployment options. Review it carefully.
• The SqlDacRuntime folder (an arbitrary chosen name) will hold the SqlPackage executable and supporting libraries

 

Contents of the SqlDacRuntime folder

Here is what you need to put in the SqlDacRuntime folder  :

  

 

You will be able to find these files in the following locations, on a machine with Visual Studio 2012 Ultimate installed :

• C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin :

SqlPackage.exe

Microsoft.Data.Tools.Schema.Sql.dll 

Microsoft.Data.Tools.Utilities.dll

Microsoft.SqlServer.Dac.dll

• C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.TransactSql.ScriptDom\v4.0_11.0.0.0__89845dcd8080cc91

Microsoft.SqlServer.TransactSql.ScriptDom.dll

 

Deploying

 

Now take your DeploymentSSDTRedistributable deployment package to your remote machine. In a standard command window, place yourself inside the DeploymentSSDTRedistributable  folder.

 

You can first perform a check of what will be updated in the target database. The DeployReport task of SqlPackage.exe will help you do that. The following command will output an xml of the changes:

 

 

   You might get some warnings on Log and Data file like I did. You can ignore them. Also, the tool is warning about data loss when removing a column from a table. By default, the publish.xml options will prevent you from deploying when data loss is occuring (see the BlockOnPossibleDataLoss inside the publish.xml file). Before actual deployment, take time to carefully review the changes to be applied in the ChangesToDeploy.xml file. 

 

When you are satisfied, you can deploy your changes with the following command :

 

Et voilà !  Your dacpac file has been deployed to your database. I’ve been testing this on a SQL 2008 Server (not R2) but it should work on 2005, 2008 R2 and 2012 as well.

 

Many thanks to Anuj Chaudhary for his article on the subject : http://www.anujchaudhary.com/2012/08/sqlpackageexe-automating-ssdt-deployment.html