Microsoft Azure Data Platform Engineering, Migration & Managed Operations

Serverless compute with auto-pause suits intermittent databases; Business Critical suits latency-sensitive OLTP; Hyperscale suits large or fast-growing databases and read scale-out through named replicas. For SQL Server compatibility at instance scope, Azure SQL Managed Instance keeps Agent jobs, cross-database queries and CLR. Engine-level depth comes from our SQL Server support practice.

T-SQL · Query Store regression finder (Azure SQL Database and Managed Instance)

-- Query Store: queries whose average duration in the last 24 h regressed against the prior 7 days
WITH w AS (
    SELECT p.query_id,
           CASE WHEN i.start_time >= DATEADD(HOUR, -24, SYSUTCDATETIME()) THEN 'recent' ELSE 'baseline' END AS win,
           rs.avg_duration * rs.count_executions AS dur_us,          -- avg_duration is in microseconds
           rs.count_executions
    FROM sys.query_store_runtime_stats          AS rs
    JOIN sys.query_store_runtime_stats_interval AS i ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
    JOIN sys.query_store_plan                   AS p ON p.plan_id = rs.plan_id
    WHERE i.start_time >= DATEADD(DAY, -8, SYSUTCDATETIME())
),
agg AS (
    SELECT query_id,
           SUM(CASE WHEN win = 'recent'   THEN dur_us END) / NULLIF(SUM(CASE WHEN win = 'recent'   THEN count_executions END), 0) AS recent_us,
           SUM(CASE WHEN win = 'baseline' THEN dur_us END) / NULLIF(SUM(CASE WHEN win = 'baseline' THEN count_executions END), 0) AS base_us,
           SUM(CASE WHEN win = 'recent'   THEN count_executions END) AS recent_execs
    FROM w
    GROUP BY query_id
)
SELECT TOP (20)
       a.query_id,
       CAST(a.base_us   / 1000.0 AS DECIMAL(12,2)) AS baseline_ms,
       CAST(a.recent_us / 1000.0 AS DECIMAL(12,2)) AS recent_ms,
       a.recent_execs,
       LEFT(qt.query_sql_text, 120)                AS query_head
FROM agg AS a
JOIN sys.query_store_query      AS q  ON q.query_id = a.query_id
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
WHERE a.recent_us > 2 * a.base_us
  AND a.recent_execs > 100
ORDER BY (a.recent_us - a.base_us) * a.recent_execs DESC;

T-SQL · cutover gate: verify synchronization and empty queues on the source

-- 1. Verify on the SQL Server source: the link's database replicas are synchronized and queues are empty
SELECT ag.name                         AS availability_group,
       ag.is_distributed,
       DB_NAME(drs.database_id)        AS database_name,
       drs.synchronization_state_desc,
       drs.log_send_queue_size         AS log_send_queue_kb,
       drs.redo_queue_size             AS redo_queue_kb,
       drs.last_commit_time
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_groups             AS ag ON ag.group_id = drs.group_id
WHERE drs.is_local = 0;

Oracle, MySQL and PostgreSQL sources follow the same principle with Azure Database Migration Service in online mode: full load, change capture, row-count and checksum reconciliation, then a gate. We favour incremental cutovers over big-bang weekends in every Azure data platform engineering plan.

PowerShell · confirmation gate, planned failover and validation

# 2. Confirmation gate, then planned failover of the Managed Instance link (Az.Sql module)
$rg   = $env:MI_RESOURCE_GROUP;  $mi = $env:MI_NAME;  $link = $env:MI_LINK_NAME

$answer = Read-Host "Writes stopped and both queues at 0 for $link? Type the link name to fail over"
if ($answer -ne $link) { Write-Host "Aborted. No change made."; exit 1 }

Start-AzSqlInstanceLinkFailover -ResourceGroupName $rg -InstanceName $mi -Name $link -FailoverType Planned

# 3. Validate: the managed instance is now primary for the link; then repoint connection strings
Get-AzSqlInstanceLink -ResourceGroupName $rg -InstanceName $mi -Name $link | Format-List *