DP-300: Choosing an Azure SQL Architecture

An Azure SQL architecture should be chosen around application behavior, recovery requirements, compatibility, and who will operate it—not around a claim that one managed service is always best. Microsoft offers Azure SQL Database, Azure SQL Managed Instance, and SQL Server running on Azure virtual machines, each with a different boundary of customer responsibility. The DP-300 exam assesses the knowledge needed to plan, secure, operate, optimize, and recover such services. Database administrators must understand why a solution fits a workload and what happens when a dependency fails. A good architecture is one that can be explained and maintained, not merely deployed.

Imagine an online marketplace with a transactional catalog, payment processing, historical reporting, and a legacy order reconciliation service. Some components use ordinary relational queries; others depend on SQL Server features or scheduled jobs. The business expects continued access during maintenance, predictable performance at sales peaks, and strict controls over customer data. Moving everything to the same database offering may simplify procurement but create compatibility or operating problems. Architecture decisions must follow the applications' technical and business constraints.

Start with compatibility and responsibility

Azure SQL Database suits many cloud-native database designs and reduces operating-system administration tasks. Azure SQL Managed Instance can address applications that need broader SQL Server compatibility, while SQL Server on an Azure VM retains greater control of the instance and guest environment. That additional control comes with patching, backup, high-availability, and security responsibilities. No offering removes database design or query optimization. Selecting among them requires an inventory of dependencies and an understanding of which administrative functions the organization is willing and able to maintain.

For the marketplace, the web catalog may migrate cleanly to a managed database, while an older reconciliation process may depend on SQL Server Agent behavior or particular instance features. The architect should prove these requirements rather than rely on the application's age as a proxy for compatibility. A managed offering can reduce infrastructure maintenance but may require changes to connectivity, jobs, or application configuration. A VM migration may preserve more existing behavior but also preserve operational debt. Document the rationale and the steps needed to reevaluate the choice after application modernization.

Size for workload patterns, not one benchmark

Compute, memory, storage throughput, transaction log activity, and concurrency interact to determine performance. A database serving thousands of short transactions behaves differently from one executing a few heavy analytical joins. The architect should measure typical and peak demand, read-write ratios, query latency, storage growth, and the effects of batch jobs. Buying more compute can reduce some bottlenecks but will not repair inefficient query plans or poorly designed transactions. Service tier choices should follow workload evidence, desired recovery behavior, and acceptable cost.

An online sales event may produce a short but intense increase in order creation, while reporting remains steady overnight. Separating analytical workloads or scheduling them carefully can protect transaction latency. Some databases may require scaling during known peaks, but automation needs safe bounds and verification. Measure actual performance after resizing and consider whether application connection pooling or query design is the binding constraint. A successful architecture includes a process for revisiting capacity, because production workloads rarely remain identical to the assumptions made during initial deployment.

Design identity and network access together

Database authorization is not equivalent to network reachability. A private endpoint or firewall restriction can reduce exposure but does not decide whether a connected principal may read sensitive tables. Similarly, a carefully constrained database role is not a reason to expose a database unnecessarily to the internet. The design must account for client identity, authentication, permission scope, DNS, routing, and encrypted transport. Managed identities and Microsoft Entra authentication can support controlled access in appropriate scenarios, while application and administrative privileges should remain separated.

The marketplace's order API might need write access to a narrow group of tables but no permission to create users or change schema. A support analyst may need restricted read access to certain records, with masking or row-level controls where appropriate. The DBA should be able to trace both the database login and the network path used by that application. Unexpected access failures should be diagnosed from evidence, not corrected by opening all firewall sources or granting server-wide permissions. Security architecture includes preventing mistakes in routine support workflows.

Recovery objectives determine architecture costs

Recovery time and recovery point objectives distinguish tolerated interruption from acceptable data loss. These are business decisions with technical consequences. A customer payment database may need a much stronger recovery design than a report that can be rebuilt from source events. High availability helps with specified infrastructure failures; disaster recovery protects against broader events and may require geographic separation. Automatic failover, backups, geo-replication, and redundant application connectivity have different capabilities and costs across Azure SQL offerings.

