question

MaraSnchezINVESCONSEGURIDAD-1763 avatar image
1 Vote"
MaraSnchezINVESCONSEGURIDAD-1763 asked MaraSnchezINVESCONSEGURIDAD-1763 commented

Problem with sycronising data between Azure databse and local because of Collation problems

Hello!

I want to syncronise some tables from one database I have on a Local Server with others in an Azure Database by using the agent Microsoft SQL Data Sync . The collation I am using is Modern_Spanish_CS_AS .

I have the same collation in both databases however the collation of the master database is different, SQL_Latin1_General_CP1_CI_AS. While trying to sycornise I have this error:

"Database provisioning failed with the exception "Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Modern_Spanish_CS_AS" in the equal to operation."


How can I change this collation?

Thanks for your help.

María


azure-sql-database
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @MaraSnchezINVESCONSEGURIDAD-1763 We have found some similar issues where people have worked with support to resolve them. This is the solution provided to them.

1.The task for provisioning the new sync member is still present in the system and I recommend that you delete and re-create the entire sync group to remove this task from the system.

https://social.msdn.microsoft.com/Forums/en-US/f87cceb7-cae7-407d-ae8a-86fec985e157/collation-conflict-on-deploy?forum=ssdsgetstarted

0 Votes 0 ·

Hello @MaraSnchezINVESCONSEGURIDAD-1763 Just checking in if you had time to check Alberto Morillo answer and the one I posted above. Please let me know if you need any further assistance. Thanks

0 Votes 0 ·

1 Answer

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered MaraSnchezINVESCONSEGURIDAD-1763 commented

Azure SQL Data Sync supports the below scenarios with collation:

  1. If the selected sync schema tables are not already in your hub or member databases, upon deploying the sync group, the service will auto create the corresponding tables and columns with the same collation settings as the selected in the empty database(s).

  2. If the tables to-be-synced are already created in both of your hub and member databases, SQL Data Sync requires the primary key columns are set with the same collation between hub/member databases to successfully deploy the sync group. No collation restriction on the non-primary key columns.

Azure SQL Data Sync is used o sync user databases and not system databases.



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks you! It was exactly that. Very good answer. Just sync without having created the tables in the azure database.

Regards,

María

0 Votes 0 ·