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?