A company may deploy a highly available database but still depend on an application hard-coded to a failed server address. In that case, database continuity does not translate into usable service. The full design needs connection handling, failover endpoint behavior, application retries, and data consistency validation. Test the complete transaction after a simulated failover, including whether the application safely retries an interrupted payment. A design claiming near-zero loss should identify precisely which supported capability and operational test justify the claim rather than rely on the phrase "geo-redundant."

Plan backups for the mistake you might make

Backups are important even when database infrastructure is redundant. Replication can carry an incorrect delete or update to another copy, so it does not provide every form of protection. Backup strategy must consider point-in-time restoration, retention, long-term requirements, encryption, and the location where recovered data will be inspected. A test restore should verify schema, permissions, application behavior, and data quality. Operational recovery may require comparing recent legitimate transactions and repairing only affected rows rather than rolling back an entire production database.

Suppose a billing deployment accidentally marks unsettled invoices as complete. Restoring yesterday's database could lose all valid changes made since then. The DBA should work with business owners to identify impact, preserve current evidence, and select a safe recovery method. That may include a separate restored copy used to reconstruct original values, subject to the platform's capabilities. The important architectural outcome is recovery flexibility: sufficient backups and audit information, plus clear procedures for decisions that affect financial or regulated records.

Optimize schema and query access patterns

Relational design should preserve correctness while supporting important query workloads. Indexes, statistics, table partitioning, data compression, and appropriate query patterns can improve performance, but all have trade-offs. An index that speeds reads can increase write overhead, and partitioning can complicate maintenance without improving a query that lacks useful filtering. The architect should measure actual execution plans, blocking, and resource consumption before proposing changes. A default index checklist is not a substitute for understanding the data and applications.

For the marketplace, a reporting query joining years of orders may need a different structure from the transaction path that inserts new orders. The team should consider whether to offload reporting, optimize queries, or introduce suitable read patterns. Dynamic management views, Query Store, and execution-plan analysis can help distinguish compute pressure from inefficient access. Database architecture is not complete when resource SKU has been chosen; physical and logical data design remain part of operating cost and service responsiveness.

Modernize with migration rehearsals

Migration planning begins with compatibility assessment, dependencies, data size, accepted downtime, and cutover strategy. An offline migration can be simpler but require a longer service pause; an online approach may reduce downtime while adding synchronization and validation complexity. Database moves can change network latency, identity, service endpoints, and automation. The team should test migration with representative data and application functions, not only confirm that tables were copied. Business reconciliation checks and a rollback decision point should be written before the final cutover.

A rehearsal might reveal that the legacy reconciliation service expects a local job scheduler or makes unsupported assumptions about instance-level configuration. That is better discovered before a customer-facing migration weekend. The architecture should account for temporary coexistence, dual-running risks, and who is authoritative for writes during transition. Repeated cutover drills can expose overlooked permissions and network settings. A migration succeeds when the application functions accurately under its new operational model, not merely when the destination database is available.

Include automation and administration from the start

Resource deployment through templates or command-line automation can make environments reproducible, but administrators also need dependable monitoring, alerting, job management, and controlled change procedures. The solution should record who owns backups, patches, permissions, performance baselines, and incident escalation. A database service that runs without clear ownership becomes fragile as staff and business requirements change. Observability should focus on user-impacting latency, availability, storage trends, and failed maintenance tasks in addition to platform health indicators.

For DP-300 preparation, compare three designs for one fictional workload: Azure SQL Database, Azure SQL Managed Instance, and SQL Server on an Azure VM. Explain the compatibility differences, operational responsibility, security approach, and recovery pattern. Then change the requirements and see whether the selection still holds. The broader Azure data engineering and administration roles provides career context; the DBA's distinguishing task is ensuring that a chosen relational platform remains secure, performant, and recoverable as the business changes.