Ever been bitten by Multi-Tenancy? The perils of a shared database...

  • Article

A reminder of Multi-Tenancy (skip to "Ever been bit?" if you are happy with Multi-tenancy)

During the last 12 months I have met with many ISVs to talk about the challenges of SaaS - the move from delivering applications "On-Site" to "On-Demand". The technical element of the conversations include an often lengthy discussion around options to deliver a system that from the perspective of a tenant (client of the ISV) behaves as if they had their very own system (their own web servers, application servers, database servers), yet actually the resources (the web servers, application servers and database servers) are servicing 10s, 100s or 1000s or tenants - Multi-Tenancy. 

We discuss the data storage element of Multi Tenancy at length in the paper Multi-Tenant Data Architectures.

This paper discusses the spectrum of approaches. With the extremes being one database per tenant (actually - in some cases one database server per tenant!) and shared database+shared schema across all tenants (although in practice you will partition your tenants across many nodes if you are very successful)

Ever been bit?

Which finally leads me to... have you ever been bitten by Multi-Tenancy? Specifically - have you ever felt the pain of having your data held in a shared database with shared schema? Well - I did yesterday and it was not pleasant.

One of my hobbies is online gaming. I do this with two groups of individuals

  • Group 1 - a bunch of workmates, real life friends and customers that get together every Monday for a spot of friendly, banter filled gaming (Think virtual lan party).
    • P.S. We are recruiting!
  • Group 2 - a proper online clan that plays in leagues and tournaments.

Each group has its own home clan site - with rosters, technical info, forums, downloads, calendars etc. In 8 years of online gaming the two clan sites have always been hosted by different companies - but not this year. This year I changed my "Group 2" and changed the hoster for "Group 1". Both clan sites are now built and hosted using https://www.spruz.com. Firstly the good bits

  • spruz deliver a great Web 2.0 community site creation and hosting offering for free, running on Windows Server 2003, IIS6 and ASP.  Big tick.
  • the two clan sites have worked great all through Jan. Big tick.
  • They upgraded the system a few days back and...

And... the shared database with shared schema bit me. Yesterday I was creating a number of posts in the forums of Group 2. Every post I created ended in an error message - but the refresh showed the post listed in the forum. A few hours later I went to edit one of the posts and ... it was empty. Actually all of them were empty. A little later I visited Group 1 and hey presto - all my posts for Group 2 were showing as replies to a post I made against Group 1. Doh!!!! Attempts to edit, delete, move etc all failed - as the metadata around these posts was now well and truly hosed. I filed a support incident with spruz.

For gaming this is not really a big deal (although it could easily have breached security if the posts had contained password for servers etc) - but it is exactly this increased risk of data leakage that prevents many classes of application being highly multi tenant. Which is why in the majority of cases my UK ISVs now building "On-demand" systems are using one database per tenant.

Had any similar experiences?