Database multitenancy architecture

With a sizable number of companies offering SaaS software. Architects are faced with typical problems of how to manage their customer's data.
Questions like, Data security, backup, recovery, cost(ROI) are typically what an architect needs to answer when offering a strategy to achieve multitenancy.
Whenever my peers ask me "What is the best database strategy to achieve multitenancy".
My answer is "It depends!"
  • It depends on the domain that you are targeting. Or rather the data that your software is going to manage. The isolation that is required. 
  • It depends on the dollars your business is willing to spend (also read as licensing fees, hardware at your disposal, ability to quickly ramp up production capacity). 
  • It depends on how fast you need to provision or setup a new customer. 
And while I can go on and on, the point here is that there has to be "buy in" from your business that lets you decide what is the best strategy to be applied to the application that is being built.

As far as databases are concerned there is only a finite set of approaches that can be used to achieve multitenancy and these approaches would take you from a isolated to a shared database architecture. The reason I go from isolated to shared architecture is because in the past decade many software companies that offered "single installation" products have modified their offerings to make them "SaaS" compatible and as they try to manage cost they knowingly or unknowingly move from an isolated database to a shared database architecture.

Separate database
Each customer/ tenant has a separate database. This is probably the most easiest approach to mutitenancy. The customers data is isolated. There is no way another tenant can access or corrupt the data. In case of customization needs the data model can be extended as per your customers requirements. Backup and restoring is straight forward and there is no impact on the another tenants data. Almost zero or minimal application logic is required to achieve isolation. This however is a costly approach as you add more and more customers to your offering. Hardware and processing costs will be higher. A single server can only hold a finite number of databases and so more hardware will be required. And you would need a substantial budget and strategy as you start gearing up for horizontal scalability. This strategy is most suited for customers who are willing to pay a premium for added security and customization.

Shared database separate schema
This approach is similar to the separate database approach. It provides logical isolation of data. Each tenants data can be accessed using the schema name and the table name. There is some application logic required to resolve the schema for the entity. You can accommodate more customers per server as compared to the separate database approach. Data restoration would provide some problems when it comes to restoring a specific customer's data. This approach is suitable when you have a small number of tables that are used by your application.
(A variation to this model would be a shared database, shared schema and separate tables per tenant. Additional logic would be required so resolve which set of tables to use for a specific customer).
This approach while costly is still cheaper to implement as compared to the separate database approach. This strategy is suited for customers who accept co-location of their data.

Shared database shared schema (and shared tables) 
In this model the customers data is stored in same set of tables. A tenant id column is required in all relevant tables to identify a tenant's data. 
This strategy is the most cost efficient as far as your hardware budget is concerned. However there would be extensive application logic required to ensure security and separation of data. Added care from a software architecture perspective has to be taken so as to ensure that a tenant can NEVER see or corrupt another tenant's data.

Data restoration in case of a failure can be big pain point especially if you are trying to restore a specific tenant's data. This would involve importing your backup into a temporary database and then copying over the relevant tenants data into the production database. This could be a fairly complex (or as a friend of mine would say "Non Trivial") and time consuming task.

As far as performance is concerned, one customer's volume could potentially impact another customer.
As you keep adding customers you could potentially reach OLAP volumes requiring OLTP operations. Hence your indexing, partitioning, archival strategies play an important role. But more on this later..

Irrespective of the approach taken, it is important to understand the trade offs being made. It will also play an important role in deciding your strategy as far as scalability, extensibility configuration capabilities are concerned.

Apple vs Google

iCloud is coming and already everyone is talking about how great it will be. Fact of the matter is that there are literally thousands of vendors with cloud based solutions over the past 5 years.

Google, Salesforce, Amazon and to an extent even Microsoft have been providing cloud based solutions to their Customers.
But today I am going to speak about Google vs Apple and take up a non-technical key aspect when it comes to comparing the two. And that is the "message" that is given out to their Customers (both current and potential)

Apple went past Google to become the most valuable brand in the world and it has achieved this in a span of 5 years (since 2006 its brand has grown 859%).

Apple's message has been user centric. They focus on the simplicity and ease of use. Their message for iCloud was "It just works!" They did not focus on what server, what login, how one can save their data on the cloud. They have built it intrinsically into their apps and said that we as users dont need to worry about details. As geeks we may say that this is another way to tie you down to the Apple ecosystem but, normal users really dont care as far as the job is getting done.

