Deploying Azure SQL Database

The first database Walt deploys in Azure will not be StockLine. Elena wants him to practise on something that cannot hurt a store: a copy of the Finch Rewards test database in the hf-nonprod subscription. Hana Sato's developers need it anyway, and building it by hand once, slowly, will show Walt every decision that a deployment involves. Later, in lesson 11, he will turn the same decisions into code. This lesson walks through those decisions in the order the Azure portal asks them, then shows the same deployment with the Azure CLI.

The logical server comes first

An Azure SQL database always lives on a logical server. Despite the name, a logical server is not a machine you can sign in to. It is an Azure resource that acts as a parent for databases and elastic pools and holds settings they share: a globally unique name that becomes part of the connection address, the administrator sign-in, the Microsoft Entra administrator, firewall rules, network settings, auditing defaults and the region. Databases on the same logical server are still separate: each has its own compute size and tier, and they do not share memory or processor unless they are placed in the same elastic pool.

The server name must be unique across all of Azure, because it becomes a DNS name such as hf-rewards-test-cc.database.windows.net. Harrow & Finch's naming standard, set by Renata in September 2026, uses the workload, the environment and a short region code. Walt picks hf-sql-rewards-test-cc for the server and finchrewards for the database, in the resource group rg-rewards-test-cc in the Canada Central region.

Authentication choices at creation

The portal asks how people will sign in before it asks anything about performance. There are three choices: Microsoft Entra authentication only, SQL authentication only, or both. Microsoft Entra authentication lets users and applications sign in with their organizational identities or managed identities, with multifactor authentication and central disabling of accounts when someone leaves. SQL authentication uses a user name and password stored in the database engine. Harrow & Finch's security manager, Sunil Rao, has set a rule for new databases: Microsoft Entra only, with an Entra group, not a single person, as the server's Entra administrator. Walt chooses the group sg-dba-admins. MSAZ-281 covers logins, users and permissions in depth; for now the important point is that the choice is made at the server and can be changed later.

Compute and storage

Next comes the workload environment and the compute size. The portal offers a development and production preset, which only changes the suggested defaults. Walt opens the configuration page and chooses the vCore model, the General Purpose tier, serverless compute with a minimum of 0.5 vCores and a maximum of 2 vCores, and an auto-pause delay of one hour, which follows the cost comparison in lesson 2. He sets a maximum data size of 32 GB, which is plenty for test data and can be raised later without downtime. He leaves standard-series hardware selected because serverless requires it.

The same page asks about backup storage redundancy. Automatic backups of Azure SQL Database are copied to a storage account managed by Microsoft, and the customer chooses how that storage is replicated: locally redundant within one data centre, zone-redundant across availability zones in the region, or geo-redundant to the paired region. Geo-redundant backup storage allows a geo-restore to the paired region if the whole primary region is lost; locally redundant and zone-redundant storage keep backup data inside the primary region, which some residency policies prefer. For the test database Walt chooses locally redundant backup storage, the cheapest. For production StockLine data the team will choose differently, and MSAZ-282 explains why.

Production databases in a region with availability zones can also be made zone-redundant, so that the database's replicas are spread across zones and survive the loss of one data centre. The option exists for General Purpose, Business Critical and Hyperscale in supported regions. Canada Central has three availability zones, so the option is available to Harrow & Finch; the test database does not need it.

Networking

A logical server has a public endpoint by default, reachable over the internet but closed by a server-level firewall until rules allow specific IP address ranges. There is also a setting that allows Azure services and resources to access the server, which opens the firewall to traffic from any Azure address, including other customers' resources; most security teams leave it off. The firewall can hold server-level rules, which apply to every database on the server and live in the master database, and database-level rules, which apply to one database and travel with it when it is copied.

The more secure design is a private endpoint: a network interface with a private IP address in one of the company's virtual network subnets, linked to the logical server. Applications in the virtual network, or on premises through the site-to-site VPN that went live in October 2026, connect to that private address, and a private DNS zone, privatelink.database.windows.net, makes the normal server name resolve to it. Once private endpoints are in place, public network access can be disabled entirely. Virtual network service endpoints with virtual network rules are an older alternative that keeps the public endpoint but restricts it to chosen subnets.

