Scenario practice

Elena writes her own case studies for Walt, because the best preparation for an exam case study is to work through one with the same structure, under time, and then argue about every answer. This lesson presents one of them in full, with the tabs a real case study would have, followed by eight questions and a worked explanation of each. The company is invented and has nothing to do with Harrow & Finch, so that Walt cannot answer from memory of his own estate. Try each question before reading the explanation, and time yourself: a case study of this size deserves about 15 to 18 minutes on the exam.

Case study: Northgate Pharmacy Group

Overview. Northgate Pharmacy Group runs 64 community pharmacies and a central fill site in Ontario. Head office is in Hamilton. The company is moving its pharmacy systems to Azure over the next year and has chosen Canada Central as its primary region and Canada East as its secondary region.

Existing environment. The prescription system, RxCore, runs on a SQL Server 2017 Enterprise instance on premises with three databases: RxCore (1.4 TB), RxAudit (300 GB) and Formulary (8 GB). RxCore uses SQL Server Agent jobs for nightly claims exports, a cross-database query from RxCore to Formulary, and a CLR assembly for label printing. Each pharmacy also has a small local database for its appointment booking app, 64 databases of 2 to 6 GB each, with peak use at different times. The reporting team runs heavy queries against RxCore directly every morning. The company's identities are in Microsoft Entra ID, synchronized from on-premises Active Directory.

Requirements. Patient health information must be kept in Canadian regions. The pharmacists' application must keep working with no more than 30 minutes of downtime during the migration. After a regional disaster, RxCore must lose no more than 5 minutes of data and be back within 1 hour, and the pharmacy application must not need a configuration change. Auditors must be able to prove that dispensing records in RxAudit were not altered. Costs must be minimized wherever the requirements allow.

Technical requirements. Database access must use Microsoft Entra identities only; SQL authentication must be disabled. The appointment databases must be managed with the least administrative effort. Reporting queries must not affect the pharmacists' application. Prescription numbers in RxCore must be unreadable to database administrators. The nightly claims export must send an email to the operations team if it fails.

Question 1

Which offering should host RxCore? A. Azure SQL Database Hyperscale; B. Azure SQL Managed Instance; C. SQL Server on an Azure VM; D. SQL database in Microsoft Fabric.

Answer: B. RxCore uses SQL Server Agent jobs, a cross-database query and a CLR assembly. Managed Instance supports all three with managed patching and backups, which keeps administrative effort and cost lower than a VM. Hyperscale is an Azure SQL Database tier with no Agent or cross-database queries. A VM would work but adds patching and backup work, and nothing in the case requires operating system access. Fabric's SQL database does not support these instance features.

Question 2

Which migration method meets the downtime requirement for RxCore? A. Back up to Blob storage and restore on the managed instance; B. The Managed Instance link; C. BACPAC export and import; D. Azure Database Migration Service in offline mode.

Answer: B. The downtime limit is 30 minutes for a 1.4 TB database. The Managed Instance link supports SQL Server 2016 and later, so SQL Server 2017 qualifies, and replicates continuously until a planned failover that takes minutes. Backup and restore of 1.4 TB is offline and takes far longer than 30 minutes, and so does an offline migration with the Database Migration Service. BACPAC targets Azure SQL Database, not Managed Instance, and is offline too. Watch for a variant where the source is SQL Server 2012; then the link would not be available, and an online option such as the Log Replay Service would be the answer.

Question 3

Which HA/DR design meets the RxCore disaster requirement at the lowest cost? A. Geo-restore from geo-redundant backups; B. Zone-redundant configuration; C. A failover group with a secondary managed instance in Canada East; D. Active geo-replication.

Answer: C. The requirement is an RPO of 5 minutes, an RTO of 1 hour and no configuration change. Geo-restore can lose up to about an hour of data. Zone redundancy does not cover a regional disaster. Active geo-replication is not available for Managed Instance. A failover group meets the objectives, and its listener keeps the connection string the same.

Question 4

Which two actions meet the authentication requirement? Each correct answer presents part of the solution. A. Set a Microsoft Entra group as the instance's Entra admin; B. Enable Microsoft Entra-only authentication; C. Create SQL logins with password policy enforced; D. Add a server-level firewall rule for head office.

Answer: A and B. Entra-only authentication cannot be enabled until an Entra admin exists, and together they disable SQL authentication. C contradicts the requirement. D is about network access, not authentication.

Question 5

How should the 64 appointment databases be hosted? A. 64 single General Purpose databases at 2 vCores each; B. An Azure SQL Database elastic pool; C. 64 databases on the RxCore managed instance; D. One Hyperscale database with a schema per pharmacy.

