I recently created a new automated deployment for a SQL Server database using the SQLDeploy Nuget package. This was for a 7 year old SQL database which, as you could imagine, is fairly large/complex. I used the Schema Import SSDT feature to initially populate my Database Project in Visual Studio which worked great and pulled in all the schema and database objects from a real database instance.
This included some objects that I didn’t want to deploy to all environments. For example, it included all users and roles for that instance but didn’t include other valid users/roles for other environments. Since a DacPac deployment attempts to synchronize the source and destination it would try to add the roles/users which don’t apply and drop roles/users that are appropriate for the given environment.
In my immediate case, I’m redeploying to pre-existing environments so I don’t need to deploy roles/user at all. Luckily, Microsoft has this covered through DacPac deployment options. You can learn more about DacPac deployments at:
Lots of Options!
A list of all the available options are listed on the following MSDN site:
https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dac.dacdeployoptions.aspx
These options can be setup in several ways.
Options in the GUI
You can set the option you want from the in the Advanced Publish Settings when deploying from Visual Studio:
Options from the CommandLine
But what about through the commandline? Yes, you can also set options too when using MSDeploy.exe to deploy a DacPac to a target:
>”c:\Program Files\IIS\Microsoft Web Deploy V3\msdeploy.exe” -verb:sync -source:dbDacFx=”%cd%/Db.dacpac” -dest:dbDacFx=”Data Source=db-server-1;Initial Catalog=Db-ci;Integrated Security=True;Connect Timeout=30″,IgnoreRoleMembership=’true’,CommandTimeout=120
Options from SQLDeploy
SQLDeploy also supports passing through options to MSDeploy.exe:
So no matter how you want to deploy, you can control how the DacPac is deployed to the target.
If you found this post helpful or have further questions please leave a comment below.
Happy Deploying!