none
Multi tenant system RRS feed

  • Question

  • Hi!

    Acutally we have web application (ASP.NET Webforms) where we use for every customer his own MS-Access database. As you can imagine, over the years Webforms and MS-Access has become a pain...

    For our new application release we are disscussing between a multi tentant system with an SQL-Database per customer (as we had it before) or one database for all users.

    I have already read many articles on the web (for exampel a good article: https://dba.stackexchange.com/questions/164043/should-a-multi-tenant-system-with-sql-server-2016-shard-or-have-tenant-isolatio). So I know the most advantages/disadvantages for both solutions. I also know that the maximum number of databases per instance ist 32,767.

    But there is something I was not able to figure out:
    If we choose one single database for all users. How can we guarantee that a customer can only see his own data. If there is one single database we have to take care that we don't do any misstakes (for example in the past we all have readed news where banking apps has sown data from wrong users, or airlines where reserverations/flight miles has been visible for others).

    So the question is (in case of a single database for all users) how can we secure the data on a database-level to prevent wrong data access -> isolation between customers.

    • Edited by Domsik Tuesday, January 21, 2020 3:16 PM
    Tuesday, January 21, 2020 3:15 PM

Answers

  • Tuesday, January 21, 2020 6:16 PM
    Answerer
  • If we choose one single database for all users. How can we guarantee that a customer can only see his own data. If there is one single database we have to take care that we don't do any misstakes (for example in the past we all have readed news where banking apps has sown data from wrong users, or airlines where reserverations/flight miles has been visible for others).

    Tom makes a good point about row-level security, or more exactly row-level filtering. There is no 100% security, as information still could leak in error messages or query plans. But for an application where users to not have direct access to SQL Server themselves, it should be good enough.

    But in the end the answer really is: you have to code correctly, and define your tables correctly.

    I should add that there is an alternate solution, and that is to have multiple schemas per client. This gives you the same amount of isolation as a multi-database solution, but makes it easier to use common reference data (if you have such a thing).
    I have at least customers with multi-tenant applications. Two them have a single database, while the third has one database per customer.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, January 21, 2020 10:45 PM

All replies

  • One caution you may already have read on multi-tenant...if the client messes up their own data, how do you restore it without affecting the other customers? 

    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    Tuesday, January 21, 2020 4:06 PM
  • Tuesday, January 21, 2020 6:16 PM
    Answerer
  • If we choose one single database for all users. How can we guarantee that a customer can only see his own data. If there is one single database we have to take care that we don't do any misstakes (for example in the past we all have readed news where banking apps has sown data from wrong users, or airlines where reserverations/flight miles has been visible for others).

    Tom makes a good point about row-level security, or more exactly row-level filtering. There is no 100% security, as information still could leak in error messages or query plans. But for an application where users to not have direct access to SQL Server themselves, it should be good enough.

    But in the end the answer really is: you have to code correctly, and define your tables correctly.

    I should add that there is an alternate solution, and that is to have multiple schemas per client. This gives you the same amount of isolation as a multi-database solution, but makes it easier to use common reference data (if you have such a thing).
    I have at least customers with multi-tenant applications. Two them have a single database, while the third has one database per customer.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, January 21, 2020 10:45 PM
  • Having dealt with and application with a database per customer, I HIGHLY recommend against that approach.  There is a huge cost in maintenance and complexity when you get 1000s of small databases (or schemas) which are almost identical.  Schema changes alone are difficult.

    The first point of contact, is your application.  Your application needs to make sure users only see what they are authorized to see.  This is not a database concern.  You can implement some level of additional security using row level security.  However, your application is still ultimately responsible to only show data authorized for the user.

    Wednesday, January 22, 2020 3:31 PM
    Answerer
  • Having dealt with and application with a database per customer, I HIGHLY recommend against that approach.  There is a huge cost in maintenance and complexity when you get 1000s of small databases (or schemas) which are almost identical.  Schema changes alone are difficult.

    In these days of DevOps and automation? I don't think so. Yes, you need more work than with a single database.

    Then again, there are lot of ISV who needs to handle the multiple-database scenario. To wit, the case when the database is located on the customer's premises. The only difference in the multi-tenant case is that you host all the databases yourself in one way or another.

    The first point of contact, is your application.  Your application needs to make sure users only see what they are authorized to see.  This is not a database concern.  You can implement some level of additional security using row level security.  However, your application is still ultimately responsible to only show data authorized for the user.

    But the application will rely on the database. If a stored procedure returns data for the wrong customer, how would the application know?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, January 22, 2020 10:21 PM
  • Having dealt with and application with a database per customer, I HIGHLY recommend against that approach.  There is a huge cost in maintenance and complexity when you get 1000s of small databases (or schemas) which are almost identical.
    Could you maybe explain (in short) what issues/trouble you have got with a database per customer? Maybe you have got some troubles which I haven't read until now. 

    For our actual application we are having to deal with around 200 MS-Access-Databases (small databases with approximately 50-80MB). I know that SQL-Server offers much (much) more than MS-Access and we have to consider many more things, but we have learned to deal with databases per customers - and we have written a lot of scripts and tools to do operations manually.

    As Erland mentioned > yes you have more work than with a single database (writing scripts, tools to automate things, plans for recovery and replication, and so on) - but in the end if I have to decide between the "easy way" and the "secure way" (one database per customers), I will choose the "secure way" and have to live with the extra work... As explained we are familiar dealing with a customer per database, and until now there was never a point where I could say that it was a bad decision (but you can't compare MS-Access directly with SQL-Server, for MS-Access it was the best solution).

    @Tom Phillips it would be great if you can share your experience so that I can imagine what problems I have to expect.



    • Edited by Domsik Friday, January 24, 2020 10:18 AM
    Friday, January 24, 2020 10:18 AM
  • One caution you may already have read on multi-tenant...if the client messes up their own data, how do you restore it without affecting the other customers

     I don't know which case do you mean. But basically, it should be possible in both cases - but restoring one customer database should be easier than restoring particular rows from one customer of one single database.


    Friday, January 24, 2020 10:19 AM
  • One caution you may already have read on multi-tenant...if the client messes up their own data, how do you restore it without affecting the other customers

     I don't know which case do you mean. But basically, it should be possible in both cases - but restoring one customer database should be easier than restoring particular rows from one customer of one single database.


    Exactly...unless you have a way to clean up customer messes inside a MT database, one per customer is easier from that standpoint.  Just one aspect to consider :)

    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    Friday, January 24, 2020 7:36 PM
  • The biggest problem is maintaining 200 schemas.  Yes, it is easier if your organization is mature enough to use "devops", auto deployments, etc.   

    However, you still run the risk of having 200 different versions of the schema and 200 different versions of the application.  What if you do a schema upgrade and the upgrade fails on 2 of the databases?  Now you have 198 new schemas and 2 old schemas.

    The simplest approach is to have a single database with a single schema and then you always do something like this:

    SELECT *
    FROM [orders] o
    INNER JOIN [usercompany] uc ON o.companyid = uc.companyid
    INNER JOIN [users] u ON uc.userid = u.userid and u.userid = @userid
    
    WHERE o.orderdate > '2020-01-01'

    You can make it fancier with row level security, but the same idea of restricting rows by user.


    Friday, January 24, 2020 8:12 PM
    Answerer
  • As Erland pointed out, there are pros/cons to each.

    Having 200 databases, requires you to have maintenance plans on 200 databases, backups on 200 databases, etc.

    However, you have the advantage of being able to restore an individual customer's data (unlikely but possible).  If you combine all the data for all customers, you would not easily be able to do that.

    Having it all in one, allows for combined reporting, if that is a need.
    Friday, January 24, 2020 8:15 PM
    Answerer
  •  I don't know which case do you mean. But basically, it should be possible in both cases - but restoring one customer database should be easier than restoring particular rows from one customer of one single database.

    If the customer says "We messed up the data for facility 43, can you restore that, but only that data", it does not matter if it is a single- or multi-database solution, since you will have to do scripts entire.

    But if a customer screws up entirely, the single-database case is easier, since restoring a single database is easier. Then again, that depends on how complex your schema is. Since you have Access today, I don't really expect that you have hundreds of tables.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, January 24, 2020 10:46 PM
  • However, you still run the risk of having 200 different versions of the schema and 200 different versions of the application.  What if you do a schema upgrade and the upgrade fails on 2 of the databases?  Now you have 198 new schemas and 2 old schemas.

    This cuts both ways. This permits you to have customers that take beta or evaluation versions before you release it to everyone.

    Since Domnik already does multi-database today, he also knows how to handle it. And, as I said, so does every one who have their products installed at customer sites. And the latter typically must deal with multi-version schemas anyway, since they cannot control when the customers runs the upgrades.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, January 24, 2020 10:49 PM
  • Tank you for all your comments. We will discuss this again in our company.
    Thursday, January 30, 2020 5:16 PM