Google on the other hand focuses on the technology aspect which overwhelms the average user. I have been using Google Apps for the past 3-4 yrs but people around me don't even know about it.  When I speak to people about it, the reaction I get is that of surprise. "Oh Google has documents and sheets like Microsoft office!" or "I didn't know I could use Gmail for my office". The minute one says "Google" people co-relate it to search. But the fact is that Google has been providing a much more advanced cloud based platform for everyday users for managing their productivity. However it seems that their message only gets through to geeks and not normal everyday users. 

Google has a clear advantage when it comes to their Cloud based offerings and the years that they have invested in it. However Apple has been more effective in marketing their message across.

iOS 5

The ball is back into Google's court as far as iOS vs Android wars are concerned.
Here are a few features that I wanted in iOS and are now available for use in iOS 5.

But let me start with one of the most eager features I thought the iPad or iPhone should have in my previous post 3 months ago which is now a reality.

Standalone Device: The iOS device can now be used as a standalone device. You can upgrade, download apps and backup (using iCloud) without requiring a PC or a MAC. Further you can optionally still back up with you computer over WiFi. This eliminates the need of connector.

iCloud: Cloud based synchronization to keep all your devices updated, with mail, contacts, photos, apps, messages and books (along with book marks) and last but not the least music. But more about this in another post

iMessage: The Apple Messages app simplified text messaging and grouped them as conversations similar to their iChat application on OSX. iMessage is now integrated into the Messages. This is Apple's answer to RIM's BBM. You can now text, send photos and videos. In addition to this you have group messaging and delivery receipts with optional read receipts. With over 200 million IOS devices one could argue that this is a blow to traditional SMS and MMS offerings. However SMS and apps like WhatsApp would still be good mediums for cross-devices messaging.

Reminders: Apple calls it a "better way to do to-dos" and I agree! Not only can you organize your tasks with due dates but you can now make them location based. So you could create tasks that you need to address as soon as you reach your work place and tasks that you need to do at the nearby grocery store. And an alert appears as soon as you reach your destination.

Notifications:  No more annoying popups. iOS5 comes with a Notification center which consolidates all notifications

Some other features include Multitasking gestures, Photo editing, airplay mirroring and much more. You can go to the Apple site to read more

iPad 2 and IOS 4.3


  • All new design
  • A5 Dual Core processor
  • 9 times faster graphics
  • Front and rear cameras
  • Gyroscope   
  • 33% Thinner (less that the iPhone4)
  • Lighter from 15 lbs to 1.3 lbs
  • Comes in 2 colors
  • Works with both AT&T and Verizon
  • Same "legendary" 10 hours batter life. (A month of standby)
  • Same pricing structure
  • Shipping March 11
  •  HDMI Video out (runs 1080p) for 39$
  • Smart Covers (bendable flap that sticks on the side). Wakes iPad from sleep when you open it and puts it to sleep when you close it. Magnetic grasp to hold the unit
Applications coming to the iPad
  • iMovie: Features:
    -Prevision editor
    -multitrack audio recording
    -new themes
    -AirPlay to Apple TV
    -Share your videos in HD
    -Universal app
  • GarageBand!! Touch instruments
    Guitar amps and effects
    8 track recording and mixing
    More than 250 loops
    Email AAC file of your song
    Compatible with Mac version.
