MS SQL Server Replication - SQL7 two-way immediate replication not working

Author

SQL7 two-way immediate replication not working

Nate

2005-09-24, 1:23 pm

Hi All,

I have two SQL 7.0 servers in a load balanced Web environment that I
want to replicate changes back and forth to each other so that the two
databases are always in synch. I have setup a publisher, distributer,
and subscriber with two-way immediate update replication. Replication
is working OK to the subscriber when I add, update, or delete a record
in database1. Replication is not working, and there are no replication
agent/job errors when I add, update, or delete a record in database2.
Do you know why replication is not working from subscriber back to
publisher?

Thanks,

Nate

Hilary Cotter

2005-09-25, 7:23 am

This is a little strange. Basically transactions originating on the
subscriber are applied via MS DTC on the Publisher first and then applied on
the Subscriber.

If MS DTC is not running or there are problems connecting transactions
originating on the subscriber are kicked back with an access denied error
message.

I would 1) manually try an insert on the subscriber to see if it is applied
successfully
2) check the conflict viewer to see if any conflicts are being logged

--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nate" < nathandeneau@braintr
ade.biz> wrote in message
news:1127584317.964137.61710@g43g2000cwa.googlegroups.com...
> Hi All,
>
> I have two SQL 7.0 servers in a load balanced Web environment that I
> want to replicate changes back and forth to each other so that the two
> databases are always in synch. I have setup a publisher, distributer,
> and subscriber with two-way immediate update replication. Replication
> is working OK to the subscriber when I add, update, or delete a record
> in database1. Replication is not working, and there are no replication
> agent/job errors when I add, update, or delete a record in database2.
> Do you know why replication is not working from subscriber back to
> publisher?
>
> Thanks,
>
> Nate
>

Nate

2005-09-26, 8:24 pm

My responses...

1) Manuall insert does not work on the subscriber, but does work on the
publisher.
2) No conflicts.

Anyway, I solved the problem! Thanks!

1) Right-click publisher, distributor, and server (e.g.
NETBIOS_SERVER)' properties, then go to Connections and enable "Allow
other SQL servers to connect remotely to this SQL Server using RPC".

2) Set 'Configure Replication' -> Publishers and Subscribers Logins to
the correct permissions. For Nate's first successfull configuration,
the Publisher login was set to "Impersonate..." and the Subscriber
login was set to "Using SQL server authentication of this account" with
user "sa" and password "<the password for sa on the subscriber - one of
Nate's regular passwords>".

3) My sa built-in account has a password. Everytime you create a
subscription you must run the sp_link_publication stored procedure on
the subscribing database to specify the SA password for the
publisher...

sp_link_publication
@publisher = '< server_name_where_pu
blisher_located>',
@publisher_db = '< publisher_database_n
ame>',
@publication = '< publication_name_on_
the_publisher>',
@security_mode = 0,
@login = 'sa',
@password = '<password>'

Sponsored Links