Click here to Skip to main content
15,867,330 members
Articles / Database Development / SQL Server

What is Multi-tenancy

Rate me:
Please Sign up or sign in to vote.
4.83/5 (14 votes)
6 Feb 2017CPOL2 min read 18.6K   9   6
Multi-Tenancy means same instance shared amongst all customers.

Introduction

Multi-Tenancy means same instance shared amongst all customers.  Each customer is called a Tenant.  Customizing of few areas is allowed, but application code changes are not allowed. What is common?

  1. Database
  2. Operating System
  3. Hardware

During application design it’s been taken care that information is accessible to correct owner.

Data is key factor for any application. SaaS (Software-as-a-Service) application concept is based on providing centralized access to data to reduce their overheads. 

Approachs for Multi-Tenant data:

  1. Totally Isolated or Separate database :

    Application code is shared between all Tenants, but every Tenant has its own set of data and it is isolated     from data that belongs to other Tenants.

    Image 1

    Advantages:

    1. Restoring data from Backup
    2. Extending data model
    3. Fully secured and private
    4. Scalability 

    Disadvantages:

    1. Higher hardware cost
    2. Maintenance cost
  2. Partially Shared or Separate schema:

    Database is common for all Tenants, but each tenant gets its own Schema. So this is partial separation of data.

    Advantages:

    1. Good for small number of database tables.
    2. Easy to implement.
    3. Tenants can add columns or table based on requirement.
    4. Moderate level of data isolation.

    Disadvantages:

    1. Resorting of data is tough
    2. Data is co-located with other tenants

    Image 2

  3. Partitioned (discriminator) data:

    In this approach all tenants share common database and schema (Tables). A single instance is shared with multiple tenants and is portioned in such a way that tenants cannot access each other’s information. Tenant Id is used to distinguish data. Extra security efforts are required, so that tenants cannot access each other’s information.

    Image 3

    Real world Example: Image 4

    We have 3 tenants looking for our application, since we are using shared database and schema, a little customization is possible.
    Tenant-1: Need same except dress color should be red.
    Tenant-2: Need same except flair should be shaded and in different color.
    Tenant-3: Needs exactly what is demoed.

    Advantages:

    1. Cost effective: Since resources are shared hardware cost reduces.
    2. Up gradation is taken care, so no extra efforts required.
    3. Backup and recovery becomes easy, as all share same database.

    Disadvantages:

    1. Less customization as everybody is sharing same database and tables.
    2. Changes can be made without notifying, so no authorization.
  4. Data sharding:

    Shard means “A piece of broken object”. Database sharding means portioning of database. Each part is known as shard or database shard. Tables are identified which are actually hitting performance and based on that portioning is handled. Data while portioning might appear in multiple shards.

    Divided tables will be distributed into multiple servers, this reduces number of rows per table, which reduces index size and helps in performance improvement.

    Image 5

    Advantages:

    1. Easy to manage
    2. Faster
    3. Cost effective

    Disadvantages:

    1. Complexity of SQL
    2. Backup becomes difficult
    3. Full System could fail because of single shard failure

References

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

http://multitenancy-in-saas-paas.wikispaces.asu.edu/

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Program Manager Infobeans
India India
I have keen interest in learning new things, exploring more on a topic and being more versatile

Comments and Discussions

 
QuestionNice explanation...... Pin
saransh897-Feb-17 19:41
professionalsaransh897-Feb-17 19:41 
Nice explanation......
AnswerRe: Nice explanation...... Pin
Anuja Pawar Indore7-Feb-17 20:17
Anuja Pawar Indore7-Feb-17 20:17 
QuestionNice explanation...... Pin
saransh897-Feb-17 16:43
professionalsaransh897-Feb-17 16:43 
AnswerRe: Nice explanation...... Pin
Anuja Pawar Indore7-Feb-17 20:18
Anuja Pawar Indore7-Feb-17 20:18 
QuestionAbout the format Pin
Nelek6-Feb-17 5:47
protectorNelek6-Feb-17 5:47 
AnswerRe: About the format Pin
Anuja Pawar Indore6-Feb-17 20:06
Anuja Pawar Indore6-Feb-17 20:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.