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.
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!
@Jamiet