Lifting a Multi-Database SQL Server Estate Without a Rewrite
Fabrikam Insurance runs SQL Server 2016 on-premises: 4 TB across 60 databases. The claims application joins three databases in a single query. Twelve SQL Server Agent jobs run nightly ETL, and two CLR assemblies implement address matching. A linked server queries an on-premises Oracle claims archive that will stay on-premises for two more years. A three-person DBA team spends most of the week applying patches and tuning backups, and leadership wants that effort measurably reduced. Migration downtime must be under 30 minutes. Fourteen of the 60 databases have had no connections in eleven months but must be retained for seven years for audit, and nobody can yet say whether the application's cross-database queries touch them.
1. Choose between Azure SQL Database, Azure SQL Managed Instance, and SQL Server on an Azure VM. Justify the choice by eliminating the other two against the stated constraints.
Read the worked answer
Eliminate Azure SQL Database first. It fails on at least three independent constraints: the three-database join (cross-database queries are not a general capability there; elastic query is not a drop-in replacement), SQL Server Agent jobs (not available - you would replace them with Elastic Jobs and rewrite the schedules and history), and CLR assemblies (not supported). Any one of these forces an application change, and the brief says minimal re-architecture.
Eliminate SQL Server on an Azure VM second. It supports every feature - which is precisely the problem. The team still patches the OS and the database engine, still configures backups and high availability, still owns the upgrade path. That is the same workload they have today, relocated and now billed hourly. It meets 'runs unchanged' but fails 'reduce operational effort'.
Choose Azure SQL Managed Instance. It provides the near-complete SQL Server surface area: cross-database queries between databases on the same instance, SQL Server Agent, CLR, and linked servers. The platform takes over OS and engine patching, built-in high availability, and automated backups with point-in-time restore.
Two consequences the plan must carry: (1) the three databases joined by the claims app must live on the same instance, which sets a floor on instance size and cost; (2) the linked server to on-premises Oracle requires private network connectivity (VPN or ExpressRoute) plus DNS and firewall work - a network project, not a database project.
2. The fourteen dormant databases must be retained for seven years but are essentially never queried. How would you handle them cost-effectively, and what single fact must be confirmed first?
Read the worked answer
Confirm first: does the claims application or any Agent job reference them? A cross-database query can reach a database the team believes is dead. If they are referenced, they must stay on the same Managed Instance and there is no cost optimisation beyond storage - moving them out breaks the application. This is a query-log and dependency review, not a guess.
If they are self-contained, the audit requirement is retain and be able to produce, not query online, so pay for storage, not compute:
- Cheapest and usually correct: take a verified-restorable backup of each database into Azure Blob storage with immutability/long-term retention, record the restore procedure and the expected restore time, then drop the databases. Storage cost is a fraction of an always-on instance. The risk to accept explicitly is restore latency if an auditor wants a query that afternoon.
- Middle ground if same-day query access is required: Azure SQL Database serverless, which pauses when idle so you pay mainly for storage. Only viable if the databases have no cross-database dependencies, since they would leave the instance.
- Most expensive: keep them on the Managed Instance at its smallest size. Do this only if options 1 and 2 are ruled out.
Judgement point: get the audit retention requirement written down in terms of access time, not just retention period. That single sentence decides between a few dollars and a few thousand dollars a month.
3. Leadership believes 'managed' means the DBA team's operational work disappears. List what the platform now handles and what the team still owns.
Read the worked answer
Handled by the platform: OS and engine patching, built-in high availability/failover, automated backups and point-in-time restore, underlying storage and hardware failures.
Still owned by the team:
- Schema, index, and query design; statistics quality; the actual reason queries are slow.
- The permission and identity model, and application compatibility testing for every engine change.
- Backup retention policy configuration and, critically, restore testing - an untested backup is a hope, and the audit requirement makes this a real deliverable.
- Network plumbing specific to Managed Instance: subnet sizing, NSGs, route tables, and the ExpressRoute/VPN path to the on-premises Oracle server.
- Cost management: instance size, storage tier, reserved capacity, and the decision above about the fourteen dormant databases.
So the answer to leadership is honest: patching and backup plumbing genuinely disappear - that is the bulk of the weekly toil - but design, security, and cost tuning remain, and they are the parts that need a skilled DBA.