Bird
Raised Fist0
Azurecloud~5 mins

Database backup and geo-replication in Azure - Commands & Configuration

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime

Start learning this pattern below

Jump into concepts and practice - no test required

or
Recommended
Test this pattern10 questions across easy, medium, and hard to know if this pattern is strong
Introduction
Backing up your database protects your data from loss. Geo-replication copies your database to another region to keep it safe and available even if one location fails.
When you want to protect your data from accidental deletion or corruption by saving copies.
When you need your database to keep working even if one data center goes down.
When you want to quickly recover your database to a previous state after a problem.
When you want to serve users from different regions with faster access to data.
When you want to meet rules that require data to be stored in multiple locations.
Config File - azure-db-backup-geo.json
azure-db-backup-geo.json
{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "serverName": {
      "type": "string",
      "defaultValue": "myazuresqlserver"
    },
    "databaseName": {
      "type": "string",
      "defaultValue": "mydatabase"
    },
    "secondaryRegion": {
      "type": "string",
      "defaultValue": "eastus2"
    }
  },
  "resources": [
    {
      "type": "Microsoft.Sql/servers/databases",
      "apiVersion": "2021-02-01-preview",
      "name": "[concat(parameters('serverName'), '/', parameters('databaseName'))]",
      "location": "eastus",
      "properties": {
        "createMode": "Default"
      }
    },
    {
      "type": "Microsoft.Sql/servers/databases/replicationLinks",
      "apiVersion": "2021-02-01-preview",
      "name": "[concat(parameters('serverName'), '/', parameters('databaseName'), '/secondaryLink')]",
      "dependsOn": [
        "[concat('Microsoft.Sql/servers/databases/', parameters('serverName'), '/', parameters('databaseName'))]"
      ],
      "properties": {
        "partnerServer": "myazuresqlserver-secondary",
        "partnerDatabase": "mydatabase",
        "partnerLocation": "[parameters('secondaryRegion')]",
        "replicationMode": "Geo"
      }
    }
  ],
  "outputs": {}
}

This JSON template creates an Azure SQL database and sets up geo-replication to a secondary server in another region.

parameters: Define server name, database name, and secondary region.

resources: First resource creates the primary database. Second resource creates a replication link to the secondary server for geo-replication.

This setup ensures your database is backed up and copied to another region automatically.

Commands
Create an Azure SQL server in the East US region with admin credentials.
Terminal
az sql server create --name myazuresqlserver --resource-group myResourceGroup --location eastus --admin-user adminuser --admin-password StrongP@ssw0rd!
Expected OutputExpected
{ "fullyQualifiedDomainName": "myazuresqlserver.database.windows.net", "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/myResourceGroup/providers/Microsoft.Sql/servers/myazuresqlserver", "location": "eastus", "name": "myazuresqlserver", "resourceGroup": "myResourceGroup", "state": "Ready", "type": "Microsoft.Sql/servers" }
→
--name - Sets the server name
→
--resource-group - Specifies the resource group
→
--location - Sets the server region
Create a database named 'mydatabase' on the server with a basic performance level.
Terminal
az sql db create --resource-group myResourceGroup --server myazuresqlserver --name mydatabase --service-objective S0
Expected OutputExpected
{ "collation": "SQL_Latin1_General_CP1_CI_AS", "creationDate": "2024-06-01T12:00:00Z", "databaseId": "12345678-1234-1234-1234-123456789abc", "edition": "Standard", "name": "mydatabase", "resourceGroup": "myResourceGroup", "status": "Online", "type": "Microsoft.Sql/servers/databases" }
→
--name - Sets the database name
→
--service-objective - Defines performance tier
Create a secondary Azure SQL server in East US 2 region for geo-replication.
Terminal
az sql server create --name myazuresqlserver-secondary --resource-group myResourceGroup --location eastus2 --admin-user adminuser --admin-password StrongP@ssw0rd!
Expected OutputExpected
{ "fullyQualifiedDomainName": "myazuresqlserver-secondary.database.windows.net", "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/myResourceGroup/providers/Microsoft.Sql/servers/myazuresqlserver-secondary", "location": "eastus2", "name": "myazuresqlserver-secondary", "resourceGroup": "myResourceGroup", "state": "Ready", "type": "Microsoft.Sql/servers" }
→
--location - Sets the secondary server region
Create a geo-replica of the primary database on the secondary server.
Terminal
az sql db replica create --resource-group myResourceGroup --server myazuresqlserver --name mydatabase --partner-server myazuresqlserver-secondary --partner-database mydatabase
Expected OutputExpected
{ "name": "mydatabase", "status": "Online", "replicationRole": "Secondary", "type": "Microsoft.Sql/servers/databases" }
→
--partner-server - Specifies the secondary server for replication
→
--partner-database - Specifies the secondary database name
Check the status of the geo-replicated database on the secondary server.
Terminal
az sql db show --resource-group myResourceGroup --server myazuresqlserver-secondary --name mydatabase
Expected OutputExpected
{ "name": "mydatabase", "status": "Online", "replicationRole": "Secondary", "type": "Microsoft.Sql/servers/databases" }
Key Concept

If you remember nothing else from this pattern, remember: geo-replication keeps a live copy of your database in another region to protect against failures.

Common Mistakes
Trying to create a geo-replica before the secondary server exists
The replication command fails because it needs a ready secondary server to connect to.
Always create and verify the secondary server before setting up geo-replication.
Using weak admin passwords when creating servers
Azure rejects weak passwords for security, causing server creation to fail.
Use strong passwords with uppercase, lowercase, numbers, and symbols.
Not checking the replication status after creation
You might think replication is ready when it is still initializing or failed.
Run 'az sql db show' on the secondary database to confirm it is online and secondary.
Summary
Create primary and secondary Azure SQL servers in different regions.
Create a database on the primary server.
Set up geo-replication to copy the database to the secondary server.
Verify the secondary database is online and replicating.

Practice

(1/5)
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