Sunday, 27 June 2021

Azure Elastic Pools - DB creation, schema alignment for SaaS

Overview:  Provisioning and seeding databases is pretty straight forward however ensuring multi-tenant databases schemas are aligned is a little tricky.  Azure has the Elastic Job Agent service that has been in preview for many years and is a good service.  It is extremely useful for updating multiple database instances en-masse.

There are 2 common scenarios

1. Provision a new database for a client.  This involves 1) creating the SQL database instance either on a server or an elastic pool. 2) Update the new database with the appropriate schema 3) insert any seed data into the database.

2. Update groups or all database instances to a specific schema i.e. change schema for all clients.

Scenario 1 needs to apply the schema to multiple databases and Elastic Job is perfect for this.  Scenrio 2 also needs to update a single database and can use the same T-SQL code to ensure new dbs have the correct schema applied.

Elastic Job Agent:

A job can be created that uses T-SQL (from source control) to ensure the schema of target databases are updated to a specific schema version.  A dedicate database is used to monitor and manage the jobs.  I call this the "agent-elasticdb" database.  

Note:  The Target can be all databases on a server, elastic pools, groups of name database instances or a single database.

If a job has multiple target database's to update, the updates are run in parallel.    


Post a Comment