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.
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.
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.
