Multi-tenant Architecture


  • In a Multi-tenant architecture there are three ways of storing tenant information in database.
    • Separate DB for each tenant
    • Separate Schema (tables) for each tenant
    • Shared Schema (tables) for all tenants
  • Following table gives a comparison of this three approaches.

 

Parameter Shared
 Schema
Separate
Schema
Separate
DB
Number of end-customers High Medium Low
Different amount of storage by each customer Not good Good Best
Concurrent end-customer login Not good Good Best
Value-added for customers Not good Good Best
Hardware/resource cost Low Medium High
Security Low Medium High
Restore in case of failure for one customer Very Difficult Difficult Easy
Best when the number of Tables 100/customer
Development Cost High Medium Medium

Separate DB

Separate DB is chosen for a premium solution where end-customers are concerned more about security, requires a lot of customization, have very different level of amount of storage, different backup requirement etc. High hardware, maintenance requirement & cost makes this approach suitable if end-customers are willing to pay extra for added security and customization.

All your application code which is executing the SQL queries remains same, the part where you making a DB connection and selecting the database will only change depends on the tenant. If you are using a naming pattern for your database based on the tenant ID then at run-time you can select the DB easily.

Separate Schema

Separate Schema in the same DB gives many advantages of “separate db” approach in terms of development and provides medium level of security as well. In this approach when a new end-customer is registered a new schema for him will be created with all set of end-customer specific tables.

  • Here most of the code changes are done to handle new customer registration.
  • Eg: When ABC registers as a end-customer

CREATE USER ABC_dbuser (creates user)
CREATE SCHEMA ABC_Schema AUTHORIZATION ABC_dbuser (creates schema)
ALTER USER ABC_dbuser WITH DEFAULT_SCHEMA = ABC_Schema (associate to user)
CREATE TABLE ABC_Schema.servers(GroupID int) (create tables)

  • Catch: A new DB user should be created for each end-customer.
  • Advantage: Same SQL queries will work for all end-customers. No need to specify Schema.TableName in SQL query as the Schema is selected by default when the user login to the DB

Select * from servers will work for both ABC & XYZ based on who logged in at runtime.

Shared Schema

  • This is not an easy to implement approach (compared to others) when an existing application migrating to multi-tenant, because it requires updating your application code executing the SQL. However cost of maintenance over long term would be less in this shared approach than isolated approach.
  • It is best suited when the number of end-customers are very high but all of them have low & similar amount of storage requirement in the DB. It also provides the least security among other approaches, so the necessary security has to be implemented at the application layer. Check the reference link given below for further reading on security.

Reference:

http://msdn.microsoft.com/en-us/library/aa479086.aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s