Transactional replication with multiple publishers seems unreliable.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris McGuigan - 30 Nov 2005 16:46 GMT

We have a SQL2000 Publisher, and SQL2005 server doing Distribution and
Subscription. We're using transactional replication with push
subscription.

We've had this running reliably for 2 weeks with one publication with
21 articles. The database is a Great Plains DB for our UK office.

We created another publication on the same SQL2000 server but from a
different DB (the Great Plains DB for our US office). The tables from
both publications are going to the same target database but with UK_ or
US_ prefixing the destination tables.

As soon as the 2nd publication gets synchronised at the subscriber the
first one goes out of sync saying rows are missing at subscriber.
This in turn brings the 2005 server to its knees.

The table sources have the same name but they are writing to
destinations with different table names but it's as though the fact the
names are the same is causing a conflict somewhere in the process.

To clarify;
    Publisher    Distributor    Subscriber
    2000        2005            2005
    ---------    -----------    ----------
    MySvr        MyNewSvr        MyNewSvr

All tables going to ReplDB on MyNewSvr

Tables:
    UK.dbo.IV00101     replicated to     UK_IV00101
    US.dbo.IV00101     replicated to     US_IV00101

I should point out that all the replication was set up via the GUI.

Signature

Regards
Chris

Reply to this Message

Raymond Mak [MSFT] - 30 Nov 2005 17:20 GMT

Hi Chris,

I would guess that the names of some (or most likely all) of the custom
stored procedures for applying changes at the subscriber are the same among
the articles of the two publications that you have configured through the
GUI. To find out if this is indeed the case, you can compare the
ins_|upd_|del_cmd columns of sysarticles from the two publisher databases.
If they are the same, the custom procedures for the publication\subcription
that receives the snapshot last will overwrite the custom procedures for the
publications\subscriptions receiving the snapshot earlier. Consequently,
replicated changes intended for destination tables for the "older"
publications\subscriptions will be erroneously re-routed to the destination
tables of the "newly synchronized" publication\subscription, and this can
definitely lead the kind of consistency errors that you saw.

-Raymond

> We have a SQL2000 Publisher, and SQL2005 server doing Distribution and
> Subscription. We're using transactional replication with push
[quoted text clipped - 29 lines]
>
> I should point out that all the replication was set up via the GUI.

Reply to this Message