Multi-Tenancy in Database...

DB - OpenEdge 11.2 

OS - Windows 

Abbreviations - MT -> Multi-Tenancy/Tenant,  OE -> OpenEdge 

Recently we had to go through on MT architecture and had to use OpenEdge for the implementation. From this post I am hoping to discuss the summary of MT architecture and a very simple use case for demonstrating multi-tenancy support in db level using OpenEdge 11.2. 

Multy-Tenancy is a resource sharing concept where deferent tenants (Logical set of users – eg. different clients of a cloud service provider) are accessing same service despite having well differentiated data access, security and resource allocations. Multi Tenancy support was there for infrastructure layer (IaaS providers) and also for application layer. Now database vendors provide MT support built in to the database layer as well so the users can access fully shared applications without even knowing it. 

Below image demonstrate the shared tenant access where all entities (Infrastructure, Database and Application) shared among different tenants.

This is mostly the desired service delivery model for SaaS providers where multiple users utilize same software application hosted on cloud and pays subscription fee based on their usage. This model allows resource sharing to use same application version on multiple tenants with difference performance levels based on particular tenant’s requirement.

Enabling Multi-Tenant support built in with the database is becoming more and more discussed topic as more vendors try to add this feature to their databases. Progress OpenEdge introduced their multi-tenant built in database with OpenEdge 11.0. And more recently Oracle also announced built in support for MT in their newest release 12C. 

Below are the basic characteristics of OE MT.

• All tenants share the same database schema. (All tenants are accessing data objects with same definition).   
• Each user accesses its own tenant instance. (Although every user access same table, each has it is own physically separated partition for its data). 
• Database utilities are aware of the tenant. 

• Utilities which operates on the whole database are not affected by the tenants – (eg - Full   backup (PROBKUP) will back up the whole database) 
• Object level utilities are tenant aware – (eg - index rebuilding is possible for particular tenant) 

Basically MT implementation model consists of two main areas.

1. Data Storage Model.
2. Data Access Model.

Data Storage Model

In OE implementation of MT, data storage is defined as below.
There can be two types of tables in a MT enabled database.

Shared Tables – Non multi-tenant Tables.

Multi-tenant Tables – Has tenant specific data on table.
Multi-Tenant Enabled table is defined as a Table Instance and each of this table instance can consists of data partitions, index partitions and LOB partitions. So each table has its own physical data partition per each tenant, and separate partitions for each index per each tenant, also separate partition for each LOB column per tenant.
 So if we enable tenancy for a table with 2 indexes and 2 LOB columns in a database where 3 tenants are defined, partition will be created as below. Number of physical data partitions – 3 (1 per each tenant) Number of index partitions – 3x2 – 6 (2 per each tenant since 2 indexes are there) Number of LOB partitions – 3x2 – 6 (2 per each tenant since 2 LOB columns are there) So in total there will be 15 partitions created for this table only.
 DBA can control the storage area (Tablespace) on which these partition will be created by defining default storage areas for data, index and LOB for each tenant. Also these defaults can be overridden at the MT table definition if required.
In OEs implementation MT enabled tables can only reside on TYPE II storage areas. (There are two types of storage areas (Tablespaces) for OE databases, TYPE I and TYPE II). Any Individual partition should be on same TYPE II area, but 1 table instance can have it is partitions in different areas.

Data Access Model

Tenant’s data access is authenticated by security domain.
OE Multi Tenancy architecture defines different tenant types as below.

• Regular Tenants - 
• Default Tenants – When user sign in without domain he can access default tenant data and shared data. 
• Regular Named Tenants – Defined and named. Should have at least one domain and one user on that domain. Can only access that tenants data and shared data. 
• Super tenants – Can access all the data from every tenant. Can become any tenant. Super tenants also should have at least one domain, one user. Super tenants will not have their own 
• Tenant Groups – More than one tenant access same data for an instance of table. 

Here “Domain” defines the authentication for users assigned for particular tenant. So one tenant can have multiple domains with different authentication properties and each of these domains can have any number of users assigned to them.
When tenant user login to database he should specify both user name and domain, eg if you have a user Stefan on dom_a of tenant ten_a then to access this tenant data user Stefan should login as stefan@dom_a . If user login with user name only (no tenant is specified) then user is assigned to the default tenant.

Tenant Group - A way to allow multiple-tenants access same (single) MT-Table. Storage allocation for group is same as for single tenant. So each group has it is own partitions for MT table instance. When data is stored group level it is stored in group’s partition. This data is owned by the whole group. A tenant can belong to zero or one group for on table. A tenant can be added to a group when tenant does not have its own data currently allocated for the table. If tenant partition is already there it has to be dropped before it can be member of a group that is accessing the table. If tenant is removed from the group it will end up having its own partition of the table.

Below is a step by step guide on enabling, defining very simple MT enabled database which consists of 3 regular tenants, 1 tenant group and a super tenant using Database Administration console of OpenEdge database.

Step 1 – Enable database for multi tenancy.

Create a database connection to an existing database using database administration console.

Under database features enable Multi-Tenancy.

Step 2 – Define tenants

From the connection properties go to tenants and select new.

You will be provided with the below screen where you will be provided with options to define tenant name, storage areas for each type of partition and space allocation rule.

This space allocation rule defines when the physical storage is actually allocated for the partitions.

Also options are provided to select between super and regular tenants.

Create 2-3 tenants as multiple tenants are required to define the Multi-Tenant group.

Step 3 – Define Super tenant.

To define a super tenant follow step2 and use tenant type “Super”.  There will not be any space allocation options for super tenants as it will not have any physical partitions.

Step 4 – Define Domains for a tenants.

Defining domains is done inside of each tenant. Go to Tenants à Edit Tenant à Domains. Fill up the form to create the domain.

Authentication system can be selected from available list for the users of this particular domain.

Also by defining a access code you can include another level of security (User required to provide both his password and domain access code)

Step 5 – Define users per domain.

Go to Tenants à Edit Tenant à Domains. Fill up the form to create the user. Need to select the domain for the user from the list provided with currently created domains for the tenant.

Step 6 – Create Tenant Group.

Go to database connections à Groups à New.

Provide group name, Table included for this group, partition data areas and object allocation rules.

Then add tenants for this group from the Tenants tab.

All configurations can also be done using ABL or SQL commands if required.

After these configurations login from different tenants and query/update/insert data and check the partition, data isolation caused.