Database backup and geo-replication in Azure - Step-by-Step Execution

Process Flow - Database backup and geo-replication

Start Database

↓

Automatic Backup Enabled

↓

Backup Stored in Primary Region

↓

Geo-Replication Setup

↓

Backup Data Sent to Secondary Region

↓

Secondary Region Database Updated

↓

Failover Ready if Primary Fails

↓

End

The database runs with automatic backups stored in the primary region. Geo-replication sends backup data to a secondary region to keep a live copy ready for failover.

Execution Sample

Azure

az sql db create --name mydb --resource-group myrg --server myserver --backup-storage-redundancy Geo
az sql db replica create --name mydb-secondary --resource-group myrg --server myserver-secondary --partner-server myserver --source-database mydb

This creates a database with geo-redundant backup and sets up a geo-replica in a secondary region.

Process Table

Step	Action	Result	State Change
1	Create database with geo-redundant backup	Database created with backups stored in primary and geo-redundant storage	Backup enabled with geo-redundancy
2	Initiate geo-replication to secondary server	Secondary database replica creation started	Replica status: Creating
3	Backup data sent to secondary region	Secondary replica receives backup data	Replica status: Syncing
4	Secondary replica sync completes	Secondary database is up-to-date	Replica status: Ready
5	Primary database fails (simulated)	Failover triggered to secondary replica	Secondary becomes primary
6	Failover complete	Application connects to secondary region database	Failover successful
7	End	System running with secondary as primary	Stable state

💡 Failover completes and secondary replica becomes primary, ensuring high availability.

Status Tracker

Variable	Start	After Step 2	After Step 4	After Step 6	Final
Backup Status	Disabled	Enabled with geo-redundancy	Enabled with geo-redundancy	Enabled with geo-redundancy	Enabled with geo-redundancy
Replica Status	None	Creating	Ready	Failover triggered	Primary
Primary DB Region	Primary	Primary	Primary	Primary failed	Secondary
Secondary DB Region	None	Creating	Ready	Becoming primary	Primary

Key Moments - 3 Insights

Why does the replica status change from 'Creating' to 'Ready'?

Because the backup data has finished syncing to the secondary region, making the replica fully up-to-date as shown in execution_table step 4.

What happens to the primary database region after failover?

The primary region is considered failed and the secondary region takes over as primary, as shown in execution_table steps 5 and 6.

Is the backup stored only in the primary region?

No, with geo-redundant backup enabled, backups are stored in both primary and secondary regions, ensuring data safety.

Visual Quiz - 3 Questions

Look at the execution_table, what is the replica status after step 3?

A Creating

B Syncing

C Ready

D Failed

💡 Hint

Check the 'Replica Status' in execution_table row for step 3.

At which step does the failover to the secondary database occur?

A Step 5

B Step 6

C Step 4

D Step 7

💡 Hint

Look for 'Failover triggered' in the 'Result' column of execution_table.

If geo-redundant backup was not enabled, what would change in the variable_tracker?

A Replica Status would be 'Ready' immediately

B Primary DB Region would switch to secondary earlier

C Backup Status would remain 'Disabled' throughout

D Secondary DB Region would be 'Primary' from start

💡 Hint

Check 'Backup Status' changes in variable_tracker to understand backup enabling.

Concept Snapshot

Database backup and geo-replication:
- Enable geo-redundant backups to store data in multiple regions.
- Create geo-replica to keep a live copy in a secondary region.
- Backup data syncs continuously to replica.
- Failover switches primary to secondary if primary fails.
- Ensures high availability and disaster recovery.

Full Transcript

This visual execution shows how a database in Azure is backed up and geo-replicated. First, the database is created with geo-redundant backup enabled, storing backups in both primary and secondary regions. Then, a geo-replica is created on a secondary server. Backup data is sent and synced to the secondary region, updating the replica status from creating to ready. If the primary database fails, failover triggers, making the secondary replica the new primary. This process ensures the application stays available even if one region goes down. Variables like backup status, replica status, and primary region change step-by-step to reflect this flow.