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

Question

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 1

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.

Answer 2

Ok so a .sqlproj file is compiled into a dacpac file which is basically a zipfile containing any pre/post deployment files and an xml file with the contents of all your sql scripts (the model).

When you use msbuild it uses the DacFx api to compare and publish the changes in the dacpac to the sql database.

You can use msbuild or you can use the DacFx api yourself or normally people use sqlpackage.exe to take the dacpac and compare to a database, it can then either generate a script so you can run it manually or it can run the script it generates to update the database.

You can do other things with sqlpackage such as generating a deployment report of all the things that it would change if it was asked to.

You generate a dacpac by building your project, it will be in your output directory.

It is idempotent so that means no matter how many times it runs, after the first instance it always ends up with the same result - so everytime you change your code, build the dacpac and deploy it - if the database doesn't exist then it will be created, if it does exist it will just deploy the changes.