locked
Sync'ing and primary keys: RRS feed

  • Question

  • Hi. Hoping to get a few questions answered that I can bring to a meeting at the end of the week about an upcoming project where Azure might work out for us.

    1. Can an on-premise table that will be pushed up to the cloud using Data Sync have a composite primary key?

    2. If multiple "local chapter" databases are pushing rows from an identically structured table up to a "national headquarters" table in Azure (single-direction, not bidirectional), will there be any problems with key-collisions if the all premises are using autoincrementing integer PKs and they all started at 1?

    3. In a unidirectional sync of multiple premise databases with Azure (premise up to Azure), can the AZURE table have its own PK column and treat the premise table's PK column as a normal column value? E.g.

    PREMISE DB1..................PREMISE DB2............................AZURE DB

    id identity pk...................id identity pk.............................id int  [not the PK in Azure]

    name varchar(10)...........name varchar(10).....................name varchar(10)

    .........................................................................................newid identity primary key

     

    Wednesday, November 30, 2011 6:22 PM

Answers

  • Hi,

    Please find the answers to your questions inline:

    1. Can an on-premise table that will be pushed up to the cloud using Data Sync have a composite primary key?
    Yes, you can sync a table with composite PKs in it.
     
    2. If multiple "local chapter" databases are pushing rows from an identically structured table up to a "national headquarters" table in Azure (single-direction, not bidirectional), will there be any problems with key-collisions if the all premises are using autoincrementing integer PKs and they all started at 1?
    If tables to be synced have PK as identity columns, sync will apply the values from last "local chapter"  it synced to "national headquarters".

    3. In a unidirectional sync of multiple premise databases with Azure (premise up to Azure), can the AZURE table have its own PK column and treat the premise table's PK column as a normal column value? E.g.
     
    PREMISE DB1..................PREMISE DB2............................AZURE DB
     
    id identity pk...................id identity pk.............................id int  [not the PK in Azure]
     
    name varchar(10)...........name varchar(10).....................name varchar(10)
     
    .........................................................................................newid identity primary key

    This scenario will not sync because the PK columns are different in on-prem and azure DBs.

    • Proposed as answer by freaky roach Thursday, December 1, 2011 6:50 AM
    • Marked as answer by Challen Fu Friday, December 9, 2011 9:09 AM
    Wednesday, November 30, 2011 10:20 PM