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

Deployment of client-specific database code using SSDT

Yesterday I attended SQL Saturday 194 in Exeter for which many people deserve credit but especially the organisers Jonathan Allen & Annette Allen and the sponsors Fusion-IO, Confio Software, Nexus, Big Bang Data Company, Purple Frog Systems, Redgate, idera, Pluralsight, Jurys Inn Exeter & Wrox.

image image


I gave a talk entitled Declarative Database Development with SSDT; both during the session and then afterwards on Twitter Leo Pasta asked me



would you have any extra references on how to handle client-specific changes to the DB schema

I promised Leo an answer, hence this blog post.

If I understand Leo’s question correctly he has an application that is used by multiple clients, he has a separate instance of the application for every client, and the database requires client-specific customisations; luckily for Leo there is a new feature in SSDT that is perfect for this situation. First let’s set up our solution in SSDT:


We have three database projects:

  • _core - This contains all the objects that are common to all clients. For demonstration purposes it simply contains a table called [Sales]
  • clientFoo – All the objects that are required only by client “Foo”. In this case there is view which aggregates the data in [Sales] by Customer
    • clientBar – All the objects that are required only by client “Bar”. In this case there is view which aggregates the data in [Sales] by Location

    In order that the reference to table [Sales] in the two views can be resolved both of the client-specific projects have a database reference to _core:


    It is those database references that allows objects in clientFoo/clientBar to refer to objects in _core. Now here’s the important bit. When we set up those two database references we must specify that the objects in the referenced project are intended to be in the same database:


    Having objects from one database split over multiple projects is called composite projects. The effect of this is that (by default) whenever clientFoo or clientBar are deployed the objects in _core will get deployed as well. We can see this in the output when we deploy clientFoo:


    Notice how even though we chose not to deploy _core two objects have been created; table [Sales] (from the _core project) and view [vSalesPerCustomer]. This is the new feature in SSDT that I spoke of above – a deployment of a project will (by default) also deploy all the objects in referenced projects where objects in the referenced database are intended to be in the same database (incidentally this functionality replaces the “partial projects” feature from previous incarnations of SSDT).

    Note that you can change this default behaviour in the advanced publish settings by unchecking the “Include composite objects” box:


    Hence, with the “Include Composite Objects” setting turned on, we can deploy both projects clientFoo & clientBar and both will contain table [Sales] plus their own client-specific view:


    which (I hope) is exactly what Leo was after!

    That’s all there is to it. A very very nice new feature of SSDT!


    Published Sunday, March 10, 2013 3:24 PM 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



    LeoPasta said:

    Great explanation, nice to see that it have been implemented in an intuitive way (which was something I couldn't say a lot for the first versions of the "Data Dude"). :-)

    One additional question, how would it handle different indexes/contraints between clients? E.g. ClientFoo requirements say that a client should have one order per day and we want to implement this as a Unique constraint on Sales. Would we have to take the whole Sales table out of _core and have a different definition for each client?

    March 10, 2013 1:30 PM

    jamiet said:

    Hi Leo,

    Exactly the same applies in regard to indexes. They're still database objects, exactly the as views are, so you can still have an index per client. SSDT doesn't stipulate that you have to put an index or constraint definition in the same file as the table that it is defined upon.



    March 10, 2013 2:59 PM

    bruce said:

    Nice feature indeed! Does SSDT also support client-specific changes for SSIS packages or SSAS cubes? That would be icing on the cake!

    March 10, 2013 3:02 PM

    jamiet said:


    No, unfortunately not :(

    I have brought this up privately to the SSIS team, I think maybe I need to make the request more public. To Connect I go!


    March 10, 2013 4:39 PM

    jamiet said:

    March 10, 2013 4:53 PM

    bruce said:

    Nice write-up on connect. It's exactly the situation we are in and you described it well. By the way, the connect item is picking up some steam.

    March 12, 2013 5:42 PM

    Kris said:

    Hi Jamie,

    What would you suggest if the new project has a different structure for the same object, like additional columns to the core table object.


    June 25, 2013 6:40 PM

    jamiet said:

    Hi Kris,

    In that example I would say that the new table can no longer be in "_core" and you would need to define the table in each separate project.

    Unfortunately there is no notion of override i.e. If an object is defined in the referenced project "_core" and also in the referencing project (e.g. "clientFoo"), use the definition from "clientFoo". You can't do that - although that may be a rather nice feature to request on Connect.



    June 26, 2013 2:57 AM

    jamiet said:


    Of course, you can also have "layers" of composite projects where you have an interim layer of projects that consume from _core, provide different implementations of certain objects, and are then consumed in your per-client projects. Of course, this could become unwieldy very quickly, but it is an option.



    June 26, 2013 3:17 AM

    Talal Yousif said:

    Hello, Jamie

    I am facing a situation where I have one database project that holds objects that are general to all customers. Then I have another project that references it and holds the objects that are specific to a certain customer. The problem is that I have a stored procedure that I want to customize while keeping the original procedure in the general project unchanged. If I add the procedure with the same name I get an error that an object with the same name already exists. And it is necessary to keep the procedure's name. Thanks.

    November 25, 2013 2:37 PM

    jamiet said:

    Hi Talal,

    Unfortunately I don't think you can achieve what you're trying to achieve here. There is no way to have one project override what is in another. An object is either shared across all projects or none of them. Sorry.

    Perhaps you could get around the problem by using Add as link: (that article applies to Windows Phone development but is equally applicable for any project type in Visual Studio, including SSDT)



    November 26, 2013 3:40 AM

    B. Stanik said:

    What if I want to generate script that includes only ClientFoo objects?

    February 24, 2014 10:55 AM

    jamiet said:

    Hi B Stanik,

    I think you can simply publish the ClientFoo project and choose the option to generate the script rather than generate the script and run it.

    This option is available when publishing either by double-clicking on a publish profile file in VS or when using sqlpackage.exe



    February 24, 2014 11:02 AM

    B.Stanik said:

    I understand but it will include every object from ClientBar project as well. And if I uncheck 'Include composite Objects' option I would get dependency error...

    I just want script with all ClientFoo objects only (VS 2010 supports that)

    February 24, 2014 11:28 AM

    Brandon said:

    Is this supposed to work with schema comparisons? cause when I do that it flags everything in the core project to insert into the client-specific project.

    June 27, 2014 1:44 PM

    Jack Corbett said:


    I think this post,, still applies to schema comparisons even though it is 2 years old.

    September 2, 2014 12:55 PM

    RGoen said:

    Jamie, You have just made my day!  Even though this was posted some time ago, this solution is just what I was looking for.

    January 7, 2015 6:04 PM

    Ravikumar said:

    How to deploy post deployment scripts in core to project to all clients

    September 26, 2015 3:27 AM

    Markus said:

    I have the same question as Ravikumar: How can we handle post Deployment / Data deployment Scripts? By default the post Deployment script of _Core won't be executed when deploying the "clientFoo" db. Can they be combined / cascaded?

    November 25, 2015 5:03 AM

    Mick said:

    Extra objects doesn't seem to be an issue, extending or overriding objects in _Core doesn't seem to have an obvious solution (other than don't do it).


    If the _Core Sales table had the following columns



    And Foo an additional column


    Is there anyway to do this with SSDT ?

    December 15, 2015 12:25 AM

    Peter said:

    I have the same issue as Ravikumar and Markus. I would also like to combine pre and post deployment scripts of multiple dacpacs in the same database. Hope anyone has a solution for this issue.

    December 18, 2015 6:03 AM

    Peter said:

    By the way, I have dropped a feature request on Microsoft Connect. You can find it on Please vote!

    December 18, 2015 8:40 AM

    ChovenSen said:

    Hi Jamie,

    Do you know how to get rid of SQLCMD variables within SSDT, once they are added?


    November 10, 2016 9:32 AM

    Leave a Comment


    This Blog


    Privacy Statement