Multi-Tenant SaaS Patterns - Azure SQL Database (2023)

  • Article
  • 12 minutes to read

Applies to: Multi-Tenant SaaS Patterns - Azure SQL Database (1)Azure SQL-Datenbank

This article describes the different tenure models available for a multi-tenant SaaS application.

When designing a multi-tenant SaaS application, you should carefully select the tenure model that best suits your application needs. A tenant model determines how each tenant's data is allocated to storage. Your choice of tenure model affects the design and management of the application. Switching to a different model at a later date can sometimes be expensive.

A. SaaS concepts and terminology

With the software-as-a-service (SaaS) model, your business doesn't selllicensesto your software. Instead, each customer makes rental payments to your business, causing each customer totenantof your company

In exchange for paying rent, each tenant receives access to your SaaS application components and has their data stored in the SaaS system.

The termmodelo miterefers to how stored tenant data is organized:

  • Individual rental:Each database stores data for a single tenant.
  • Multiple Tenants:Each database stores data from multiple independent tenants (with privacy protection mechanisms).
  • Hybrid lease models are also available.

B. Selection of the appropriate rental model

In general, the tenure model does not affect how an application works, but it probably does affect other aspects of the overall solution. The following criteria are used to evaluate each of the models:

The focus of the discussion on the lease is theGivenCap. But consider theapplicationCap. The application layer is treated as a monolithic entity. Splitting your application into many small components can change your choice of tenure model. You may treat some components differently than others in terms of useful life and the storage platform or technology used.

C. Single-tenant standalone application with single-tenant database

Application level isolation

In this model, the entire application is installed repeatedly, once for each tenant. Each application instance is a separate instance, so it never interacts with another separate instance. Each application instance has only one client and therefore requires only one database. The tenant has the database to himself.

Multi-Tenant SaaS Patterns - Azure SQL Database (2)

Each instance of the app is installed in a separate Azure resource group. The resource group can belong to a subscription owned by the software vendor or tenant. In both cases, the provider can manage the software for the customer. Each application instance is configured to connect to the appropriate database.

Each tenant database is implemented as a single database. This model provides the best database isolation. However, isolation requires that each database be allocated enough resources to handle its peak loads. The point here is that elastic pools cannot be used for databases that are deployed to different resource pools or for different subscriptions. This limitation makes this stand-alone, single-tenant application model the most expensive solution in terms of total database cost.

supply Management

The provider can access all databases on all separate app instances, even if the app instances are installed in different tenant subscriptions. Access is through SQL connections. This cross-instance access can allow the provider to centralize schema management and cross-database queries for reporting or analysis purposes. If this type of centralized administration is desired, a catalog must be provided that maps tenant identifiers to database URIs. Azure SQL Database provides a partitioning library that is used together to provide a catalog. The chunking library is formally invokedElastic Database Client Library.

D. Multi-tenant application with database per tenant

The following pattern uses a multi-tenant application with many databases that are all single-tenant. A new database is provided for each new customer. Application layer scalinghochvertically adding more resources per node. Or app scaleForadd more nodes horizontally. Scaling is based on workload and is independent of the number or size of each database.

Multi-Tenant SaaS Patterns - Azure SQL Database (3)

Customize for a tenant

As with the standalone app pattern, using single-tenant databases provides strong tenant isolation. In any application whose model specifies only single-tenant databases, the schema can be customized for each specific database and optimized for its tenant. This customization does not affect other app tenants. Perhaps a tenant needs data beyond the basic data fields that all tenants require. Also, the additional data field may need an index.

With the per-tenant database, it's easy to customize the schema for one or more individual tenants. The application provider must create procedures to carefully manage large-scale schema customizations.

elastic polish

When the databases are deployed in the same resource pool, they can be grouped into elastic pools. Groups provide an inexpensive way to share resources among multiple databases. This pooling option is less expensive than if each database had to be large enough to handle the usage spikes it experiences. Although clustered databases share access to resources, they can still achieve a high level of performance isolation.

Multi-Tenant SaaS Patterns - Azure SQL Database (4)

