CH06-02: Relational Data with Azure SQL

Goal

The previous section introduced data selection as a match between data shape, access pattern, consistency, lifecycle, and operations. This section focuses on relational data: records with structure, relationships, constraints, and transaction needs.

Azure SQL is often the right Azure direction when relational behavior is part of the workload's value, not just a familiar database habit.

The durable idea is:

Azure SQL fits when structured data, SQL queries, and transactional integrity are central to the workload.

The Short Version

Azure SQL Database is a managed relational database service in Azure. In plain language, it gives a team a SQL database without making the team manage the underlying database servers, operating system patching, and many platform maintenance tasks directly.

Relational data works well when the application needs:

• Structured records.

• Relationships between entities.

• Constraints that protect data quality.

• Transactions where related changes succeed or fail together.

• SQL queries for application behavior, reporting, troubleshooting, or integration.

Relational design is not old-fashioned by default. It is a strong fit when the business problem is relational.

The Relational Workload Shape

Think of Azure SQL as a good home for data that naturally forms connected tables.

Order service -> Azure SQL
                 |-- Customers
                 |-- Orders
                 |-- OrderLines
                 |-- Payments
                 |-- Invoices

In this model, an order is not just a blob of data. It relates to a customer, one or more order lines, payment records, invoice records, shipment status, and financial reporting. The relationships matter because the business cares whether the system can trust them.

When Relational Structure Helps

Relational modeling is useful when the data has clear rules.

Examples include:

The important phrase is "may fit." Azure SQL is not automatically correct because data exists. It is correct when the workload benefits from relational guarantees and SQL access.

Transactions Are a Design Signal

A transaction is a group of changes that should be treated as one unit. For example, creating an order may need to create the order header, order lines, payment authorization record, and inventory reservation together.

If those changes cannot be safely separated, relational design has a real advantage. The database can help protect the system from half-completed business facts.

This does not mean every workflow belongs in one relational database. Distributed systems still need careful boundaries. But when a set of data changes truly belongs together, Azure SQL can give the application a clear consistency boundary.

Managed Does Not Mean Design-Free

Azure SQL reduces infrastructure management, but it does not remove database architecture.

The team still needs to decide:

• Schema design and ownership.

• Indexing strategy.

• Query performance and execution plans.

• Backup retention and restore testing.

• High availability and disaster recovery requirements.

• Authentication, authorization, encryption, auditing, and private access.

• Scaling model, service tier, capacity, and cost controls.

• Monitoring, alerts, and operational runbooks.

This is a recurring managed-service lesson. Azure can operate much of the platform. The team still owns the data model and the business behavior that depends on it.

Scale and Cost Are Still Part of the Choice

Azure SQL can scale in several ways, including changing service tiers, using different compute models, using elastic pools for groups of databases, and choosing specialized tiers for larger workloads. You do not need to memorize those options now.

The architecture point is simpler:

Relational databases scale best when schema, indexes, queries, and capacity planning are treated as design work.

Poor queries can make a strong database look weak. Missing indexes can make normal traffic expensive. Over-indexing can slow writes and increase storage. Keeping years of data in the same hot tables can make routine operations harder. These are not reasons to avoid Azure SQL. They are reasons to design it properly.

Example: Orders, Payments, and Invoices

Imagine the commerce platform needs to store orders, customers, payments, and invoices.

Azure SQL is a strong candidate because:

• Orders have line items.

• Payments must be associated with the right order.

• Invoices need a reliable relationship to the purchase.

• Business users may need operational reporting.

• The system may need to enforce constraints, such as valid order status transitions or required customer references.

A simplified model might be:

Customer
    |
    +-- Order
            |
            +-- OrderLine
            +-- Payment
            +-- InvoiceMetadata

Notice the word InvoiceMetadata. The invoice PDF itself may belong in Blob Storage, because it is a durable object. Azure SQL can store the invoice record, amount, customer, date, status, and blob reference. The large binary file does not need to live inside the relational table for the relational model to remain authoritative.

When Another Store May Be Better

Azure SQL may not be the best fit when the primary requirement is:

• Flexible document aggregates with frequent shape changes.

• Very high-scale key-based access where the partition model is clear.

• Globally distributed low-latency reads and writes with a matching data model.

• Large binary objects such as images, videos, PDFs, backups, and exports.

• Shared file access through SMB or NFS semantics.

• Large-scale analytical scans that belong in an analytics platform.

The point is not to make Azure SQL smaller. It is to keep it focused on the data responsibilities where relational behavior helps.

Common Mistakes

• Avoiding relational databases because NoSQL sounds more modern.

• Choosing Azure SQL without considering query patterns, indexes, capacity, and cost.

• Treating a managed database as if backup, security, restore, and performance design no longer matter.

• Storing large binary content in relational tables when object storage would fit better.

• Using one shared database for many services without clear ownership boundaries.

• Assuming a migration target is automatically the final modernization architecture.

If Someone Asks You

When should you choose Azure SQL?

A good answer is:

I choose Azure SQL when the workload benefits from relational structure, SQL queries, constraints, and transactional integrity. It is a strong fit for data such as orders, payments, billing, inventory, and operational records where relationships and correctness matter. I still design schema, indexes, scaling, backup, security, monitoring, and cost because managed relational does not mean decision-free.

What You Should Remember

Use Azure SQL when relational structure and transactional behavior are part of the value of the system.

Questions to Check Understanding

1. What kinds of requirements make relational data a good fit?

2. Why does managed Azure SQL still require database design?

3. When might another storage service be better than Azure SQL?