Building and Managing SaaS Applications on SQL Database SaaS

Building and Managing SaaS Applications on SQL Database SaaS patterns explored

Session objectives and takeaway Equip you To choose among database models for multi-tenant SaaS apps With SaaS patterns that can reduce development, management and resource costs With a sample SaaS app scripts to explore the patterns Takeaway PaaS features SaaS patterns make SQL DB a highly scalable, cost-efficient data platform for multi-tenant SaaS

agend a SaaS data management concerns Database models for multi-tenant SaaS Design and management patterns Sample application details

Wingtip Tickets SaaS app demo

SaaS data management concerns Cost per tenant Scale - tenants, data volume, workload Tenant isolation - security, privacy, performance, lifetime mgt. Business continuity, disaster recovery Customization modularization, per-tenant schema variation

Alternative SaaS database tenancy patterns Standalone App Tenant C Tenant B Tenant A App Database per Tenant Sharded Multi-tenant Database App App Catalog Tenant DB Tenant Tenant Tenant A B C Catalog Tenant sA,B,C, D Tenant sE,F,J, K Tenan Tenan tL tM

Comparing the models, what we see Standalone App Database per tenant Sharded Multi-tenant Scale High 1-1000s Very high 1-100,000s Unlimited 1-1,000,000s Database cost–per tenant High (sized for peaks) Low (using pools) Lowest (small tenants) Tenant isolation Very High High Low (high for singletons) Performance monitoring/mgt. Per-tenant Aggregate per-tenant Aggregate, per-tenant for singletons only Restore single tenant Easy Easy Hard (easy for singletons) Disaster recovery (all tenants) Moderate Many components to recover Moderate Patterns address complexity at scale Easy (for multi-tenant dbs) Patterns address singleton complexity at scale Development complexity Low Low Medium (sharding) Operational complexity Medium-High Individually simple, Low-Medium Patterns address Low-High Individual tenant

demo Wingtip Tickets sample Standalone app Database per tenant Sharded multi-tenant

Microservices allows mixed storage/tenancy Microservice s Modularized UI, orchestrates service calls UI UI UI REST API Each service uses most appropriate language/stack Servic e Servic e Servic e Servic e Catalog Services are mapped to storage and tenancy models to optimize cost and/or ease of development and management Tenant Tenant D D Tenant Tenant C C Tenant Tenant B B Tenant Tenant A A Database per tenant Tenants E,F,J,K Tenant sA,B,C, D Sharded multi-tenant Shard map per service or logical database

Microservices allows mixed storage/tenancy Microservice s Modularized UI, orchestrates service calls UI UI UI REST API Each service can use most appropriate language/stack Servic e Servic e Servic e Servic e Catalog Services are mapped to storage and tenancy models to optimize cost and/or ease of development and management Tenant D Tenant C Tenant B Tenant A Database per tenant Shard map per service or logical database

Microservices allows mixed storage/tenancy Microservice s Modularized UI, orchestrates service calls UI UI UI REST API Each service uses most appropriate language/stack Servic e Servic e Servic e Servic e Catalog Services are mapped to storage and tenancy models to optimize cost and/or ease of development and management Tenants E,F,J,K Tenant sA,B,C, D Sharded multi-tenant Shard map per service or logical database

Microservices allows mixed storage/tenancy Microservice s Modularized UI, orchestrates service calls UI UI UI REST API Each service uses most appropriate language/stack Servic e Servic e Servic e Servic e Catalog Services are mapped to storage and tenancy models to optimize cost and/or ease of development and management Tenants Tenants Tenants Tenants E,F,J,K Tenant E,F,J,K Tenant E,F,J,K Tenant E,F,J,K Tenant sA,B,C, sA,B,C, sA,B,C, sA,B,C, D D D D Sharded multi-tenant Shard map per service or logical database

Microservices allows mixed storage/tenancy Microservice s Modularized UI, orchestrates service calls UI UI UI REST API Each service uses most appropriate language/stack Servic e Servic e Servic e Servic e Catalog Services are mapped to storage and tenancy models to optimize cost and/or ease of development and management Tenant D Tenant C Tenant Tenants B Tenant E,F,J,K Tenant A sA,B,C, D NoSQL SQL Shard map per service or logical database

Patterns compose into E2E SaaS scenario Tenant self service Azure Automation Schedule d Reactive Proactive Geo-replication, failover groups Geo-replicate dbs for fastest recovery Geo-restore, aliases Restore from georedundant backup Active/active Managed recovery of cross-replication tenants at scale Handle intense Reactiv isolated tenant e Proworkload active Geo-distributed Handle fluctuating BCDR tenant dbs aggregate workload Schedul ed Azure Automation ARM templates Monitoring and alerting Portal OMS Monitor app and data layer Load-balance Elastic Pools Manage performance Elastic Pools Scale databases Auto-provision databases Tenant self service Recover tenant data after tenant error SQL DW SQL DB ColumnStore Extract and Search Crosstenant Analytics bacpac, dbcopy Catalog tenants and databases Elastic Database Client Library, Shard Management schema Pre-provision databases to reduce tenant onboarding latency Connect app to tenant database , access control Elastic Azure Search Jobs Multitenant SaaS app ARM template, Provision database and schema Azure Active Directory Authentication PITR Browse tenants analyze tenant data PowerBI Query across tenant databases Manage schema change Elastic Query Elastic Jobs Manage schema versioning via catalog Manage extended tenant meta data

