SQL Data Sync require GUID/UUID for primary key field?

Question

I want to use SQL Data Sync to perform bi-directional syncing of data between an Azure SQL database and an SQL database hosted elsewhere. For this to work, do the primary key fields on my tables need to use UUIDs instead of int? Thanks in advance.

Answer 1

Hello,

Int data type is supported on Azure SQL Data Sync. For a list of supported and not supported data types please read page 88 on the following documentation:

https://github.com/Microsoft/sql-server-samples/raw/master/samples/features/sql-data-sync/Data_Sync_Preview_full_documentation.pdf?raw=true

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 2

Thanks for the information, Alberto. I'm specifically wondering if GUID data types are required for primary keys in order to ensure uniqueness across tables in databases that are synced bidirectionally. If using type Int (with auto-increment) for the primary key and record(s) are created in a specific table on one server, when those record(s) are synced back to the hub how are they inserted to the hub table without causing a conflict? Or does Data Sync store its own index "under the hood" to manage data uniqueness and integrity across multiple databases?

Answer 3

Hello,

If you are using an int data type with identity to make the autoincrement then that identity will have to be a primary key also. That is a general requirement of SQL data Sync. For more information, please read below.

https://github.com/MicrosoftDocs/azure-docs/blob/master/articles/sql-database/sql-database-sync-data.md

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 4

Ok, so I believe that the answer to my question is "no". A GUID primary key is not required to ensure uniqueness when syncing data between table(s) using Azure Data Sync. Thanks for your help.

Answer 5

Hello,

You are welcome!

Thank you for visiting Azure forums!

Regards,

Alberto Morillo
SQLCoffee.com

Answer 6

Hi @HowardTheDuck4Ever

I am fighting the exact same problem as you were. I have currently several on premises databases that I need to sync. They are setup to replication merge which has created GUID columns in the database but not as primary key. The only way I can get Azure sync to work is with Unique identifiers as primary key. If you could elaborate on how you made it work i would be forever grateful!

Best

Jeppe