Azure SQL Database provides the necessary tools to configure, monitor, and manage sharing. Pool and database level performance metrics are available in the Azure portal and through Azure Monitor logs. Metrics can provide great insight into aggregate and tenant-specific performance. Individual databases can be moved between groups to provide dedicated resources for a specific tenant. These tools are an inexpensive way to ensure good performance.

Scaling operations for the database by tenant

Azure SQL Database has many management features designed to manage large numbers of databases at scale, such as: B. more than 100,000 databases. These features make the multi-tenant database pattern plausible.

Suppose a system has a database with 1000 customers as the only database. The database can have 20 indexes. If the system moves to 1,000 single-tenant databases, the number of indexes increases to 20,000. In Azure SQL Database as part ofautomatic voting, the automatic indexing features are enabled by default. Automatic indexing manages the 20,000 indexes for you and your ongoing creation and deletion optimizations. These automated actions take place in a single database and are not coordinated or constrained by similar actions in other databases. Automatic indexing treats indexes differently in a busy database than in a less busy database. This type of index management customization would not be practical on a per-tenant database scale if this huge management task had to be done manually.

Other management functions that are appropriate include the following:

  • Integrated backups.
  • High availability.
  • Disk encryption.
  • performance telemetry.


Management processes can be scheduled and offered through adeveloperModel. Processes can even be automated and displayed in the app.

For example, you can automate point-in-time recovery for a single tenant. The restore only needs to restore the single tenant database where the tenant is stored. This restore has no impact on other tenants, confirming that management operations are performed at a granular level for each individual tenant.

E. Multi-user application with multi-user databases

Another available pattern is to store many tenants in a multi-tenant database. The application instance can have any number of multi-tenant databases. The schema of a multi-tenant database must have one or more tenant ID columns to selectively retrieve data from each tenant. Additionally, the schema may require some tables or columns that are only used by a subset of tenants. However, the static code and reference data is only stored once and is shared by all tenants.

Tenant isolation sacrificed

Data:A multi-tenant database inevitably sacrifices tenant isolation. Data from various customers is stored together in a database. During development, ensure that queries never expose data from more than one tenant. SQL database supportedRow level security, which can force the data returned by a query to be restricted to a single tenant.

processing:A multi-tenant database shares computing and storage resources among all of its tenants. The database as a whole can be monitored to ensure that its performance is acceptable. However, the Azure system does not have an integrated way to monitor or manage the use of these resources by an individual tenant. Therefore, the multi-tenant database is at greater risk of encountering noisy neighbors where the workload of one hyperactive tenant impacts the performance experience of other tenants in the same database. Additional monitoring at the application level can monitor performance at the tenant level.

lowest cost

In general, multi-tenant databases have the lowest cost per tenant. The resource cost for a single database is less than for an elastic pool of the same size. Also, in scenarios where tenants require limited storage space, millions of tenants can be stored in a single database. No elastic pool can contain millions of databases. However, a solution with 1,000 databases per pool and 1,000 pools can scale to millions at the risk of becoming unmanageable.

Two variants of a multi-tenant database model are discussed below, with the sharded multi-tenant model being the most flexible and scalable.

F. Multi-user application with a single multi-user database

The simplest multi-tenant database pattern uses a single database to hold data for all tenants. As more tenants are added, the database scales with more compute and storage resources. This scaling improvement may be all that is needed, although there is always a final scaling limit. However, long before that limit is reached, the database becomes unmanageable.

Management operations that focus on individual tenants are more complex to implement in a multi-tenant database. And at scale, these operations can become unacceptably slow. An example is a point-in-time data restore for a single tenant.

G. Multiuser application with fragmented multiuser databases

Most SaaS applications only access the data of one tenant at a time. This access pattern allows tenant data to be distributed across multiple databases or shards, with all data for a given tenant contained in one shard. Combined with a multi-tenant database pattern, a sharding model allows for almost unlimited scale.

Multi-Tenant SaaS Patterns - Azure SQL Database (5)

manage fragments

Fragmentation increases the complexity of design and operational management. A catalog is required to manage mapping between tenants and databases. In addition, administration procedures are required to manage the shards and the tenant population. For example, procedures must be developed for adding and removing shards and for moving tenant data between shards. One way to scale is to add a new shard and populate it with new tenants. At other times, you can split a densely populated shard into two less densely populated shards. After moving or deprecating multiple tenants, you can merge sparsely populated shards. A merger would lead to a more economical use of resources. Tenants can also be moved between shards to balance workloads.

