Multi-tenancy database connection SAAS RRS feed

  • Question

  • Hi

    I am building a multi tenancy SAAS application which will be developed using with sql backend.

    Everytime a client logs in they will have a schema created and the tables created under that schema. So shared schema and shared database approach.

    I will have a public database which will need to contain the master account database storing tenant id and schema names so we know which schema to connect to get the correct data for the tenant.

    So my approach was that there is a master account db which has a connection string which is static. But was wondering would it be better to create a new connection to the database and use this when they login. it would be stored in a table but that would be quite unsecure so encryption would be required

    would be great for some thoughts



    Tuesday, August 16, 2011 3:33 PM

All replies

  • Hi tjsingh

    Please check out Wolf Frameworks PaaS which is multi tenant, built on and MySQL backend. It is access over a secure SSL connection. 




    Friday, August 19, 2011 6:09 AM
  • Hi,

    In the multi tenancy implementation, If you having single db for all the tenants (Shared Schema model) the table level tenant identificatio key will be maintained.  In this case the The Database connection information is going to be same for all the tenants.

    If you are keeping the connection string another Master account db. While lauching the application first you will establish the connection with the Master account db and then read the actual db connection string and establish the connection.  then the application needs to have always two DB connections.

    If you keep the tenant realed master information in a table. in the first time itself the connection will be established to the DB. Keeping seperate DB is going to create more problem in terms of performance and managing db..etc.

    In the Single DB itselt you keep the data having concern on the security. MS SQL 2005 aprovides out of the box encription and decription features. that can used.

    Some of the disadvantages in this model is

    1. In the multi tenant scenario, One particular tenant is having more transactions and more number of cun-current users then decided to cluster the db. This will model will not allow the cluster the db alone for this tenant.

    2. By any chance if the DB goes down, then the entire tenant will not be able to access the application.

    3. The concurent users for accessing the db is going to be more.

    4. If the tenants are spreded across the geo locations in varios time zones, then the database/ application will be operated most of the time as peak time and even regular backup and any activity is going to make the problem.

    It is better to have a seperate db for individual tenant. which will be very easy for implementing availablity, disaster recover, maintenance ...etc.



    • Proposed as answer by Nehruraj Friday, August 19, 2011 10:39 AM
    • Unproposed as answer by Nehruraj Wednesday, September 7, 2011 3:29 AM
    Friday, August 19, 2011 10:15 AM
  • hi

    Thanks for the reply. I understand it is easier to have seperate databases for each customer but the problem is being economical alot of the big players in the SAAS world tend to use a seperate schemas and shared databases. its because they can provide SAAS cheaper

    There are pros and cons for each really

    Tuesday, August 23, 2011 7:47 AM