Memory hook: Compatibility determines the destination; downtime and rollback determine the migration path.
Must remember
- Compare SQL Database, Managed Instance and SQL Server on VMs using required instance features, OS access, engine compatibility, networking, maintenance and cost. Arc extends supported hybrid management; Fabric SQL capabilities have their own scope and maturity.
- Select service tier, compute, storage and purchasing/scaling model from workload evidence. Elastic pools share resources across suitable databases; serverless/autoscale options have workload and tier constraints. Table partitioning manages data/access boundaries; sharding distributes data across databases with application/routing complexity.
- Compression can reduce I/O/storage at CPU cost. Partition elimination depends on query predicates and partition design; merely partitioning a table does not accelerate every query. Check index alignment and maintenance consequences.
- Inventory schema, features, jobs, logins, dependencies, collation and data size. Assess compatibility before migration. Offline migration accepts a write outage; online migration uses supported ongoing synchronisation and a final cutover.
- Reconcile data, test application queries and permissions, plan DNS/connection changes and preserve a rollback strategy. Managed Instance copy/move features have prerequisites; moving a database does not automatically transfer every server-level dependency.
- Use reviewed ARM/Bicep, CLI/PowerShell or database-deployment tooling. Patch IaaS/hybrid engines under a maintenance plan. Inspect migration/deployment logs for the original error, including quota, storage, network and unsupported features.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Application requires unsupported managed-instance features | Evaluate SQL Server on a VM. |
| Many databases have non-coincident demand | Evaluate an elastic pool. |
| Very small cutover window | Assess supported online migration and replication lag. |
Traps
- Database copy is not complete application migration.
- Partitioning is not a universal performance fix.
- A target accepting connections does not prove compatibility.
Active recall
1. What must an online migration still plan?
Final synchronisation, cutover, validation and rollback.
2. Why inventory SQL Agent jobs?
They may be server-level dependencies not automatically migrated with the database.
3. What is the compression trade-off?
Less storage/I/O can require more CPU.
4. Why test real application queries?
Syntax compatibility alone does not prove performance or behavioural compatibility.
5. When is sharding justified?
When distribution requirements exceed a simpler design and its routing/operational costs are acceptable.