If you run workloads on both platforms, you already know Azure SQL vs SQL Server schema differences cause more migration surprises than most teams expect. Azure SQL Database and Azure SQL Managed Instance share the SQL Server engine's T-SQL surface, but they are not drop-in replacements at the schema level — and a script that deploys cleanly on-premises can fail, or silently behave differently, once it hits an Azure SQL Database.
Collation defaults diverge
On-premises SQL Server typically inherits its default collation from the instance, which in turn was set at install time — often SQL_Latin1_General_CP1_CI_AS in older environments, but frequently customized. New Azure SQL databases default to the same collation unless the server-level collation is overridden at creation. The gotcha isn't the default itself, it's inconsistency: teams provisioning Azure SQL databases through different pipelines (ARM templates, Terraform, portal clicks) end up with mismatched collations across environments, which then breaks cross-database joins and comparison operations that assume a shared collation.
Cross-database queries and three-part names
Classic SQL Server schemas lean on three-part naming (OtherDB.dbo.Table) and cross-database joins within the same instance. Azure SQL Database does not support this — each database is an isolated unit, and cross-database access requires elastic query, external tables, or Azure SQL Managed Instance (which restores cross-database querying within an instance). Any schema comparison between an on-prem source and an Azure SQL Database target needs to flag views or stored procedures that reference external databases, because they cannot be migrated as-is.
Feature parity gaps that show up as schema errors
- Instance-level objects — SQL Server Agent jobs, linked servers, and instance-level triggers do not exist in Azure SQL Database. Managed Instance restores Agent and some instance features, but Database still doesn't have them.
- Filegroups — Azure SQL Database supports a subset of filegroup functionality compared to on-prem; scripts that place tables on explicit secondary filegroups need review.
- CLR and extended stored procedures — server-level CLR assemblies and most extended stored procedures aren't available in Azure SQL Database, though Managed Instance has broader (not complete) support.
- Full-text search and Service Broker — supported differently across the two Azure SQL tiers, and not identical to on-prem behavior.
None of these show up as syntax errors during a plain schema dump — they show up as deployment failures or runtime errors, which is why a structural schema comparison catches more than a raw DDL diff.
Temporal tables, dynamic data masking, and Always Encrypted
Azure SQL Database was often the first place Microsoft shipped newer T-SQL features — system-versioned temporal tables, dynamic data masking, and Always Encrypted all landed in Azure SQL before broad on-prem availability, and version-gating on older on-prem instances means some still lack them entirely. If your target schema uses these constructs and your source is an older on-prem SQL Server, a migration tool needs to know which SQL Server version supports what — not just whether the two platforms share a dialect name.
Index and constraint behavior at scale
Functionally, indexes, primary keys, foreign keys, and check constraints behave the same on both platforms — the DDL syntax is identical. Where things diverge is scale and tier limits: Azure SQL Database enforces per-tier limits on max database size and DTU/vCore-based resource limits that can make an index strategy built for a large on-prem box infeasible on a lower service tier. A schema diff tool that only checks DDL correctness won't catch this — it's an operational constraint, not a syntax one, and worth reviewing manually before you commit to a target tier.
Comparing schemas across both platforms
FoxSchema treats SQL Server and Azure SQL as related but distinct dialects, so a comparison between them surfaces exactly this class of gap: objects that parse fine as T-SQL but rely on a feature unavailable on the target tier. The readiness panel flags tables, columns, keys, and indexes that translate cleanly, and calls out views, functions, and procedures that need manual review before you generate migration SQL — rather than after a failed deployment. You can run the comparison from the self-hosted web app or from the fox compare CLI as part of a deploy pipeline. For the general workflow, see our guide on comparing two database schemas and generating migration SQL.
Ready to see where your Azure SQL and on-prem SQL Server schemas actually diverge? Self-host FoxSchema or check the install guide to get started.
