Provisioning if an often overlooked aspect when architecting a SaaS solution, but it’s one of the most important ones. How do you make sure new users and tenants can be automatically created and ready to use?

Recently, I worked on a provisioning API for Azure SQL Managed Instance and ran into some interesting quirks.

Database restores

When restoring databases, SQL Managed Instance will not provide any STATS or restore completion events to the user; this is because internally, the SQL Managed Instance has a background worker that does the actual restore - this is well documented by Brent Ozar on his blog. You can see this in action by restoring a large backup (say, >2GB) and then querying active restores:

-- Query active restores
SELECT query = a.text, start_time, percent_complete, eta = dateadd(second,estimated_completion_time/1000, getdate())
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a WHERE r.command IN ('RESTORE DATABASE', 'DROP DATABASE');

for example:

In short, SQL MI accepts a restore command and begins a restore to a database named after a random GUID. Once completed, it renames it to the user-requested database name.

If you need to map the physical database name to the logical (user-facing) database names, check the physical_database_name column value from sys.databases:

SELECT name, physical_database_name FROM sys.databases

Note that this value only becomes available after restore is complete as a result, detecting restore progress programmatically is impractical (theoretically possible if OK with throwing a bunch of string matching-kludges into the query above… but I’m not).

Detecting restoring completion

As a result of the physical vs logical DB name mappings, detecting database restore progress or completion is non-trivial and can be a blocker if you have downstream tasks to perform, e.g. running some T-SQL against your newly restored DB to prepare it for use.

When a restore is completed and the physical (GUID) database is renamed to the user-requested one, it appears to be online:

SELECT
    name,
    DATABASEPROPERTYEX(name,'Updatability') AS 'Updatability',
    DATABASEPROPERTYEX(name,'Status') AS 'Status'
FROM dbo.sysdatabases;

However attempting to query that database (either read or write) will fail with Unable to access database 'mydb' because it lacks a quorum of nodes for high availability. Try the operation again later. So what gives?

SQL MI’s Business Critical tier uses something similar to Always On to prevent data loss. Those availability groups are configured for you automatically - but while initial replication occurs and until quorum is established, the database is present on the master but not queriable!

You can use the following query to determine if a DB is still under replication, and therefore ready or not on the Business Critical tier:

-- Query synchronization state
SELECT sdb.database_id, sdb.name, sdb.physical_database_name, t.is_ready FROM sys.databases sdb
LEFT JOIN (
    SELECT r.database_id, is_ready = CASE WHEN COUNT(CASE WHEN r.synchronization_state = 2 THEN 1 ELSE NULL END) > 1 THEN 1 ELSE 0 END
    FROM sys.dm_hadr_database_replica_states r
    GROUP BY r.database_id
) t ON t.database_id = sdb.database_id
WHERE name LIKE 'Works_tid%'

Once is_ready=1 your database is good to go, and you can safely execute additional queries against it. Note that this is during normal operation - if you are doing something fancy like resizing your instance, the ready detection not work as expected.

One last word of caution regarding SqlConnection and SMO

This is not specific to SQL Managed Instance, but a word of caution for those automating query execution with the C# SqlConnection class from System.Data.SqlClient: it does not necessarily open a new physical connection (see connection pooling for details) and it can only support one command at a time.

Attempting to re-use a SqlConnection (e.g. via multiple RestoreAsync() in SMO) before it is ready can result in very bizarre behavior (the SqlConnection closes itself and the active thread may stop executing). Best practice is:

1. Avoid SMO altogether in favor of T-SQL, which is what SMO produces anyways
2. Create a new SqlConnection instance for every T-SQL command you wish to execute, and close the connection after. Wrapping the creation in a using statement will do this for you.