0

I have a single schema that is deployed to 10 databases. Each database though has some schema customization - additional columns in some tables, additional/changed procedures, etc. The end result being that 75% of the schema is shared, but the other 25% is unique to each database.

I know I could simply create 10 different SQL Server database projects and manage all of them separately. I suspect there is a better way with less duplication for this common use case, though.

Does Visual Studio's SQL Server database project support mostly centralized management for this sort of design? I'm looking for something that would allow changing the shared code in a single spot rather than multiple locations.

If so, how would I configure things?

This question may sound somewhat similar to the first sentence of Is it possible to use MS VS Database Project as a complete solution for database versioning?, but that one ends up being quite general. I'm looking to zero in specifically on the issue of mostly shared schema.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
davidpricedev
  • 2,107
  • 2
  • 20
  • 34

1 Answers1

2

This is possible in theory. How well it will work for your situation remains to be seen. Database projects can have references to other projects in the solution. Below is a quick setup that I did as a test.

enter image description here

When adding the database reference, you'll want to choose the "same database" option as shown here:

enter image description here

With this setup, anything that is indeed the same between your databases would go into the referenced database project (in my example, CascadeSchema). Anything that's different would need to go into the individual database projects. You'll deploy from the subordinate database projects (in my example, DBOne, DBTwo).

One thing that this doesn't handle well is if the same table has different structure between the subordinate databases. You mentioned the same table having different columns. In that case, the table would need to exist in the subordinate database projects, likely with most of the create table DDL duplicated. Technical debt (which this is), like financial debt, has a cost and you have to pay it somewhere.

Maybe having differing schema is deliberate or maybe it happened accretively. In the latter case, as/when you consolidate/reconcile the object differences across the databases, you can move that object into the common schema database project. Even where the differences are deliberate, you can now at least do a diff between databases to quantify/qualify those differences.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • After testing things out for a bit, I've concluded that this is not a good solution for this use case. Referencing another database project does not merge the CascadeSchema down into DBOne when doing a compare or deploy, so unfortunately, this setup can't be used. – davidpricedev Feb 02 '21 at 20:57
  • Interesting. I still have my test solution and tried your use case. I made changes at all levels (specific to the example, I added a new column to the table in CascadeSchema and changed the procedure in both DBOne and DBTwo). The schema compare incorrectly identified the tables as needing to be dropped. However, if I went to do a Publish everything happened as expected. I'll investigate some more. – Ben Thul Feb 02 '21 at 23:58
  • 2
    Figured it out. In the schema compare options, there's an "include composite objects" option. In my install, it defaulted to off. When I set it to on and re-compared, it correctly picked up the new column (instead of wanting to drop the table). – Ben Thul Feb 03 '21 at 00:04