Azure SQL Data Sync After a Database Restore: Troubleshooting Leftover Sync Metadata

Recently, I encountered an intriguing Azure SQL Data Sync challenge that I feel is worth sharing with everyone.

Initially, the situation seemed quite simple: a database had been restored from a different environment and was being set up as a Member database within a new Data Sync group. However, things didn’t work out as planned with the synchronization.

The surprising aspect wasn’t the new Sync Group itself, but rather what the restored database had carried over from its previous Data Sync setup.

The Scenario

The Member database was a restored version of a database that had previously been part of another Azure SQL Data Sync group.

After configuring the restored database as a Member in this new environment, we discovered that synchronization wasn’t functioning properly.

During our troubleshooting efforts, we uncovered an important clue: the restored database retained a history with Azure SQL Data Sync. We needed to check if the metadata and objects from its prior configuration were still intact.

This issue became one of the focus areas for our investigation.

Understanding Data Sync Architecture First

Before diving into any scripts, it’s crucial to identify the three roles that databases play in Azure SQL Data Sync:

• Hub Database: This is the main database that contains the application data being synchronized.
• Member Database: This database syncs with the Hub.
• Sync Metadata Database: This is a separate Azure SQL Database that holds Data Sync metadata and logs.

According to Microsoft’s documentation, Data Sync operates on a hub-and-spoke model, with the Sync Metadata Database being the repository for Data Sync metadata and logs.

This distinction was particularly important during our investigation. The initial diagnostic configuration was not referencing the expected Sync Metadata Database.

Troubleshooting Approach

Here’s the troubleshooting process we followed:

Step 1: Run the Azure SQL Data Sync Health Checker

One of the first tools I recommend for this kind of issue is the public Azure SQL Data Sync Health Checker:

Azure SQL Data Sync Health Checker on GitHub

This tool assesses whether the metadata for both the Hub and Member databases is correctly set up and compares the sync scopes against the Sync Metadata Database. A key feature of the Health Checker is that it validates information without altering Data Sync or user objects.

The tool requires connection details for:

• Sync Metadata Database
• Hub Database
• Member Database

Check the GitHub repository for the current script and instructions for execution.

What We Observed

During our investigation, the initial output from the Health Checker included messages such as:

WARNING: dss schema IS MISSING!

WARNING: TaskHosting schema IS MISSING!

Invalid object name ‘dss.syncgroup’.

Invalid object name ‘dss.userdatabase’.

These error messages provided important hints, but we needed to interpret them with the context of the database roles in mind.

According to Microsoft, the DataSync schema is utilized for system-created objects in Hub and Member databases, whereas the dss and TaskHosting schemas relate to objects in the Sync Metadata Database.

This understanding helped us confirm that we first needed to check which database was being presented to the Health Checker as the Sync Metadata Database.

Lesson Learned

Before jumping to conclusions about missing dss or TaskHosting schemas, ensure that you are indeed connected to the Sync Metadata Database.

This initial verification can save a lot of time in your investigation.

Step 2: Inspect the Member Database for Data Sync Artifacts

Once we clarified the architecture, we turned our attention to the restored Member database.

Since this database had previously been in Data Sync, we wanted to identify any remaining Data Sync objects.

One of the SQL queries we used during troubleshooting was:

SELECT name
FROM sys.tables
WHERE SCHEMA_NAME(schema_id) = 'DataSync'
  AND name NOT LIKE '%_tracking%';

We also checked:

SELECT *
FROM DataSync.schema_info_dss;

And for the Data Sync scope information in the Member database:

SELECT *
FROM DataSync.scope_info_dss;

These checks helped us gauge the Data Sync metadata status of the restored Member database, revealing whether it still retained artifacts from its previous setup.

Why This Matters

Microsoft’s current best practices for Data Sync suggest that the DataSync schema is crucial for understanding system-created objects in the Hub and Member databases.

Therefore, when dealing with a restored database that has been part of Data Sync in the past, the DataSync schema is a critical element of your analysis.

Step 3: Consider the History of the Restored Database

This was a pivotal moment in our investigation.

Instead of viewing the database simply as a “new Member,” we began to see it as:

A restored database that had previously been set up for another Data Sync configuration.

This distinction is crucial.

When troubleshooting a restored database, it’s wise to ask early on:

Has this database been part of another Azure SQL Data Sync group before?

If the answer is yes, the previous Data Sync state of the database should be a major consideration in your investigation.

Step 4: Remove the Member Before Cleanup

In our situation, the remediation steps were essentially as follows:

1. Remove the restored database from the current Sync Group.
2. Clean up the old Data Sync metadata from the restored Member database.
3. Add the database back as a Member.
4. Start the synchronization again.

