THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

Schema Compare or Publish [SSDT]

Yesterday on Twitter Ryan Desmond asked Is there a good read for #SSDT regarding deploying changes via schema compare vs solution deployment?


I don’t know of any article that covers this so in this blog post I offer my opinion on the subject.

First some background. When building databases offline using the database project type (.sqlproj) in SSDT you have two options for deploying the DDL code in your project into a physical database:

  • Schema Compare
  • Publishing

Under the covers both do the same basic operation; build a dacpac from your project, compare it to the target database, build a script that will make the requisite changes to the target database, execute that script.

Ryan was asking which of these one should use. I suggested that publishing was a better option and here are two reasons why:

  1. Publish will include your pre and post deployment scripts as well whereas Schema Compare will not. (And if your retort is that you cannot run those scripts more than once then you’re doing it wrong, rewrite them.)
  2. If the debug target for your project is configured correctly then a publish operation can be triggered simply by pressing F5. That’s massively more productive than the point-and-click nature of Schema Compare. Its even better if you have multiple SSDT projects in your solution as you can publish all of them with a single key stroke.

Does anyone out there have a different opinion? Let me know in the comments.


Published Thursday, December 19, 2013 9:11 AM by jamiet
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Martin Cairns said:

I tend to use the Schema Compare as check that the changes been released to Live are what I expected and to make sure that any schema changes are safe (i.e. not doing a huge INSERT into a new table and rename) and use publish if possible.  I tell other team members to use build and then publish on development, which has worked well so far.

December 19, 2013 3:54 AM

Mark Wojciechowicz said:

For your first point, I rely on publish. However, schema compare is very useful for analyzing changes, performing drops and for constraining a comparison to a lower granularity of objects to compare or deploy. I.e. one can exclude users, so they are not dropped or added in the target.

All that said, publish ensures that the entire database is deployed and will be in working order once the operation is complete. It also allows for automated deployment in the case of CI.

I do wish that I could see the preview report before I published the changes though.

December 19, 2013 7:07 AM

Aaron Lowe said:

For PROD we do a schema comparison to create and review the actual T-SQL itself prior to running.  However for a DB where we have view partitioning (i.e., multiple tables with UNION ALL view) we had to go to RedGate for performance reasons.

On DEV/QA/UAT it's automated publishing via TFS builds (whether CI, scheduled or Manual).

Any other environment local, etc), we let the person doing the work make that decision.

One of the pro's of publishing is that everyone can share the publish file as it's checked in.  I have found that SSDT schema compare is a little more difficult when sharing a template with specific options.

December 19, 2013 8:11 AM

Peter Schott said:

Aaron, you may want to consider automating your script/diff generation. You can use SQLPackage to generate a diff report in XML and a change script as well. I've done that for several of our builds. We even have that set as an option in our automated build jobs to either just gen the report/change script or to also push the changes. (We haven't built the process yet to just push changes instead of creating the report/script, but it's something we could add.)

Those publish files are great for sharing. It's made our releases - local and to server - much easier.

December 19, 2013 6:33 PM

Robert L Davis said:

We export the dacpac from production once a week. The dev team uses the production dacpac as the target for the schema compare and provides scripts for us based on this output.

January 7, 2014 3:39 PM

Leave a Comment


This Blog


Privacy Statement