I need to sync Hub database to on-prem SQL express using Data Sync 2.0 agent but no tables are showing for both.

Question

I need to sync Hub database to on-prem SQL express using Data Sync 2.0 agent but no tables are showing for both.

MTBmojave80 40

I'm trying to sync the Azure SQL database to the on-prem member SQL express and all the necessary configurations (details below) are all configured and setup, but when I select either the Hub database or the on-prem member database, refresh the schema and not tables are showing.

1.) All accounts that I'm using are all local, I'm using Azure local SQL account that I created and the on-prem are local account.
2.) The data sync agent is using a local account and port 1433 is active and enabled.
3.) The sync agent key is correct and the host is reachable.
4.) The firewall is turn off on the on-prem computer running Windows 10 Pro.
5.) A new sync group is created and 2 databases are shown, when I select the tables for both the Hub database or the on-prem member, I clicked on refresh schema - no results are found.

I'm exhausted already from to much troubleshooting and can no longer find any idea, has anyone ran into the same issue and how you identify resolving this situation?

MTBmojave80 40 Reputation points

2023-05-31T22:27:19.2+00:00

I tried to repeat the sync agent again to the host making sure the authentication is correct and I was able to test the connection successfully. But the tables were not displaying the results for both on-prem and Azure SQL. The primary keys are assign to the tables and I have explored further with the permission. When I added the db owner role membership to both the on-prem and Azure SQL database to the account that I was using, I finally were able to see the result to the tables.

Final question, do you think syncagent account require DB_owner as a role membership or a minor membership such as the db_datareader or db_accessadmin, etc? Please let me know.

I appreciate everyone's responses.
SSingh-MSFT 16,371 Reputation points Moderator

2023-06-01T11:23:11.0333333+00:00

Hi MTBmojave80 •,

Just checking in to see if the below answer by Alberto on your follow up quey helped. If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-06-01T20:33:34.74+00:00
Final question, do you think syncagent account require DB_owner as a role membership or a minor membership such as the db_datareader or db_accessadmin, etc? Please let me know.

I don't know anything about the Sync Agent, but it it is only a matter of the Agent being able to see table definitions, this should suffice:

GRANT VIEW DEFINITION TO <account for Sync Agent>

This gives the account to see all definitions in the database.

If the Agent also creates objects, it would need permissions on the schema where it creates the tables. (I take for granted that it does not create tables in the dbo schema.)

That would be something like:

GRANT CREATE TABLE TO <SyncAgent> GRANT ALTER ON SCHEMA::<schemaname> TO <SyncAgent>

In no situation should memebership in db_owner be requires. In that case, something is broken.

Accepted answer

2 additional answers

Your answer

MTBmojave80 40 Reputation points

2023-05-31T22:27:19.2+00:00

I tried to repeat the sync agent again to the host making sure the authentication is correct and I was able to test the connection successfully. But the tables were not displaying the results for both on-prem and Azure SQL. The primary keys are assign to the tables and I have explored further with the permission. When I added the db owner role membership to both the on-prem and Azure SQL database to the account that I was using, I finally were able to see the result to the tables.

Final question, do you think syncagent account require DB_owner as a role membership or a minor membership such as the db_datareader or db_accessadmin, etc? Please let me know.

I appreciate everyone's responses.
SSingh-MSFT 16,371 Reputation points Moderator

2023-06-01T11:23:11.0333333+00:00

Hi MTBmojave80 •,

Just checking in to see if the below answer by Alberto on your follow up quey helped. If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-06-01T20:33:34.74+00:00

Final question, do you think syncagent account require DB_owner as a role membership or a minor membership such as the db_datareader or db_accessadmin, etc? Please let me know.

I don't know anything about the Sync Agent, but it it is only a matter of the Agent being able to see table definitions, this should suffice:

GRANT VIEW DEFINITION TO <account for Sync Agent>

This gives the account to see all definitions in the database.

If the Agent also creates objects, it would need permissions on the schema where it creates the tables. (I take for granted that it does not create tables in the dbo schema.)

That would be something like:

GRANT CREATE TABLE TO <SyncAgent> GRANT ALTER ON SCHEMA::<schemaname> TO <SyncAgent>

In no situation should memebership in db_owner be requires. In that case, something is broken.

Answer 1

Alberto Morillo 34,671 MVP Volunteer Moderator

For permissions needed by Azure SQL Data Sync, please refer to this forum thread.

Hope it helps.

Answer 2

Alberto Morillo 34,671 MVP Volunteer Moderator

Please make sure all tables you would like to sync have a primary key. Usually that is the reason that SQL Data Sync does not load the schema and tables.

Missing clustered indexes on the schemas may also trigger the same issue.

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-05-30T21:21:09.9366667+00:00

I assume that you mean that the table should have a primary key?
Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator

2023-05-30T21:28:23.47+00:00

Thank you very much Erland. I answered this question in a hurry. Busy at work.
MTBmojave80 40 Reputation points

2023-05-30T23:07:14.0866667+00:00

I just confirmed the on-prem does have the ID as the primary key for the tables and the Hub database tables does contain the primary key if I navigate to both the design > tables and I do see the primary key check mark. Any other ideas?

Answer 3

Hi MTBmojave80 •,

Welcome to Microsoft Q&A forum and thanks for using Azure Services.

As I understand, you need to sync Hub database to on-prem SQL express using Data Sync 2.0 agent but no tables are showing for both.

You have also mentioned that all tables have primary key.

Please check if the Snapshot Isolation is enabled for both Sync members and hub. For more info, see Snapshot Isolation in SQL Server.

The following statements activate snapshot isolation and replace the default READ COMMITTED behavior with SNAPSHOT:

ALTER DATABASE MyDatabase  
SET ALLOW_SNAPSHOT_ISOLATION ON  
  
ALTER DATABASE MyDatabase  
SET READ_COMMITTED_SNAPSHOT ON

Also, please consider below general limitations:

A table can't have an identity column that isn't the primary key.
A primary key can't have the following data types: sql_variant, binary, varbinary, image, xml.
Be cautious when you use the following data types as a primary key, because the supported precision is only to the second: time, datetime, datetime2, datetimeoffset.
The names of objects (databases, tables, and columns) can't contain the printable characters period (.), left square bracket ([), or right square bracket (]).
A table name can't contain printable characters: ! " # $ % ' ( ) * + - or space.
Azure Active Directory authentication isn't supported.
If there are tables with the same name but different schema (for example, dbo.customers and sales.customers) only one of the tables can be added into sync.
Columns with user-defined data types aren't supported.
Moving servers between different subscriptions isn't supported.
If two primary keys are only different in case (for example, Foo and foo), Data Sync won't support this scenario.
Truncating tables is not an operation supported by Data Sync (changes won't be tracked).
Using an Azure SQL Hyperscale database as a Hub or Sync Metadata database is not supported. However, a Hyperscale database can be a member database in a Data Sync topology.
Memory-optimized tables are not supported.
Schema changes aren't automatically replicated. A custom solution can be created to automate the replication of schema changes.
Data Sync supports only the following two index properties: Unique, Clustered/Non-Clustered. Other properties of index like IGNORE_DUP_KEY, Where filter predicate etc are not supported and the destination index is provisioned without these properties even if the source Index has these properties set.

Please check and let us know if this helps in your case.

Awaiting your inputs. Thank you.

Share via

I need to sync Hub database to on-prem SQL express using Data Sync 2.0 agent but no tables are showing for both.

2 additional answers

Your answer