Design data storage solutions practice questions

Some SQL Server features exist only when you run SQL Server yourself on an Azure VM.

FILESTREAM isn't supported by Azure SQL Database or Azure SQL Managed Instance, and neither runs SSRS as a service (Microsoft points to Power BI paginated reports, or SSRS on a VM). With both a no-schema-change and a no-reporting-change requirement, the only target that keeps FILESTREAM and SSRS is SQL Server on an Azure virtual machine. Managed Instance is the usual tempting answer because it is the most compatible PaaS option, but it misses both features.

1. Spot the features PaaS doesn't offer: FILESTREAM and SSRS.
2. Check whether the stem allows changing them: it doesn't.
3. Choose IaaS, SQL Server on an Azure VM, as the only compatible target.

Exam tip: FILESTREAM or SSRS that must stay = SQL Server on an Azure VM.

Managed Instance's linked servers are limited to SQL Server and Azure SQL targets.

Least administrative effort usually points to Azure SQL Managed Instance, and it does support SQL Server Agent. But Managed Instance supports linked servers only to SQL Server and Azure SQL Database targets; it can't host the Oracle provider, so the hourly Oracle query through the linked server breaks. Replacing it with Data Factory or rewriting jobs violates the stated requirements. SQL Server on an Azure VM is therefore the lowest-effort design that still meets them all.

1. Note each hard requirement: Oracle linked server unchanged, Agent jobs unchanged.
2. Check Managed Instance: Agent is supported, but linked servers can't target Oracle.
3. Discard options that replace the linked server or jobs, leaving a SQL Server VM.

Exam tip: Linked server to a non-SQL Server source must stay? Managed Instance can't do it; use a SQL Server VM.

Azure Database for PostgreSQL flexible server is Azure's managed PostgreSQL service.

When the engine must stay PostgreSQL and the team wants Microsoft to run the platform, the fit is Azure Database for PostgreSQL flexible server: it supports extensions such as PostGIS, zone-redundant high availability, automatic backups and managed minor version upgrades. VMs keep the engine but shift operations to the customer, Azure SQL Database is a different engine, and Azure Cosmos DB for PostgreSQL is no longer recommended for new projects.

1. Keep the engine: PostgreSQL.
2. Prefer a managed service over VMs because the startup wants HA, backups and patching handled.
3. Choose flexible server, the current managed PostgreSQL offering.

Exam tip: Managed PostgreSQL on Azure = Azure Database for PostgreSQL flexible server.

Distributed (sharded) PostgreSQL on Azure for new projects means elastic clusters in flexible server.

Two facts decide the answer. First, the requirement is horizontal scale-out of the data itself, which read replicas and bigger SKUs can't provide: replicas copy everything and a bigger SKU is still one node. Second, of the two Citus-based options, Microsoft now states that Azure Cosmos DB for PostgreSQL is on a retirement path and directs new PostgreSQL workloads to the elastic clusters feature of Azure Database for PostgreSQL flexible server.

1. Classify the need: data must be split across nodes (scale out writes and storage), not just reads.
2. Remove read replicas and scale-up, which keep all data on each node.
3. Pick the distributed option Microsoft recommends for new projects: elastic clusters.

Exam tip: New distributed PostgreSQL design = flexible server elastic clusters, not Azure Cosmos DB for PostgreSQL.

Azure Hybrid Benefit applies to the vCore model only, and not to new Hyperscale databases.

Independent scaling of compute and storage is a vCore feature; DTU tiers bundle them. Azure Hybrid Benefit is also vCore-only, but since Hyperscale's simplified pricing (December 2023) it isn't available for new Hyperscale databases, because Hyperscale has no SQL software license fee to offset. Business Critical could accept the licenses, but the option offers reservations instead and over-provisions for a balanced workload. General Purpose with Azure Hybrid Benefit meets every stated requirement.

1. Independent compute and storage scaling rules out the DTU model.
2. Applying licenses through Azure Hybrid Benefit rules out new Hyperscale databases.
3. Balanced performance below 4 TB points to General Purpose with the benefit applied.

Exam tip: Licenses to reuse in Azure SQL Database? vCore General Purpose or Business Critical, never new Hyperscale.

The DTU purchasing model offers bundled, preconfigured compute, storage and I/O.

Azure SQL Database has two purchasing models. The DTU model sells blended performance levels (Basic, Standard, Premium) where compute, storage and I/O come together at a fixed price, which suits simple, steady workloads and teams that don't want to size resources. Within the DTU model, Standard suits light, steady workloads; Premium is built for I/O-intensive, latency-sensitive work and costs more. The vCore model sizes compute and storage independently and adds options such as serverless and Hyperscale, which is more control than this team wants.

1. Note the preference: no separate sizing, one bundle, one price.
2. Match it to the purchasing model built on bundled performance levels: DTU.
3. Apply the cost requirement: a steady, light workload fits Standard rather than Premium.

Exam tip: Want a simple preconfigured bundle? DTU model. Want to size compute and storage yourself? vCore.