Post reply

Synchronization without SQL server Replication

  • February 2, 2006 at 4:51 am

    #103070

    We have our application using SQL Server 2000 as back end. One of our Client requires to run same application on different unconnected sites. Some Parts of application will be running at some Branches. Where as some at Head Office.

    The users wants a customized solution to have their data synchornized without using SQL server replication.

    Before asking question, I want to state my line of action :

    What I have solution in mind seems like that :

    The change in any Table leads to addition of row(s) in table say tbl_Change_record whose structure is like this

    tbl_Change_Record ( surrogate_key,site,  table_name, Table_Primary_key_components, Primary_key_value, Operation,time_stamp)

    for example data in this table can be like this

    (1,'site1', 'std_student','std_cd','001','INSERT','2002-12-09 10:30:00')

    (2,'site1', 'std_student','org_cd','ABC','INSERT','2002-12-09 10:30:00)

    (3,'site1', 'std_student','std_cd','002','DELETE','2002-12-09 10:32:00')

    (4,'site1', 'std_student','org_cd','ABC','DELETE','2002-12-09 10:32:00')

    This table will be used to generate Queries for each operation which will be ran on other machine in sequential order of time stamp.

    For example ,

    • For Insert operation, my solution will work like this

      Get all primary key values for particular table, goto  record of table named table_name with those primary key values and extract all values of remaining columns  and make query for INSERT

    • For Update operation , same series of operation will perform
    • But for Delete operation(DELETE AFTER INSERT), we will do nothing

      or no query creation for previous INSERT AND DELTE(meaning client side ADDITION and then DELETION doesnot mean any thing to other branch or sites)

    • For normal DELETE , the query will be created normally
    • For DELETE AFTER UPDATE, UPDATE Query will not be created but DELETE QUERY will be developed

    Now the questions :

    1. If I don't want to use triggers, then if there is any way I can get intermediate states of tables involved in transaction(i.e before commit or rollback) and use these intermediate values to update my tbl_Change_Record and then allow the transaction to commit ?

    2. Any other suggestion or comments

    Regards

    Badr

     

     

  • February 2, 2006 at 5:14 am

    #618345

    Why dont you use replication, its the easiest and efficient way to sync. data and matadata.


    Mehmood

  • February 2, 2006 at 5:48 am

    #618352

    The requirement emerges from client's constraints. No connection(like VPN) is availabe between branches and head office. They need some utility which generate some "thing" (which will indeed be scripts) and running this utility should result in synchronization

  • February 3, 2006 at 9:25 am

    #618715

    If there is no connection, how will they get the scripts you generate to the other locations?

    If there is a connection at some time then Snapshot or Merge replication should be available to you.

    Steve

  • February 3, 2006 at 9:57 am

    #618728

    Is this an application written in house?  If so you can have it call stored procedures that will create your change records.

  • February 3, 2006 at 1:09 pm

    #618788

    How about Log Shipping ?

  • February 7, 2006 at 12:13 am

    #619170

    The senario is that we dont have connection between both servers.

    And it is required to copy data from some tables of one server to another.

    Both servers already have the same schema with different tuples.

  • February 8, 2006 at 12:10 pm

    #619610

    Back to your original question, 1) If you don't want to use triggers...

    I would do it one of two ways:

    a) use triggers and generate the table like you laid out.  I would then use bcp (Bulk Copy) to export the transaction table to a flat file.  I would then use ftp to send the file to the remote machine.  On that machine I would use bcp to import the flat file to a transaction in table.  Use a script or program to update the table corresponding to the original table.  You have to have some way to know which transactions have been already applied or clear out the original transaction table after all flat files have been created and sent.

    b) without triggers, enter in the information into a transaction file directly and then apply it to the master file at a later time.  This transaction file can then be sent to the remote sites like above.

    Hope this Helps.

    Steve

  • February 16, 2006 at 1:04 am

    #621269

    Please verify my understanding of the problem

    You required to track data changes and need to update some of these changes to other server using flat files.

    And definitely you donot required to use the log shipment or replication or bcp or triggers.

    Am I Right?


    Mehmood

  • February 16, 2006 at 1:06 am

    #621270

    yes ,you are right

  • October 5, 2006 at 11:09 pm

    #664507

    Dear Newbie,

    Sorry for the delay, as I was out of station. Here comes the simplest solution

    You must write a gloabl class for your project with a method which logs the DML quries in a table along with the execution time, last extraction date and other required parameters (if any).

    Then you must call that function just before the call of execution of any DML in your project.

    .Design a small application that fetches the queries from this log and writes them in a flat file, also this application also updates the last extraction date for the queries.

    .Design another small application to just open a file and execute each query and finally mark the file as executed successfully.

    I hope this will help you


    Mehmood

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply