DevOps series : Composite projects in Visual Studio SSDT

Introduction

In my current project, I'm working in a rather complicated environment with people working in teams in databases and these are also shared (used) among the teams. All kind of projects are deployed in a common database (in reality there a couple of more, but for the sake of simplicity for this blogpost, I'll assume there is one database. The main story is that objects (View, Stored procedures) of databases are shared between teams). This introduces an extra complication when using Visual Studio in combination with SSDT, because normally a project is a database (and a database is a project).

One of the challenges I see here is that if code is not present in a Visual Studio project it will try to delete the code in a database. Yet another challenge I'm investigating is version management through the different environments and what happens when a dacpac is pushed to a new step in the Software Development environment (DTAP).

Composite projects

For reasons described in the introduction, I was looking into composite projects. Bill Gibson mentioned something about working with different projects in a database (7 years ago! Time flies) in a blogpost. In my opinion, the following situations are possible in a database project (not SSDT project;-)) :

In this example there are 4 working projects (not Visual studio) on different systems and these are deployed into the same database. Working Project 1 uses code from Working Project 2 and Working Project 3. Working project 3 uses code from Working Project 2. Working project 4 uses code from Working project 2. Now, in this imaginary situation, I'm focusing on Working Project 1. As said before Working Project 1 uses code from Working Project 2 and 3. So the scope of Working project 1 is 2 and 3 (and off course itself). Working Project 4 is out of sight and not used by Working Project 1.

All the projects in a Visual Studio solution

There are a couple of options possible when designing your Visual Studio projects. First, You can include all of the code of projects in one Visual Studio solution. In my current situation I'm talking about 20 to 30 projects in the database and I don't need them (all of the time). The problem is that when one of the projects has some changes in the code, I've to update my local development database every time (even for projects that I'm not using for my current development). The advantage is that when I execute a Schema Compare between the database and the project it would show all of the changes easily.

Using DacPacs for decoupling

Yet another option is using dacpac's for decoupling the projects. So, not every change is reflected in your Visual Studio project. You set a database reference with the option "Same Database" and include the DacPac in your project and you're done. But, what about projects you don't even reference in your project? In the example is that Working Project 4.

In reality, It would look something like the situation below. We have THE project (eg. working Project 1), where we working on, we have the real referenced database project which code we are using in the THE project and we have projects that we are not using in the development of a database project at all.

To see what actually happens, I've created a mock up with two demo databases of Microsoft AdventureworksLT2017 and WideWorldImporters. I imported both databases in a Visual studio project, deployed them into one database and investigated what would happen if I execute a Schema Compare. If I don't add Database References to the projects, the Visual Studio WideWorldImporters wants to delete the code of AdventureWorksLT2017 and when I execute a Schema Compare in the AdventureWorksLT2017 it wants to delete the WideWorldImporters code in the database.

The next thing I tried was adding a Database reference, option Same Database and use the DacPac of the other project, I executed the Schema Compare again (don't forget to check "Include Composite Projects") and check the results again. The result was that the errors were gone. Even when the code is not used in a project, you can reference a dacpac for a "no error" comparison between a composite project and the database.

I can understand "following" the updates of the DacPacs from the referenced projects, but updating a local development database for every "not referenced" (other) projects can be time consuming. So, what will happen when you deploy your project to the Central Integration database and other steps in the DevOps pipeline.

Composite projects in DTAP

How will this work in the real world, where projects have different releases in different environments? Perhaps they need to execute a roll back of a release in an environment or are working in a different way? What if a project is provided with an old version of a dacpac and the responsible person of the project updated one of environments in the OTAP-line with a newer version and your project is planning an update to that environment?

I experimented with my AdventureWorksLT2017 and WideWorldImporters projects in one database and deployed these to a new database (as in a scenario like a new environment in the OTAP) and I added a new function in the WideWorldImporters project and deployed that to the new database. The result was that the AdventureWorksLT2017 project wants to delete the newly created function (because it is not in the dacpac of WideWorldImporters). So, I need to update the WideWorldImporters DacPac in the AdventureWorksLT2017 project.

Final Thoughts

This blogpost is an imagination of some experiments I ran with multiple database projects in one database. At the moment, I've not found a satisfying solution for my problem. The option "Same database" in the Database reference seems handy in a small environment but in a multi team project environment, it introduces all kind of version issues and a high probability of errors and mistakes. It requires a certain skill set. Deployment should be done with high caution.

Hennie