THE SQL Server Blog Spot on the Web

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

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Publish Profile Files in SQL Server Data Tools (SSDT)

I have been using SQL Server Data Tools (SSDT) both at work and on some hobby projects for quite a few weeks now and of all the new features I have to say the one that I am appreciating the most is Publish Profile files. I have been searching around on MSDN for an article that explains Publish Profile files but it seems no such article exists so I’ll attempt to surmise here.

Publish Profile files are, essentially, a collection of all the property key-value pairs that are needed to deploy a database model (i.e. a .dacpac) to some target database. Those properties include (but are not limited to):

  • database name
  • connection string
  • whether or not to publish SSDT projects on which the current project has a dependency
  • Recreate the database from scratch (or not)
  • Backup before deploy
  • Drop unknown objects in target
  • SQLCMD Variables

Those that have used the predecessor to SSDT, Visual Studio Database Projects, will be familiar with a lot of those properties however in that case the properties had to be specified on a case-by-case basis; typically in an msbuild script or similar. Often those scripts are not maintained by a developer – rather they are maintained by a DevOps team that are not familiar with the code being deployed and as a developer myself that’s not a situation that I’m at all comfortable with. On my most recent project where Visual Studio Database Projects were being used I faced the maddening situation where every new SQLCMD variable added to a project required an email to be sent to the DevOps team to ask them to update their scripts accordingly. As you can imagine human errors crept in (on our side more than the DevOps side) and we ended up deploying projects with the wrong SQLCMD values. Moreover, we had to deal with different DevOps folks and often they would store the values in different places; totally infuriating, believe me.

Publish Profile files make this process much easier because we can define all those properties on a per-environment basis and keep them in a dedicated Publish Profile file. The obvious benefit then is that a Publish Profile file abstracts all of the environment-specific information into a single file so deploying an SSDT project now requires only two things; the build output (i.e. a .dacpac file) and a Publish Profile file:

>sqlpackage.exe /sf:MyDB.dacpac /pr:DEV.publish.xml

The implied benefit (and this is what I really like about them) is that Publish Profile files are a source code artefact that a developer maintains the same as they would any other source code artefact, all that the DevOps people need are the build output (i.e. a .dacpac file) and an appropriately named Publish Profile file. Developers are now wholly in charge of how their code gets deployed which keeps them happy and the DevOps people have less work to do – so they’re a happy bunch too! I’m not saying that the wrong values won’t ever be supplied but at least now we know exactly where to go to fix those errors.

SSDT also provides a friendly UI for maintaining these Publish Profile files. Double-click on one and this UI appears:

image

Its fairly self-explanatory to fill these things out. A connection string, a database name and values for each SQLCMD variable defined within the project and you’re pretty much there. (I have written previously about one other benefit of Publish Profile files - that they can be used to make SQLCMD variables mandatory. Read more at SQL Server Data Tools does support required variables.)

One minor downside of Publish Profile files is that they get created in the root of your SSDT project (I have griped about this previously at Folders in SQL Server Data Tools) so the convention that I have been using is to create a folder called Publish in each SSDT project and move all Publish Profile files into there.

image

Hopefully this has given you a small taster of what Publish Profile files are all about. I’ll probably share some msbuild scripts that we’re using to deploy these things in a later blog post.

@Jamiet 

P.S. As an aside, I have requested that the SSIS/SSAS/SSRS teams adopt the Publish Profile file approach in future iterations of their products. If you think that that is something you would like to see happen then click through, vote and leave a comment.

UPDATE: If you want a bit more info on Publish Profile files and sqlpackage.exe check out Ben Day's blog post Deploy a SQL Server Database project’s *.dacpac with SqlPackage.exe

Published Wednesday, May 09, 2012 12:07 AM by jamiet

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

Comments

 

Peter Schott said:

That's pretty cool. I knew there had to be something like that around, but this will be great once we get our projects cleaned up and are ready to push to different environments.  It will definitely save some typing on command-line deployments.

May 8, 2012 6:10 PM
 

jamiet said:

Hi Peter,

I knew you'd be the first person to comment here :) Thanks as always!

Yeah, Publish Profile files are very cool indeed. One more reason to upgrade.

JT

May 8, 2012 6:18 PM
 

SSIS Junkie said:

I’ve just discovered a new, somewhat hidden, feature in SSDT that I didn’t know about and figured it

May 31, 2012 6:55 AM
 

SSIS Junkie said:

I discovered a peculiar little nuance in SQL Server yesterday that I think is worth sharing. Execute

July 20, 2012 3:49 AM
 

Aaron Lowe said:

In VSDBPro I did something similar and was able to tie it into VS Configuration Manager.  So selecting the Dev Configuraiton automatically changed my deploy properties.  I have, so far, been unable to find that functionality in SSDT publish.  Have you seen a way to use VS Configuraitons to select which publish profile you want?

Thanks,

--Aaron

September 24, 2012 11:12 AM
 

jamiet said:

Hi Aaron,

No, I haven't VS configurations too much since Publish Profile files came along. I'm not sure if there is a way to do what you're after (that doesn't mean such a thing does not exist - only that I don't know about it).

Regards

Jamie

September 24, 2012 11:25 AM
 

SSIS Junkie said:

In my blog post SQL Server devs–what source control system do you use, if any? (answer and maybe win

January 26, 2013 5:41 PM
 

SSIS Junkie said:

Continuous deployment is described by Wikipedia as: Most CI systems allow the running of scripts after

January 27, 2013 3:07 PM
 

Jamie White said:

Great tip, thanks.

January 30, 2013 12:47 PM
 

Kyle Hale said:

Hey Jamie,

I was wondering if you could do a followup post about how you manage environment differences in your deployments using the publish profiles? Specifically we have different Windows service accounts for each tier (dev, uat, and production) and I was wondering how you might manage permissions for those segregated accounts in each tier using the Publish Profiles.

July 11, 2013 1:03 PM
 

Peter Schott said:

@Kyle, I built on Jamie's initial work on this and blogged about it here: http://schottsql.blogspot.com/2013/05/ssdt-setting-different-permissions-per.html

There's also a link to Jamie's original article - http://sqlblog.com/blogs/jamie_thomson/archive/2010/07/21/a-strategy-for-managing-security-for-different-environments-using-the-database-development-tools-in-visual-studio-2010.aspx

It's a bit of initial work, but once set up it doesn't change very often. :)

--Peter

July 11, 2013 1:08 PM
 

jamiet said:

Thanks Peter, you took the words right out of my mouth :)

July 12, 2013 3:27 AM
 

SSDT and TeamCity – What you need to do. | Make Sharp said:

July 28, 2014 7:44 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement