Database backup and geo-replication in Azure - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Geo-Replication Purpose

What is the primary benefit of enabling geo-replication for an Azure SQL Database?

A It provides a backup copy of the database in a different region for disaster recovery.

B It automatically scales the database compute resources based on usage.

C It encrypts the database data at rest and in transit.

D It compresses the database backups to save storage space.

💡 Hint

Think about what happens if the main region fails.

Geo-replication creates a readable secondary database in a different region to provide disaster recovery in case the primary region becomes unavailable.

❓ Configuration

intermediate

Configuring Automated Backups

Which Azure SQL Database setting controls the retention period for automated backups?

A Backup Retention Period

B Failover Group Configuration

C Backup Storage Redundancy

D Geo-Replication Mode

💡 Hint

It defines how long backups are kept before deletion.

The Backup Retention Period setting specifies how many days automated backups are stored, allowing point-in-time restore within that window.

❓ Architecture

advanced

Designing for High Availability with Geo-Replication

You need to design an Azure SQL Database solution that ensures minimal downtime during a regional outage. Which architecture best achieves this?

A Use geo-backups stored in the same region as the primary database.

B Use active geo-replication with readable secondary databases in multiple regions and configure automatic failover groups.

C Deploy a single database with zone-redundant configuration within one region.

D Use only automated backups with a 7-day retention period and restore manually if needed.

💡 Hint

Consider automatic failover and multiple regions.

Active geo-replication with failover groups provides automatic failover to a secondary region, minimizing downtime during outages.

❓ security

advanced

Securing Geo-Replicated Databases

Which practice ensures data security when using geo-replication for Azure SQL Databases?

A Store geo-replicated backups in public blob storage without access controls.

B Disable encryption to improve replication speed.

C Use unencrypted backups for faster restore times.

D Enable Transparent Data Encryption (TDE) on both primary and secondary databases.

💡 Hint

Think about protecting data at rest.

Transparent Data Encryption (TDE) encrypts data at rest on both primary and secondary databases, ensuring data security during geo-replication.

❓ service_behavior

expert

Behavior of Failover in Geo-Replication

During a failover event in an Azure SQL Database failover group, what happens to the DNS endpoint used by applications?

A The DNS endpoint is deleted and must be recreated after failover.

B The DNS endpoint remains pointed to the original primary database until manually updated.

C The DNS endpoint automatically redirects to the new primary database in the secondary region.

D The DNS endpoint switches to a random database in any region.

💡 Hint

Consider how applications maintain connectivity without changes.

The failover group DNS endpoint automatically redirects to the new primary database after failover, allowing applications to connect without changing connection strings.

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