Post reply

Synchronizing Data between two servers

  • September 28, 2005 at 7:12 am

    #165633

    Hi SQL Gurus,

     I'm quite new to database administration and thereby facing a lot of problems. My problem is as follows...

    I got a central server and two remote servers which all have the same database structure. Now most of the time the remote servers will be offline. When it becomes online I want what ever new data updated in the remote server to get updated into the central server. Could anyone out there tell me how to do this?

    Thanks in advance.

    JM

     

  • September 28, 2005 at 7:40 am

    #593663

    Assuming that the data to be synced on the remote servers are effectively read-only then PULL Snapshot replication should do it.

    If both the central and remote servers are writeable and you want both to be aware of the other then you are looking at merge replication.

  • September 28, 2005 at 8:16 am

    #593680

    What edition do you use? Standard or Enterprise. Replication is a good tool in 2000 but is a tricky one if not setup correctly. BOL has a bunch of readings regarding replication. I do recommend to test first using a different servers (development maybe) to make sure everything works as it should.

    If you use enterprise edition you can also use LogShipping which allows you to send the log to a different server every now and then (you set the frequency) to be in synch.

    Depending of the type of replcation you MUST act accordingly. Snapshot, merged, etc Plan before, test, backup frequently,and when everything, and I mean everything, works, then implement replication or logshipping. Once you have done ot, you won't have to worry about synch data ever! (well, ever is too absolute, isn't it?)

    Good luck and keep an eye here to ask questions or get solutions.

  • September 29, 2005 at 1:08 am

    #593930

    Hi,

    First of all thanks for the advice. I'm using the Enterprise Edition. I tried out merge replication but I have a problem. Both the server and client databases are writeable. When I tried merge replication what ever new data which was inserted into the client got updated on the server. But the new data inserted on the server side got deleted. How to resolve this since I need to retain both data's?

    Regards,

    JM

  • September 29, 2005 at 1:32 am

    #593940

    BOL recommends that each server be given a range of IDs in the shared tables. That is your master would use IDs 0 - 19,999, remote1 would use 20,000-29,999 etc obviously with a wider range of values.

    You could use GUIDs which sidesteps the issue but there is a performance hit in doing so.

  • September 29, 2005 at 1:51 am

    #593945

    Deleted or overwritten?

    Sounds like perhaps you have identity columns that aren't being handled by replication properly.  If you do have identity cols that are not managed properly, you will end up with collisions and data will be overwritten.

    Look at the NOT_FOR_REPLICATION setting.  I have done quite a bit of reading on replication (our app will need it soon) but have not played with it a lot, but I do know that you can ask SQL to manage the identities for you, automatically assigning ranges of numbers to servers to ensure they don't conflict.

     

    This is all assuming that data is being overwritten due to identity column collisions of course

Viewing 6 posts - 1 through 5 (of 5 total)

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