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

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

 

image

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:

image

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:

    image

    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:

    SNAGHTMLa9aab43

    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:

    image

    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:

    SNAGHTMLaac90d1

    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:

    image

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

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

    @Jamiet

    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

    Comments

     

    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.

    regards

    Jamie

    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:

    Bruce,

    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!

    Jamie

    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.

    Thanks.

    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.

    Regards

    Jamie

    June 26, 2013 2:57 AM
     

    jamiet said:

    Kris,

    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.

    Regards

    Jamie

    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: http://msdn.microsoft.com/en-us/library/windowsphone/develop/jj714082(v=vs.105).aspx (that article applies to Windows Phone development but is equally applicable for any project type in Visual Studio, including SSDT)

    Regards

    Jamie

    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

    Regards

    Jamie

    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

    Leave a Comment

    (required) 
    (required) 
    Submit

    This Blog

    Syndication

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