3 distinct Multitenant data architecture:

 

Introduction:

The earlier articles focused on developing a rigid definition and framework for evaluating and comparing different multitenant data architectures. A set of seventeen criteria were used to comprehensively articulate the effects of the choices made in the multitenant architectures and these choices manifested in combinations of application and database variations as (AD, DD), (AS, DD), (AI, DD), (AD, DS), (AS, DS), (AI, DS), (AD, DB), (AS, DB), (AI, DB), (AD, DC), (AS, DC), (AI, DC) where the notations are explained in the legend. This article talks about the best practices in terms of patterns for realizing the data architectures.

Description:

A number of patterns can help plan and build the data architecture for SaaS applications. A well designed SaaS application can demonstrate scalability, configurability, zero downtime and multi-tenant efficiency. These qualities cannot be mutually exclusive. For example, optimizing for multitenant efficiency in a shared environment must not compromise the level of security safeguarding data access. A security pattern to resolve this conflict involves the use of “virtual isolation” mechanisms such as permission, SQL views and encryption.

The table below lists some of these patterns:

Approach	Security Patterns	Extensibility Patterns	Scalability Patterns
Separate databases	Trusted database connections Secure database tables Tenant data encryption	Custom columns	Single Tenant scaleout
Shared database, Separate schema	Trusted database connections Secure database tables Tenant Data encryption	Custom columns	Tenant-based horizontal partitioning
Shared database, shared schema	Trusted database connections Tenant View Filter Tenant Data Encryption	Preallocated fields Name-Value pairs	Tenant-Based Horizontal Partitioning

Trusted database connections:

Access to data stored in databases is secured using one of two methods: impersonation and trusted subsystem account. The former enables users to access different database objects. The latter is for applications to connect to database using process identity and involves additional security to be implemented in the application itself. For multitenant applications where each tenant grants access to end user accounts, a hybrid approach is justified.

Secure database tables

This involves granting select, update, insert, delete on [TableName] for [UserName] and must be done once during the tenant provisioning process. It is appropriate for separate database and separate schema approaches.

Legend:

AD-  A dedicated application server is running for each tenant, and therefore, each tenant receives a dedicated application instance.

AS – a single application server is running for multiple tenants and each tenant receives a dedicated application instance.

AI – a single application server is running for multiple tenants and a single application instance is running for multiple tenants.

DD – a dedicated database server is running for each tenant and therefore the database is also isolated.

DS – a single database server is running for multiple tenants and each tenant gets an isolated database.

DB – a single database server and a single database is being used for multiple tenants

DC – a single database server is running for multiple tenants and data from multiple tenants is stored in a single database and in a single set of tables with same database schema but separation based on records.