Task 2.1: Design data storage solutions for relational data

• AAzure SQL Database single databases in the General Purpose tier

  Incorrect

  SQL Database doesn't support SQL Server Agent or cross-database queries the way an instance does, so the app would need changes.

• BAzure SQL Managed Instance

  Correct

  SQL Managed Instance offers near full SQL Server engine compatibility, including SQL Server Agent, cross-database queries and CLR, as a managed service.

• CSQL Server on Azure Virtual Machines

  Incorrect

  The team would patch the operating system and SQL Server itself, which the requirement rules out.

• DAzure Database for PostgreSQL flexible server

  Incorrect

  Moving to PostgreSQL means rewriting T-SQL, jobs and CLR code.

SQL Managed Instance is the PaaS option with the broadest SQL Server compatibility: instance-scoped features such as SQL Server Agent, cross-database queries and CLR work, while Microsoft handles patching, backups and high availability.

• AProvisioned compute in Business Critical, scaled down at night

  Incorrect

  Provisioned compute bills every hour it exists, and the idle times here are unpredictable rather than nightly.

• BThe DTU-based Standard tier with a fixed number of DTUs

  Incorrect

  DTU tiers bill a fixed amount of compute all the time; they don't pause when idle.

• CThe serverless compute tier in General Purpose, with auto-pause on

  Correct

  Serverless scales compute automatically, bills compute per second used, and pauses after an idle period so only storage is billed.

• DAn elastic pool sized for the database's busiest hour of the week

  Incorrect

  A pool keeps its eDTUs or vCores provisioned all the time, so an idle database still costs the same.

The serverless compute tier for single databases in General Purpose (and Hyperscale) scales compute with demand and bills per second. With auto-pause, an idle database is paused and only storage is charged; the first connection afterwards resumes it with a short delay.

• AHyperscale

  Correct

  Hyperscale supports databases up to 128 TB, restores from snapshots quickly regardless of size, and adds named replicas with independent compute.

• BBusiness Critical

  Incorrect

  Business Critical caps database size far below 9 TB and offers no named replicas with compute sizes of their own.

• CGeneral Purpose

  Incorrect

  General Purpose caps database size far below 9 TB and offers no named read replicas.

• DPremium in the DTU model

  Incorrect

  Premium DTU databases also have a size limit far below 9 TB.

Hyperscale separates compute from page-server storage, so databases grow to 128 TB, backups are snapshot-based and restores are fast at any size, and up to 30 named replicas can each have their own compute size for read scale-out.

• ADynamic data masking on the card number column

  Incorrect

  Masking hides values in query results for non-privileged users, but administrators and the data itself stay unencrypted.

• BRow-level security with a predicate on the customer ID

  Incorrect

  Row-level security encrypts nothing, so administrators would still read card numbers in plaintext.

• CAlways Encrypted with deterministic encryption on the card number column

  Correct

  Data is encrypted in the client driver, so administrators see only ciphertext, and deterministic encryption still allows equality lookups.

• DAuditing to a Log Analytics workspace

  Incorrect

  Auditing records who ran which queries; it doesn't stop anyone seeing plaintext.

• ETransparent data encryption with a customer-managed key in Key Vault

  Correct

  TDE with a customer-managed key encrypts the database at rest with a key the company controls and can revoke.

Always Encrypted keeps sensitive columns encrypted with keys that never reach the database engine, protecting them from high-privileged users; deterministic encryption supports equality searches. TDE with a customer-managed key (BYOK) covers the whole database at rest, and revoking the key makes the database inaccessible.

• ASize every single database for its own peak

  Incorrect

  Sizing each database for its peak pays for idle capacity most of the time, which is what pools avoid.

• BPut the databases in elastic pools that share vCores

  Correct

  Pools suit many databases with low average use and peaks at different times, which share the pool's resources.

• CConsolidate the customers into one Hyperscale database

  Incorrect

  Merging tenants into one database is a redesign of the app and its isolation, not a no-code change.

• DMove each database to its own SQL managed instance in an instance pool

  Incorrect

  Instance pools group managed instances; moving each tenant to its own instance is a platform change and doesn't share idle capacity across databases.

Elastic pools are a cost-effective way to manage many databases with varying and unpredictable usage: databases in a pool share a set of resources and use them when they need them.

• AAzure SQL Database Hyperscale

  Incorrect

  SQL Database runs the SQL Server engine, so PostgreSQL queries and extensions would have to be rewritten.

• BAzure Cosmos DB for NoSQL

  Incorrect

  Cosmos DB for NoSQL isn't PostgreSQL, so the schema and queries would change.

• CAzure Database for PostgreSQL flexible server

  Correct

  It's the managed PostgreSQL service in Azure, with community versions, extensions and a custom maintenance window.

• DAzure SQL Managed Instance

  Incorrect

  Managed Instance is SQL Server, not PostgreSQL.

Azure Database for PostgreSQL flexible server is a fully managed service for the community PostgreSQL engine, with control over versions, extensions, configuration and maintenance windows.

• AAdd an active geo-replica in another region and point the reports at it

  Incorrect

  A geo-replica is a second billed database, which the company wants to avoid.

• BSend reports to the read-only replica with ApplicationIntent=ReadOnly

  Correct

  Business Critical includes a read-only replica for read scale-out at no extra charge, reached with the read-only application intent.

• CScale the database up to more vCores

  Incorrect

  A larger size costs more and still runs reports on the primary.

• DMove the database to the serverless compute tier

  Incorrect

  Reports would still compete with orders on the primary.

Read scale-out lets read-only workloads use the compute of a read-only replica in Premium, Business Critical and Hyperscale databases. Connections with ApplicationIntent=ReadOnly go to the replica, keeping reports off the primary.

• ATemporal tables that keep the history of every row

  Incorrect

  Temporal tables keep history, but a privileged user can change it without cryptographic evidence.

• BAuditing that writes to a Log Analytics workspace

  Incorrect

  Auditing records actions but doesn't prove the data itself wasn't altered.

• CLedger tables with database digests stored in immutable storage

  Correct

  Ledger hashes changes into a blockchain-style structure, and stored digests let anyone verify that history wasn't tampered with.

• DTransparent data encryption with a customer-managed key

  Incorrect

  Encryption at rest protects files from theft, not rows from changes by administrators.

The ledger feature keeps a cryptographic record of changes. Database digests saved to immutable storage let auditors verify that data hasn't been tampered with, including by privileged users.

• AMicrosoft Defender for Azure SQL Databases

  Correct

  Defender for SQL detects anomalous activities such as SQL injection and unusual access and includes vulnerability assessment.

• BDynamic data masking on sensitive columns

  Incorrect

  Masking limits what query results show; it doesn't detect attacks.

• CMicrosoft Entra-only authentication on the servers

  Incorrect

  A change of sign-in method doesn't alert on injection attempts.

• DLong-term backup retention for every database

  Incorrect

  Backups help recovery after damage; they don't detect attacks.

Microsoft Defender for Azure SQL Databases, part of Defender for Cloud, detects threats such as SQL injection and anomalous access patterns and finds misconfigurations through vulnerability assessment.