Edit

Manage schema in a SaaS application using the database-per-tenant pattern with Azure SQL Database

  • Article

Applies to: Azure SQL Database

As a database application evolves, changes inevitably need to be made to the database schema or reference data. Database maintenance tasks are also needed periodically. Managing an application that uses the database per tenant pattern requires that you apply these changes or maintenance tasks across a fleet of tenant databases.

This tutorial explores two scenarios - deploying reference data updates for all tenants, and rebuilding an index on the table containing the reference data. The Elastic jobs feature is used to execute these actions on all tenant databases, and on the template database used to create new tenant databases.

In this tutorial you learn how to:

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

To complete this tutorial, make sure the following prerequisites are met:

Introduction to SaaS schema management patterns

The database per tenant pattern isolates tenant data effectively, but increases the number of databases to manage and maintain. Elastic Jobs facilitates administration and management of multiple databases. Jobs enable you to securely and reliably, run tasks (T-SQL scripts) against a group of databases. Jobs can deploy schema and common reference data changes across all tenant databases in an application. Elastic Jobs can also be used to maintain a template database used to create new tenants, ensuring it always has the latest schema and reference data.

screen

Elastic Jobs public preview

There's a new version of Elastic Jobs that is now an integrated feature of Azure SQL Database. This new version of Elastic Jobs is currently in public preview. This public preview currently supports using PowerShell to create a job agent, and T-SQL to create and manage jobs. See article on Elastic Database Jobs for more information.

Get the Wingtip Tickets SaaS database per tenant application scripts

The application source code and management scripts are available in the WingtipTicketsSaaS-DbPerTenant GitHub repo. Check out 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 you use PowerShell to create a job agent and its backing job agent database. The job agent database holds job definitions, job status, and history. Once the job agent and its database are 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 osagent on the catalog server. It then creates the job agent, using the database as a parameter.

Create a job to deploy new reference data to all tenants

In the Wingtip Tickets app, each tenant database includes a set of supported venue types. Each venue is of a specific venue type, which defines the kind of events that can be hosted, and determines the background image used in the app. For the application to support new kinds of events, this reference data must be updated and new venue types added. In this exercise, you deploy an update to all the tenant 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.

Now let's create a job to update the VenueTypes table in all the tenant databases to add the new venue types.

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

  1. In SSMS, connect to the catalog server: catalog-dpt-<user>.database.windows.net server
  2. In SSMS, open the file …\Learning Modules\Schema Management\DeployReferenceData.sql
  3. Modify the statement: SET @wtpUser = <user> and substitute the User value used when you deployed the Wingtip Tickets SaaS Database Per Tenant app
  4. Ensure you are connected to the jobagent database and press F5 to run the script

Observe the following elements in the DeployReferenceData.sql script:

  • sp_add_target_group creates the target group name DemoServerGroup.
  • sp_add_target_group_member is used to define the set of target databases. First the tenants1-dpt-<user> server is added. Adding the server as a target causes the databases in that server at the time of job execution to be included in the job. Then the basetenantdb database and the adhocreporting database (used in a later tutorial) are added as targets.
  • sp_add_job creates a job named 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 on all the target databases.

Once the script has completed, you can verify the reference data has been updated. In SSMS, browse to the contosoconcerthall database on the tenants1-dpt-<user> server and query the VenueTypes table. Check that Motorcycle Racing and Swimming Club are now present.

Create a job to manage the reference table index

This exercise uses a job to rebuild the index on the reference table primary key. This is a typical database maintenance operation that might be done after loading large amounts of data.

Create a job using the same jobs 'system' stored procedures.

  1. Open SSMS and connect to the catalog-dpt-<user>.database.windows.net server
  2. Open the file …\Learning Modules\Schema Management\OnlineReindex.sql
  3. Right-click, select Connection, and connect to the catalog-dpt-<user>.database.windows.net server, if not already connected
  4. Ensure you are connected to the jobagent database and press F5 to run the script

Observe the following elements 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.

Next steps

In this tutorial you learned how to:

  • Create a job agent to run across T-SQL jobs 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.

Additional resources