The previous lesson ended with a written decision: Contoso Airlines' bookings and flights go to Azure SQL Database, because a seat cannot be sold twice and that demands genuine ACID transactions and referential integrity. Today that decision gets executed. By the end, the logical server sql-contoso-reservas-pro and the database db-reservas will exist, with their schema, their indexes, their Microsoft Entra ID authentication and their private endpoint pe-sql-reservas inside snet-datos, closing the gap module 2 drew but left empty. It is Azure's most mature relational service and the one that forces the most decisions — purchasing model, tier, redundancy, authentication and networking — so we will go through them with a clear criterion for when to choose each option.

Important cost warning: a General Purpose database with 2 vCores runs at around €370 a month and bills 24 hours a day: there is no "stopped" state like a virtual machine's. The only way to stop paying for compute is the serverless tier with auto-pause, or deleting the database. Business Critical multiplies the price by three. To practice, use the serverless tier in rg-contoso-reservas-dev and delete the resource group when you finish.

1. What it is, and why the server is not a machine
2. Purchasing models and service tiers
3. Serverless, auto-pause and elastic pools
4. Full deployment with Azure CLI
5. Authentication with Microsoft Entra ID
6. Private endpoint and closing off public access
7. The bookings schema and its indexes
8. Backups and point-in-time restore
9. High availability, read replicas and failover
10. Data security: encryption, masking and auditing
11. Performance, live scaling and spend control
12. Common Mistakes and Tips
13. Exercises
14. Conclusion

Azure SQL Database is the SQL Server engine offered as a managed platform, with one important conceptual difference: there is no SQL Server instance that is yours. There is a database with its own compute, its own backups and its own lifecycle.

Contoso chooses Azure SQL Database because db-reservas is a new schema: it drags along no SQL Agent jobs and no cross-database queries, so Managed Instance would mean paying for compatibility nobody will use. And here is the point where almost everyone gets it wrong: sql-contoso-reservas-pro is not a machine. It is a logical server, an administrative container with no CPU and no memory, and you pay nothing for it. What lives on it is the DNS name <name>.database.windows.net, the SQL and Microsoft Entra ID administrators, the server firewall rules, the network configuration (public access, minimum TLS, private endpoints) and the inheritable auditing policies. What lives in each database is the service tier and its cost, the schema and the data, its users, backup retention and redundancy, and dynamic masking. Practical consequence: two databases on the same server can cost wildly different amounts, but they share DNS, firewall and administrators; that is why Contoso separates servers by environment instead of mixing production and development.

Contoso chooses vCore: it needs the serverless tier for development and it wants to apply Azure Hybrid Benefit with its SQL Server licenses, something detailed in lesson 08-03. Within vCore there are three tiers, whose names describe their internal architecture better than you would expect (in DTU the equivalents are Basic, Standard and Premium, in the same price order):

Contoso's decision: General Purpose, 2 vCores, zone-redundant in production. The volume is 40 GB growing by 12 GB a year, nowhere near the ceiling, and 5–10 ms of latency is irrelevant inside a 200 ms HTTP request. Business Critical would triple the cost to solve a problem that does not exist.

The serverless tier (available in General Purpose and Hyperscale) defines a minimum and maximum range of vCores, scales within it and bills per second consumed. If the database spends a configurable amount of time with no connections, it pauses and stops billing compute. Four essential caveats:

• Storage is always paid for, paused or not; what disappears is the dominant line.
• The first connection after a pause takes between 30 and 60 seconds and fails if the client does not retry. The application needs retries with exponential backoff, which is mandatory in the cloud anyway.
• The minimum auto-pause delay is 60 minutes: it works between working days, not between requests.
• At full occupancy it works out more expensive than provisioned. It is for intermittent load.

An elastic pool is the other saving tool: a set of vCores shared by many databases whose peaks do not coincide. Contoso has it earmarked for a specific scenario: if each partner agency ends up with its own isolated database — around 40 small, sporadic databases in different time zones — the pool would cost a fraction of 40 provisioned databases. The rule: many small databases with non-simultaneous peaks.

First the logical server, remembering that it creates no machine and generates no cost by itself. The password is never written into the script: it is typed in or read from Key Vault (04-03).

Two details separate a correct deployment from one you will have to fix later: closing public access before any data exists — opening up just enough afterwards is far easier than closing something that was already working — and applying the four mandatory tags from minute zero, because without centro-coste you cannot break the bill down in module 8.

An administrator username and password is a poor permanent mechanism: a shared secret, with no expiry, no MFA and, if it leaks, it opens the whole database. The alternative is to designate a Microsoft Entra ID administrator:

A group is assigned rather than Marta Ríos directly, because people change jobs and groups do not. With ad-only-auth enable, the username and password stop working and all authentication goes through Entra ID, with its MFA and its conditional access. The App Service application will connect using its managed identity, with no password at all in the connection string: it is set up in lesson 04-02, and the remaining secrets are centralized in Key Vault (04-03).

With public access disabled, the database is only reachable from the network. You create pe-sql-reservas in snet-datos (10.20.3.0/24) and, along with it, name resolution: the application will keep asking for sql-contoso-reservas-pro.database.windows.net, and that name has to resolve to the private IP.

