Skip to content

Latest commit

 

History

History
313 lines (209 loc) · 15.7 KB

active-geo-replication-configure-portal.md

File metadata and controls

313 lines (209 loc) · 15.7 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic ms.custom
Tutorial: Geo-replication & failover in portal
Learn how to configure geo-replication for a SQL database using the Azure portal or Azure CLI, and initiate failover.
rajeshsetlem
rsetlem
wiassaf, mathoma, randolphwest
10/08/2024
azure-sql-database
high-availability
tutorial
sqldbrb=1
devx-track-azurecli
ignite-2023
devx-track-azurepowershell

Tutorial: Configure active geo-replication and failover (Azure SQL Database)

[!INCLUDE appliesto-sqldb]

This article shows you how to configure active geo-replication and initiate a failover for Azure SQL Database by using the Azure portal, PowerShell, or the Azure CLI.

Active geo-replication is configured per database. To fail over a group of databases, or if your application requires a stable connection endpoint, consider Failover groups instead.

Prerequisites

Add a secondary database

The following steps create a new secondary database in a geo-replication partnership.

To add a secondary database, you must be the subscription owner or co-owner.

The secondary database has the same name as the primary database and has, by default, the same service tier and compute size. The secondary database can be a single database or a pooled database. For more information, see DTU-based purchasing model overview and vCore-based purchasing model. After the secondary is created and seeded, data begins replicating from the primary database to the new secondary database.

If your secondary replica is used only for disaster recovery (DR), and doesn't have any read or write workloads, you can save on licensing costs by designating the database for standby when you configure a new active geo-replication relationship. For more information, see license-free standby replica.

Note

If the partner database already exists, (for example, as a result of terminating a previous geo-replication relationship) the command fails.

Portal

  1. In the Azure portal, browse to the database that you want to set up for geo-replication.

  2. On the SQL Database page, select your database, scroll to Data management, select Replicas, and then select Create replica.

    :::image type="content" source="media/active-geo-replication-configure-portal/azure-cli-create-geo-replica.png" alt-text="Screenshot that shows the Configure geo-replication option.":::

  3. Select your geo-secondary database Subscription and Resource group.

    :::image type="content" source="media/active-geo-replication-configure-portal/subscription-resource-group.png" alt-text="Screenshot from the Azure portal of the subscription and resource group." lightbox="media/active-geo-replication-configure-portal/subscription-resource-group.png":::

  4. Select or create the server for the secondary database, and configure the Compute + storage options if necessary. You can select any region for your secondary server, but we recommend the paired region.

    Optionally, you can add a secondary database to an elastic pool. To create the secondary database in a pool, select Yes next to Want to use SQL elastic pool? and select a pool on the target server. A pool must already exist on the target server. This workflow doesn't create a pool.

  5. Select Review + create, review the information, and then select Create.

  6. The secondary database is created and the deployment process begins.

    :::image type="content" source="media/active-geo-replication-configure-portal/azure-portal-geo-replica-deployment.png" alt-text="Screenshot that shows the deployment status of the secondary database." lightbox="media/active-geo-replication-configure-portal/azure-portal-geo-replica-deployment.png":::

  7. When the deployment is complete, the secondary database displays its status.

    :::image type="content" source="media/active-geo-replication-configure-portal/azure-portal-sql-database-secondary-status.png" alt-text="Screenshot that shows the secondary database status after deployment." lightbox="media/active-geo-replication-configure-portal/azure-portal-sql-database-secondary-status.png":::

  8. Return to the primary database page, and then select Replicas. Your secondary database is listed under Geo replicas.

    :::image type="content" source="media/active-geo-replication-configure-portal/azure-sql-db-geo-replica-list.png" alt-text="Screenshot that shows the SQL database primary and geo replicas.":::

Azure CLI

Select the database you want to set up for geo-replication. You need the following information:

  • Your original Azure SQL database name.
  • The Azure SQL server name.
  • Your resource group name.
  • The name of the server to create the new replica in.

Note

The secondary database must have the same service tier as the primary.

You can select any region for your secondary server, but we recommend the paired region.

Run the az sql db replica create command.

az sql db replica create --resource-group ContosoHotel --server contosoeast --name guestlist --partner-server contosowest --family Gen5 --capacity 2 --secondary-type Geo

Optionally, you can add a secondary database to an elastic pool. To create the secondary database in a pool, use the --elastic-pool parameter. A pool must already exist on the target server. This workflow doesn't create a pool.

The secondary database is created and the deployment process begins.

When the deployment is complete, you can check the status of the secondary database by running the az sql db replica list-links command:

az sql db replica list-links --name guestlist --resource-group ContosoHotel --server contosowest

PowerShell

Select the database you want to set up for geo-replication. You need the following information:

  • Your original Azure SQL database name.
  • The Azure SQL server name.
  • Your resource group name.
  • The name of the server to create the new replica in.

Note

The secondary database must have the same service tier as the primary.

You can select any region for your secondary server, but we recommend the paired region.

As usual, begin your PowerShell session with the following cmdlets to connect your Azure account and set the subscription context:

Connect-AzAccount
$subscriptionid = <your subscription id here>
Set-AzContext -SubscriptionId $subscriptionid

$parameters = @{
    ResourceGroupName = 'PrimaryRG'
    ServerName = 'PrimaryServer'
    DatabaseName = 'TestDB'
    PartnerResourceGroupName = 'SecondaryRG'
    PartnerServerName = 'SecondaryServer'
    PartnerDatabaseName = 'TestDB'
}

New-AzSqlDatabaseSecondary @parameters

When the deployment is complete, you can check the status of the secondary database by running the Get-AzSqlDatabaseReplicationLink command:

