How to set up an SSDT database project for customization?

Chris Sijtsma 141 Reputation points
2021-11-24T08:37:46.5+00:00

Dear colleagues,

Our firm has two departments that do database development. One department has 5 teams building our general solutions that are rolled out to the customers. The other department has consultants that add customization to the general solutions.

For the general solutions, I have SSDT database projects in place. At the moment, deploying the customization changes is a hassle and the customization code isn't in any version control system. I would like to add database projects for the customization. How can I do that? Is it possible to add a reference to a general database project? Also, if a customization project is deployed, I never want the deployment of such a project to change anything in the general standard. Luckily, customization objects do have their own prefixes.

Do you have any recommendations?

Thank you.

P.S.: Is there a good way to find the proper tags for a question?

Visual Studio
Visual Studio
A family of Microsoft suites of integrated development tools for building applications for Windows, the web and mobile devices.
4,606 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,713 questions
0 comments
Accepted answer
  1. Tom Phillips 17,716 Reputation points
    2021-11-24T19:24:33.983+00:00

    Does the "customization code" deploy to the same database as the "general" code in the same schema(s)?

    If so, that makes it difficult to manage. There is no way to prevent something in the customization code from overwriting "general" code.

    You can create customization projects which reference the dacpac or project directly. When you deploy the customization code. If you put them in the same solution, then you can deploy them at the same time as a "composite" project. https://learn.microsoft.com/en-us/previous-versions/visualstudio/visual-studio-2010/bb386242(v=vs.100)#creating-composite-projects-by-using-references A composite solution will error if the same object exists in multiple "projects" in the solution.

    1 person found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Chris Sijtsma 141 Reputation points
    2021-11-24T19:56:17.72+00:00

    Hi Tom,

    Thank you for your answer. The customization code will never have the same objects as the general code since the customization code uses other prefixes.
    Example "upg_" for a "user procedure, general" and "upc_" for a "user procedure, customization". Our technical consultants that work at our users site are never allowed to change or override the general code. We have hooks they can use if they want to override the general behaviour without the need to override general code. I already did some test with including a reference to the "general" dacpac. Everything works fine as long as the "publish" doesn't throw away objects it doesn't know. I have to think about how to get rid of discontinued objects, but that doesn't overly bother me. Maybe I can cook something up with the ScriptDom.

    Do you happen to know if I can publish the customization project without publishing the general project? The general project contains over 14,000 objects (it is an ERP solution; ERP solutions are big as a general rule) and takes a long time to publish. The customization code contains a couple of hundred objects, max. Also, the general project only changes twice a year because we ship a new version every April and October. The customization code is much more volatile. It seems a waste of time to check for changes in 14,000 objects every other week while we know nothing has changed. Also, we have partner companies that also write customization. I am willing to give them the general dacpac, but I do not want them to publish the general project. Deploying a new version of our application is always done under the supervision of one of our most experienced technical consultants. Most of our 600+ customers do not have a real DBA. Although publishing a dacpac at the customer's site works fine in 99% of the cases, for the 1% that does throw an error, you really ought to know what you are doing. After all, most of the time production for the customer will stop if the ERP solution doesn't work.

    It is fine with me if you do not have an answer for my second question. I can experiment myself.

    Thanks again for the tip of referencing the general dacpac, it works like a charm.