Multi-tenancy stuff

I spent some time today researching various bits and bobs on multi-tenancy web apps, and I thought I'd store my findings here for posterity(!) and for discussion.

My 2 cents:

1.       1.       One database, multiple tenants using a tenantID column on most tables

a.       Pros

                                                               i.      Cheaper from a DBA point of view, as less to manage/backup etc

                                                             ii.      Easier to merge tenants, or to do cross tenant reporting

                                                            iii.      Easier to update – create a new feature and roll out to clients using a TenantFeatures table

b.      Cons

                                                               i.      Less secure – a bug could introduce a data leak, though there are things you can do other than try and make sure all queries have a “where tenantId=” clause.

                                                             ii.      Arguably harder to develop client specific data structures

2.       2.     Single Database per Tenant (client)

a.       Pros

                                                               i.      Inherently secure – the client is always only connected to their own database, so we don’t need to do additional filtering

                                                             ii.      Easiest to “handover” to client – back it up and send it to them

                                                            iii.      Easy to add a column for a specific client (though perhaps not desirable from a maintenance point of view)

                                                           iv.      Easier to push one client to a new server, if one client has much larger capacity requirements

b.      Cons

                                                               i.      Maintenance requires more DBA resource

                                                             ii.      Multiple versions of data layer become possible, introducing additional complexity

                                                            iii.      Harder to develop initially – how can we still make it easy to create new sites through a control panel?

                                                           iv.      Would require more IT resource generally

                                                             v.      Need a strategy for dealing with “shared” data such as supplier rates.

http://msdn.microsoft.com/en-us/library/aa479086.aspx 

Good MS overview of the different options, plus some cunning ways of walling tenant data in one database.

http://blogs.imeta.co.uk/jyoung/archive/2010/03/22/845.aspx

Extends the cunning way of walling data in one DB

http://ayende.com/blog/search?q=multi

Decent series of blogs on multi tenancy

http://romiller.com/2011/05/23/ef-4-1-multi-tenant-with-code-first/

Possible solution for changing DB context based on tenant, with EF Code First

http://stackoverflow.com/questions/20321/one-database-or-many

http://stackoverflow.com/questions/13348/what-are-the-advantages-of-using-a-single-database-for-each-client

Stack over flow Multi Tenancy discussions

http://lukesampson.com/post/303245177/subdomains-for-a-single-application-with-asp-net-mvc

Sub domains for one app with MVC