How to avoid database comparison between on-prem and azure sql after database restore

Aparna Manoharan 20

We have existing Azure data sync setup (in dev ,test and prod) between on-prem and azure sql .

As part of data refresh activity, lower env (dev and test) db will be refreshed (delete and recreate db) with prod data along with data sync objects in both on-prem and azure sql.

How to resume existing azure data sync after refresh activity assuming both source and target has exact same data.?

Does this require any reconfiguration to sync metadata.?

We have 150+ tables and we would like resume the sync operation without comparing row by row.

Vijayalaxmi Kattimani 490 Reputation points Microsoft Vendor

2024-11-13T17:19:54.4833333+00:00
Hi @Aparna Manoharan,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

We would like to inform you that, when refreshing your development and test databases with production data in Azure Data Sync, it is crucial to maintain the metadata and configuration so that syncing can resume smoothly without requiring a full data comparison. Here is how you can approach this:

Steps to Resume Azure Data Sync After Database Refresh

Backup Sync Metadata (Optional, but recommended): Before refreshing the dev and test databases, it may be useful to export or document the sync metadata from Azure SQL (Ex: the Sync Group, Sync Member configurations, etc.)

Recreate Data Sync Objects During Refresh: Ensure that all Data Sync objects, such as Sync Groups and Sync Members, are re-created in the refreshed databases. Since the dev and test databases are recreated, the existing metadata in the Sync Group will not immediately recognize these as the original sources.

Reconfigure Sync Members (if needed):After the refresh, go to the Azure Portal:

Delete and re-add Sync Members associated with the dev and test environments. This step is necessary because the database refresh changes the database instance, and the Sync Group may need to re-establish a link to these instances.

In each environment, re-add the Sync Member pointing to the refreshed database.

Initiate Sync Manually: After re-adding Sync Members, perform a manual sync in the Azure Portal for each environment. By initiating a sync, you can avoid a row-by-row comparison since Azure Data Sync will recognize the schema and data match (as they are refreshed from production) and resume syncing with minimal checks.

Verify Synchronization: Once the sync completes, verify that there are no issues by checking the sync logs for any errors or conflicts.

Tips to Minimize Overhead

Database Object Consistency: Ensure that the Sync Group configuration in Azure aligns exactly with the refreshed databases. The metadata consistency allows Azure Data Sync to avoid extensive data comparisons.

Permissions and Connections: Verify that the on-premises agent and Azure SQL database have the correct permissions and connections post-refresh.

This process should help maintain the sync without needing to reconfigure extensively or perform full table comparisons for each row.

I hope this information helps. Please do let us know if you have any further queries.
Aparna Manoharan 20 Reputation points

2024-11-13T17:50:02.64+00:00

Thank you for these suggestions.

How or what would be the best way to backup sync metadata?

1 answer

Vijayalaxmi Kattimani 490 Reputation points Microsoft Vendor

2024-11-14T13:42:37.4266667+00:00

Hi @Aparna Manoharan,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

We would like to inform you that, If you are refreshing the lower environment databases with production data and want to resume the existing Azure Data Sync setup, you can follow these troubleshooting steps:

1.Delete the existing sync group: Before you can resume the sync operation, you need to delete the existing sync group. You can do this by using the Azure portal or PowerShell.

2.Create a new sync group: After deleting the existing sync group, you can create a new sync group with the same configuration as the previous sync group. You can do this by using the Azure portal or PowerShell.

3.Initialize the sync: After creating the new sync group, you need to initialize the sync by running a full sync between the source and target databases. This will ensure that the data in the source and target databases is in sync and that the sync metadata is up to date.

4.Resume the sync: After initializing the sync, you can resume the sync operation by running incremental syncs between the source and target databases. This will ensure that any changes made to the source or target databases are synchronized in real time.

If this not help, If you have a support plan could you please file a support ticket for deeper investigation and do share the SR# with us? In case if you don't have a support plan please let us know here.

I hope, This response will address your query and helped you to overcome on your challenges.

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.
Please sign in to rate this answer.
Vijayalaxmi Kattimani 490 Reputation points Microsoft Vendor

2024-11-15T12:24:47.0533333+00:00

Hi @Aparna Manoharan,

Following up to see if the above answer was helpful.

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.

Aparna Manoharan 20 Reputation points

2024-11-15T14:57:54.4133333+00:00

Hi @Vijayalaxmi Kattimani ,

We tried taking back up of sync metadata and restore them after refresh . But this didnot work.

When Initial sync is triggered after db restore , there are new set up dss procedure gets created and new entry is made to scope_config_dss . And this creates Sync to do row by row comparison again.

Please let us know if there is any other way to avoid row by row comparison

Vijayalaxmi Kattimani 490 Reputation points Microsoft Vendor

2024-11-19T04:58:39.2966667+00:00

Hi @Aparna Manoharan,

If you have a support plan could you please file a support ticket for deeper investigation and do share the SR# with us? In case if you don't have a support plan please let us know here.

Vijayalaxmi Kattimani 490 Reputation points Microsoft Vendor

2024-11-20T09:30:19.33+00:00

Hi Aparna Manoharan,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Aparna Manoharan 20 Reputation points

2024-11-20T11:01:15.8033333+00:00

Hi @Vijayalaxmi Kattimani

I have tried to take back up of sync objects and import them after db is refreshed. But New config id and new data sync objects gets created everytime new sync group is created , which ignores the sync objects that was imported from backup.

And this is not helping in avoiding row by row comparison

Vijayalaxmi Kattimani 490 Reputation points Microsoft Vendor

2024-11-21T08:47:35.92+00:00

Hi @Aparna Manoharan,

If you have a support plan could you please file a support ticket for deeper investigation and do share the SR# with us? In case if you don't have a support plan please let us know here.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to avoid database comparison between on-prem and azure sql after database restore

1 answer

Your answer