The result: from snet-app, the usual name resolves to a 10.20.3.x address. From the internet it resolves to nothing useful and, even if somebody knew the IP, the firewall would reject the connection. To administer from outside there are three routes and only two are advisable: the point-to-site VPN (02-06), a management VM in snet-gestion reached through Bastion, or a temporary firewall rule with your IP, which is the option that ends up forgotten and left open for months.

The CHECK constraint on PlazasLibres is the safety net no application can bypass: even if the code has a concurrency bug, the engine will stop the seat count dropping below zero, which is exactly the guarantee a relational database was chosen for. The order of the columns in the index is not decorative: the engine can use IX_Vuelos_Ruta_Salida filtering by Origen, by Origen + Destino or by all three, but not to search by SalidaUtc alone. The INCLUDE adds the columns the query returns without making them part of the key, so that the query resolves without going back to the table: that is a covering index. And the opposite warning, which is always forgotten: every index has a price, in space and in write speed. Three well-chosen indexes cover 95% of the workload; fifteen degrade ticket sales.

Azure SQL Database backs up automatically and with no configuration: full backups weekly, differentials every 12–24 hours and transaction log backups every 5–10 minutes. That is what makes it possible to restore to any instant within the retention period. There are two retentions: short-term, from 1 to 35 days (7 by default), which covers operational mistakes, and long-term, up to 10 years in weekly, monthly and yearly backups, for legal obligations and auditing.

Tuesday afternoon. At 16:40 Diego Salas runs a script that was meant to update 300 old bookings and, because of a badly copied WHERE, marks the entire table as anulada. At 16:52 the customer service calls start. The right answer is not to repair the data by hand:

Three things to burn into your memory: a restore always creates a new database and never overwrites the original, which lets you compare before deciding; the time is in UTC, and in August Spain is two hours ahead, so getting this wrong means restoring to a useless point; and the restored database bills too, so you recover the rows, swap the names and delete it the same day.

Geo-restore is a different thing: it uses the backups replicated to North Europe and it is for when the whole of West Europe is down. Its recovery point objective can be up to an hour, so it may lose the last few minutes, and it requires having chosen Geo or GeoZone backup redundancy when the database was created.

Within the region, high availability is included: with --zone-redundant true the compute has replicas across several zones and the SLA is 99.99%. Business Critical adds a four-node cluster and a free read replica, reachable with ApplicationIntent=ReadOnly in the connection string: the clean way to stop the operations dashboard's reports competing with ticket sales. Across regions you use a failover group, which replicates the database and provides a stable DNS name that always points at the primary:

The application connects to fg-contoso-reservas.database.windows.net without knowing which region is active. Consistently with the cost decision from module 1, it is not active-active: North Europe only serves reads and is promoted if West Europe goes down. Even so it doubles the compute cost, because the replica is a full database that bills; Contoso will weigh it up along with the rest of the continuity plan in lesson 07-05.

Four complementary layers, from least to most effort:

• Transparent data encryption (TDE): encrypts data files and backups at rest. Enabled by default. It protects against theft of the physical medium, not against a user with permissions.
• Always Encrypted: encrypts specific columns on the client, with a key the engine does not have; not even an administrator can read them. The price is high: on a column with randomized encryption you cannot filter or sort on the server. Contoso reserves it for payment data, with the key in Key Vault (04-03).
• Dynamic data masking: it does not encrypt; it hides the value in the result depending on who is querying.
• Auditing: it records who ran what and when, sent to Log Analytics (module 7).

Masking does not apply to administrators or to anyone granted UNMASK, and it is not a substitute for permissions: if somebody must not see a column, the right answer is not to give them access to it. Auditing is turned on with az sql server audit-policy update -g $RG -n $SERVER --state Enabled --log-analytics-target-state Enabled --log-analytics-workspace-resource-id $WORKSPACE_ID, sending every event to the workspace you will mine with KQL in lesson 07-02.

Query Store is enabled by default and it keeps queries, plans and execution statistics. It is what turns "the website has been slow since yesterday" into a concrete diagnosis:

Sorting by total CPU (executions × average time) rather than by duration is the key: an 8 ms query fired 400,000 times a day does far more damage than a 4-second report that runs once. In the portal, Query Performance Insight presents the same thing as charts and is the first place to go when somebody complains about slowness; automatic tuning can also create missing indexes and revert plans that got worse. Scaling, for its part, is a single command and it applies live, with a few seconds of disconnection at the end that the application absorbs if it retries:

This scaling is automated on a schedule with Azure Automation (07-04), just like the apertura-temporada-verano profile from module 2.

