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.

Practice

1. What is the main purpose of geo-replication in Azure SQL Database?

easy

A. To increase the database query speed

B. To compress the database backup files

C. To encrypt the database data at rest

D. To copy the database to another region for high availability

Solution

1. Step 1: Understand geo-replication concept

   Geo-replication copies your database to a different geographic region to ensure availability if one region fails.

2. Step 2: Compare options with geo-replication purpose

   Options B, C, and D describe backup compression, encryption, and performance, which are unrelated to geo-replication.

3. Final Answer:

   To copy the database to another region for high availability -> Option D

4. Quick Check:

   Geo-replication = High availability by copying database [OK]

Hint: Geo-replication means copying data to another region [OK]

Common Mistakes:

• Confusing geo-replication with backup compression
• Thinking geo-replication improves query speed
• Mixing encryption with replication

2. Which Azure CLI command is used to create a geo-replica of an existing Azure SQL Database named mydb in the eastus2 region?

easy

A. az sql db replica create --name mydb --resource-group mygroup --server myserver --partner-server eastus2server

B. az sql db replica create --name mydb --resource-group mygroup --server myserver --partner-server eastus2

C. az sql db replica create --name mydb --resource-group mygroup --server myserver --partner-server eastus2server --location eastus2

D. az sql db replica create --name mydb --resource-group mygroup --server myserver --partner-server eastus2server --geo-location eastus2

Solution

1. Step 1: Identify correct Azure CLI syntax for geo-replica

   The command az sql db replica create requires the partner server name, not just the region name.

2. Step 2: Analyze options for correct partner-server parameter

   az sql db replica create --name mydb --resource-group mygroup --server myserver --partner-server eastus2server uses eastus2server as partner-server, which is the correct server name format. az sql db replica create --name mydb --resource-group mygroup --server myserver --partner-server eastus2 uses region name incorrectly, A and D add unsupported parameters.

3. Final Answer:

   az sql db replica create --name mydb --resource-group mygroup --server myserver --partner-server eastus2server -> Option A

4. Quick Check:

   Partner server must be server name, not region [OK]

Hint: Partner server must be server name, not region name [OK]

Common Mistakes:

• Using region name instead of partner server name
• Adding unsupported parameters like --geo-location
• Confusing resource group with server name

3. Given this Azure CLI command output snippet after creating a geo-replica:

{
  "name": "mydb-replica",
  "location": "eastus2",
  "status": "Online",
  "replicationRole": "Secondary"
}

What does the replicationRole value indicate?

medium

A. The database is the primary writable copy

B. The database backup is in progress

C. The database is a read-only secondary replica

D. The database is offline and not replicating

Solution

1. Step 1: Understand replicationRole meaning

   In Azure SQL geo-replication, 'Secondary' means the database is a read-only copy that replicates changes from the primary.

2. Step 2: Match status with replicationRole

   Status 'Online' confirms the replica is active and available for read-only queries, not offline or backup state.

3. Final Answer:

   The database is a read-only secondary replica -> Option C

4. Quick Check:

   replicationRole 'Secondary' = read-only replica [OK]

Hint: Secondary role means read-only replica [OK]

Common Mistakes:

• Thinking 'Secondary' means primary writable copy
• Confusing backup status with replication role
• Assuming 'Secondary' means offline

4. You tried to create a geo-replica using this command:

az sql db replica create --name mydb --resource-group mygroup --server myserver --partner-server eastus2

But you get an error saying partner server not found. What is the likely cause?

medium

A. The partner server name is incorrect; it should be the server's actual name, not the region

B. The resource group name is invalid

C. The database name is missing

D. Geo-replication is not supported in eastus2 region

Solution

1. Step 1: Check partner-server parameter usage

   The partner-server parameter requires the actual server name, not the region name like 'eastus2'.

2. Step 2: Verify error cause

   Using 'eastus2' as partner-server causes the 'not found' error because Azure expects a server name, e.g., 'myserver-eastus2'.

3. Final Answer:

   The partner server name is incorrect; it should be the server's actual name, not the region -> Option A

4. Quick Check:

   Partner server must be server name, not region [OK]

Hint: Partner server must be server name, not region [OK]

Common Mistakes:

• Using region name instead of server name
• Assuming resource group or database name causes this error
• Believing geo-replication is unsupported in common regions

5. You want to ensure your Azure SQL Database backups are geo-redundant and can be restored in a different region if the primary region fails. Which backup configuration should you choose?

hard

A. Use Locally Redundant Storage (LRS) for faster backups

B. Use Geo-Redundant Backup (GRS) storage for automated backups

C. Manually copy backups to another region using Azure Storage Explorer

D. Disable automated backups and create manual backups only

Solution

1. Step 1: Understand backup redundancy options

   Geo-Redundant Storage (GRS) replicates backups to a secondary region automatically, ensuring recovery if primary region fails.

2. Step 2: Compare options for geo-redundancy

   LRS stores backups only in one region, manual copying is error-prone, and disabling automated backups risks data loss.

3. Final Answer:

   Use Geo-Redundant Backup (GRS) storage for automated backups -> Option B

4. Quick Check:

   Geo-redundant backups = automatic cross-region backup [OK]

Hint: Choose Geo-Redundant Storage for cross-region backup safety [OK]

Common Mistakes:

• Choosing LRS which stores backups only locally
• Relying on manual backup copies
• Disabling automated backups risking data loss