Simon Hayes (sq*@hayes.ch) writes:
"someguy" <in*********@yahoo.com> wrote in message
news:Xn*********************************@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,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp