How to take back up of azure data sync metadata

Question

We are have azure sql db which is part of azure data sync group.

This db will be refreshed as part of environment refresh and restored again

To avoid row by row comparisons again should we take back up of data sync metadata before refresh? If so how to take backup of data sync metadata ?

Answer 1

Hi @Aparna Manoharan,

Thanks for the question and using MS Q&A platform.

Yes, you should take a backup of the Data Sync metadata before refreshing the Azure SQL Database, especially if you're concerned about having to perform row-by-row comparisons after the environment is refreshed. When you refresh a database in Azure SQL, the metadata related to Azure Data Sync (such as synchronization settings, conflict resolution policies, and the state of syncs) might be lost or reset, so backing it up ensures that you can restore your sync configurations quickly after the refresh.

You can take a full backup of the Data Sync metadata by exporting the data from these tables. There are a couple of ways to do this:

# This script exports the sync configuration of your Sync Group:

Export-AzSqlSyncGroup -ResourceGroupName "your-resource-group" `
                      -ServerName "your-server-name" `
                      -DatabaseName "your-database-name" `
                      -SyncGroupName "your-sync-group-name" `
                      -ExportFilePath "path-to-save-the-configuration"

# This script imports the sync configuration to your Sync Group:

Import-AzSqlSyncGroup -ResourceGroupName "your-resource-group" `
                      -ServerName "your-server-name" `
                      -DatabaseName "your-database-name" `
                      -SyncGroupName "your-sync-group-name" `
                      -ImportFilePath "path-of-the-exported-configuration"

These steps ensure that your sync group configuration is restored without needing row-by-row comparisons again.

Hope this helps. Do let us know if you any further queries. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

Hello Aparna Manoharan,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that you would like to know how you can take back up of azure data sync metadata.

Yes, you need the backup of the data sync metadata to avoid row-by-row comparisons after refreshing your Azure SQL Database that’s part of an Azure Data Sync group, you should back up the data sync metadata. First, export the sync group metadata using the Export-AzSqlSyncGroup cmdlet:

Export-AzSqlSyncGroup -ResourceGroupName "YourResourceGroupName" -ServerName "YourServerName" -DatabaseName "YourDatabaseName" -SyncGroupName "YourSyncGroupName" -FilePath "C:\Path\To\Backup\SyncGroupMetadata.json"

If you have a dedicated sync metadata database, back it up with the Backup-AzSqlDatabase cmdlet:

Backup-AzSqlDatabase -ResourceGroupName "YourResourceGroupName" -ServerName "YourServerName" -DatabaseName "YourSyncMetadataDatabaseName" -StorageAccountName "YourStorageAccountName" -StorageContainerName "YourContainerName" -StorageKeyType "StorageAccessKey"

After the environment refresh, restore the sync metadata using the Import-AzSqlSyncGroup cmdlet:

Import-AzSqlSyncGroup -ResourceGroupName "YourResourceGroupName" -ServerName "YourServerName" -DatabaseName "YourDatabaseName" -FilePath "C:\Path\To\Backup\SyncGroupMetadata.json"

This make sures your sync metadata is preserved and can be restored efficiently.

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.