Archive for May, 2011

Multi-Tenant – Marketing Buzz ? May 30th, 2011

Vinod Kumar

There has been lot of talk around various architecture – moving to cloud, SaaS, PaaS etc. And many ISVs we interact are exploring to achieve increased revenues and expand their customer base by moving to these marketing hype. Today, I wanted to just blog around things that come to mind of ISVs for building a strategy of moving their existing product to a SaaS model and this is exactly the time when we hear –

“How can we design for multi-tenancy?"

What does this mean?
Well, Maybe an online search can give you multiple definitions. From an product based ISVs point of view – they design software application to be consumed and used within a single company’s domain. Now what if you wanted to move the same application to the web for multiple users across multiple companies maintaining a single or simpler infrastructure.

A multi-tenant architecture in the SaaS world basically means one instance of your product serving multiple companies and multiple users within each company. There is a difference between users and tenants SaaS which is important to understand – think of a tenant as a company or a team – basically a collection of users. A user on the other hand is an individual person or system that is interacting with the product. There are already well written whitepapers on this very topic and repeating them here will be a waste of your time. I highly recommend reading the below papers for reference:

  1. Hosting a Multi-Tenant Application on Windows Azure :
  2. Multi-Tenant Data Architecture: – Article is quite dated but still has the concepts covered well. I was years ago directed to this paper when talking about SODA (Service Oriented Data Architecture).

Being a Database person, I get thrown with a question – “If we add the tenant_ID as part of every table, are we multi-tenant?” For me, that is a good start. Whenever I get a chance to talk on this topic – I bring out some more basic considerations:

  1. Data isolation – This will describe how each of the tenants want their data. And secondly, if the application mandates some tenants to move their data as part of the backup/restore or maintenance. Also, this can be an regulatory or compliance need. If thought properly, the Single Database (big-bang) approach or the Shared Schema approach fails here. Worst case, if that single DB gets corrupt – every other tenant gets affected?
  2. Data scoping and access control – When thinking this is also part of the above isolation technique. The important thing here is around who gets access to what part of data. The complications happen when you need to show different *master* data to different tenants.
  3. Horizontal scaling – This is to get the infinite scale and keeping manageability in mind, partitioning row-wise i.e. tenant-wise would give isolation and scales great. This also is an implementation of isolation tenant-wise. On-premise this can translate into an SQL Server Partitioning concept. In the Azure world we talk about SQL Server Sharding capability (Link) and recommend reading the whitepaper.
  4. Tenant wise customizability and configurability – Though in the SaaS world the scope for general profile customization are fine and easily implemented. At a business layer, many times the application needs to be extended and customized for different regions based on regulatory, compliance and other needs of a region or country. This is a very important factor – standardization will lead to ease of maintenance, single codebase, Reduced Opex etc. Be careful while taking a decision here, you don’t want to open a can of worms – “Evaluate flexibility at what cost?”. There are standard techniques for allowing customization as discussed in the Data Architecture whitepaper.
  5. Hosting on a hybrid but elastic infrastructure – When talking about SaaS or Cloud architecture people say Azure, AWS etc. Step back, when I mean hybrid or elastic – it can also mean at your own Data Centers offered to customers or DC of a Hoster or from standard cloud offering vendors. Your application needs to be built for such deployments – stateless, able to run independently on physical or virtual boxes in a dedicated and/or scale-out scenario without any problems seamlessly. Though the specifics on this very topic can be for a discussion some other day, keeping this in mind is an important factor.

Finally, All these when considered wisely could indeed give enterprises cost savings and benefits. If this is not achieved there is no point moving on first place. And if you don’t architect it right the first time and create a chatty application – remember you pay for data-in, data-out in the cloud world and that can dent your credit-card bills end of the month Smile.

There is more on this topic than what has got covered, atleast I got you a start and some thinking pointers if it has not been considered in the past. Do pass around your thoughts and comments.

Continue reading...


Database Mirroring–Witness Failure May 28th, 2011

Vinod Kumar

At a discussion with a customer they asked me what if the mirroring witness database fails. Does that mean the mirroring also fails as part of this? What about automatic failover? Is there a way to automatically remedy the same?

It was an interesting question and I felt it is worth to write a mini-post around the same.

  1. If Witness goes down, mirroring will not fail. 
  2. It will lose the automatic failover capability.

But there are some special case scenarios when the witness behavior can become interesting.

When both Witness and Mirror goes down, Principal loses quorum and becomes “isolated”.  It can’t serve the applications at this point. If the Mirror or the Witness can’t be brought back online quickly, the only way out is to break the mirroring session.


Re-establish database mirroring session when the mirror becomes available.

In the sys.database_mirroring catalog view there is a column – mirroring_witness_state. The witness server has three states in the sys.database_mirroring catalog view, CONNECTED, DISCONNECTED, and UNKNOWN.

Witness server unavailable


Principal unable to initialize the mirror


Since the state of the witness server is actually recorded in the partner servers, and not on the witness server, these states are set from the vantage point of the partners. So seeing a DISCONNECTED status for the witness server implies that the partners are disconnected from the witness. When database mirroring starts up, if the mirror cannot initialize with the principal, the witness will enter an UNKNOWN state. So, you could write some event handler to watch this state through the DMVs.

Besides, Witness could be on a failover cluster for higher availability. 

For more on the states of Database Mirroring can be got from the BOL.

Continue reading...