Edit

Manage schema in a SaaS application that uses sharded multi-tenant databases

  • Article

Applies to: Azure SQL Database

This tutorial examines the challenges in maintaining a fleet of databases in a Software as a Service (SaaS) application. Solutions are demonstrated for fanning out schema changes across the fleet of databases.

Like any application, the Wingtip Tickets SaaS app will evolve over time, and will require changes to the database. Changes may impact schema or reference data, or apply database maintenance tasks. With a SaaS application using a database per tenant pattern, changes must be coordinated across a potentially massive fleet of tenant databases. In addition, you must incorporate these changes into the database provisioning process to ensure they are included in new databases as they are created.

Two scenarios

This tutorial explores the following two scenarios:

  • Deploy reference data updates for all tenants.
  • Rebuild an index on the table that contains the reference data.

The Elastic Jobs feature of Azure SQL Database is used to execute these operations across tenant databases. The jobs also operate on the 'template' tenant database. In the Wingtip Tickets sample app, this template database is copied to provision a new tenant database.

In this tutorial you learn how to:

  • Create a job agent.
  • Execute a T-SQL query on multiple tenant databases.
  • Update reference data in all tenant databases.
  • Create an index on a table in all tenant databases.

Prerequisites

Introduction to SaaS schema management patterns

The sharded multi-tenant database model used in this sample enables a tenants database to contain one or more tenants. This sample explores the potential to use a mix of a many-tenant and one-tenant databases, enabling a hybrid tenant management model. Managing changes to these databases can be complicated. Elastic Jobs facilitates administration and management of large numbers of database. Jobs enable you to securely and reliably run Transact-SQL scripts as tasks, against a group of tenant databases. The tasks are independent of user interaction or input. This method can be used to deploy changes to schema or to common reference data, across all tenants in an application. Elastic Jobs can also be used to maintain a golden template copy of the database. The template is used to create new tenants, always ensuring the latest schema and reference data are in use.

screen

Elastic jobs

In 2024, elastic jobs was released as a generally available product with new features. An integrated feature of Azure SQL Database, see elastic database jobs.

Get the Wingtip Tickets SaaS Multi-tenant Database application source code and scripts

The Wingtip Tickets SaaS Multi-tenant Database scripts and application source code are available in the WingtipTicketsSaaS-MultitenantDB repository on GitHub. See the general guidance for steps to download and unblock the Wingtip Tickets SaaS scripts.

Create a job agent database and new job agent

This tutorial requires that you use PowerShell to create the job agent database and job agent. Like the msdb database used by SQL Agent, a job agent uses a database in Azure SQL Database to store job definitions, job status, and history. After the job agent is created, you can create and monitor jobs immediately.

  1. In PowerShell ISE, open ...\Learning Modules\Schema Management\Demo-SchemaManagement.ps1.
  2. Press F5 to run the script.

The Demo-SchemaManagement.ps1 script calls the Deploy-SchemaManagement.ps1 script to create a database named jobagent on the catalog server. The script then creates the job agent, passing the jobagent database as a parameter.

Create a job to deploy new reference data to all tenants

Prepare

Each tenant's database includes a set of venue types in the VenueTypes table. Each venue type defines the kind of events that can be hosted at a venue. These venue types correspond to the background images you see in the tenant events app. In this exercise, you deploy an update to all databases to add two additional venue types: Motorcycle Racing and Swimming Club.

First, review the venue types included in each tenant database. Connect to one of the tenant databases in SQL Server Management Studio (SSMS) and inspect the VenueTypes table. You can also query this table in the Query editor in the Azure portal, accessed from the database page.

  1. Open SSMS and connect to the tenant server: tenants1-dpt-<user>.database.windows.net.
  2. To confirm that Motorcycle Racing and Swimming Club are not currently included, browse to the contosoconcerthall database on the tenants1-dpt-<user> server and query the VenueTypes table.

Steps

Now you create a job to update the VenueTypes table in each tenants database, by adding the two new venue types.

To create a new job, you use the set of jobs system stored procedures that were created in the jobagent database. The stored procedures were created when the job agent was created.

  1. In SSMS, connect to the tenant server: tenants1-mt-<user>.database.windows.net.

  2. Browse to the tenants1 database.

  3. Query the VenueTypes table to confirm that Motorcycle Racing and Swimming Club are not yet in the results list.

  4. Connect to the catalog server, which is catalog-mt-<user>.database.windows.net.

  5. Connect to the jobagent database in the catalog server.

  6. In SSMS, open the file ...\Learning Modules\Schema Management\DeployReferenceData.sql.

  7. Modify the statement: set @User = <user> and substitute the User value used when you deployed the Wingtip Tickets SaaS Multi-tenant Database application.

  8. Press F5 to run the script.

Observe

Observe the following items in the DeployReferenceData.sql script:

  • sp_add_target_group creates the target group name DemoServerGroup, and adds target members to the group.

  • sp_add_target_group_member adds the following items:

    • A server target member type.
      • This is the tenants1-mt-<user> server that contains the tenants databases.
      • Including the server includes the tenant databases that exist at the time the job executes.
    • A database target member type for the template database (basetenantdb) that resides on catalog-mt-<user> server,
    • A database target member type to include the adhocreporting database that is used in a later tutorial.

  • sp_add_job creates a job called Reference Data Deployment.

  • sp_add_jobstep creates the job step containing T-SQL command text to update the reference table, VenueTypes.

  • The remaining views in the script display the existence of the objects and monitor job execution. Use these queries to review the status value in the lifecycle column to determine when the job has finished. The job updates the tenants database, and updates the two additional databases that contain the reference table.

In SSMS, browse to the tenant database on the tenants1-mt-<user> server. Query the VenueTypes table to confirm that Motorcycle Racing and Swimming Club are now added to the table. The total count of venue types should have increased by two.

Create a job to manage the reference table index

This exercise creates a job to rebuild the index on the reference table primary key on all the tenant databases. An index rebuild is a typical database management operation that an administrator might run after loading a large amount of data load, to improve performance.

  1. In SSMS, connect to jobagent database in catalog-mt-<user>.database.windows.net server.

  2. In SSMS, open ...\Learning Modules\Schema Management\OnlineReindex.sql.

  3. Press F5 to run the script.

Observe

Observe the following items in the OnlineReindex.sql script:

  • sp_add_job creates a new job called Online Reindex PK__VenueTyp__265E44FD7FD4C885.

  • sp_add_jobstep creates the job step containing T-SQL command text to update the index.

  • The remaining views in the script monitor job execution. Use these queries to review the status value in the lifecycle column to determine when the job has successfully finished on all target group members.

Additional resources

Next steps

In this tutorial you learned how to:

  • Create a job agent to run T-SQL jobs across multiple databases
  • Update reference data in all tenant databases
  • Create an index on a table in all tenant databases

Next, try the Ad hoc reporting tutorial to explore running distributed queries across tenant databases.