How to achieve database multi tenancy in cloud foundry

[man...@gmail.com]

If I have a Web application which can be deployed as a software as a service application then to achieve database multi tenancy I can have either of the 3 below approaches in cloud foundry

1. separate database service for each tenant

2. Shared database service for all tenants but how do I achieve data isolation, I know we can't create schema either for MySQL, Postgres service. In that case I need to have a tenant based identifier column in every table for e.g.: TENANT_ID and every query will have the TENANT_ID as part of the where clause.

Suppose if I want to divert SQL query(s) to a specific schema such that each tenant will have a separate schema and based on user logged-in, application will decided which schema to query at runtime. How do I achieve this in CF ?

[Frank Lu]

Try postgresql service which supports schema. You could create a schema for each registered user.
Speficy the schema name in queries during logged-in user's session.

On Wed, Dec 26, 2012 at 4:13 PM, <man...@gmail.com> wrote:

Suppose if I want to divert SQL query(s) to a specific schema such that each tenant will have a separate schema and based on user logged-in, application will decided which schema to query at runtime. How do I achieve this in CF ?

--

Frank(mingfan) Lu

Tel: 86-21-61976515

VMware Cloudfoundry Services Team

10F, KIC III, No. 333 Songhu Road, Yangpu District. Shanghai 200433

[Andrea Campi]

On Wed, Dec 26, 2012 at 9:13 AM, <man...@gmail.com> wrote:

If I have a Web application which can be deployed as a software as a service application then to achieve database multi tenancy I can have either of the 3 below approaches in cloud foundry

You forgot approach #3 ? 

1. separate database service for each tenant

What is wrong with this one? CF gives you this out of the box.

I think the most important consideration is, how does your app work?

If you can afford to run one app instance per tenant, then you're done really--one app instance per tenant, bound to a separate DB instance per tenant.

If you cannot / don't want to go that way, then your app will need to collaborate in keeping data for your tenants separate.

One easy way that is CF-friendly is to still have a DB service instance per tenant, and bound several to the same app instance. Your app will need to know which DB to use for each tenant; how you do that depends on your application framework.

Hope that helps.

Andrea

[man...@gmail.com]

The approach 2 which I mentioned has two ways of doing it:

1. Have multiple DB services, one for each tenant, but the question now is, how can a web app decide which service to connect for which user, what can be the possible ways in case if you have a single Web-App for all tenants, 

I can bind multiple DB services when I push an web app to CF but what will have if I create new DB service, do I need to push my Web-App every time I create a new DB service and bind those services

2. Have a single DB service and manage the tenant data isolation using schema, how this can be achieved in CF, does postgresql DB service gives this feature

[Andrea Campi]

On Wed, Dec 26, 2012 at 10:48 AM, <man...@gmail.com> wrote:

The approach 2 which I mentioned has two ways of doing it:

1. Have multiple DB services, one for each tenant, but the question now is, how can a web app decide which service to connect for which user, what can be the possible ways in case if you have a single Web-App for all tenants, 

I can bind multiple DB services when I push an web app to CF but what will have if I create new DB service, do I need to push my Web-App every time I create a new DB service and bind those services

Nope, you can do `vmc bind-service` without pushing the app. However this will still require a restart of the app.

 

2. Have a single DB service and manage the tenant data isolation using schema, how this can be achieved in CF, does postgresql DB service gives this feature

As Frank mentioned, the postgresql service in particular supports that.

[man...@gmail.com]

How do I achieve data-source connection to multiple DB services. I can create a data-source for each db service and using application logic decide at run time which data-source connection to use based on user session

 

The problem here is, if there is a new DB service created then I will have to change application configuration files, can this be abstracted ? suppose if the service stops and after service restart, will the data-source connection still be active (or) do I need to restart my app.

[Andrea Campi]

On Wed, Dec 26, 2012 at 11:39 AM, <man...@gmail.com> wrote:

How do I achieve data-source connection to multiple DB services. I can create a data-source for each db service and using application logic decide at run time which data-source connection to use based on user session

 

The problem here is, if there is a new DB service created then I will have to change application configuration files, can this be abstracted ?

That's up to your app. If you name your CF DB service "cust-xyz", it will be available to your app; you can then keep a "map" of "cust-xyz" -> "data source for cust-xyz" and use the correct one as needed.

CF knows nothing about that nor should it. And your app is not going to be much different because of CF--the only thing that changes is how your data source gets instantiated, after that it's just the same.

 

suppose if the service stops and after service restart, will the data-source connection still be active (or) do I need to restart my app.

That's the rub: as I wrote before, when you add a new service instance you need to restart the app.

If that's not doable, then your best bet is probably to use per-tenant schemas.

There is no need for the service to restart, though, so I'm not sure what you are really asking here. Are you concerned about the DB service failing?

[Manikandan]

Yes when DB service which one binds to a app, if it stops, does it restart automatically, and if our data source connection pool becomes stale and I will have to restart the app for refresh

Cheers,

Mani

Mobile:+91.9500029049