Feature map
| Feature | Azure SQL MI | Azure SQL Database | Replacement / approach |
|---|---|---|---|
| SQL Agent jobs | ✅ | ❌ | Elastic jobs (SQL DB); keep Agent on MI |
| Cross-database queries (same server) | ✅ | ❌ | Merge DBs, elastic query |
| Linked servers | ✅ (SQL Server and some others) | ❌ | Elastic query / ADF / app |
| CLR assemblies | ✅ (from binary, SAFE/EXTERNAL_ACCESS limits) | ❌ | Rewrite in T-SQL or app |
| Database Mail | ✅ | ❌ | Logic Apps / Azure Communication Services |
| Service Broker | ✅ within the instance | ❌ | Service Bus |
| FILESTREAM / FileTable | ❌ | ❌ | Blob Storage + path column |
xp_cmdshell, OS access | ❌ | ❌ | Azure Automation / Functions |
| Windows authentication | ✅ Entra ID Kerberos (Windows auth for MI) | ❌ | Entra ID users and groups |
| Distributed transactions (MSDTC) | ✅ between MIs (server trust group) | Elastic transactions (limited) | Redesign if cross-platform |
Trace flags, sp_configure | Limited | ❌ | Database-scoped configuration |
Agent jobs → Elastic jobs (Azure SQL Database)
-- in the job database
EXEC jobs.sp_add_target_group 'SalesDBs';
EXEC jobs.sp_add_target_group_member 'SalesDBs', @target_type = 'SqlDatabase',
@server_name = 'sqlsrv-prod.database.windows.net', @database_name = 'Sales';
EXEC jobs.sp_add_job @job_name = 'NightlyPurge', @description = 'Purge old rows';
EXEC jobs.sp_add_jobstep @job_name = 'NightlyPurge',
@command = N'DELETE FROM dbo.AuditLog WHERE CreatedAt < DATEADD(day,-90,SYSUTCDATETIME());',
@target_group_name = 'SalesDBs';
EXEC jobs.sp_update_job @job_name = 'NightlyPurge', @enabled = 1,
@schedule_interval_type = 'Days', @schedule_interval_count = 1;
Elastic jobs can authenticate to targets with a user-assigned managed identity.
Cross-database queries → elastic query
CREATE DATABASE SCOPED CREDENTIAL ElasticCred WITH IDENTITY = 'reader', SECRET = '...';
CREATE EXTERNAL DATA SOURCE HRDb WITH (TYPE = RDBMS, LOCATION = 'sqlsrv-prod.database.windows.net',
DATABASE_NAME = 'HR', CREDENTIAL = ElasticCred);
CREATE EXTERNAL TABLE dbo.Employees (EmployeeId int, Name nvarchar(100))
WITH (DATA_SOURCE = HRDb);
Elastic query is fine for occasional lookups; for heavy joins, merge the databases or choose MI.
Linked servers on MI
EXEC sp_addlinkedserver @server = N'ONPREM_ERP', @srvproduct = N'', @provider = N'MSOLEDBSQL',
@datasrc = N'erp-sql.corp.local';
EXEC sp_addlinkedsrvlogin @rmtsrvname = N'ONPREM_ERP', @useself = N'False',
@rmtuser = N'link_reader', @rmtpassword = N'...';
MI must reach the target over the network (VNet, VPN/ExpressRoute) and resolve its name.
FILESTREAM → Blob Storage
Store files in Blob Storage, keep the blob URL and metadata in a table, and let the app read/write blobs with managed identity. Migrate existing FILESTREAM data with a one-off script or ADF.
Decision rule
Count the gaps per database. One or two fixable gaps → Azure SQL Database is realistic. Several instance features → Managed Instance. OS-level dependencies → SQL Server on a VM.
Tip
Search the code base, not only the database. Many cross-database references live in application SQL strings, SSIS packages and report queries that database assessment tools never see.