Deploy DACPAC with SqlPackage.exe with references to master and msdb

4
votes

We have a Visual Studio DB-project that has DB-objects that reference to the 'master'- or 'msdb'-database.

Now there are two cases:

(1) With having NO references to the system databases 'master' and 'msdn' in the project settings, it is possible to build the project but there are a lot of warnings because the references to 'master' or 'msdb' could not be resolved. Deployment of the DACPAC is no problem.

(2) With having references to the system databases 'master' and 'msdn' in the project settings, it is possible to build the project without any warning but deployment is only possible when also 'master.dacpac' and 'msdn.dacpac' are delivered with our projects DACPAC.

So both cases work, but in the first case we have a lot of warnings during development. In the second case 'master.dacpac' and 'msdn.dacpac' have to be delivered to the customer and I don't know if they are also updated during deployment.

So is there a way to solve this disadvantages of both cases?

The target platform for the DB-project is set to SQL Server 2012. We want to use the DACPAC file for updating SQL Server 2012 and SQL Server 2014.

1
sql-servervisual-studiosql-server-data-toolsdacpac
What do you mean "have to be delivered to the customer", are you transferring the dacpac to the client's server? How are you deploying the dacpac? I'm wondering because I have a similar issue. - Davos
Yes we are transferring the dacpac to the customer and deploy it using SqlPackage.exe - user1027167
Do you store the dacpac in the same directory structure on that client's server? I have an issue where the dacpac is storing hard-coded paths to the references. - Davos
If you unzip the dacpac file, there is a model.xml file which contains the paths to the referenced databases. How did you get this to work in just the same folder, i.e. relative path reference? Do you do something like build to C:\Project on your machine, and then copy the files to C:\Project on the CLient's server? I think if you copy to C:\SomeOtherFolder on the client's server then it doesn't work, SQLPackage.exe won't be able to find the referenced databases because it will expect them to be in C:\Project - Davos
I know this references in model.xml. On our target machine the absolute pathes do not exists. I think I read somewhere that SqlPackage.exe will take the referenced dacpacs that are at the same location of the project-dacpac, if they are not at the correct referenced location. So for us it works this way. - user1027167

1 Answers

3
votes

Go with option 2, they do not actually deploy to msdb or master they are only used to validate the code in your dacpac.

ed