locked
Data sync by time stamp value in table between few database by store proc and jobs RRS feed

  • Question

  • i do not know replication. i want to achieve my goal by writing store proc and job which will fire store proc every day 5 times. so please do not tell me to manage this job by replication rather see my scenario and advise me how could i write my store proc which can get the job done. let me tell you what is the scenario.

    we have a windows interface by which we insert customer info in our remote database in cloud. we have many branches and every branch has its own db.

    my customer table will have customer data and one fields will be timestamp. the moment we insert or update customer data in remote db then timestamp value will be changed.

    we have sql server job which will execute my store proc after every few hour to pull down data from remote db. so the same sql server job and store proc will be running in every sql server installed in all branches.

    now my plan is how to pull down data and insert or update that data in my local db in branches. all branch db is connected with remote db over link server.

    i will issue a select which will compare remote db timestamp field with local db binary field to check if any upper version of time stamp exist in remote db if yes then dump those data into temp table in my SP and iterate that temp table and check customer id already exist in my local db if yes then delete that customer and insert that record if no then insert that remote customer data into my local branch db.

    why i create a field in my branch customer table whose type is binary(8) because if i would create timestamp then i can not update that field but binary filed can be updated with timestamp value.

    the problem i am facing to compose the comparing local timestamp and remote time stamp. any one can help me to find out highest time stamp value store in my local customer table. if i get highest timestamp value from local customer table then i can issue select for remote customer where i can check any upper version of timestamp exist in remote db.

    if anyone see there would be problem the way i am thinking to get the job done then please discuss here in details. thanks

    Tuesday, October 25, 2016 2:17 PM

Answers

  • Timestamp, the synonym for the rowversion data type, tracks a relative time within a database, not an actual time that can be associated with a clock. You can not compare timestamp from two databases.

    Maybe you will need to use MERGE (Transact-SQL)  to compare UTC time (Coordinated Universal Time).


    • Edited by Riaon Thursday, October 27, 2016 8:11 AM
    • Marked as answer by Sudip_inn Thursday, October 27, 2016 9:14 AM
    Thursday, October 27, 2016 8:09 AM

All replies

  • You say nothing about deletes.  How are you going to detect them?

    I realize you are tracking inserts and updates by the timestamp column which is globally incremented by database. So I update a table with a timestamp value on it, and then a different table with a timestamp column on it and the global timestamp reflects both these updates. As you realize it is local and you can't really use it for comparative purposes against the timestamp column on another database.

    What you need to implement is some other method of change tracking or change data capture.  This way you can detect what changes and make a decision as to what to do.

    Some people use triggers to track changes.

    Now other people will have identity pools. London might have an identity pool from 1 to 1,000,000. NYC might have a pool from 1,000,001 to 2,000,000. This way you can track ownership and uniquely rows and to ensure that you do not have pk collisions.

    You also have to figure out what to do is the same column on the same row is updated on different sides of your branch/head office topology.

    If someone's salary is decremented on one side by 10% and decremented somewhere else by 15% what is the net decrement 25%, 15%.

    This is a very complicated undertaking you are embarking on.

    In my experience most people use a triggered solution to track inserts, updates, deletes, a column with a utc time stamp value and then an engine which processes these changes. I have seen it done in service broker.

    Most people use merge replication for this as it was designed for exactly this purpose. Others use sync services. Others choose to roll their own solution.

    Tuesday, October 25, 2016 2:26 PM
  • our scenario is different. we never delete record just only update and insert. our UI will always insert data into remote db and sql server job and SP will query table from remote db and update branch table. i will detect change by timestamp. so tell me what is wrong in this design?

    thanks

    Tuesday, October 25, 2016 3:12 PM
  • Suppose I update a row on the head office. I then update a row on the branch office. Who wins? Does the row on the branch go to the head office and the change on the head office go to the branch where they swap values?

    When the row from the branch comes up to the head office it will increment the timestamp there. This will signal that the row is in motion again and will go down to the branch.  You need some form of loopback detection in here.

    What is the network like between the head office and the branch office? If it is a WAN or ISDN you will saturate your network connection under load.

    Also how are you going to handle identity inserts in the head office/branch office?

    How are you going to handle FKs? Do triggers fire on both the head office and branch office?

    There is a lot too this.

    You have a rudimentary transfer process which may work well in simple scenarios. I would doubt it is scalable.

    You are rolling your own replication engine and will be encountering all the problems that the replication design team have architected into their product.

    Tuesday, October 25, 2016 3:22 PM
  • you said: Suppose I update a row on the head office. I then update a row on the branch office. Who wins? Does the row on the branch go to the head office and the change on the head office go to the branch where they swap values?

    in our case data will be always update in romote db and in other branch will download data from remote db. so in our case communication is one way not multiple.

    we are not using identity fields for PK.

    Wednesday, October 26, 2016 8:41 AM
  • Timestamp, the synonym for the rowversion data type, tracks a relative time within a database, not an actual time that can be associated with a clock. You can not compare timestamp from two databases.

    Maybe you will need to use MERGE (Transact-SQL)  to compare UTC time (Coordinated Universal Time).


    • Edited by Riaon Thursday, October 27, 2016 8:11 AM
    • Marked as answer by Sudip_inn Thursday, October 27, 2016 9:14 AM
    Thursday, October 27, 2016 8:09 AM