$parameters = @{
    ResourceGroupName = 'PrimaryRG'
    ServerName = 'PrimaryServer'
    DatabaseName = 'TestDB'
    PartnerResourceGroupName = 'SecondaryRG'
}

Get-AzSqlDatabaseReplicationLink @parameters

Initiate a failover

The secondary database can be switched to become the primary.

Portal

  1. In the Azure portal, browse to the primary database in the geo-replication partnership.

  2. Scroll to Data management, and then select Replicas.

  3. In the Geo replicas list, select the database you want to become the new primary, select the ellipsis, and then select Forced failover.

    :::image type="content" source="media/active-geo-replication-configure-portal/azure-portal-select-forced-failover.png" alt-text="Screenshot that shows selecting forced failover from the drop-down." lightbox="media/active-geo-replication-configure-portal/azure-portal-select-forced-failover.png":::

  4. Select Yes to begin the failover.

Azure CLI

Run the az sql db replica set-primary command.

az sql db replica set-primary --name guestlist --resource-group ContosoHotel --server contosowest

PowerShell

Run the following command:

$parameters = @{
    ResourceGroupName = 'SecondaryRG'
    ServerName = 'SecondaryServer'
    DatabaseName = 'TestDB'
    PartnerResourceGroupName = 'PrimaryServer'
}

Set-AzSqlDatabaseSecondary @parameters -Failover

The command immediately switches the secondary database into the primary role. This process normally should complete within 30 seconds or less.

Both databases are unavailable, for up to 25 seconds, while the roles are switched. If the primary database has multiple secondary databases, the command automatically reconfigures the other secondaries to connect to the new primary. The entire operation should take less than a minute to complete under normal circumstances.

Remove secondary database

This operation permanently stops the replication to the secondary database, and changes the role of the secondary to a regular read-write database. If the connectivity to the secondary database is broken, the command succeeds but the secondary doesn't become read-write until after connectivity is restored.

Portal

  1. In the Azure portal, browse to the primary database in the geo-replication partnership.
  2. Select Replicas.
  3. In the Geo replicas list, select the database you want to remove from the geo-replication partnership, select the ellipsis, and then select Stop replication.
  4. A confirmation window opens. Select Yes to remove the database from the geo-replication partnership. (Set it to a read-write database that isn't part of any replication.)

Azure CLI

Run the az sql db replica delete-link command.

az sql db replica delete-link --name guestlist --resource-group ContosoHotel --server contosoeast --partner-server contosowest

Confirm that you want to perform the operation.

PowerShell

Run the following command:

$parameters = @{
    ResourceGroupName = 'SecondaryRG'
    ServerName = 'SecondaryServer'
    DatabaseName = 'TestDB'
    PartnerResourceGroupName = 'PrimaryRG'
    PartnerServerName = 'PrimaryServer'
}
Remove-AzSqlDatabaseSecondary @parameters

Cross-subscription geo-replication

  • To create a geo-secondary replica in a subscription different from the subscription of the primary in the same Microsoft Entra tenant, you can use the Azure portal or the steps in this section.
  • To create a geo-secondary replica in a subscription different from the subscription of the primary in a different Microsoft Entra tenant, you must use SQL authentication and T-SQL as described in the steps in this section. Microsoft Entra authentication for Azure SQL for cross-subscription geo-replication isn't supported when a logical server is in a different Azure tenant

  1. Add the IP address of the client machine executing the T-SQL commands in this example, to the server firewalls of both the primary and secondary servers. You can confirm that IP address by executing the following query while connected to the primary server from the same client machine.

    SELECT client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

    For more information, see Azure SQL Database and Azure Synapse IP firewall rules.

  2. In the master database on the primary server, create a SQL authentication login dedicated to active geo-replication setup. Adjust login name and password as needed.

    CREATE LOGIN geodrsetup
    WITH PASSWORD = 'ComplexPassword01';

  3. In the same database, create a user for the login, and add it to the dbmanager role:

    CREATE USER geodrsetup FOR LOGIN geodrsetup;

ALTER ROLE dbmanager ADD MEMBER geodrsetup;

  4. Take note of the SID value of the new login. Obtain the SID value using the following query.

    SELECT sid
FROM sys.sql_logins
WHERE name = 'geodrsetup';

  5. Connect to the primary database (not the master database), and create a user for the same login.

    CREATE USER geodrsetup FOR LOGIN geodrsetup;

  6. In the same database, add the user to the db_owner role.

    ALTER ROLE db_owner ADD MEMBER geodrsetup;

  7. In the master database on the secondary server, create the same login as on the primary server, using the same name, password, and SID. Replace the hexadecimal SID value in the sample command below with the one obtained in Step 4.

    CREATE LOGIN geodrsetup
    WITH PASSWORD = 'ComplexPassword01', SID = 0x010600000000006400000000000000001C98F52B95D9C84BBBA8578FACE37C3E;

  8. In the same database, create a user for the login, and add it to the dbmanager role.

    CREATE USER geodrsetup FOR LOGIN geodrsetup;

ALTER ROLE dbmanager ADD MEMBER geodrsetup;

  9. Connect to the master database on the primary server using the new geodrsetup login, and initiate geo-secondary creation on the secondary server. Adjust database name and secondary server name as needed. Once the command is executed, you can monitor geo-secondary creation by querying the sys.dm_geo_replication_link_status view in the primary database, and the sys.dm_operation_status view in the master database on the primary server. The time needed to create a geo-secondary depends on the primary database size.

    alter database [dbrep] add secondary on server [servername];

  10. After the geo-secondary is successfully created, the users, logins, and firewall rules created by this procedure can be removed.

Related content