Azure database sync database provisioning failed

Question

We are re-initializing database sync from Azure sql database to on-premise db. Database provisioning failed as bellow for one sync group, while the other group was succeed setup.

From the error, ActiveDirectoryLoginId(nvarchar(max)) was used in an index, but no index was found on our source db. We can manually create follow tables on member db to work round. Anyone know the reason. Thanks.

Database provisioning failed with the exception "SqlException Error Code: -2146232060 - SqlError Number:1919, Message: Column 'ActiveDirectoryLoginId' in table 'dbo.Employee' is of a type that is invalid for use as a key column in an index. For more information, provide tracing ID ‘8fbe0d77-126b-496c-9121-b9ed3c633669’ to customer support."

Another table also failed with similar error. Both of them are nvarchar(max) type, is there anything related to this?

Answer 1

Make sure the problem is not created because you entered an extra column on the source table. So the hub database table is missing a new column which the datasync / source database is trying to push.

In addition, when you configure a sync group you select the tables within the databases and the columns within the tables that are synchronized. You can even create value filters on a column so that only rows that satisfy the filter, such as "Region = Northwest", are synchronized. You cannot filter columns with data type nvarchar(max) or varchar(max).

Please review the limitations listed on this article also.

You can use this script to perform a Health Check on Azure Data Sync for you.