• 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.

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