Colin Peters <(E-Mail Removed)> wrote in
news:4a2ea7e9$(E-Mail Removed):
>
> My requirement is to have a windows service that monitors 2 data
> stores. When there’s a change in one it should be written to the
> other. One data store is a local DB, and the other is remote and is
> accessed through a http-based interface. The data stores aren’t
> mirrors of each other; they just contain some common data that needs
> to be synchronised. Each data store already exposes a method for
> determining if there is anything to be synchronised.
>
> My initial thought is that the service launches a thread that will
> poll each data store at regular intervals. (This would be done in a
> thread because the “queries” can take many seconds, and I want the
> service to still respond to system message during this time). If it
> discovers updates to be made, it will then write them to the other
> data store. The thing is, the remote data store can be quite slow, so
> I’ll need to have different polling frequencies for each end. Also, I
> want to ensure that the last transaction with a particular store is
> finished before I launch the next one. For example, I read from
> localStore, and determine that an update to remoteStore needs to be
> made. During the time that the remoteStore is being updated I don’t
> want to perform a query on remoteStore; there's no point, because it's
> busy doing its' update.
>
> Clearly some kind of workflow/queuing mechanism is required that can
> respect my wish to have at most 1 connection to each data store. And
> maybe some well-established framework for keeping track of what
> changes are pending/timed out etc. Does C#/.Net provide any
> interesting namespaces and classes to prevent me having to hand-roll
> my own implementation?
>
> Just a few links or topic names would be great.
There are a couple of things you can try:
1. If there is anyway to "hard wire" the network connection (VPN?), SQL
Server replication is your best option, as it can work both ways, if
necessary.
2. Another option, which has a learning curve, is to set up a queue for
insert. Service Broker (SQL 2005 or newer) is a great way to set this
up. You would queue on both sides with a service that inserts and sends
to a queue on the other side. If two ways, you end up with two queues on
each side, or some logic in the service to ensure a second item has not
been inserted (check insert for record before inserting).
3. Set up a trigger to insert to the other machine on insert in one
table. Same for updates and deletes.
If both sides, however, you have a potential issue with numbering of
items (options 2 & 3). If using row ids (unique identifier or GUID),
there is no real issue, as the network card is included in the
algorithm. If Identity Insert, you will have to set aside different
identities for each machine to make sure two inserts at the same time do
not clash.
Setting up the HTTP is not difficult either, as you can set up an HTTP
endpoint in SQL Server.
Without knowing more about the constraints between the databases, I
cannot tell you which of the options will actually work for you.
Replication is the easiest, if you can set the plumbing up correctly.
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
Twitter: @gbworld
Blog:
http://gregorybeamer.spaces.live.com
*******************************************
| Think outside the box! |
*******************************************
Similar Threads