• Believing the logical server costs money or is a machine. It costs nothing and has no CPU: the databases are what bill.
• Restoring using local time. The timestamps are UTC; in summer, a two-hour offset can mean recovering data that was already destroyed.
• Forgetting the restored database. It bills exactly like the original. Delete it the same day.
• Leaving "Allow Azure services and resources to access this server" turned on. It does not mean "my services": it means any Azure resource, including a third party's subscription. With a private endpoint you do not need it.
• Picking Business Critical "just in case". It triples the cost. Move up when Query Store proves that I/O is the bottleneck, and not before.
• Creating an index for every slow query, or confusing masking with encryption. First check whether an existing index can cover the query by changing the column order or the INCLUDE; and remember that masking is cosmetic: an administrator sees everything.
• Tip: turn on long-term retention before an audit demands it; it does not apply retroactively to backups that have already expired.
• Tip: always implement retries with exponential backoff. In PaaS, brief disconnections during maintenance or scaling are normal, not a fault.

db-reservas in production holds 40 GB and handles 300 transactions per second at peak, available 24×7; in development, 2 GB used Monday to Friday from 9 to 18.

1. Choose the purchasing model, tier and compute model for each environment, justifying each decision.
2. What backup redundancy would you set in each one, and why?

On a Wednesday at 09:15 mainland Spain time (summer), a script deletes 12,000 rows from dbo.Reservas. It is detected at 11:30.

1. What command would you run, and with exactly what timestamp?
2. Why is the restore not done directly over db-reservas?
3. What would you do afterwards with the restored database?

You audit sql-contoso-reservas-pro and find: public access enabled, a firewall rule 0.0.0.0 - 255.255.255.255, the Azure services checkbox turned on, authentication by username and password only, and auditing disabled.

1. Rank the fixes from highest to lowest risk.
2. Write the commands for the first three.
3. What has to be verified before disabling public access so that the website is not left without a database?

Solution 1:

1. Both on vCore, which enables serverless and Azure Hybrid Benefit. Production: General Purpose, 2 vCores Gen5, provisioned and zone-redundant, because the load is sustained and 24×7, and 300 transactions per second with 5–10 ms of latency fit comfortably. Development: General Purpose serverless, from 0.5 to 2 vCores with a 60-minute pause, because it is used about 45 hours out of the week's 168: it removes around 70% of the compute line without changing how the team works.
2. Production, Zone as a minimum, and GeoZone if you want to be able to do a geo-restore, which requires it. Development, Local: the data is synthetic and regenerable, so geo-redundancy is pure waste.

Solution 2:

1. 09:15 mainland Spain time in summer is 07:15 UTC; take a safety margin backwards:

2. Because you cannot: a point-in-time restore always creates a new database. And that is a virtue, not a limitation: during those two and a quarter hours legitimate bookings have been created that would be lost if you replaced the whole database. The right approach is to extract from the restored copy only the rows that no longer exist (WHERE NOT EXISTS on ReservaId) and insert them into the original with SET IDENTITY_INSERT ON; since Azure SQL Database has no cross-database queries, you do it by exporting with bcp or with a data pipeline.
3. Delete it the same day with az sql db delete, after verifying the row counts. If it is forgotten, it bills every hour like any other database.

Solution 3:

1. Ranked by risk: (a) the 0.0.0.0-255.255.255.255 rule, which exposes the database to the entire world; (b) public access enabled; (c) the Azure services checkbox, which allows connections from other people's subscriptions; (d) local authentication with no Entra ID and no MFA; (e) auditing disabled, which opens no risk but makes it impossible to investigate what happened.
2. The commands:

3. That pe-sql-reservas is provisioned and approved, that the privatelink.database.windows.net zone is linked to vnet-contoso-pro, and that the App Service application has virtual network integration enabled (02-05); without that it would resolve the public name and lose the connection the moment access is closed.

db-reservas now genuinely exists. You know that sql-contoso-reservas-pro is a logical server with no CPU and no cost, grouping DNS, firewall and administrators, while the spend lives in each database. You have chosen with judgement between DTU and vCore and between General Purpose, Business Critical and Hyperscale, you have applied the serverless tier with auto-pause to the development environment, and you know the scenario for elastic pools. You have deployed the server and the databases with Azure CLI, with the four mandatory tags and public access closed from the very first command, you have designated a Microsoft Entra ID administrator on a group and enabled Entra-only authentication, and you have connected the database to snet-datos through pe-sql-reservas and its private DNS zone, so that the usual name resolves to a 10.20.3.x IP.

On top of that you have created the schema of flights, passengers and bookings with a CHECK constraint that prevents selling seats that do not exist and two covering indexes justified by real queries; you have recovered the platform from Tuesday's failed migration with a point-in-time restore in UTC; and you have covered geo-restore, high availability with zone redundancy, read replicas, the failover group to North Europe with its stable DNS name and its doubled cost, the four layers of data security — TDE, Always Encrypted, masking of the loyalty card and auditing — Query Store and live scaling.

But the relational engine does not solve everything. Contoso's fare catalog has different conditions for each ticket type, changes shape every season and is queried thousands of times a minute from the website and from the Availability API, with customers all over Europe and the Americas. Normalizing it would mean twenty tables and queries with ten joins to answer something as simple as "give me this fare in full". In the next lesson, Azure Cosmos DB, you will see the other end of the data map: JSON documents, global distribution, the partition key as the most irreversible decision in the design, the request units that measure what each query costs, and five consistency levels to choose, piece of data by piece of data, between accuracy and latency.