Need urgent help in designing SQL database strategy

Question

Introduction: We want to develop a C# ASP.Net web application which will use SQL Server database. Now clients will license in our application and then use it. This application also supports PDF uploading option. Objective: There might be a stage where user may want us to separate his data and deploy that at his own server OR from beginning he don’t desire to have shared database and wants to have a separate database for him at our server. If we design a single database for all users then in future how will we separate a specific client’s data? And if we create separate databases dynamically for each user then let’s say if 200 number of clients desire to have independent databases at our server then in the end our server will end up having 200 Independent and 1 Shared database. If that would be the situation then how will we backup all databases? Moreover I think our server downtime may increase too. Please help & suggest me in deciding and designing the most appropriate, effective and accurate solution. Please Note: We are using Microsoft Azure services at the moment.

Salman Beg · Answer

Hi. First thing as you mentioned of Pdf uploading then don't directly store the pdf in db and as you mentioned for 200 number of clients and if each one upload then the size of db increase for which it will effect on the performance. So for that upload the pdf in a folder and save the path of the image in db. Shared db is also called as Multitenant and where there is a master db and all subsequent db connected to master. As many number of clients increase you can create that many number of db and for which there is no link up from one db to another. Even if once i worked on one project related to mulitenant application and one thing make sure that all client database have same structure. thanks.

Nitin Sontakke · Answer

While I may not be quite qualified to answer your questions, here are my thoughts.   The database which you call "shared" is called "multi-tenant" database. To use it effectively, I am sure there is already an Id implemented in tables which identify Client.   You will need to develop an application, which will upon request, prepare a script which will extract all of that client's data + master data, which should be able create a separate db.   If a client is asking for a separate db from the beginning then you should have a copy of a model db which has all of the master data. You will use this copy as a starting point for a client. Important point: Even if clients are in separate db, give them unique numbers. So that you can bring independent client into the shared environment.   You definitely will not take back-up manually of 200 dbs, there will be scheduled task which will run in the night.   I don't think downtime is anything to with number of dbs. Your typical downtime would be for patching servers and Windows Update, etc.