Typical Concerns of Multitenant SaaS Application Design

Creating a SaaS application, or migrating an existing application into a SaaS model can be difficult for a few reasons. In this post I will address some of the common issues developers and managers face when building a SaaS application and offer some pointers on how to solve them from a data management standpoint.

Common Roadblocks

Let’s first perform an inventory of the common concerns that developers and managers face when building a SaaS application, or when converting an existing single-tenant application into a multitenant, SaaS application.

  • - Data Isolation: how will I be able to isolate each customer database in a way provides sufficient security and reduces the risk of exposing the wrong customer’s data?
  • - Performance: how can I manage my customer databases in a way that allows me to collocate, or isolate customers based on service level agreements or performance?
  • - Management: how can I more easily manage multiple customers, such as performing cross-customer searches, adding new customers easily, moving customers from one database to another… and so forth?
  • - Technology Platform: How can keep my customers on different platforms (SQL Server and SQL Azure/Database) so that I keep my options open?

Although other concerns exist, these are the ones I hear the most about.

Addressing Multitenant SaaS Concerns With Enzo SQL Shard

Some of the concerns discussed above can be addressed by combining proper standards with technology. First, let’s talk about standards. To simplify the management of your multitenant and standardize common maintenance scripts you should establish specific rules upfront that will help you over the long run.

Here are a couple of important standards you should consider in your multitenant design:

  • - Customer Naming Convention: First and foremost, you should establish a naming convention for naming your customers. This naming convention will be applied to a few types of objects, like: Customer Key (the identifier for a customer), URL Redirection, UserId of the database login, Schema Name in which the customer data and objects will reside… and more. Normally it is best to store a customer database inside a uniquely named schema, which makes it much easier to move at a later time (you can use the Enzo Cloud Backup tool to move schemas for example).
  • - Customer Isolation Criteria: The next important step is to identify a set of rules you will use to determine the SQL platform your customers will reside and the amount of resources they can use. This will help you identify different service levels you are willing to offer to your customers based on a monthly plan they select (such as Basic Plan, Premium Plan) that could determine whether their data will reside on SQL Database (collocated database) or SQL Server 2012 IaaS (for more performance, flexibility, tuning, encryption…). Understanding the customer isolation criteria will help you define customer database migration needs and security requirements as well.

From a technology standpoint, you need to build an API or use an existing API that will give you a good level of abstraction for your multitenant queries. Normally a multitenant system requires a root database that contains every customer connection string, and a way to map a customer key to the correct login credentials. You may also want to build an API that provides additional functionality, like the ability to cache results, and fan queries across multiple databases/customers for management reports for example. Last but not least your API should be able to work on SQL Server, or possible on SQL Database. And if you decide to use SQL Database Federations, your API should also understand how to query for tenant data in that environment.

Because of the complexities involved in developing this type of API, I built one for you! Smile   It’s called Enzo SQL Shard; it was built in a way that makes it very easy to query a specific customer database, and even send requests across multiple customer databases. It understand SQL Database Federations if needed, or work on SQL Server or SQL Database, or both at the same time.

The Enzo SQL Shard API allows you to choose from 3 sharding methods (a shard, in this context, is the mechanism you use to spread your data across multiple databases): Compressed, Federation and Expanded. The first two, Compressed and Expanded, allow you to build SaaS applications. The Compressed strategy is designed to work on both SQL Server and SQL Database by separating each customer in its own schema/database. The Federation strategy is designed to work with Windows Database Federations. For more information on the various options available, please take alook at this document; it provides an overview on the Enzo SQL Shard API and some sample code on how to query tenant data: https://enzosqlshard.codeplex.com/downloads/get/687199.

You can download the Enzo SQL Shard open-source API on codeplex: http://enzosqlshard.codeplex.com/.  The code contains sample applications to see how to use the API.

Note that the Enzo SQL Shard doesn’t provide a complete, end-to-end solution for managing your application layer’s multitenant needs, such as identifying the customer key from a set of credentials, or the Customer Key that could be part of a URL query. However it provides a starting point for managing your data storage needs. If you have some feedback on the API please do not hesitate to use Codeplex’s comments section.

About Herve Roggero

Herve Roggero, Windows Azure MVP in South Florida, works for AAJ Technologies (http://www.aajtech.com) and is the founder of Blue Syntax Consulting (http://www.bluesyntax.net). Herve's experience includes software development, architecture, database administration and senior management with both global corporations and startup companies. Herve holds multiple certifications, including an MCDBA, MCSE, MCSD. He also holds a Master's degree in Business Administration from Indiana University. Herve is the co-author of "PRO SQL Azure" and “PRO SQL Server 2012 Practices” from Apress and runs the Azure Florida Association (on LinkedIn: http://www.linkedin.com/groups?gid=4177626).

Print | posted @ Friday, May 31, 2013 3:47 AM

Comments on this entry:

Comments are closed.

Comments have been closed on this topic.