Database backup and geo-replication in Azure - Mini Project: Build & Apply

Database backup and geo-replication

📖 Scenario: You are managing a cloud database for a global company. To ensure data safety and availability, you need to set up automatic backups and geo-replication to another region.

🎯 Goal: Build an Azure SQL Database resource with automated backups enabled and configure geo-replication to a secondary region.

📋 What You'll Learn

Create an Azure SQL Server resource with a specific name and location

Create an Azure SQL Database on the server with a defined edition and compute size

Enable automated backups with a retention period of 7 days

Configure geo-replication to a secondary region

💡 Why This Matters

🌍 Real World

Cloud databases need backups and geo-replication to protect data and ensure availability across regions.

💼 Career

Database administrators and cloud engineers often configure backups and geo-replication to meet business continuity requirements.

1

Create Azure SQL Server resource

Create an Azure SQL Server resource with the name myserver123 in the eastus region. Use myadmin as the administrator login and MyP@ssword123 as the administrator password.

Azure

# Your code here to create Azure SQL Server resource named myserver123 in eastus

Hint

Use the azurerm_sql_server resource with the specified properties.

2

Create Azure SQL Database with backup retention

Create an Azure SQL Database named mydatabase on the server myserver123 with the edition Standard and compute size S1. Set the backup retention period to 7 days.

Azure

resource "azurerm_sql_database" "mydb" {
  # Your code here to create database mydatabase with Standard edition and S1 compute size
  # Set backup retention days to 7
}

Hint

Use azurerm_sql_database resource and set backup_retention_days to 7.

3

Configure geo-replication to secondary region

Create a secondary Azure SQL Database named mydatabase-secondary in the westus region for geo-replication of the primary database mydatabase.

Azure

resource "azurerm_sql_database" "mydb_secondary" {
  # Your code here to create secondary database mydatabase-secondary in westus
  # Configure it as a geo-replica of mydatabase
}

Hint

Use create_mode = "Secondary" and source_database_id to link the geo-replica.

4

Finalize resource group and output server names

Add an output named primary_server_name that outputs the name of the primary SQL server myserver123. Also add an output named secondary_database_name that outputs the name of the secondary database mydatabase-secondary.

Azure

output "primary_server_name" {
  # Your code here to output primary server name
}

output "secondary_database_name" {
  # Your code here to output secondary database name
}

Hint

Use output blocks with value set to the resource names.

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