Identify Considerations for Relational Data on Azure
Relational fundamentals
The relational model stores data in tables of rows and columns, with a schema enforcing types and constraints. Know: primary key (unique row identifier), foreign key (enforces a relationship and referential integrity), unique, check, not null, and default constraints; views (saved queries); and indexes (clustered = physical row order; nonclustered = separate lookup structure that speeds reads but slows writes). Normalization removes duplication; denormalization trades duplication for fewer joins in reporting.
Choosing an Azure service
- Azure SQL Database - PaaS, fully managed, ideal for single databases or many databases sharing resources (elastic pools); serverless tier auto-pauses. No SQL Server Agent, no cross-database queries (without workarounds), no instance-level features.
- Azure SQL Managed Instance - PaaS with near-full SQL Server compatibility: VNet injection, SQL Agent, cross-database queries, instance-scoped features. Use it for "lift and shift with minimal changes".
- SQL Server on Azure Virtual Machines - IaaS; full OS and engine control, you patch and manage. Use when you need OS-level access or an unsupported version/feature.
- Open-source engines - Azure Database for MySQL, PostgreSQL (Flexible Server), and MariaDB as managed options.
Migration and traps
Use Azure Database Migration Service (with assessment tooling such as Data Migration Assistant) to move on-premises databases to Azure.
Traps: recommending SQL Database for a workload that requires SQL Server Agent jobs; assuming PaaS gives OS access; forgetting that managed services still require you to design indexes and tune queries.
Worked example: a legacy app needs cross-database joins, SQL Server Agent scheduling, and near-zero code changes, but the team does not want to patch the OS. Answer: Azure SQL Managed Instance - not SQL Database (missing features) and not a VM (needs patching).