Azure SQL Database, Managed Instance and SQL on VMs

Choosing a member of the Azure SQL family is only the first decision. Each option comes with its own settings: how resources are bought, which service tier to use, whether databases share resources, and how backups and failover work. In the seventh data hour, Elena walks the team through the options they will meet when they actually create the resources, using the three databases they placed last week: the Finch Rewards API on Azure SQL Database, StockLine on Managed Instance and the warehouse system on a virtual machine. The exam expects recognition rather than deep configuration knowledge, but the concepts explain why the choices matter.

Azure SQL Database deployment options

Azure SQL Database can be deployed in two ways. A single database has its own dedicated set of resources, sized for that one database. An elastic pool is a shared set of resources that several databases draw on. Databases in a pool can each use more when they are busy, as long as the pool as a whole has capacity. Pools suit groups of databases with varying, unpredictable loads that rarely peak at the same time, such as a software company that gives each customer its own database. Paying for one pool is usually cheaper than sizing every database for its own peak.

Harrow & Finch has only one new database for now, so the Finch Rewards API uses a single database. Ines notes that if the company later builds small databases for each of its supplier portals, a pool would be worth considering.

Purchasing models

Azure SQL Database offers two ways to buy resources, called purchasing models.

• The DTU model, short for database transaction unit, bundles processing, memory and storage input and output into a single measure. You choose a service level, such as Basic, Standard or Premium, and a size within it. It is simple and predictable, and suits small or steady workloads where you do not want to think about the parts.
• The vCore model, short for virtual core, lets you choose the number of cores and the hardware type separately from storage. It is more flexible, maps more directly to on-premises servers, and is the only model that supports Azure Hybrid Benefit and reserved capacity. Microsoft generally recommends vCore for new deployments.

Within the vCore model, compute can be provisioned or serverless. Provisioned compute is a fixed size that runs and bills all the time, which suits steady, predictable workloads. Serverless compute scales automatically between a minimum and a maximum number of vCores based on demand, bills per second for the compute actually used, and can pause automatically after a period of inactivity, when you pay only for storage. The first request after a pause has to wait while the database resumes, so serverless suits development databases and workloads with long quiet periods better than a shop's checkout.

# Illustrative comparison of provisioned and serverless compute for one month.
# The rate is made up for the example; real prices vary by region, tier and hardware.
RATE = 0.20          # illustrative price per vCore-hour
HOURS = 730          # hours in an average month

provisioned_vcores = 2
provisioned = provisioned_vcores * HOURS * RATE

# A test database: busy 8 hours on 22 weekdays at about 1.5 vCores, paused otherwise.
active_hours = 8 * 22
serverless = 1.5 * active_hours * RATE

print("Provisioned (2 vCores all month):", round(provisioned, 2))
print("Serverless (1.5 vCores while active):", round(serverless, 2))
print("Serverless costs", round(serverless / provisioned, 2), "times as much as provisioned here")

Provisioned (2 vCores all month): 292.0
Serverless (1.5 vCores while active): 52.8
Serverless costs 0.18 times as much as provisioned here

def vcore_hours(usage):
    total = 0
    # multiply hours by vcores for each period and add them up
    return total

print(vcore_hours([(8, 1.5), (2, 4), (0.5, 0.5)]))

Service tiers

In the vCore model, the service tier decides the storage design and the level of performance and availability. There are three.

• General Purpose is the default, balanced tier. Compute and storage are separate, with data held on remote storage, and Azure replaces a failed compute node automatically. It suits most business workloads at a moderate price.
• Business Critical keeps data on fast local storage and maintains several replicas of the database in the background, giving the lowest latency and the fastest recovery from failures. One replica can be used for read-only queries at no extra charge, which helps with reporting. It suits workloads where every millisecond and every second of downtime matter, at a higher price.
• Hyperscale uses a distributed storage design that lets a single database grow to very large sizes, with fast backups and restores regardless of size, and the option of extra read replicas. It suits large and fast-growing databases, and Microsoft increasingly positions it as a general choice for new databases too.