Answer: B. Many small databases with peaks at different times are the textbook case for an elastic pool, which shares compute and keeps management simple. Separate single databases cost more because each is sized for its own peak. Putting them on the RxCore instance mixes workloads and gains nothing. Merging them into one database is a redesign that the case does not ask for.

Question 6

How should the company meet the requirement that reporting queries must not affect the pharmacists' application, at the lowest cost? A. Add a second managed instance and replicate RxCore with transactional replication; B. Use the Business Critical tier and connect reports with ApplicationIntent=ReadOnly; C. Run reports against the failover group secondary through the read-only listener; D. Schedule reports at night.

Answer: B or C, depending on the tier chosen. This item is ambiguous on purpose, and Elena uses it to make a point. If RxCore runs on Business Critical, its built-in readable secondary can serve reports at no extra cost through ApplicationIntent=ReadOnly. The failover group's secondary is also readable through the read-only listener, and since the company is paying for it anyway, it costs nothing extra too, though it sits in another region with higher latency. A real exam item would include a constraint that decides between them, such as 'reports must run in the primary region' or 'the solution must use General Purpose'. When an item seems to have two right answers, look again for the constraint you skipped. Option D changes the business process, which the case does not allow.

Question 7

Which feature proves that dispensing records in RxAudit were not altered? A. Temporal tables; B. Ledger tables with database digests stored in immutable storage; C. Server-level auditing; D. Change data capture.

Answer: B. Ledger tables make changes tamper-evident, and digests stored in immutable Azure Blob storage let auditors verify the history. Temporal tables keep history but anyone with permissions can alter it. Auditing records who did what but does not prove that the data itself was not changed. Change data capture is for feeding changes to other systems.

Question 8

Prescription numbers must be unreadable to database administrators, and the pharmacy application must still look up prescriptions by exact number. Which configuration meets this? A. Dynamic data masking on the column; B. Transparent data encryption with a customer-managed key; C. Always Encrypted with deterministic encryption and the column master key in Key Vault accessible only to the application; D. Always Encrypted with randomized encryption and no enclave.

Answer: C. Only Always Encrypted keeps data unreadable to administrators, and exact-match lookups need deterministic encryption, or randomized encryption with a secure enclave. Masking does not stop administrators, and TDE protects files, not query results. Randomized encryption without an enclave does not allow lookups.

Reviewing a case study afterwards

The most useful part of a practice case study is the half hour after it. Elena and Walt go through every question, right or wrong, and Walt has to say which requirement decided it. For the questions he got right, this checks that he was right for the right reason; twice in the first practice case he chose the correct answer because it sounded familiar, and could not name the deciding requirement. Those count as half-right in Elena's notes, because on another day the same instinct could pick the wrong option.

For the questions he got wrong, the review names the distractor family from lesson 2: a neighbouring feature, the right feature at the wrong scope, an option that breaks a constraint, or an outdated tool. Question 7 in this case caught him with a neighbouring feature, temporal tables for ledger. That pattern is common in the compliance area, because several features keep history or record changes, and only one of them proves the history was not altered.

Writing your own case studies

Writing a case study is an even better exercise than reading one. Elena asks Walt to write one in February about a company he invents, with all four tabs and six questions, using at least one requirement from each skill group. Writing forces him to decide what makes one option right and another wrong, and to put the deciding constraint somewhere a careful reader would find it. He finds it harder than expected: his first draft had two right answers for three of the six questions, which taught him more about how exam items are built than any practice set.

Good case study practice also means getting used to the pace. A real case study can be longer than this one, with more tabs and more questions, and it uses time from the same 100 minutes as the rest of the exam. Walt's target is to spend no more than two minutes skimming and no more than about two minutes per question on average, which leaves time to return to the requirements tab as often as each question needs.

Common case study themes

Case studies on DP-300 tend to draw on a small set of themes, because they are the decisions a DBA makes when moving a company to Azure: which offering for each database, which migration method under a downtime limit, which HA/DR design for given RPO and RTO, how to authenticate and authorize users and applications, how to protect sensitive columns, how to separate reporting from the main workload, how to automate maintenance, and how to monitor after the move. Practising one case study for each theme, with different constraints each time, covers most of what can appear.

For each theme, Walt keeps a single sentence that names the deciding requirement and the answer it usually points to, such as 'instance features point to Managed Instance' or 'no connection-string change points to a failover group'. The twelve sentences fit on one card, and he reads it before every practice case.