incompatiable data sync

someguy

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

Jul 23 '05 #1

Simon Hayes

"someguy" <in*********@yahoo.com> wrote in message
news:Xn*********************************@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

Jul 23 '05 #2

someguy

"Simon Hayes" <sq*@hayes.ch> wrote in news:42**********@news.bluewin.ch:

Thanks Simone. Any books/online tutorial you would recommend?
"someguy" <in*********@yahoo.com> wrote in message
news:Xn*********************************@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

Jul 23 '05 #3

Simon Hayes

"someguy" <in*********@yahoo.com> wrote in message
news:Xn********************************@216.196.97 .142...

"Simon Hayes" <sq*@hayes.ch> wrote in news:42**********@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

Jul 23 '05 #4

Erland Sommarskog

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

Jul 23 '05 #5