There is one more networking choice that matters for performance: the connection policy. With the redirect policy, a client's first connection goes to a gateway, which then tells the client to connect directly to the node hosting the database, so later traffic skips the gateway and latency is lower. With the proxy policy, all traffic flows through the gateway. Connections from inside Azure use redirect by default; redirect needs the client to reach a wider range of ports, 11000 to 11999, which matters when firewalls sit in the way.

Additional settings

The last pages of the wizard ask about data source, collation, maintenance and tags. A new database can start empty, be restored from a backup, or include the small AdventureWorksLT sample. The database collation decides how text is sorted and compared, for example whether colour and COLOUR are equal; the default, SQL_Latin1_General_CP1_CI_AS, is case-insensitive and accent-sensitive and matches what StockLine uses, so Walt keeps it. Collation is hard to change once data exists, so it is worth checking before the first deployment rather than after. The maintenance window lets the team move planned Azure maintenance to a weekday or weekend window in the evening rather than the system default. Tags such as cost-centre=it-data and environment=test help Dana's team divide the bill.

Microsoft Defender for SQL can be enabled when the server is created. It adds vulnerability assessments and alerts for suspicious activity such as SQL injection or unusual sign-ins. Sunil's team turns it on at the subscription level, so Walt does not need to enable it database by database; MSAZ-281 covers it.

The same deployment with the Azure CLI

The portal is a good teacher, but nobody wants to click through nine pages for every environment. The study guide asks you to choose an automated deployment method, and the commands below show the same deployment as Azure CLI commands, which Walt runs in Azure Cloud Shell. Values are written out in full rather than stored in variables, so that every setting is visible.

az group create --name rg-rewards-test-cc --location canadacentral --tags environment=test cost-centre=it-data

az sql server create --name hf-sql-rewards-test-cc --resource-group rg-rewards-test-cc --location canadacentral --enable-ad-only-auth --external-admin-principal-type Group --external-admin-name sg-dba-admins --external-admin-sid 7f3c2a10-0000-4c1e-9a55-000000000000

az sql db create --name finchrewards --server hf-sql-rewards-test-cc --resource-group rg-rewards-test-cc --edition GeneralPurpose --compute-model Serverless --family Gen5 --min-capacity 0.5 --capacity 2 --auto-pause-delay 60 --max-size 32GB --backup-storage-redundancy Local --zone-redundant false

az sql server update --name hf-sql-rewards-test-cc --resource-group rg-rewards-test-cc --enable-public-network false

Reading the commands is a DP-300 skill in its own right. The family value Gen5 is the CLI name for standard-series hardware, capacity is the maximum vCores, min-capacity the serverless minimum, and auto-pause-delay is given in minutes. The private endpoint is created with its own command, az network private-endpoint create, and its DNS zone link; Renata's networking team owns those, so Walt asks them rather than creating them himself. PowerShell offers matching cmdlets, New-AzSqlServer and New-AzSqlDatabase, with the same settings as parameters.

Once the database exists, Walt connects from SQL Server Management Studio on his workstation, which reaches the private endpoint through the VPN, signs in with his Entra account and runs a simple check of the service objective.

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Edition') AS edition,
       DATABASEPROPERTYEX(DB_NAME(), 'ServiceObjective') AS service_objective,
       DATABASEPROPERTYEX(DB_NAME(), 'MaxSizeInBytes') / 1073741824 AS max_size_gb,
       DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS collation_name;

The result shows the edition GeneralPurpose, the service objective GP_S_Gen5_2, where S means serverless, Gen5 the hardware family and 2 the maximum vCores, a maximum size of 32 GB and the expected collation. Walt pastes the result into his binder. Old habits, he says, but a runbook that proves the settings is worth more than a memory of having clicked them.

EXECUTE sp_set_database_firewall_rule
    @name = N'webfirm-office-temp',
    @start_ip_address = '203.0.113.16',
    @end_ip_address = '203.0.113.23';

Choosing a deployment method

The portal, Azure CLI, PowerShell, ARM templates, Bicep and Terraform can all deploy Azure SQL Database. The study guide's skill of choosing an automated deployment method comes down to a few questions. Is this a one-off experiment, where the portal is fine? Does someone need a repeatable script that runs from a laptop or a pipeline, where CLI or PowerShell fit? Should the desired state of the whole environment be described in files that can be reviewed, versioned and redeployed, where Bicep, ARM or Terraform fit? Harrow & Finch has chosen Bicep for production infrastructure, so lesson 11 rebuilds this database as a Bicep file.