Re-establish replication

I had Merge replication working fine between two SQL 2000 servers for months... then the subscriber server had a hard drive crash and we rebuilt it... I am now having trouble re-establishing the replication with the subscriber. On the publisher I have de
leted the subscription and reestablished it... but I never see the database being pushed to the subscriber, and the Merge agent always fails because it says it cannot find the database on the subscriber.
What am I missing here? Any advice would be greatly appreciated.
Thanks,
Jeff
Jeff,
you say "I never see the database being pushed to the subscriber" but the
database isn't created by the snapshot synchronization. Can you check to see
if it exists on the subscriber? If it does, then on the publisher
properties, subscriptions tab does the subscription database name appear?
Cheers,
Paul Ibison
|||what is the error message you are getting?
"dev@.mycompany.com" <anonymous@.discussions.microsoft.com> wrote in message
news:DEFD3DDB-3465-44E2-B1EB-1050147A2BC7@.microsoft.com...
> I had Merge replication working fine between two SQL 2000 servers for
months... then the subscriber server had a hard drive crash and we rebuilt
it... I am now having trouble re-establishing the replication with the
subscriber. On the publisher I have deleted the subscription and
reestablished it... but I never see the database being pushed to the
subscriber, and the Merge agent always fails because it says it cannot find
the database on the subscriber.
> What am I missing here? Any advice would be greatly appreciated.
> Thanks,
> Jeff
|||Paul,
Thanks for the response... you reminded me that I had to manually create the database on the subscriber... then replciation agenst would populate it for me... which they did... and I thought all was fine (I tested on table and changes on both sides propog
ated properly to the other side)... but after a few days, we noticed that new records in another table on the publisher would not only NOT get replicated to the subscriber, but would be deleted from the publisher. The Merge logs showed conflicts... how c
ould there be conflicts with a new record being added on the publisher?
Also, when I try to manually add a record to the same tabel on the subscriber, I get a stored proc error... I am guessing related to replication.
What did I forget here?
1) Existing replication working fine
2) Subscriber machine failed
3) Rebuilt subscriber from scratch, reloaded SQL
4) Set-up SQL User list, and created databse that si used for replication
5) Deleted old subsciption on publisher, and created a new one
6) Then the story above took place...
Jeff
|||What triggers do you have on the tables?
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||I have no triggers on the tables at all. The only triggers that fire should be those that SQL Replication uses.
|||Jeff,
there is definitely something extra/abnormal/odd going on here. I think your best bet would be to use profiler and try to trace the event sequence that leads to this behaviour. Alternatively, if you don't want to do this detective work, then you could set
up the whole thing from scratch (drop publisher, remove replication on publishing db and subscribing db, add publisher and subscriber, initialize).
HTH,
Paul Ibison
|||Paul,
Thanks for your help... I deleted the subscription and the publication and started from scratch... set up the publication... ok... set up the subscription... then I get an error saying the publisher could not log-in to the subscriber.
I have the SQL server and SQL Agent set to use a Windows account on both machines... same account ID/PW. But the Merge agent error says "Error logging in to subscriber. sa login failed".
What am I missing here? I thought the Merge agent used the security settings set for the SQL Agent (set to a Windows account)... so why is the merge agent trying to use the sa login at the subscriber?
I know I am just forgetting something very simple here. Any advice would be greatly appreciated.
Thanks,
Jeff
|||Jeff,
right-click the replication monitor and go to properties. Using the elipsis,
make sure that your agents are using trusted security and not sql server
security.
HTH,
Paul
|||Paul,
Under Distributor properties, Subscribers tab, ellipsis next to subscriber, it shows Agent connection to subscriber as "Impersonate the SQL Server Agent account on 'PublisherName' (trusted connection)"
Why else would the Merge agent be trying to connect with sa instead of the Windows account that both SQL Agents are running under on both publisher and subscriber?
Jeff