I have a system which is going to be talking to other sql servers and am
looking at various options on transfering data realtime.  Mainly this is
going to be for changes in the database (a persons name or status as well as
periodic updates that may affect a couple hundred records).

At the moment, we use web services for some of the machines we are support
and I have also used Com to handle the transfer.  We don't want to
import/export files as that causes timing issues.

It has been suggested that Web Services wouldn't scale well as we get
larger.  I assume this is a problem with encoding/decoding of XML, but XML
also seems the way everyone is going.  Also, it helps to have that option
available for respectability issues.

Biztalk was another option that was mentioned, but then we are locked into
MS technology.  Also, I got the impression that this would not work in a
real time environment ( realtime Sql updates), but I could be wrong here.

Notification Services was looked at, but this is mainly for Notifying by
Email, Fax etc that some event has taken place not to move data from Sql
Server to another - as far as I can tell.

I was curious about other experiances out there - good or bad.

Thanks,

Tom

You haven't mentioned the two main methods of sql data sharing:
replication or log shipping.  Have you ruled those out, or were you
just unaware of them?  For smaller databases, there are also some third
party tools (such as DoubleTake)

We're currently experimenting with log shipping, and it seems to work
OK.  The only problem we've had is if we load a bad transaction log it
hoses the database on the target machine.  I'm trying to come up with a
workaround, but it seems to be the way to go for us.

Stu

I knew of replication and have used it, but I am not trying to replicate the
data (publish/subscribe), but when a field changes in a record, I want a
trigger to fire that sends the data in that field or takes some action
because the field changed.  Typically, though if the certain fields in
Database A on a Server in Los Angeles changes ---> A message is sent to
Database X on a server in San Francisco Telling it that there was a change
and what it was.  The Server can then take some action based on that.

I hadn't heard of Log Shipping.

We are looking at Web Services as it can be called from one system to
another over the internet and encrypted (Soap/SSL).

There are questions on Scalability and efficiency, however.

Thanks,

Tom
Show quoteHide quote

[OT] SQL Humor
composite key structure
Default Column Values
Updating Tables with Computed Columns - SET ARITHABORT
Need Query Help