I'm trying to generate an output dacpac file which I can then deploy to different environments. Until now I was deploying directly from msbuild using the target publish, something like:
msbuild dbproject.sqlproj /t:Build;Publish /p:SqlPublishProfilePath=test.publish.xml /p:UpdateDatabase=True /p:PublishScriptFileName=test.sql
The size of the ouput script generated (just for reference as UpdateDatabase is set to true) is 2.5MB.
My new approach is to just build the project, store the dacpac and then deploy using sqlpackage with action 'Publish'. This new method is reporting some warnings which were not reported by msbuild SqlPublishTask.
For reference I ran the next command:
sqlpackage.exe /action:script /outputpath:test.sql /sourcefile:dbproject.dacpac /pr:test.publish.xml
and the size of the output script is now 4.9MB.
The specific warnings reported are not the concern here, the concern is that the script is different, my question is what is the difference?
and more importantly which one would be the best/safest method to do the deploy?
Sqlpackage.exe is more detailed and includes more objects during the deployment, it drops the permissions to tables to avoid any modification during the modification of the objects and then it proceeds to create or modify the them.
The difference is that ALL the objects are included in the script of sqlpackage, independently on whether they are different or not from the target database.
Although the warnings given were not a concern initially, using the sqlpackage method I discovered that some store procedure were not in the solution, only in the database. These 'orphan' sp were referencing tables being deployed, which is giving a warning stating that a change in the table may break the sp.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With