Synchronizing Data between two servers

Question

Synchronizing Data between two servers

James-263931

SSC Rookie

Points: 29
More actions
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

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

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

David Poole-249495 Ten Centuries Points: 1106 More actions · Answer 1

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.

Fernando-235287 SSCrazy Points: 2485 More actions · Answer 2

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.

James-263931 SSC Rookie Points: 29 More actions · Answer 3

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

David Poole-249495 Ten Centuries Points: 1106 More actions · Answer 4

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.

Ian Yates SSCoach Points: 19738 More actions · Answer 5

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