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

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 Output

{

  "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 Output

{

  "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 Output

{

  "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 Output

{

  "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 Output

{

  "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.