Life was good – your SaaS app was humming along, customers were happy, and business was booming. Then one day, it happened – a customer’s data is corrupt and needs to be restored ASAP! Or maybe a runaway job locked the database and brought the app to its knees, or a diligent customer demanded a more secure, isolated data store. You realize all your proverbial data eggs are in one basket, but you don’t want to maintain tens or hundreds of databases. Multi-tenancy is the solution to your problem!
What is Multi-Tenancy?
The problems mentioned all stem from many customers accessing the same, singular database. You could scale up your database with more resources or scale out to have replicated copies. However, these options quickly incur heavy expenses.
Multi-tenancy takes a different approach. Each customer’s data is isolated in its own database and can be managed separately from the others. Some databases may require more horsepower than others; some may need to reside in a specific part of the world (e.g., GDPR); maybe a customer requires an IaaS approach rather than PaaS, or a subset of the schema will vary from everyone else’s. A multi-tenant strategy offers organizations’ the flexibility they need for their unique situation.
Managing Databases with Azure Elastic Scale
Now you’re probably thinking, “That sounds great, but how do I manage all those databases?” Nightmare schema deployments and cross-database queries are already playing out in your head. Not to worry! Microsoft’s Azure Elastic Scale makes things easy.
Elastic Scale allows you to maintain many Azure SQL Server databases with one central point of reference for schema management, querying, reporting, and maintenance. This approach is accomplished by implementing a map of servers and databases and the tenants which belong to each. Data is then broken down into shards (fragments), which can be housed in any of these databases according to different strategies.
Time to Shard
In Elastic Scale, data is sharded (split into fragments) according to a key. Sharding keys can be an ID or GUID field identifying a customer, an event timestamp, or maybe an ISO code indicating a part of the world. The important thing is that this key is unique to each shard and relates to all the entities (tables and views) being sharded. Elastic Scale supports many sharding key data types, but be sure to confirm your type is supported first.
Once you have identified a sharding key, it’s time to think about a sharding strategy. You can shard by list (one shard for each unique key) or range (consecutive ranges of keys housed in the same shard). List shard maps offer a high level of isolation for each shard, and with that, a great deal of flexibility (geography, scale, security, etc.). Range maps allow many small tenants to be clustered together (i.e., customer ID’s 1-99, 100-199). If you choose a range map, consider some implementation of Row Level Security to ensure tenants are not able to access each other’s data. For master data that remains the same for each tenant, you can implement reference tables that are copied in their entirety to each shard.
Now you are ready to shard your data using Elastic Scale’s split-merge tool. Begin by migrating shardlets to their new shard homes. If you are using a range map, you have additional options to set the minimum and maximum bounds of the range. Later on, you can use this same tool to split ranges into finer shards, or merge into coarser ones. The split-merge tool runs as an Azure web job and provides an out-of-the-box web interface, and a more powerful web service backend for automation.
Where’s that Shard?
Using Elastic Scale and Entity Framework, your .NET or Java application does not need to know where each shard, database, or server lives. It simply connects to the shard map and allows it to route a query to the correct destination. Because sharding has been abstracted away from the application, you can migrate, split, or merge data without ever having to change any code. Elastic Scale also provides cross-database querying so that you can aggregate results from many or all shards, which can be helpful for reporting or auditing purposes.
Entity Framework is a powerful tool for schema management, and Elastic Scale extends this functionality to work in the sharding paradigm as well. You can apply schema changes to all shards to keep them in sync, rather than having to deploy the same change many times as you iterate through your databases.
Bottom Line
Whether you need to isolate data, separate noisy neighbors, or support heterogeneous tenants, multi-tenancy can be a low-cost and flexible solution to your database needs. Azure Elastic Scale is a very powerful solution that can be used to shard databases right out of the box or customized to meet specific requirements. Contact us if you need help getting started with multi-tenancy.
