question

MWeiberg-3898 avatar image
0 Votes"
MWeiberg-3898 asked MWeiberg-3898 answered

azure synchronisation delete records

hallo,
i have a sql database on server (azure) and 3 databases on laptops. if 2 or more laptops are offline it can be passible that each laptop will create a new record (for example "customer") with a primary key. On synchronisation only one record is posted, the other ones are deleted.

I thing because of the indexes between the table the server changes the primary key if it still there ? This key is saved to many other tables when it was created - but they are connected about the relationchip-management !?

Hope for answers

Mike

azure-sql-database
· 4
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.



OK sorry for the missing details :

-It is a Azure SQL Database.

-for the laptops i use the SQL data synch tool

 when i use the uni-directional the other laptops will never get the customers from the others ? But i need a complete synchronisation about all Computers.

0 Votes 0 ·

Hi @MWeiberg-3898

Obviously if you are using a sync to one direction then the other direction is not done

You can try to use bi-directional between the Azure Database to each (separate sync) of the databases in the laptops and in the databases you should manage the primary keys so each database will create a different value

In your case you have 4 databases so you can use Modulo 4 operation for identity

Database 1: will use ID BIGINT IDENDIDY(1,4)
Database 2: will use ID BIGINT IDENDIDY(2,4)
Database 3: will use ID BIGINT IDENDIDY(3,4)
Database 4: will use ID BIGINT IDENDIDY(4,4)

This way, there is no conflict in the values and you also get information of the source of the database from the value of ID using Modulo 4

For example if the ID is 1 or 5 or 9 then the Modulo 4 of all these is 1... then you know it was first created in the database 1, and if the ID Modulo 4 is 2 then you know it was first created in the database 2...

0 Votes 0 ·

This is a good solution. Thanks. The last question is :

Can i change this with ssms and can i change it with the existing datas inside ?

0 Votes 0 ·
Show more comments
MWeiberg-3898 avatar image
0 Votes"
MWeiberg-3898 answered

Hallo, sorry for being here again. But when i use the identity's like written before something happens :

for example :

  • database 1 (first id is 1, second is 5)

after synchronisation :

  • database 2 the next id is 9 (because of 5+4), but it must be 2

i have read about setting the IDENTITY_Insert to on and off - i try it at the update and insert triggers, but there is no changing by using the id's.

any solutions ???



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.

pituach avatar image
0 Votes"
pituach answered pituach edited

Hi,

please clarify

i have a sql database on server (azure)

Do you mean SQL Server on Azure virtual machine or Azure SQL Database?

azure synchronisation

Which service/tool do you use for the synchronization?

If you use names which are not the name of the product/service then it make it harder to understand.

Note! there are many tools/service to sync data between databases so if the tool you selected does not cover what you need then we can help you a different solution if you can elaborate exactly what is the scenario

Do you mean "SQL Data Sync"?

In this case you can simply configure the sync to work only in one direction and not bi-directionally. Check the documentation (the link above)

The Sync Direction can be bi-directional or can flow in only one direction. That is, the Sync Direction can be Hub to Member, or Member to Hub, or both.



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.