exploring the patterns

Provisioning tenants and connecting at runtime Apps Catalog Tenant Catalog Cust 1 Cust 2 Cust 3 Cust N Tenant Databases

Provisioning tenants and connecting at runtime Application Connection Tenant Onboarding 1. User connects to the app A. Venue signs up as new tenant 2. App uses key to get connection from catalog B. App provisions new tenant db Apps C. registers tenant key, db location in catalog Tenant bacpac Base TenantDB ARM / SQL Database 3. then connects to correct tenant database EDCL 4. On subsequent requests, uses database location cached in EDCL Catalog Tenant Catalog Cust 1 Cust 2 Cust 3 Cust 4 Cust N Tenant Databases EDCL Elastic Database Client Library .NET, Java

demo Management scripts Provisioning a single tenant

Pools are cost-effective for unpredictable workloads share resource s 9:0 9:15 9:30 9:45 10:0 20123:5 12:0 0:00 201 Tue Wed 201 Mon Thu Fri Sat 0 0 Sun 7 9 06 5 Hourly Daily Weekl y Yearly Elastic Pool Individually unpredictable Collectively stable and predictable Expensive, must provision each db for peaks Cost effective, scale pool up/down based on trends

Case study: Large accounting ISV Targets small/medium businesses Migrated an established desktop app to the cloud Client/server app migrated easily to a database-per- tenant model Initially used standalone databases, now using databases in elastic pools 150,000 tenant databases in 300 elastic pools Growing at 3-5K databases per month

Elastic pool usage example 385 dbs in 200 DTU pool, per-database max at 100 DTUs

Managing unpredictable tenant workloads Apps ARM / SQL Database Catalog pool2 pool1 Cust 4 Cust 1 Cust 2 Cust 3 Cust 10 Cust 11 Cust 12 Cust Cust 17 13 pool3 1. Provisioning service creates pool 2. pre-provisions 3. databases As tenants register they are assigned databases 4. Service pre-provisions next pool and databases 5. Pool is scaled to handle more load/databases 6. Databases are moved to load-balance pools 7. Databases can be moved out if they become very active

demo Performance experience with unpredictable SaaS workloads elastic pool vs multi-tenant db

Manage 1000s of databases as one Azure Portal T-SQL T-SQL CREATE TABLE Job CREATE INDEX SELECT INSERT* INTO FROM SELECT * FROM Apps Jobs, target groups, Job Account schedules, Catalog Tenant Catalog credentials Elastic Jobs Cust 1 Cust 2 Cust 3 Cust 4 T-SQL T-SQL T-SQL T-SQL T-SQL Tenant Databases Cust N To join preview of elastic jobs contact [email protected] Schema management at scale

Distributed query across tenant databases Query all tenants as if they are in a single database PowerBI Apps Query from any client Database locations are retrieved from catalog Catalog Tenant Catalog Cust 1 Cust 2 Cust 3 Cust 4 Tenant Databases Elastic Query External tables Adhoc Analytic project data from tenant dbs s Cust N Distributed query plan

Extract tenant data into an analytics DB or DW Unlock operational and application insights PowerBI Azure Portal T-SQL T-SQL CREATE TABLE Job CREATE INDEX SELECT INSERT* INTO FROM SELECT * FROM Apps Job Account Elastic Jobs Query from any client Catalog Tenant Catalog Cust 1 Cust 2 Cust 3 Cust 4 T-SQL T-SQL T-SQL T-SQL T-SQL Tenant Databases Cust N Adhoc Analytic s Tenant Analytic s SQL DB with ColumnSt ore or SQL DW

Multiple versions explore different tenancy Wingtip Tickets sample SaaS apps models Database-per-tenant Sharded multi-tenant Standalone app Microservices (planned) Each app deploys to Azure in under 5 mins All code, scripts on GitHub https://github.com/Microsoft/WingtipTicketsSaaS-DbPerTenant https://github.com/Microsoft/WingtipTicketsSaaS-MultiTenantDb https://github.com/Microsoft/WingtipTicketsSaaS-StandaloneApp Management tutorials online

Tenant provisioning and registration in catalog Wingtip Tickets sample SaaS app and scenarios patterns explored in the app and tutorials Connection to tenant database via catalog Monitoring and alerting Performance management (incl. scaling/balancing pools) Tenant database schema management Distributed query across tenants Cross-tenant analytics using PowerBI Restore single tenant to recover from tenant error DR using geo-restore (in progress) DR using geo-replication (in progress)

Wrap up and call to action You should now be equipped To choose the SaaS database model appropriate to your scenario To explore SaaS-specific patterns that can reduce development, management and resource costs, and timeto-market Takeaway PaaS features SaaS patterns make SQL DB a highly scalable, cost-efficient data platform for multi-tenant SaaS Install the samples, try the tutorials, send us feedback [email protected]

SaaS ISV survey https://aka.ms/saassurvey

Resources Blog post https://azure.microsoft.com/en-us/blog/saas-patterns-accelerate-saas-a pplication-development-on-sql-database/ GitHub repo https://github.com/microsoft/wingtipsaas Tutorials https://aka.ms/sqldbsaastutorial Getting started guide - installing and exploring the app https://docs.microsoft.com/en-us/azure/sql-database/sql-database-saastutorial

Thank You Learn more from Bill Gibson [email protected]