Learning: Azure SQL Database for beginners - create, connect, secure, load data, back up and restore

#About this session

By the end you will be able to create an Azure SQL Database on your own, connect to it securely, and recover it when something goes wrong. The advanced session continues from here.

#Module 1: the ideas you need first

#What Azure SQL Database is

A fully managed relational database built on the SQL Server engine. Microsoft runs the OS, patching, backups and high availability. You manage the schema, data, access and performance.

This session covers Azure SQL Database.

#The logical server

Every database belongs to a logical server (<name>.database.windows.net). It's not a VM. It's a management container that holds:

• the admin logins (SQL admin and/or Microsoft Entra admin)
• server-level firewall rules and network settings
• auditing, Defender and other security settings shared by its databases

#Purchasing models and tiers

Serverless scales compute automatically between a min and max vCore setting and can auto-pause when idle, so you pay mostly for storage. That's ideal for this lab. Check the current pricing page and the Azure SQL free offer before creating anything.

#Module 2: create the server and database

Use Cloud Shell (Bash) or a local Azure CLI. Pick names that are globally unique for the server.

RG=rg-sqllab
LOC=centralindia
SERVER=sql-lab-$RANDOM            # must be globally unique
DB=sqldb-sales
ME_UPN=$(az ad signed-in-user show --query userPrincipalName -o tsv)
ME_OID=$(az ad signed-in-user show --query id -o tsv)

az group create -n $RG -l $LOC

# Logical server with Microsoft Entra-only authentication (no SQL passwords)
az sql server create -g $RG -n $SERVER -l $LOC \
  --enable-ad-only-auth \
  --external-admin-principal-type User \
  --external-admin-name "$ME_UPN" \
  --external-admin-sid "$ME_OID"

Now the database: General Purpose, serverless, 2 vCores max, auto-pause after 60 minutes idle, locally redundant backups (cheapest for a lab).

az sql db create -g $RG -s $SERVER -n $DB \
  --edition GeneralPurpose --compute-model Serverless \
  --family Gen5 --capacity 2 --min-capacity 0.5 \
  --auto-pause-delay 60 \
  --backup-storage-redundancy Local

Tip

If your subscription is eligible for the Azure SQL Database free offer, you can add --use-free-limit --free-limit-exhaustion-behavior AutoPause (check the flags in your CLI version with az sql db create --help). The database then pauses instead of billing when the monthly free amount runs out.

Check what you created:

az sql db show -g $RG -s $SERVER -n $DB \
  --query "{name:name, tier:currentSku.tier, vcores:currentSku.capacity, status:status, autoPause:autoPauseDelay}" -o table

#Module 3: connect

#Open the firewall for your IP

By default nothing can connect. Add a rule for your client's public IP:

