Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Differences between SqlPackage.exe and MsBuild SqlPublishTask

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?

like image 556
Cristian T Avatar asked Nov 09 '22 08:11

Cristian T


1 Answers

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.

like image 143
Cristian T Avatar answered Nov 14 '22 23:11

Cristian T