And the iPad 2 is now displayed on the apple site 
      IOS 4.3 (March 11, 2011). Supports iPad, iPhone (GSM) and iPod Touch (3rd and 4th generation)
      • Safari performance bump with the nitro javascript engine. Runs javascript twice as fast as before
      • iTunes Home sharing (See your other iTunes libraries from your PC and Mac on your iOS Device
      • Airplay improvements (Photo slide shows, Video and Audio from apps to other devices
      • Preference for iPad Switch (Mute or rotation lock)
      • Personal Wi-Fi hot spot
      • Photo Booth in IOS 4.3 with nine live video streams and various effects
      • Facetime on iPad
      Some pictures from the Apple Event going on right now!!

      Ipad 2 release

      I've been eagerly waiting for the next generation of Apple's iPad to hit the market. And hopefully tomorrow (March 2, 2011) should reveal something exciting.

      As mentioned in my previous post Apple would have to come up with something that would trump Google's Honeycomb.
      The hardware pricing for the Honeycomb tablets could probably be an issue.
      The XOOM is priced at $799 and $600 for the Wi-Fi only option as compared to the $729 and $599 for a 32GB IPad. Ofcourse the manufacturers can justify the pricing considering that their offering has more to offer as far as hardware is concerned. This could however change tomorrow.

      One of the reason's I didn't jump onto the iPad bandwagon was because I felt it was incomplete. Apple released this early so as to get a hold of the market and in doing that compromised on certain features.

      While the internet has been buzzing with guesstimates on what these features could be. Here is what I would want Apple to offer.
      • A front facing camera along with Facetime support. 
      • No back facing camera. I wont complain if the new iPad comes with one, but really don't see how you can click photographs and shoot videos using a 10 inch slab.
      • The iPad should become a standalone device much like a Laptop or a MacBook. This means ability to activate, download and backup data without having to connect to another device.
      • Cloud based synchronization similar to the Android Marketplace
      • Retina Display or rather a higher resolution display to enhance the movie and video calling experience.
      • More memory and processing power.
      • And true multitasking capabilities.
      I also hope that IOS 4.3 is also showcased tomorrow. 

      Apple iPad IOS 4.2 vs Google Android 3.0 Honeycomb tablets

      In Apple's quarterly financial conference call for Q4 2010. Steve Jobs declared that the new tablets flooding the market were dead on arrival (DOA). One reason was the fact that the 7" is 45% smaller than the 10 inch iPad. This makes the smaller tablets sit it an awkward spot. It is too big to be a mobile phone and too small to be a serious mobile computing device.

      The other reason was simple. The operating system, running on these tablets, was not essentially built for a tablet experience. It was more like taking a mobile phone O/S and pushing it on a bigger so-called Tablet.

      Most of the tablet manufacturers rushed to get their devices out in the market only to ensure that they don't lose out to the iPad(7.5 million sold in 2 quarters).

      But Honeycomb changes the game for these manufacturers. While Apple redefined the mobile phone platform and then took that platform to the iPad. It is quite obvious that the iPad has its limitations.

      Honeycomb on the other hand brings the desktop experience to the tablet in a seamless manner.
      In short, it means that the tablet manufacturers will no longer try to play catch up with Apple as far as the operating system is concerned. Apple will really need to dig deep into its bag of tricks(IOS 4.3) if it has to surpass Honeycomb.

      That said, a lot will depend on the hardware and the new tablets that will come into the market with honeycomb. Battery life will also be a key factor. Both Samsung and Dell are working on a 10inch version of the Galaxy Tab and Streak. Not to mention the MOTOROLA XOOM

      Some of the features in Honeycomb are as follows:
      • Hardware acceleration for 2D and 3D: Applications can now use hardware acceleration there by improving the gaming and graphic experience.
      • New animation framework to add transitions
      • Better multitasking menu, with faster switching across apps.
      • All software based buttons, such as the Home etc.
      • Renderscript: A brand new graphics engine there by improving the YouTube, Album and Books experience (particularly the page flipping is close to the iBook experience)
      • Support for multi-core processors
      • Media Capabilities: New Camera application with re-designed user interface giving the user more options. Ability to tap into a front facing camera, built in image stabilizing technology (which should improve the video calling capabilities and reduce bandwidth)
      • Built in video chat capabilities coupled with a contact shortcut widget that enables a quicker way to contact, chat and video call.
      • Seamless synchronization with the cloud.
      • Market Place: Hot synchronization of apps purchased on the desktop with your mobile devices (eliminates the need for cables to transfer your purchases from your desktop to mobile devices. Friend based recommendation for apps using email and twitter. Soon to be rolled out buyer currency support so that you can buy apps in your own currency without having to do conversions. Developers can monetize their application by promoting apps within their existing apps using the in-app purchasing SDK. Ability to promote your app over YouTube.
      To get a more in-depth understanding of Honeycomb please follow the video below:

      Customer Service and Satisfaction - Part 2


      This is another company that started out with a lot of promise. Around 4 yrs back I searched for a Telephone and Internet Service Provider. Being a small company, I didn't want an expensive lease line.
      After looking at all the options I settled on Airtel. The sales team was efficient, the technical team came within a day of signing the contract and everything worked seamlessly. For 2 yrs there was only a couple of instances when I had at outage. The service team was quick to respond which minimized the downtime to less that 2 hrs.
      However after that it has been all downhill for this brand. In their rush to expand they have got their basics all mixed up and overall quality , integrity and service has been pathetic if not worse.
      This is a company which recently spent a lot of dollars trying to re-invent its brand image.

      Over the past 6 months there has been frequent internet outages. The response and resolution time has gone up from 2 hrs to 24-48 hrs. Imagine a software company with no internet connection for 48 hrs. It is next to impossible to get in touch with customer service. And when you get through, more often than not their systems are down. (I think Airtel uses IBM based upon the numerous Tivoli errors I get when trying to pay my bills online)
      As far as their cell phone services are concerned, their network quality has dropped. My cell phone only catches their network in "strategic" locations, for e.g. my office restroom, my kitchen at home and of-course when you stand close to their network antennas which are also placed at "strategic" locations

      When I signed up with Airtel for an internet plan the monthly charges I paid was around 3000 INR (Approx $80). As my demands for bandwidth increased so did my bill which was fair enough. Within a year's time I started paying around 18000 INR ($400) a month for a 2Mbps DSL line. After a couple of months I realized that the same plan was being offered by the company for 4000 INR (approx 90$). I contacted the sales team and asked them why I was still paying so much when the prices had dropped.  I was told that since it was a corporate account, which meant a dedicated line which meant I got a true 1:1 bandwidth, the prices for the same was high. Also I was told that the plan mentioned on the website was only for home users and not SMBs.
      After some months I realized that the speed wasn't 1:1. A trip to their local office also confirmed the same. After repeated attempts of trying to get in touch with their sales team, I kept getting excuses that someone would come down to my office and explain why they were charging exorbitant rates. But no one did really show up.
      After some days I noticed that the company had updated their website and now also displayed their corporate plans. The pricing was similar to the home users plan. This meant that I was unnecessarily payment way too much.
      I contacted customer service and was told that they would immediately change my rates and I would have to pay less from the next billing cycle. The next billing cycle came and I was still charged the exorbitant rate. On contacting customer service again, now I got a different explanation. This time I was told that when changing a plan there is a one month notice period that I have to give and so I would have to pay this bill. The new billing cycle would be effective from the following month.
      No prizes for guessing that the following month also I was charged same amount. Its been close to 4 months that I have been trying change my plan.

      The last time I used their toll free number and tried to get in touch with a "person". Their IVR system conveniently reminded me that since I opted to speak with someone, instead of the 20+ automated options that their system provided, I would be charged a flat rate per minute. Need I say more?

      Here is a company that cannot scale with the growth they are experiencing, has lost its way as far as quality and customer service is concerned. And they don't even want their customers to speak to them.
       Part 1 of the post is here

      Customer Service and Satisfaction - Part 1

      Over the past decade, the Indian market has grown exponentially. The onset of Malls, Multiplexes, Brands has changed the lifestyle of the average indian. Being from Mumbai, I have been fortunate enough to experience this first hand. However along with the good also comes the bad.
      Things have become more expensive, real estate prices have shot through the roof and even essentially commodities cost a fortune. People today don't get the service then deserve even after paying a premium for the goods they purchase.

      I will take examples of 2 companies in different areas that have failed to deliver.
      • Dell
      • Airtel
      Around around December 27, 2010 a colleague of mine purchased a Dell Streak. He took extra care (and extra money-33K INR) to purchase it from an authorized Dell outlet to ensure that he got  legitimate device and could utilize the warranty.  I was skeptical at first, since there are many android based touch phones available in the market and also for the kind of work he was doing (mobile apps), I would go with either the Samsung Galaxy Tab or the iPad. However he convinced me of features, the speed, swype, the small size and most importantly the Gorilla glass.
      Everyone at work was excited with his new acquisition. And so each one of us took a test drive. After a couple off days, I noticed him without his device only to find out that one of our colleagues dropped it and the lcd inside the "gorilla" glasses broke.
      This is how it happened, the colleague was sitting on the sofa and decided to wipe the screen. He accidentally dropped the device, which bounced off his shoe and hit the floor (approximately from a height of 12inches) and the screen broke. My first reaction was to go an google and search for this only to find that a number of people complained about the same problem.
      On calling Dell support, he was told that they don't have parts and he should check again later.  He also called the dealer who directed him back to Dell support.
      After trying numerous times he was told that it would take 3 weeks for the part to be available. Also he would have to pay for the screen replacement even though the product was still under warranty. After 3 weeks he was told that they still don't have the part (Also the dealer has stopped taking his calls :))

      On the 17th of Jan 2011 he bought an iPad.

      Part 2 of this post is here