MYIP=$(curl -s https://api.ipify.org)
az sql server firewall-rule create -g $RG -s $SERVER -n allow-my-ip \
  --start-ip-address $MYIP --end-ip-address $MYIP

#Connect with your client

• Server: <server>.database.windows.net
• Authentication: Microsoft Entra MFA (SSMS) / Microsoft Entra ID - Universal with MFA, or "Microsoft Entra ID" in VS Code
• Database: sqldb-sales
• Encrypt: mandatory (default)

Run a first query:

SELECT DB_NAME() AS db, SUSER_SNAME() AS me, @@VERSION AS version,
       DATABASEPROPERTYEX(DB_NAME(), 'Edition') AS edition,
       DATABASEPROPERTYEX(DB_NAME(), 'ServiceObjective') AS slo;

Note

The first connection after auto-pause takes a little longer while the database resumes, and may fail once with a "not currently available" error. Retry after a few seconds. Applications should have retry logic for this and for transient errors in general.

#Module 4: create tables and load data

CREATE SCHEMA sales;
GO
CREATE TABLE sales.Customer (
    CustomerId   INT IDENTITY(1,1) CONSTRAINT PK_Customer PRIMARY KEY,
    Name         NVARCHAR(100) NOT NULL,
    City         NVARCHAR(60)  NOT NULL,
    CreatedAt    DATETIME2(0)  NOT NULL CONSTRAINT DF_Customer_Created DEFAULT SYSUTCDATETIME()
);

CREATE TABLE sales.[Order] (
    OrderId      INT IDENTITY(1,1) CONSTRAINT PK_Order PRIMARY KEY,
    CustomerId   INT NOT NULL CONSTRAINT FK_Order_Customer REFERENCES sales.Customer(CustomerId),
    OrderDate    DATE NOT NULL,
    Amount       DECIMAL(12,2) NOT NULL CONSTRAINT CK_Order_Amount CHECK (Amount >= 0)
);

CREATE INDEX IX_Order_Customer_Date ON sales.[Order] (CustomerId, OrderDate);

Load some sample rows with a set-based insert:

INSERT INTO sales.Customer (Name, City)
VALUES (N'Asha Rao', N'Pune'), (N'Ravi Kumar', N'Chennai'), (N'Meera Iyer', N'Bengaluru'),
       (N'John Smith', N'London'), (N'Sara Lee', N'Singapore');

-- 10,000 random orders spread over the last year
INSERT INTO sales.[Order] (CustomerId, OrderDate, Amount)
SELECT TOP (10000)
       1 + ABS(CHECKSUM(NEWID())) % 5,
       DATEADD(day, -(ABS(CHECKSUM(NEWID())) % 365), CAST(SYSUTCDATETIME() AS date)),
       CAST((ABS(CHECKSUM(NEWID())) % 100000) / 100.0 AS DECIMAL(12,2))
FROM sys.all_objects a CROSS JOIN sys.all_objects b;

Query it:

SELECT c.City, COUNT(*) AS Orders, SUM(o.Amount) AS Revenue
FROM sales.[Order] o
JOIN sales.Customer c ON c.CustomerId = o.CustomerId
WHERE o.OrderDate >= DATEADD(month, -3, CAST(SYSUTCDATETIME() AS date))
GROUP BY c.City
ORDER BY Revenue DESC;

#Other ways to load data

#Module 5: give other people and apps access

In Azure SQL Database, users are normally contained database users: they exist in the database, not as server logins.

-- A colleague (Microsoft Entra user) who may only read
CREATE USER [colleague@contoso.com] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [colleague@contoso.com];

-- An Entra group of analysts: grant once, manage membership in Entra ID
CREATE USER [sg-sales-analysts] FROM EXTERNAL PROVIDER;
GRANT SELECT ON SCHEMA::sales TO [sg-sales-analysts];

-- An application with a managed identity (e.g. an App Service called app-sales-api)
CREATE USER [app-sales-api] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [app-sales-api];
ALTER ROLE db_datawriter ADD MEMBER [app-sales-api];

Check who has what:

SELECT dp.name AS principal, dp.type_desc, r.name AS role
FROM sys.database_role_members rm
JOIN sys.database_principals r  ON r.principal_id = rm.role_principal_id
JOIN sys.database_principals dp ON dp.principal_id = rm.member_principal_id
ORDER BY principal;

#Module 6: backups and point-in-time restore

Azure SQL Database backs up automatically: full backups weekly, differentials every 12–24 hours, and log backups about every 10 minutes. The default point-in-time restore (PITR) retention is 7 days and can be set from 1 to 35 days.

az sql db str-policy show -g $RG -s $SERVER -n $DB -o table          # short-term retention
az sql db str-policy set  -g $RG -s $SERVER -n $DB --retention-days 14

#Practice a restore after an "accident"

1. Note the time in UTC, then make a mistake on purpose:

SELECT SYSUTCDATETIME() AS before_mistake;
DELETE FROM sales.[Order] WHERE OrderDate >= DATEADD(month, -1, CAST(SYSUTCDATETIME() AS date));

2. Restore the database as a new database to a time just before the delete (wait at least 10–15 minutes after creating the database so backups exist):

az sql db restore -g $RG -s $SERVER -n $DB \
  --dest-name sqldb-sales-restored \
  --time "2026-10-25T10:15:00Z"          # use your before_mistake time

3. Compare and copy back what you need:

-- in sqldb-sales-restored
SELECT COUNT(*) FROM sales.[Order];

PITR always creates a new database. To get rows back into the original, copy them across (for example export/import, a script, or a data pipeline), or swap the databases by renaming them.

Warning

Dropping the logical server deletes all its databases and their PITR backups. Deleted databases can be restored within their retention period; a deleted server can't be recovered by you. Protect production servers with a resource lock.

Long-term retention (LTR) keeps weekly, monthly or yearly full backups for up to 10 years; see the advanced session.

#Module 7: scale and monitor

#Scale

# Raise the serverless maximum to 4 vCores
az sql db update -g $RG -s $SERVER -n $DB --capacity 4

# Change auto-pause delay (minutes; -1 disables auto-pause)
az sql db update -g $RG -s $SERVER -n $DB --auto-pause-delay 120

Scaling is online. Connections drop briefly at the end, so apps need retry logic.

#Watch resource use

-- Last hour, 15-second intervals
SELECT TOP (20) end_time, avg_cpu_percent, avg_data_io_percent,
       avg_log_write_percent, avg_memory_usage_percent
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC;

-- Longest-running queries recorded by Query Store (on by default)
SELECT TOP (10) qt.query_sql_text,
       SUM(rs.count_executions) AS executions,
       AVG(rs.avg_duration) / 1000.0 AS avg_ms
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q          ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p           ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
GROUP BY qt.query_sql_text
ORDER BY avg_ms DESC;

In the portal, the database Overview and Metrics blades show CPU, data IO, log IO, storage and connections. Create one alert: CPU percentage > 80% for 15 minutes, sent to your email.

#Module 8: clean up

az group delete -n $RG --yes --no-wait

This removes the server, both databases and the firewall rules.

#Exercises

1. Create a database-level firewall rule with sp_set_database_firewall_rule, then remove your server-level rule. Can you still connect to sqldb-sales? To master?
2. Create a view sales.vRevenueByCity and grant SELECT on it (not on the tables) to a test Entra user. Confirm the user can't read sales.Order directly.
3. Leave the database idle and watch its status change to Paused in the portal. Then connect and time how long the first query takes.
4. Export the database to a .bacpac in a storage account (portal: Export), then import it as a new database.
5. Change the short-term retention to 3 days, then back to 7. What does that change affect?

#Knowledge check

1. What is a logical server, and what lives at server level rather than database level?
2. Why is serverless a good fit for a lab or a dev database, and when is provisioned compute better?
3. What is a contained database user, and why do we grant to Entra groups?
4. Point-in-time restore creates what, exactly? How do you get deleted rows back into the original database?
5. Why should every application connecting to Azure SQL Database have retry logic?

#Summary

• An Azure SQL Database lives on a logical server; firewall, admin and security settings sit at server level.
• Serverless General Purpose with auto-pause is a cheap way to learn and to run dev databases.
• Use Microsoft Entra ID sign-in, contained users, groups and managed identities instead of SQL passwords.
• Backups are automatic; point-in-time restore creates a new database, so practise it before you need it.
• Next step in the learning path: the advanced session on private networking, auditing, high availability with failover groups, performance tuning and CI/CD.

#Further reading

• Microsoft Learn: What is Azure SQL Database?, Serverless compute tier, Microsoft Entra authentication with Azure SQL, Automated backups, Restore a database from a backup.
• On this site: Azure SQL Database best practices and the Azure database service choice guide.