deploymentmsdeployvsdbcmdsqlprojsqlpackage

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


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):

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:

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,


Solution

  • 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.