We used the following publicly available repository for cleanup:

SQL Data Sync Cleanup Scripts on GitHub

This repository offers various scripts for different scenarios, including:

• Data Sync complete cleanup.sql
• Data Sync cleanup hub or member.sql
• cleanup data sync object V2.sql

You can find the specific complete-cleanup script here:

Data Sync complete cleanup.sql

Important Warning About the Cleanup Script

It’s crucial to not treat this as a general-purpose Data Sync troubleshooting script.

The script carries a warning that it will immediately remove Data Sync-related objects from the database and should only be used in specific scenarios that are detailed in the script, including when advised by support during a help request.

The repository also explains the different purposes of its cleanup scripts. For instance, the complete-cleanup script has stricter usage guidelines, while the Hub/Member cleanup and object cleanup scripts target different situations.

My Recommendation: Start with the Health Checker and read-only diagnostic queries. Don’t rush into metadata cleanup without fully grasping the database architecture and existing Data Sync configurations.

Step 5: Validate with One Table

After addressing the old Data Sync artifacts, we didn’t assume everything was fixed right away.

Instead, we conducted a controlled test.

We truncated a test table on the Member side, reinitiated synchronization, and the table began to sync successfully.

This provided the reassurance we needed that the previous Data Sync state of the restored Member database was indeed a key part of the problem.

Root Cause

Our troubleshooting pinpointed that the restored Member database had prior involvement in another Data Sync setup and retained its associated metadata and artifacts.

After cleaning up the old Data Sync state and reconfiguring the restored database as a Member, we successfully verified synchronization again.

The key takeaway for me wasn’t just the cleanup itself, but the realization that restoring a database doesn’t automatically equate to starting with a fresh Data Sync state.

A Practical Troubleshooting Flow

<pFor similar scenarios, here’s how I would approach the investigation:

1. Confirm the architecture

Identify the:

• Hub Database
• Member Database
• Sync Metadata Database

2. Run the Health Checker

Use the tool from the Microsoft Azure SQL Data Sync Health Checker.

Review the output before implementing any changes.

3. Inspect the Restored Member

Check for existing Data Sync-related objects:

SELECT name
FROM sys.tables
WHERE SCHEMA_NAME(schema_id) = 'DataSync'
  AND name NOT LIKE '%_tracking%';

Next, check applicable database states:

SELECT *
FROM DataSync.schema_info_dss;

And:

SELECT *
FROM DataSync.scope_info_dss;

4. Ask About the Database History

Was the database:

• Restored from a different environment?
• Previously a Data Sync Member?
• Linked to another Sync Group?

That historical context can significantly alter the direction of your investigation.

5. Consider Cleanup Only After Understanding the Environment

Find the public cleanup scripts here:

SQL Data Sync Cleanup Scripts

These scripts modify Data Sync objects and should not be the first step in your troubleshooting process.

6. Validate Using a Controlled Test

Once the environment has been cleaned or reconfigured, validate synchronization in a controlled scope before concluding that the entire setup is in good shape.

My Key Takeaways

This troubleshooting experience reinforced several lessons:

1. A Restored Database Isn’t Necessarily a Clean Data Sync Database

Restoring application data doesn’t mean you should disregard the database’s past synchronization setup.

2. Always Distinguish Between Hub, Member, and Sync Metadata Databases

This distinction is especially vital when interpreting Health Checker results.

The DataSync schema relates to system-created objects in Hub and Member databases, while dss and TaskHosting pertain to objects in the Sync Metadata Database.

3. Use Diagnostics before Cleanup

The Azure SQL Data Sync Health Checker is designed to assess Data Sync metadata and objects without making any changes.

This makes it a more suitable starting point compared to immediately deleting objects.

4. Database History Matters

One of my go-to questions now is:

“Has this database ever been part of another Data Sync Group?”

It’s a simple question, but in restore or copy scenarios, it can uncover a critical aspect of the troubleshooting process.

5. Exercise Caution with Metadata Cleanup

The public cleanup repository provides specific guidance on when to use each script, and the complete-cleanup script includes a clear warning before execution.

Always understand your environment and safeguard your data before undertaking destruction tasks.

One More Important Consideration: SQL Data Sync Retirement

There’s also a significant long-term architectural factor to bear in mind.

According to Microsoft, SQL Data Sync will be retired on September 30, 2027. Existing Sync Groups can keep functioning until this date, but Microsoft suggests exploring alternative data replication and synchronization methods before then.

Therefore, while it remains necessary to troubleshoot existing Data Sync operations, organisations currently utilising the service should begin planning their migration strategy.

References and Useful Tools

Microsoft Documentation

Troubleshooting Tools