SQL Database provides a split/merge tool that works in conjunction with the partition library and catalog database. The deployed application can split and merge shards and move tenant data between shards. The app also maintains the catalog during these operations and marks affected tenants offline before moving them. After the move, the app again updates the catalog with the new mapping and brings the client back online.

Easier management of smaller databases

By distributing tenants across multiple databases, the multi-tenant sharding solution results in smaller databases that are easier to manage. For example, when restoring a specific tenant to a specific point in time, instead of restoring a larger database containing all the tenants, you now need to restore a single, smaller database from a backup. The database size and number of tenants per database can be chosen to balance workload and management overhead.

Rental ID without scheme

Depending on the partitioning approach used, additional database schema restrictions may apply. The SQL Database split/merge application requires that the schema contain the partition key, which is typically the tenant identifier. The tenant identifier is the primary element of the primary key of all fragmented tables. The tenant ID allows the split/merge application to quickly find and move data associated with a specific tenant.

Elastic pool for chips

Sharded multi-tenant databases can be placed in elastic pools. In general, having many single-tenant databases in a pool is just as cost-effective as having many tenants in a few multi-tenant databases. Multi-tenant databases are beneficial when you have a large number of relatively inactive tenants.

Modelo H. Hybrides Sharding-Multi-Tenant-Datenbank

In the hybrid model, all databases have the tenant identifier in their schema. All databases can contain more than one tenant, and databases can be fragmented. So in terms of the schema, they are all multi-tenant databases. In practice, however, some of these databases contain only one customer. Regardless, the number of tenants stored in a given database does not affect the database schema.

transfer of tenants

You can move a specific customer to your own multi-user database at any time. And you can change your mind at any time and move the tenant back to a database that contains multiple tenants. You can also assign a tenant to a new single-tenant database when you deploy the new database.

The hybrid model shines when there are vast differences between the resource needs of identifiable groups of tenants. For example, suppose that tenants participating in a free trial are not guaranteed the same high level of performance as subscribing tenants. The policy might be that during the free trial period, tenants are stored in a multi-tenant database shared by all free trial tenants. If a free trial tenant signs up at the basic service level, the tenant can be moved to another multi-tenant database that may have fewer tenants. A subscriber who pays for the premium level of service can move to their own single tenant database.


In this hybrid model, single-tenant databases can be allocated resources to subscribing tenants to reduce per-tenant database costs. This is also done in the multi-user database model.

I. Rental contract models in comparison

The following table summarizes the differences between the main rental models.

Measurementstandalone appDatabase by tenantfragmentierter multi-tenant
Very high
1-100.000 s
isolation of tenantsVery highalternativeUnder; except for a single tenant (which is only in one MT database).
Database costs by tenantHigh; it is scaled to peaks.Under; used saucers.Lower, for small renters in MT DB.
Performance monitoring and managementper tenant onlyAdded + by tenantadd; although it only applies to singles per tenant.
complexity of developmentUnderUnderAverage; by crushing.
operational complexityLow High. Individually simple, complex in scale.medium depth. Patterns address complexity at scale.Low High. Individual tenant management is complex.

Next steps

  • Deploy and explore a multi-tenant wingtip application that uses the database-per-tenant SaaS model: Azure SQL Database

  • Welcome to the Wingtip Tickets sample SaaS rental app for Azure SQL Database

Top Articles
Latest Posts
Article information

Author: Laurine Ryan

Last Updated: 02/05/2023

Views: 5523

Rating: 4.7 / 5 (77 voted)

Reviews: 84% of readers found this page helpful

Author information

Name: Laurine Ryan

Birthday: 1994-12-23

Address: Suite 751 871 Lissette Throughway, West Kittie, NH 41603

Phone: +2366831109631

Job: Sales Producer

Hobby: Creative writing, Motor sports, Do it yourself, Skateboarding, Coffee roasting, Calligraphy, Stand-up comedy

Introduction: My name is Laurine Ryan, I am a adorable, fair, graceful, spotless, gorgeous, homely, cooperative person who loves writing and wants to share my knowledge and understanding with you.