0

I'm redoing a legacy system, and currently, I have a database with the same structure for each client. I'm really unsure whether I should maintain it this way for the new system or if I should create a unified model. What would be the advantages and disadvantages of these models?

Considering that I also need a way to unify the reports, facilitate backup retrieval for verification, and handle large data insertion throughout the day.

2
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Oct 28, 2023 at 3:29
  • This is a complex question, but "database-per-tenant" is the most robust and flexible model. Only downsides are that it requires more DBA attention, and can be painful for thousands of tenants. Oct 31, 2023 at 14:49

2 Answers 2

1

You could provide more information but :- Having worked with both I would definitely stick with database per tenant. Security is much more robust & migration of a client to a new server is a simple backup & restore, if clients share a database moving a client to a new server becomes a nightmare of ordering tables by FK, exporting rows, importing rows accommodating any version/schema changes etc etc. Having a database per client means that you can deploy new versions only to those clients who are ready for the new version. Multiple databases work really well with Azure elastic pools once you are ready for that journey. To unify reporting create a datawarehouse & ETL, potentially with SQL Server 2022 you could use managed instance link to handle this for you, or just dump the data in a Fabric datalake. For all your dba tasks I would highly recommend using the central management server feature so that you can run the same TSQL on multiple databases. To handle inserting data you will just need to build some automation, at least if each client has their own database you will easily be able to rebalance workloads by moving clients to a new server should one client suddenly start to generate much larger workloads.

0

Areas around this issue are called tenancy and like most things in computer science, the answer is - "it depends". Check out the link and links within for more.

I would advise you to study and think long and hard about this because you'll be living with the consequences for quite a while (rest of your career?)! Try and run a couple of simulations if you can.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.