Use SaaS patterns to accelerate SaaS app development on SQL Database

Posted on May 22, 2017

Principal Program Manager

We’re delighted to announce availability of a sample SaaS application and a series of management scripts and tutorials that demonstrate a range of SaaS-focused design and management patterns that can accelerate SaaS application development on SQL Database. These patterns extend the benefits of SQL Database, making it the most effective and easy-to-manage data platform for a wide range of data-intensive multi-tenant SaaS applications.

Database-per-tenant model gives tenant isolation

The discussion around patterns starts with the consideration of what data model to use. Multi-tenant applications have traditionally been implemented using a multi-tenant database. While multi-tenant databases remain effective for some applications, particularly where the amount of data stored per tenant is small, many SaaS applications benefit from the isolation inherent in using a database per tenant. The fully-managed nature of SQL Database and the use of elastic pools have made managing massive numbers of databases practical. Many ISVs are now running SaaS applications on SQL Database with tens of thousands of tenant databases in elastic pools. MYOB, a leading Australian accounting ISV, is managing over 130,000 tenant databases without breaking a sweat! A database-per-tenant model allows these customers to achieve levels of tenant isolation not possible with a multi-tenant database, with improvements in data security, privacy, performance management, extensibility, and more.

Learning from customer experience

By working closely with many of these customers, and learning from their experience, we have harvested a set of design and management patterns applicable to any business domain that simplify the adoption of a database-per-tenant approach and its use at scale. Based on these patterns, a sample SaaS application and a set of management scripts, backed by easy-to-follow tutorials, is now available, with all code on GitHub and the tutorials online.

You can install the sample application in less than 5 minutes and explore the patterns first-hand by playing with the app and looking at how it’s built using the Azure portal, SQL Server Management Studio, and Visual Studio. By studying the app and management scripts, and working through the tutorials, you can jump start your own SaaS app project.

Screenshot_5

The sample app is a simple event listing and ticketing SaaS app, where each venue has its own database with events, ticket prices, customers, and ticket sales, all securely isolated from other venues’ data. The app uses a canonical SaaS app architecture for the data layer. Each tenant is mapped to its database using a catalog database, which is used for lookup and connectivity. Other databases are installed to enable other scenarios as you explore the various tutorials.

Apps

SaaS scenarios explored

The app and management scripts address many common SaaS-related scenarios, including:

  • Tenant registration, including database provisioning and initialization, and catalog registration
  • Routing and connection from the app to the correct tenant database
  • Database performance monitoring, alerting and management, including cross-pool monitoring and alerting
  • Schema management, including deployment of schema changes and reference data to all tenant databases
  • Distributed query across all tenant databases, allowing ad hoc real-time query and analysis
  • Extract of tenant data into an analytics database or data warehouse
  • Restoring a single tenant database to a point in time

A load generator simulates unpredictable tenant activity, allowing you to explore resource management scenarios, including scaling pools to handle daily or weekly workload patterns, load-balancing pools, and managing large variations in individual tenant workloads. A ticket-generator allows you to explore analytics scenarios with significant amounts of data.

The app also benefits from other SQL Database features that are especially relevant in a database-per-tenant context, including automatic intelligent index tuning, that optimizes tenant database performance based on each tenant’s actual workload profile.

Integrated with other Azure Services for an end-to-end SaaS scenario

Several other Azure services are also showcased as part of the app, including App Services and Traffic Manager in the app layer, Log Analytics (OMS) for monitoring and alerting at scale, SQL Data Warehouse for cross-tenant analytics, and Azure Resource Management (ARM) templates for deployment.

The app will be extended over time to include more scenarios, from additional management patterns to deeper integration with other Azure services, including Power BI, Azure Machine Learning, Azure Search, and Active Directory, to build out a complete E2E SaaS scenario. We also want to explore the same scenarios with a multi-tenant database model in due course.

These SaaS patterns are also informing planning for future improvements to the SQL Database service.

Get started

Get started by installing the app with one click from GitHub, where you can download the code and management scripts.  Learn more about the patterns and explore the tutorials. Let us know at saasfeedback@microsoft.com what you think of the sample and the patterns, and what you’d like to see added next.