SSDT- How to setup SQL Server Database Project with a database that has cross database queries

Question

I'm trying to create a SQL Server Database project with a database that has cross database queries with multiple databases on the same SQL Server instance.
Ideally the SSDT- database project solution should be checked in some version control tool (github, etc ) and should be used across the team by all the team members.

My SQL server instance have around 20 databases and each database has cross database queries with multiple databases in the stored procedures, views, etc..

I'm wondering what would be the ideal way/industry standard to set up a database project for the databases like this ?

In my research I came across the below few options:

1. Create a database project for each database (A-Z) and add the specific database project/projects in the another database project (B) where database B has cross database queries with any of the databases (A-Z). So, if each and every database has cross database queries with all 20 databases, I have to modify all 20 database projects for any change to a specific database.
2. Generate Dacpac's for each of the database and add them in the database project. Again, same challenge like above. So, if each and every database has cross database queries with all 20 databases, I have to modify all 20 database projects for any change to a specific database.
3. Maintain one solution with all the DACPAC's and add this solution in all the database projects. However, when working on a specific database and it's dependent cross database query, I've to modify the database project of the cross database query and then generate a new dacpac and add it in the solution which has only DACPAC's and finally modify the database project that I want to tweak. Before I spend time with all these options, I would like to know what's the industry standard here and the main caveat with all the above 3 options is if I have to add a new column to the Database A and new table in Database B, I've to modify both the A and B database projects and then modify one more project based on what option I go with from the above 3 options. To make it even more complicated, this whole thing gets complicated in a team structure with some version controlling(github, bitbucket, etc ) in place. I feel like the above the 3 options are tedious approach and there should be some easy approach for this.

Thanks in advance, I appreciate all your help!

Answer 1

Under the database project, there is a tab "References". Right click on it and select "Add Database Reference ...". The "Add Database Reference" window pops up. You can add a name of a database which is used in your cross database queries. You may need to add them one by one if those databases are used in your cross databases queries.

Answer 2

First I need to select one of the below options though. Also, what should be my approach to setup a database project in my case with multiple databases referencing in the same database?

Answer 3

As you have discovered, cross database access in SSDT can be complicated.

If you have a single database, which only accesses one or a few databases, then the simplest is to create a "master" solution with all the databases, and then "focused" solutions to only have the databases relevant to that project, but linking existing projects from the master solution. Then you deploy using the "master" solution.

If however, everything accesses every database, then you need to always use the "master" solution.

The best method is to avoid cross database access. I would highly suggest you rethink if you actually need multiple databases and what you are accomplishing by having multiple databases.

Answer 4

I agree avoiding cross-database access is the best policy, but I imagine this has come up when dealing with legacy databases that have been changing over the previous 20 years and the only source control is "It looks like it works in the dev database, so compile the procedure in production", and trying to put them into a database project and get some modern source control and CI/CD process setup is a pain.

When extracting an existing database into a SQL Database project, you will have all sorts of errors for the cross-database queries. I would start with creating a folder in your project and extracting the databases it uses into DACPACs in that folder. Then reference those DACPACs and solve all the errors with search and replace with the proper variable reference. Your deploy will have to specify values for all the variables created for those references. A couple of gotchas - you should use the format [$(variable_name)] with the braces just in case your eventual database name has characters like dashes that would cause an issue when being compiled, I think it is basically a text replace, there isn't a real 'variable'. Also when referencing the dacpac you can tell it to ignore referenced entities in the DACPAC, so if database A references B and C, and database B also references D and E, you don't have to reference D and E in your database A project.

Obviously this is not good for a system where you perform a lot of ongoing updates to the referenced entities, but as long as you don't change them in a breaking manner it will work just fine. Your CD pipeline should fail in dev with any errors when it tries to deploy if the referenced entity has breaking changes. That's your cue to create and update a new version of the DACPAC for that database inside your project. This is wasteful since they are binary and don't lend themselves to git's diff mechanism so you'll end up with blobs in your source control.

If you try to remove the cross-database access when you can and not add any new ones, this should be doable. I do wish nuget was supported for sql database projects, that would make it easy to have a place where both the developer and devops pipelines could get a correct, stable version of the DACPAC.