Elena's plan for Finch Rewards is a serverless General Purpose database for development and testing, which will sit paused most nights and weekends, and a provisioned General Purpose database for production, reviewed after the first big promotion. Business Critical would be tempting for StockLine's tills, she says, but that is a Managed Instance decision.

Changing size later

One of the biggest differences from a physical server is that size is not fixed. An Azure SQL database can be moved to more or fewer vCores, or to a different tier, with a setting change or a command; the service copies or adjusts the database in the background and switches over with only a brief interruption to connections, usually seconds. Storage can be increased the same way. This changes how teams plan. Instead of buying a server large enough for the next five years, they start with a reasonable size, watch the performance metrics and adjust. Finch Rewards will start small, grow for the spring promotion and shrink again afterwards, paying for the larger size only while it is needed.

Built-in backups and availability

Both platform services take backups automatically: full, differential and transaction log backups on a schedule that Microsoft manages. This allows point-in-time restore, which recovers a database to any moment within the retention period, by default seven days and configurable up to 35. Long-term retention can keep weekly, monthly or yearly backups for up to ten years for compliance. Backups can be stored in locally redundant, zone-redundant or geo-redundant storage, the same choices covered in the Azure fundamentals courses, and geo-redundant backups allow a database to be restored in another region after a disaster.

For high availability, the platform keeps the database running through hardware failures and maintenance without any setup. Zone redundancy, where supported, spreads copies across availability zones in the region. For disaster recovery across regions, active geo-replication keeps readable copies of a database in other regions, and failover groups manage a set of databases that fail over to another region together, with connection names that stay the same so applications do not need to change.

Walt reads this section twice. On his own servers, every one of these features is a project: configuring log shipping, testing failover, buying storage for backups. On the platform they are settings. He still intends to test restores every month. "A backup you have never restored," he says, "is a rumour."

Azure SQL Managed Instance in more detail

Managed Instance uses the vCore model only, with General Purpose and Business Critical tiers, and Microsoft has introduced newer hardware and storage options over time. Each managed instance is deployed into a dedicated subnet of an Azure virtual network, which gives it a private address and lets on-premises systems reach it over a VPN or ExpressRoute connection, as if it were in the company's own network. That suits StockLine: the tills and back-office servers will connect over the company's private link to Azure rather than across the internet.

Migrating to Managed Instance can use native SQL Server backups, restored into the instance from Azure Blob Storage, or the Azure Database Migration Service, or a feature called the Managed Instance link, which replicates a database from an on-premises SQL Server to Azure continuously so that the final switch takes minutes. Walt's migration plan, still in draft, uses the link so that the cutover can happen on a quiet Sunday night in 2027 with only a short outage.

SQL Server on Azure VMs in more detail

A SQL Server virtual machine is created from an Azure Marketplace image that includes Windows or Linux and a chosen SQL Server version and edition, or from a plain virtual machine where you install SQL Server yourself. When created from a Marketplace image, the VM registers with an Azure extension that unlocks management features in the portal, such as automated patching windows, automated backups to Azure storage, licence management and storage configuration. These features narrow the gap with the platform services, but the customer still owns the configuration and the outcome.

High availability on VMs uses the same SQL Server features as on-premises, such as Always On availability groups and failover cluster instances, spread across availability zones or availability sets. Disaster recovery can add a replica in another region. Because the options are the familiar ones, an experienced DBA can design them, but they take real work, which is the price of control.

az sql db create --resource-group rg-rewards-prod-cc --server sql-rewards-cc --name rewards --edition GeneralPurpose --compute-model Serverless --family Gen5 --min-capacity 0.5 --capacity 2 --auto-pause-delay 60

Elena points out that the command names every important choice from this lesson: the edition, which is the service tier; the compute model; the hardware family; the minimum and maximum vCores; and how long to wait before pausing. The resource group name ends in cc for Canada Central, following the naming standard from Project Skylight.