Sqlproj: Which solution to deploy a database in command line?

deployment msdeploy vsdbcmd sqlproj sqlpackage
JM89 picture JM89 · Apr 10, 2015 · Viewed 9.2k times · Source

I am currently deploying a website to a server by using the Microsoft Web Deployment technologies (msbuild and msdeploy commands). The website requires the deployment of a database and both will be in the same server. I am searching the best solution to deploy a database project (with a command line) and I would like to understand better all the technologies around database deployment.

Content of the solution (Visual Studio 2013):

  • a database project (for SQL Server 2008),
  • a class library (Data access layer with NHibernate)
  • a web project (ASP.NET MVC4).

Note: I am not using voluntarily a continuous integration/delivery tool or publish method in VS. My first goal with this project was understanding how msbuild / msdeploy work...

I had a look at the vsdbcmd command which seems to do all the steps I want... except I would need to import Visual Studio DLLs/files into my remote server and I wonder if there is no better way... I also looked at the msdeploy providers dbSqlPackage/dbDacFx, but from what I understood, it is using a dacpac for applying the schema changes. Similarly, the SqlPackage.exe seems to use a dacpac as well.

Using a dacpac sounds a good idea, but I am confused with the following questions:

  • Does that mean that I would need a different process the first time my database is created? If yes, which command would be the best?
  • Is it possible to create a dacpac from my sqlproj file? If yes, how to do it?

Are there other ways of deploying from a command line and from your experiences and projects, what was the best way to deploy this kind of project?

Many thanks,

Answer

JM89 picture JM89 · Apr 10, 2015

With further research, I discovered that Visual Studio was creating a dacpac when it builds a sqlproj (bin/Debug or bin/Release depending on your build configuration). The first time you deploy, the dacpac create your database. When you do schema changes, it seems to apply them.

Here a sum up of the command lines for the website and for the database:

Website build

msbuild %fullpathwebcsproj% /P:Configuration=Release /T:Package

Website deployment (default application pool)

msdeploy -verb:sync -source:package=%fullpathpackage% -dest:auto

%fullpathpackage%: the path of the zip file created by msbuild when /T:Package is there (bin/Release)

Database build:

msbuild %fullpathsqlproj% /P:Configuration=Release

Database deployment:

msdeploy -verb:Sync -Source:dbDacFx=%fullpathdacpac% -Dest:dbDacFx=%connectionstring%

This solution satifies me for now. Nonetheless, I am still open to comments and suggestions for improvement.

Related questions

Web deployment task failed. Could not connect...server did not respond

I've been publishing my Lightswitch app using Visual Studio 2012 RC to my localhost (Win 7, SQL 2008 R2, IIS 7.5) just fine. Now I'm trying to publish to a remote server (Win 2008 R2, SQL 2008 R2, IIS 7.5) and I'm having trouble. When I try …

Read More
Make MSDeploy (Visual Studio) not delete App_Data folder but delete everything else

I'm using Visual Studio's Publish button to deploy my website, and want a different App_Data folder on the server. There's a checkbox for Leave extra files on destination (do not delete) which prevents my App_Data folder from getting …

Read More
Visual Studio 2010 Publish Web feature not including all DLLs

I have an ASP.NET MVC 2 application. Web project contains a reference to SomeProject SomeProject contains references to ExternalAssembly1 and ExternalAssembly2. SomeProject explicitly calls into ExternalAssembly1, but NOT ExternalAssembly2. ExternalAssembly1 calls into ExternalAssembly2 When I perform a local build everything …

Read More