SaaS Application Database design approaches
One of the concern areas for any clients (tenants) in using any SaaS model application is “Data privacy”. “Data Sharing” option would really help the tenants, provided the application database design approach is fool-proof and build the confidence amongst the tenants.
SaaS (Software as a Service) is a model of software development whereby a provider licenses an application to customers (subscribers or tenants) for use as a service on demand. SaaS based applications are multi-tenancy not multi-instance application architecture.
“Data Storage”, “Data Synchronization” and “Database Maintenance” are three key focus areas from the deployment perspective. I came up with the following 3 database design approaches and optimized them to make the best out of each.
1. Individual databases servers:
The best way to isolate the tenant’s data is to deploy the tenant data in separate database server. This approach is it is easy to extend the existing database to meet tenant’s customization requirements. Flip side of this approach is its high cost of hardware inventory, licensing cost per hardware, and maintenance. Few tenants’ who requires a high degree of data isolation (physical) and are willing to pay more can opt for this design approach.
2. Shared database servers with Individual Schemas:
Tenant specific schemas would be created in the same database server and access rights permissions are enabled in such a way that a tenant can access only the resources (tables, stored procedures etc) in their specific schema. The key advantage of this approach is lower cost and support a virtually unlimited number of tenant’s databases. This approach will improve the performance of the application as we can work around a solution to effectively utilize the connection pooling. Even though the tenant’s databases are in different schemas in the same server, we can use the following technique to maximize the performance in DB connection. The following code is in C# (Microsoft .Net platform) and I am sure we can do the same with other languages as well.
1) objConnection.ConnectionString ="Data Source=MSSQL1;
InitialCatalog=FunnelDatabase; Integrated Security=true;";2) objConnection.Open();
3) objConnection.ChangeDatabase(”Tenant1″);
4) objCommnad.execute ("storedprocedurename");
The point # 3 in the above code is important. An initial connection to the database server has been established to FunnelDatabase. However, before executing the stored procedure / SQL statement, line #3 is changing the target database to Tenant1. By doing so, we are still utilizing the initial connection string and hence using the same connection pool for all the target databases.
3. Same Database with Same Schema:
All tenants’ data resides in same schema and share the same set of tables. But, a tenant identifier can be added to the primary key (composite key). A key challenge in this approach is it is not customizable at tenant level and rigorous testing required to gain the tenant’s confidence for data security and data privacy. Barring the above concerns, this is the best among all three approaches in terms of cost and maintenance. Even in this approach, we can establish some degree of data isolation by using some of the advanced techniques such as “Partitioning methods“, which allow physical data separation of each tenant’s data across physical devices while providing simplification of maintenance due to shared table definitions.
Let us talk about deployment strategies with networking options in our next blog.
How does one implement a Shared database servers with Individual Schemas in Oracle with ODP.NET as the provider as ODP.NET doesnt support changing of the database.
ODP.net does not support altering the database on the fly. However, we can look into the following options. Each approach has some limitations, but worth trying based on your requirement.
1) Create dynamic SQL statements in a stored procedure. Pass the DB schema name and prefix it before the table names while building the SQL statement.
2) You can write a Java stored procedure, which in-turn does the marshalling between various DB’s.
3) Even though I have not tried out, OCI (Oracle Call Interface) driver connection pooling by using Java language can help us in optimizing the connection pools.
>> okay provided all db access is through sps
How do you control the credentials to access these db?
Also, will this approach work in a cluster environment to address any performance issues.
— this should have gone before Murali’s comment above - but its essentially an email exchange we had—-
I’ve been thinking on the dynamic sql option and have come to the conclusion that dynamic sql might be quite okay provided all db access is through sps…
Basically we’ll need a sql call routing proc like route_call ( client name, proc name, param string) where all three are varchars. The proc maybe uses a lookup table to translate client name to a db schema name and then issues a dynamic sql – “proc_name params”
@S Murali
1)All DB access still goes through Stored Procedures.
2)We can pass the tenant-id (SaaS application subscriber) to the SP. Retrieve the database schema name based on tenant-id and build SQL statements with a prefix of this schema name.
3)We will not pass end user’s credentials to access the database, as it is not possible in the real time environment. Will use only one set of credentials to access all database schemas. We cannot use the same connection pool if the definition of connection string changes (dbname, userid, pwd etc).
4)Clustered environment (App servers or DB servers) will still work with this approach. DB cluster may be an Active-Passive or Active-Active with an external storage media such as SAN.
@Raghu
Ya Raghu. This is one thing I have tried and found manageable. However, we still need to evaluate the performance comparing multiple connection pools vs. dynamic SQL (re-compilation and lack of cache etc).