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.
