Today, SaaS (Software as a Service) applications power many of businesses’ internal and external processes. The SaaS industry is projected to have its largest annual growth in 2021-2022 at a value of $171.9 billion by the end of this year, surpassing its yearly growth rate of 17%.
However, SaaS application development has numerous challenges, whether you are migrating existing packaged software or developing new services. One of the challenges is database management at scale as, by nature, many SaaS applications need to support thousands of data tenants or more.
Now, the largest applications (in terms of database count) developed on SQL databases are all SaaS applications. And this article introduces a sample application and patterns that you can use to accelerate the development of SaaS applications on SQL Database (if you are considering Microsoft Azure for hosting your application).
Let’s start with a quick overview of SQL Database.
Introduction to SQL Database
Typically, SQL databases are relational databases that use a structured approach to store data and perform operations for data creation, modification, storage, and retrieval.
In this article, we will discuss Azure SQL Database, a relational database service in the cloud based on the Microsoft SQL Server engine, with mission-critical capabilities. It delivers predictable performance, scalability with no downtime, data protection, and business continuity– all with near-zero administration. Thus, you can focus on rapid app development and accelerate your time to market while it manages virtual machines and infrastructure. It also minimizes your overall costs and is an ideal fit for building SaaS applications.
SaaS app patterns to accelerate your SaaS development
After working closely with many SaaS customers, Microsoft harvested a set of SaaS-specific design and management patterns that can accelerate the development of SaaS applications on SQL Database. These patterns apply to any business domain seeking to simplify the adoption of a database-per-tenant approach and use it at scale.
● Database-per-tenant pattern
Here, a database is created for each tenant (customer who will sign up for the SaaS app), and all the databases are centrally managed. Additionally, a catalog database holds the mapping between the tenants and their databases, facilitating lookup and connectivity. The databases can be hosted in elastic pools for cost efficiency and easy performance management, leveraging the unpredictable workload patterns of the tenants.
This pattern is useful for service providers that benefit from tenant isolation and want to run a centralized service for cost-efficient use of shared resources. For instance, it could be beneficial when the amount of data to be stored per tenant is small. This model also allows service providers to achieve levels of tenant isolation not possible with multi-tenant databases.
● Standalone application per tenant pattern
It uses a single-tenant application with a database for each tenant. Each tenant’s apps and database are deployed in a separate Azure resource group. The group can be deployed in the service provider’s or tenant’s subscription and is managed by the service provider on the tenant’s behalf.
This approach provides the greatest tenant isolation but is expensive as no resources are shared between tenants. It is well suited for applications that are complex and deployed to a smaller number of tenants. However, standalone deployments allow for the app to be customized for each tenant easily.
● Sharded multi-tenant database pattern
It allows packaging a large number of tenants into an individual database, making near-infinite scaling possible by sharding the tenants across multiple databases. Further, a catalog database maps tenants to databases. It reduces cost-per-tenant and is, thus, effective for service providers looking to lower costs and approve reduced tenant isolation.
This pattern also allows a hybrid model where you can optimize for isolation (with a single tenant in their own database) or cost (with multiple tenants in a database). The choice will differ on a tenant-by-tenant basis. This model is ideal when groups of tenants need to be treated differently. For instance, low-cost tenants can be assigned to shared databases, while premium tenants can be assigned to their own databases.
Wingtip – A sample SaaS app
Based on the patterns identified, Microsoft produced a sample app, Wingtip, that embodied the SaaS patterns and best practices, along with a set of management scripts and tutorials. It is available online with all code on GitHub.
You can deploy the sample application into your own as a subscription in less than 5 mins and explore the patterns first-hand by playing with the app. You can also study the app and work through the management tutorial at your own pace to jump-start your SaaS project. However, developing a SaaS app from scratch can be overwhelming. If you are plagued with questions like – how to build a SaaS application? What things to keep in mind? What steps to follow? – refer to this in-depth guide for founders on SaaS development.
Wingtip is a simple event management and ticket-selling application for venues such as theaters, pubs, clubs, etc. It is a multi-tenant in which each venue has its own database storing information on events, ticket prices, purchases, customers, and so on, all securely isolated from other venues’ data.
The app can also implement different patterns including a standalone application per tenant with its own database and a multi-tenant app with sharded multi-tenant databases.
Exploring various SaaS-related scenarios
The sample app, along with the tutorials and management scripts, showcases an end-to-end SaaS scenario. However, here is a list of common SaaS-related scenarios that app and management scripts can address.
- Tenant registration, including database provisioning and initialization
- Catalog registration
- Routing and connecting from the app to the correct tenant database
- Database performance monitoring, alerting, and management, including cross-pool monitoring and alerting
- Schema management, with deployment of schema changes and reference data to all tenant databases
- Restoring a single tenant database to a point in time
- Distributed query across all tenant databases for ad hoc real-time query and analysis
- Extracting tenant data into an analytics database or data warehouse
In addition, you can explore resource management scenarios with a load generator that simulates unpredictable tenant activity. The scenarios can include scaling pools to handle daily/weekly workload patterns, managing large variations in individual tenant workloads, or load balancing pools. Using a ticket generator, you can also explore more analytics scenarios with large amounts of data.
The sample also benefits from other features of SQL Database that are particularly useful in a database-per-tenant context. For example, automatic intelligent index tuning optimizes tenant database performance based on each tenant’s workload profile.
Integration with other Azure Services
Many Azure services are also a part of the sample application, such as
- Azure App Services and Azure Traffic Manager in the app layer
- Azure Resource Management (ARM) templates for deployment
- Log Analytics (OMS- Operations Management Suite) for monitoring and alerting at scale
- SQL Data Warehouse for cross-tenant analytics
Microsoft plans to extend the app over time to include more scenarios and then also explore them with a multi-tenant database model. The scenarios range from additional management patterns to deeper integration with Azure services, such as Power BI, Azure Search, Azure Machine Learning, and Active Directory.
Conclusion
The SaaS patterns aimed to address key data management concerns of SaaS developers– cost per tenant, scale, and tenant isolation. However, you must choose a pattern that best suits your application needs as it impacts the application design and management. Also, switching to a different model or pattern later can be costly most of the time.
