← Azure Course7 / 13

Azure SQL Database

Run a managed SQL database without servers: create a logical server and database, open the firewall, connect with a client, scale the compute tier, and protect data with automated and long-term backups.

Ad 728×90

What Azure SQL gives you

Azure SQL Database is a fully managed relational database (the SQL Server engine). Why managed: Azure handles patching, backups, and high availability, so you get a database endpoint to connect to instead of a server to babysit. You still write normal SQL.

Azure SQL has two pieces: a "logical server" (the endpoint + login) and one or more databases on it. We create the server first.

echo "Server = connection endpoint + admin login; database = your data."

Creating a logical server and database

The logical server is the addressable endpoint with an admin login; the database holds your tables. Why separate: one server can host several databases that share networking and firewall settings. These commands create real, billable resources.

Create the logical server with an admin login

az sql server create \
  --resource-group learn-rg \
  --name learn-sql-7f3k \
  --location eastus \
  --admin-user sqladmin \
  --admin-password 'Ch00se-A-Strong-One!'

Create a database on it using the cheap serverless General Purpose tier

az sql db create \
  --resource-group learn-rg \
  --server learn-sql-7f3k \
  --name appdb \
  --edition GeneralPurpose \
  --compute-model Serverless \
  --family Gen5 --capacity 1

Firewall rules — who can connect

Azure SQL blocks all connections by default. A firewall rule allows a range of IPs. Why: you open access only to the addresses that need it — your app or your own IP — keeping the database off the open internet.

Allow your current public IP to connect

MY_IP=$(curl -s https://api.ipify.org)

az sql server firewall-rule create \
  --resource-group learn-rg --server learn-sql-7f3k \
  --name my-laptop --start-ip-address $MY_IP --end-ip-address $MY_IP

A special rule to allow other Azure services (start+end 0.0.0.0)

az sql server firewall-rule create \
  --resource-group learn-rg --server learn-sql-7f3k \
  --name allow-azure --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0

Connecting to the database

The server has a fully-qualified name like learn-sql-7f3k.database.windows.net. You connect with any SQL client (sqlcmd, Azure Data Studio, your app). Why it matters: this proves the firewall and login work end to end before you wire it into an app.

Show the connection string template for your database

az sql db show-connection-string --client sqlcmd \
  --server learn-sql-7f3k --name appdb

Then connect, e.g.: sqlcmd -S learn-sql-7f3k.database.windows.net -d appdb -U sqladmin -P '...'

Scaling the database

You can change a database's compute and storage without rebuilding it. Why: start small and cheap while learning, then scale up (more vCores) when traffic grows — or switch tiers entirely. The change applies online with minimal disruption.

Bump the serverless database up to a max of 4 vCores

az sql db update --resource-group learn-rg \
  --server learn-sql-7f3k --name appdb \
  --capacity 4

Or move to a fixed Business Critical tier for low-latency production az sql db update ... --edition BusinessCritical --capacity 2

Backups and point-in-time restore

Azure SQL takes automated backups continuously and lets you restore to any point within the retention window. You can also configure long-term retention for compliance. Why: automated backups cover "undo the last few days"; restoring creates a fresh database so the original stays untouched.

Restore the database to a point in time (creates a new database)

az sql db restore --resource-group learn-rg \
  --server learn-sql-7f3k --name appdb \
  --dest-name appdb-restored \
  --time "2024-05-01T10:00:00Z"

Set long-term backup retention (e.g. keep weekly backups for 12 weeks)

az sql db ltr-policy set --resource-group learn-rg \
  --server learn-sql-7f3k --database appdb --weekly-retention P12W