Active geo-replication with ledger automatic digest management

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

Active geo-replication is a feature in Azure SQL Database and Azure SQL Managed Instance that lets you create a continuously synchronized readable secondary database for a primary database. Geo-replication is configured per database, and only supports manual failover. The failover groups feature allows you to manage the replication and failover of some or all databases on a logical server to a logical server in another region.

Combining active geo-replication and ledger automatic digest management requires some specific considerations. Database digests can be generated either automatically by the system or manually by the user. You can use them later to verify the integrity of the database. In this blog post, I will explain how it works and potential issues to look out for.

How it works

Replication across geographic regions is asynchronous for performance reasons and, therefore, the secondary database is slightly behind the primary. Ledger will only issue database digests for data that is replicated to geographic secondaries to guarantee that digests will never reference data that might be lost in case of a geographic failover. This only applies for automatic generation and storage of database digests.

Setup

In this example I have created 2 logical SQL Servers.

Ledgerdemoserver in North Europe region - primary
Ledgerdemoserverdr in East US region – secondary

On the primary server I have created a database, ContosoHR, with automatic digest configuration to a storage account.

Create failover group and perform a failover

In this step I’m going to create a failover group between the 2 servers for the ContosoHR database. As you can see in the picture below, my ledgerdemoserver is my primary server and ledgerdemoserverdr is my secondary server.

In a failover group, both primary and secondary databases will have the same digest path. Even when you perform a failover, the digest path doesn't change for both primary and secondary database. Primary and secondary are both writing to the same digest path.
Make sure that the secondary replica has sufficient RBAC permissions to write digests by adding it to the Storage Blob Data Contributor role. This permission is automatically set when you configure the storage path on the primary but needs to be done manually for the secondary replica.

When I perform a failover, meaning that ledgerdemoserverdr becomes my new primary, notice that digests are written under the original primary server name. There is no new folder for the secondary server created on the storage account. This is important otherwise we would have 2 locations to store the digests.

To verify this, execute the query below on the new primary replica, in my case ledgerdemoserverdr.

DECLARE @digest_locations NVARCHAR(MAX) = (SELECT * FROM sys.database_ledger_digest_locations FOR JSON AUTO, INCLUDE_NULL_VALUES); SELECT @digest_locations as digest_locations;

The result set should show the path to the primary server even though you are connected to the secondary replica.

[{"path":"https:\/\/ledgerdemdrstg.blob.core.windows.net\/sqldbledgerdigests\/ledgerdemoserver\/ContosoHR\/2024-01-10T14:27:14.7830000","last_digest_block_id":2,"is_current":true}]

Remove failover group or drop link

Now that we know the ledger digest management behavior when your database is part of a failover group or geo-replication, let’s have a look at what happens when we drop the link between the databases or remove the failover group.
Let’s switch back to the original situation where my ledgerdemoserver is the primary replica and my ledgerdemoserverdr is the secondary replica. Once the failback is done, I will remove the ContosoHR database from the failover group.

Removing a database from a failover group doesn't stop replication, and it doesn't delete the replicated database. You'll need to manually stop geo-replication. This is one of the limitations of a failover group. At this point, nothing changed for the ledger digest management for both primary and secondary replica.
To stop the geo-replication, in the Azure portal, go to the database, click on Replicas in the left ribbon, select the 3 dots of the secondary replica and click Stop Replication.

Both databases will now behave as primary databases. At that point the digest path of the previous secondary database will change. A new folder with the name of the secondary replica is created and we will add a folder RemovedSecondaryReplica to the path (see picture below).

Running the same query to fetch the digest location will now return 2 digest locations, which is expected.

DECLARE @digest_locations NVARCHAR(MAX) = (SELECT * FROM sys.database_ledger_digest_locations FOR JSON AUTO, INCLUDE_NULL_VALUES); SELECT @digest_locations as digest_locations;

[{"path":"https:\/\/ledgerdemdrstg.blob.core.windows.net\/sqldbledgerdigests\/ledgerdemoserver\/ContosoHR\/2024-01-10T14:27:14.7830000","last_digest_block_id":2,"is_current":false},{"path":"https:\/\/ledgerdemdrstg.blob.core.windows.net\/sqldbledgerdigests\/ledgerdemoserverdr\/ContosoHR\/RemovedSecondaryReplica\/2024-01-11T09:34:58.0000000","last_digest_block_id":3,"is_current":true}]

Tip: Always verify the digest locations before running the ledger verification process.

Conclusion

The digest path for both primary and secondary databases will point to the same location. Make sure that your secondary replica has the correct permissions on the storage account by adding it to the Storage Blob Data Contributor role. If the failover group is deleted or you drop the link, both databases will behave as primary databases. At that point the digest path of the previous secondary database will change, and we will add a folder RemovedSecondaryReplica to the path. As you can see ledger digest management in combination with geo-replication or failover groups requires a bit more attention but not too hard to handle.