0

I have a Microsoft SQL Server database project with hundreds of objects (tables, procedures, etc). The resulting database is used in two contexts though -- some customers get a database with all the objects, but some customers' databases contain only a subset of the objects. About 75% of them.

Is there a way to mark each object in the database project in such a way I can say whether it should be deployed or not based on a toggle of some sort?

I thought of a few options:

  1. Use pre/post deploy scripts. I really don't want to do this, as it defeats a lot of the benefits of using a database project in the first place.
  2. Install ALL objects, and have a post-deploy step that deletes the objects that we don't want in the "subset" install. Seem hackish to me.
  3. Break the one database project into two project: one with the required objects and one with the optional.
  4. Have two projects, one with "all" and one with the subset. 75% of the files will appear in both projects. Then I install the appropriate project's database

I think #3 and #4 sound doable, but I'm still hoping there is a simpler, more maintainable solution.

Frogger
  • 94
  • 8
  • 1
    I agree that your work around sounds hackish. But then changing the database based on some criteria is pretty hackish. Sounds like maybe that should be two databases. Then it is easy to install one or both. – Sean Lange Oct 09 '18 at 21:07
  • The "optional" tables reference the "required" tables via foreign keys, and the "optional" stored procedures join across "optional" and "required" tables. So I would prefer to keep them in one database. Cross database consistency is a pain. The business need isn't hackish though. One install is for someone who busy the "whole package", and one install is for someone who buys the "light package". We don't want the latter to get all the objects of someone who buys the "whole package". – Frogger Oct 09 '18 at 21:19
  • Also, I tried to keep the question simple. In practice, we have multiple optional packages. Breaking it into separate database projects is doable but is quite a pain as well. – Frogger Oct 09 '18 at 21:26
  • I can see changing functionality in the app but why go through all the hassle of not using the same database? Or maybe it should become two projects where the objects in the extended version inherit objects from the base version. This sounds a lot like an [xy problem](http://xyproblem.info/) – Sean Lange Oct 09 '18 at 21:36
  • A valid point about changing functionality through the code. That is actually my recommendation, but I'm getting push-back from management so I'm doing my investigation on alternatives. – Frogger Oct 09 '18 at 23:20

1 Answers1

0

It looks like the proper way to do this is to create a second database project that references the first.

Database A will have all the common objects, and Database B will have a reference to Database A (References -> Add database reference). I can ship database A as a "reduced" database, or database B as a "full" database.

Still working on it, but it seems to be the cleanest and proper way of doing what I want.

There is a good reference here from Microsoft, specifically creating a "composite project". https://msdn.microsoft.com/en-us/library/jj684584(v=vs.103).aspx

Frogger
  • 94
  • 8