synchronize the database

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • umeshpotdar
    New Member
    • Mar 2007
    • 34

    synchronize the database

    hi frdz
    i'm doing web site
    i have to use two databases
    one is for web site (suppose call it as web db server-A) &
    another is for some trasaction(call it as trans db server-B)
    so i want to synchronize these databases.
    means i want to do updation to these db [once in the day ]
    [whtever changes made in B will be update db A from last updation point
    at the end of day ]
    so can u tell me how can i synchronize the databases.
    thnks.........
  • iburyak
    Recognized Expert Top Contributor
    • Nov 2006
    • 1016

    #2
    You can write a stored proc to update all tables in a database and run it by a scheduled job nightly.



    Thank you.

    Comment

    • umeshpotdar
      New Member
      • Mar 2007
      • 34

      #3
      can u give me the procedure how to write the Syncronization of database
      or any link that can refer to syncronise database....... ........

      thanks for ur's reply.

      Comment

      • iburyak
        Recognized Expert Top Contributor
        • Nov 2006
        • 1016

        #4
        Do you update both databases independently or one is primary and another secondary?
        If yes the easiest way besides replication just delete everything from tables and reload new data.

        On server B

        [PHP]BEGIN TRAN

        DELETE FROM TABLE1

        INSERT INTO TABLE1
        SELECT * FROM ServerA.dabase_ name.dbo.TABLE1

        IF @@ERROR = 0
        COMMIT TRAN
        ELSE
        ROLLBACK TRAN


        BEGIN TRAN

        DELETE FROM TABLE2

        INSERT INTO TABLE2
        SELECT * FROM ServerA.dabase_ name.dbo.TABLE2

        IF @@ERROR = 0
        COMMIT TRAN
        ELSE
        ROLLBACK TRAN[/PHP]

        If tables are big you have to reload them in a loop taking chunks of data depend on a size.
        This way you will not overload transaction log at insert time but you will loose ability to roll back data to original in case of a problem.

        [PHP]SET ROWCOUNT 2000
        While @@ROWCOUNT > 0
        BEGIN
        BEGIN TRAN
        DELETE FROM TABLE1
        COMMIT TRAN
        END
        SET ROWCOUNT 0

        While @@ROWCOUNT > 0
        BEGIN
        BEGIN TRAN
        INSERT INTO TABLE1
        SELECT TOP 2000 * FROM ServerA.dabase_ name.dbo.TABLE1
        WHERE primery_key_col umn not in(select primary_key_col umn From TABLE1)
        COMMIT TRAN
        END[/PHP]

        Comment

        • umeshpotdar
          New Member
          • Mar 2007
          • 34

          #5
          thanks for ur suggetion.
          ur idea is very good but i don't want to delete the tables.
          will database mirroring solve this problem?
          i'm trying this thing but i got errors
          so plz can u tell me abt any other idea.
          or how to use db mirroring concept?
          thanks ....

          Comment

          Working...