Moving to Azure SQL Database: The Features You Give Up

The tables migrate successfully, then the first scheduled task reveals a missing instance feature. Moving to Azure SQL Database changes more than the location of data files. Inventory the surrounding dependencies before deciding that a successful table copy completes the move.

Moving to Azure SQL Database Starts With the Target

Azure SQL Database and Azure SQL Managed Instance are different deployment targets. A logical server for Azure SQL Database is not an ordinary SQL Server instance under your control. Features supported by Managed Instance do not automatically exist in Database.

The database service supports a broad T-SQL surface while managing its underlying infrastructure. Instance-level operating-system and storage dependencies therefore require special attention. A database compatibility level does not restore missing instance capabilities.

I inventory application behavior before judging migration readiness from schema scripts. I also ask who owns each scheduled operation and external dependency. A feature used once a month can still stop the business after an otherwise quiet cutover.

Keep this assessment separate from changing the source database. The queries below read metadata on the existing SQL Server. Run them with enough metadata visibility to see the relevant objects, and record any visibility limitations.

Replace Agent Scheduling With a Defined Job Design

Azure SQL Database does not include SQL Server Agent or its msdb job infrastructure. Elastic jobs can schedule T-SQL operations against supported database targets. They need their own agent, target definitions, authentication, and monitoring setup.

An existing job also can contain operating-system steps, application calls, or instance administration. Those steps are not automatically portable to elastic jobs. Separate the database command from the surrounding orchestration and assign an execution environment for each part.

SELECT j.name AS JobName, j.enabled,
       s.step_id, s.step_name, s.subsystem, s.database_name, s.command
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.sysjobsteps AS s ON s.job_id = j.job_id
ORDER BY j.name, s.step_id;

Inspect schedules, retries, failure notifications, and credentials alongside the step text. A stored procedure can migrate while the job that calls it disappears. Review jobs executing in master or another database too, because they can operate on the migration database indirectly.

Elastic jobs are not a direct import of every Agent subsystem. For T-SQL tasks, prototype equivalent retry and outcome behavior against a test target. For other steps, keep them in an approved external execution process with explicit database permissions.

Replace Cross-Database Assumptions Deliberately

Ordinary three-part references to another user database do not work like same-instance SQL Server references. Supported references to the current database and tempdb have different rules. Four-part linked-server queries are not available in Azure SQL Database.

Elastic query provides supported read-only access to remote SQL data through external-table arrangements. It remains a feature with documented preview and capability limits. It does not supply unrestricted cross-database writes or replicate every linked-server behavior.

SELECT OBJECT_SCHEMA_NAME(referencing_id) AS SourceSchema,
       OBJECT_NAME(referencing_id) AS SourceObject,
       referenced_server_name, referenced_database_name,
       referenced_schema_name, referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referenced_database_name IS NOT NULL
   OR referenced_server_name IS NOT NULL;
SELECT name, base_object_name
FROM sys.synonyms
ORDER BY name;

Static dependencies do not expose every dynamically built name. Synonyms and application-generated SQL also need review. Trace representative business operations and scheduled tasks before calling the dependency inventory complete.

Choose between consolidating tightly related tables, moving coordination into the application, or maintaining a deliberate data-copy boundary. Decide how writes and transactions cross that boundary. A remote read alternative does not settle write consistency for you.

Moving to Azure SQL Database Without CLR or FILESTREAM

Custom CLR integration is unavailable in Azure SQL Database. Move required custom code into the application or replace it with supported T-SQL functionality. Managed Instance supports CLR with its own restrictions, but that is a different target decision.

FILESTREAM and FileTable are also unavailable. For suitable small binary data, ordinary varbinary storage remains an option. Larger file-oriented workloads need an intentional external storage and application-access design, with metadata and access rules kept consistent.

SELECT name, permission_set_desc
FROM sys.assemblies
WHERE is_user_defined = 1;
SELECT OBJECT_SCHEMA_NAME(c.object_id) AS TableSchema,
       OBJECT_NAME(c.object_id) AS TableName, c.name AS ColumnName
FROM sys.columns AS c
WHERE c.is_filestream = 1;
SELECT SCHEMA_NAME(schema_id) AS TableSchema, name AS TableName
FROM sys.tables
WHERE is_filetable = 1;

A deployed assembly is evidence for review, not proof that every application still uses it. Map its functions, procedures, types, and callers before removing it. Conversely, an empty assembly result does not rule out equivalent external code called by jobs.

For file storage, test retrieval, permissions, deletion, and backup consistency after redesign. A pointer row can survive while its external file disappears. Moving to Azure SQL Database should include that relationship in the recovery contract.

Rework Server Settings and Linked Servers

You do not control server memory, worker threads, file placement, or service startup through ordinary SQL Server instance settings. Use the service's supported compute configuration and database scoped settings. Review a setting's exact applicability rather than translating every sp_configure command literally.

Linked servers are unavailable in this target. Replace remote lookups with supported elastic query, application-mediated access, or staged data where appropriate. Each option has a different latency, security, and consistency contract.

SELECT name, product, provider, data_source
FROM sys.servers
WHERE is_linked = 1;
SELECT name, value_in_use
FROM sys.configurations
WHERE name IN
(N'clr enabled', N'xp_cmdshell', N'max server memory (MB)', N'cost threshold for parallelism');
SELECT OBJECT_SCHEMA_NAME(object_id) AS ModuleSchema,
       OBJECT_NAME(object_id) AS ModuleName
FROM sys.sql_modules
WHERE definition LIKE N'%sp_configure%'
   OR definition LIKE N'%xp_cmdshell%'
   OR definition LIKE N'%OPENQUERY%';

The text search supplies candidates, including references inside comments. Encrypted modules and application code require separate inspection. Do not label the result a complete portability scan or execute discovered commands during assessment.

Replace login and authorization assumptions too. Contained database users and supported Microsoft Entra authentication differ from traditional Windows instance authentication. Test the deployed application identity rather than relying on an administrator's successful connection.

Replace File-Based Backup Operations With Recovery Policy

Azure SQL Database manages automated backups and point-in-time recovery. You do not schedule native BACKUP DATABASE files or choose its physical backup chain with ordinary RESTORE statements. You still choose supported retention, redundancy, and recovery arrangements.

Long-term retention addresses older recovery points outside the routine point-in-time window. Configure and inspect it separately from short-term retention. A policy must exist before the backups you need to retain are selected.

An exported BACPAC is a logical schema-and-data package, not a native transaction-log recovery chain. Do not present it as equivalent point-in-time protection. Validate the actual recovery method required by your recovery objectives.

Test restoration into a separate database and verify application access after recovery. Record recovery permissions, naming, connectivity, and retained backup availability. A managed backup service does not make your application's restoration procedure self-explanatory.

Finish Moving to Azure SQL Database With Named Owners

Turn every discovered dependency into a decision, an owner, and a verification step. Include infrequent jobs and failure-handling paths. Test realistic reads, writes, reports, and recovery operations before approving the migration design.

I review the feature inventory with both application and operations owners. I also keep unresolved dependencies visible until their replacement behavior is tested. A migration checklist should not turn red items green merely because the data arrived.

Which instance feature does your application quietly assume will follow the database? Find that assumption before cutover. Moving to Azure SQL Database succeeds when the entire operating contract fits the target's supported boundaries.

Related reading on this blog: Azure SQL Database or Managed Instance and Azure SQL Hyperscale: When the Database Outgrows One Server.

A migrated database is not a migrated application, it is one component of a verified operating design.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.