1

I,

We are currently working on a .net core project that will use multiple databases with the same structure. In short, this is a multi tenant project and each tenant will use the same web application (multiple instances behind a load balancer) BUT each tenant will have its own database.

We are searching for the best solution to ease our deployment process. When the application (and DB) is updated, we need to update the database structure on all SQL servers and all databases (one SQL can contain x databases).

FYI, application and SQL server are hosted on AWS, our CI/CD is Azure DevOps.

And last (but not least) limitation: we are working on VSCode only (MAC & Linux laptop).

So, we looked for some solutions :

  • Using Database projects (.sqlproj) + DACPAC generation deployed using DevOps, but it's not available on VSCode
  • Using Migration: not working with multiple databases and dynamic connection strings
  • Using SQL script: too complicated to maintains by hand a SQL script that takes care of possible cases

So could someone give us some advice to solve this problem?

Improve this question
2
  • Azure Data Studio supports database projects and runs on Mac ,though it's not quite as flexible as visual studio as you cant compare a live database with a project (I have no idea why not) Commented Oct 14, 2021 at 0:15
  • SImiliar to this answer stackoverflow.com/questions/65499094/… Commented Oct 15, 2021 at 0:06

1 Answer 1

Reset to default
2

The general solution here is to generate SQL Scripts for each deployment, and integrate those into your CI/CD process.

You could use EF Migrations to generate a SQL Script, that is then tested, deployed to your repo as a first-class asset, and deployed by your CI/CD pipeline. Or you could use SSDT to manage the schema and generate change scripts. But those aren't the only reasonable ways.

If you are modifying the schema by hand without using SSDT, you would normally just use a tool to generate the change script. And go from there.

There are many tools (including SSDT) that help you to diff a development environment against a target production schema and generate the change scripts. Eg Redgate ReadyRoll

Note that if you intend to perform online schema updates you need to review the change scripts manually for offline DDL operations, and to ensure that your code/database changes have the correct forward and backward compatibility to support a rollout while the application is online.

And preparing, reviewing, testing, and editing the database change scripts is not something that everyone on the team dev needs to do. So you can always consider jumping onto a Windows VM for that task.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

We used migration in the past, but with long living projects, it become impossible to use. Our previous project end with 300 migrations in about 2 years. Working in teams is painfull (you always have 2-3 migration that cause merge issues and are not in synch with the migration table). And we also have to add some custom setting in our SQL structure (for perfs, like indexes or stored procedure) that are not possible to do with EF core
Totally agree about out-growing Migrations.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.