incompatiable data sync

Hi,

We are going to be running two SQL Server 2000's from the same
physical server, with Windows Server 2003, and I need to have certain
tables between them syncronized. Database A is a backend to a website and
database B is going to be used by a different department. Both the
databases have certain data in common but it isn't stored in the same
format. I can't just auto sync one with the other, the data (records) has
to be manipulated before updates between them can take place.

So, what I need to do is convert certain data when A is updated and tranfer
that to B and vice-versa, in real-time. I've looked at Snapshot, that
isn't an option. I imagine some kind of Event Driven API is needed, where
do i being?

TIA"someguy" <inderjitrai@.yahoo.com> wrote in message
news:Xns9600EF1CC26FAinderjitraiyahoocom@.216.196.9 7.142...
> Hi,
> We are going to be running two SQL Server 2000's from the same
> physical server, with Windows Server 2003, and I need to have certain
> tables between them syncronized. Database A is a backend to a website and
> database B is going to be used by a different department. Both the
> databases have certain data in common but it isn't stored in the same
> format. I can't just auto sync one with the other, the data (records) has
> to be manipulated before updates between them can take place.
> So, what I need to do is convert certain data when A is updated and
> tranfer
> that to B and vice-versa, in real-time. I've looked at Snapshot, that
> isn't an option. I imagine some kind of Event Driven API is needed, where
> do i being?
> TIA

Have you considered replication? It sounds as if transactional replication
may be what you need, there's plenty of information in Books Online, and
there's also a newsgroup for replication -
microsoft.public.sqlserver.replication.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in news:4215a0ea$1_3@.news.bluewin.ch:

> Thanks Simone. Any books/online tutorial you would recommend?
> "someguy" <inderjitrai@.yahoo.com> wrote in message
> news:Xns9600EF1CC26FAinderjitraiyahoocom@.216.196.9 7.142...
>>
>> Hi,
>>
>> We are going to be running two SQL Server 2000's from the same
>> physical server, with Windows Server 2003, and I need to have certain
>> tables between them syncronized. Database A is a backend to a
>> website and database B is going to be used by a different department.
>> Both the databases have certain data in common but it isn't stored
>> in the same format. I can't just auto sync one with the other, the
>> data (records) has to be manipulated before updates between them can
>> take place.
>>
>> So, what I need to do is convert certain data when A is updated and
>> tranfer
>> that to B and vice-versa, in real-time. I've looked at Snapshot,
>> that isn't an option. I imagine some kind of Event Driven API is
>> needed, where do i being?
>>
>> TIA
> Have you considered replication? It sounds as if transactional
> replication may be what you need, there's plenty of information in
> Books Online, and there's also a newsgroup for replication -
> microsoft.public.sqlserver.replication.
> Simon|||"someguy" <inderjitrai@.yahoo.com> wrote in message
news:Xns9602CCEBFA3Binderjitraiyahoocom@.216.196.97 .142...
> "Simon Hayes" <sql@.hayes.ch> wrote in news:4215a0ea$1_3@.news.bluewin.ch:
>> Thanks Simone. Any books/online tutorial you would recommend?

<snip
As always with MSSQL, read through the relevant Books Online sections first;
next, set up a simple replication scenario and play around with it. By the
time you've done that, you'll have a better understanding of what you need
to know, and you'll be able to use sources like microsoft.com, Google and
newsgroups more efficiently.

I don't have much experience of replication myself, so if you feel you need
more detailed help on finding good information sources, then I would suggest
asking in the replication newsgroup. But I strongly suggest you read through
the Books Online information first, or else people will just refer you back
to it anyway.

Simon|||Simon Hayes (sql@.hayes.ch) writes:
> "someguy" <inderjitrai@.yahoo.com> wrote in message
> news:Xns9600EF1CC26FAinderjitraiyahoocom@.216.196.9 7.142...
>> We are going to be running two SQL Server 2000's from the same
>> physical server, with Windows Server 2003, and I need to have certain
>> tables between them syncronized. Database A is a backend to a website
>> and database B is going to be used by a different department. Both the
>> databases have certain data in common but it isn't stored in the same
>> format. I can't just auto sync one with the other, the data (records)
>> has to be manipulated before updates between them can take place.
>>
>> So, what I need to do is convert certain data when A is updated and
>> tranfer that to B and vice-versa, in real-time. I've looked at
>> Snapshot, that isn't an option. I imagine some kind of Event Driven
>> API is needed, where do i being?
> Have you considered replication? It sounds as if transactional replication
> may be what you need, there's plenty of information in Books Online, and
> there's also a newsgroup for replication -
> microsoft.public.sqlserver.replication.

Transactional replication certainly could be a possibility. But it seems
that the two databases have differnt layout. Replication is normally used
between identical databases. I believe that there are hooks in replication
to permit you to deviate from the pattern, but if there is to be trans-
formatation on a greater scale, the solution can be very complex.

An alternative would be to have triggers that writes data to events
table, and then you have a job running from SQL Server Agent that
pick up the events and relays them to the other server. With the very
brief information given here, I would rather investigate such a
solution, before I tried replication. (But then again, I haven't looked
at replication since 1998, and that was 6.5.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp