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:
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.
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.
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