Groups | Blog | Home
all groups > sql server replication > july 2006 >

sql server replication : Merge Replication keeps exchanging bulk copies


Some_Ignorance_IN_Replication
7/6/2006 11:01:01 AM
Background:

2 servers identically configured running server 2003 ent 64 bit edition.
Both DBS are MS SQL 2005 standard 64 bit. Publication and distribution
running on same server. Type of replication is merge replication. Snap-shot
folder is in a shared folder. VPN set up over Internet between two servers
on workgroup with same admin pwd and sa pwd. Reporting services not running
due to 32 bit ASP.net registered instead of 64 bit.

In the event viewer application log of subscriber I see Failure audit every
3-5 mins during usage of DB. Then it stops when my backup runs and starts
again after the backup.

event reads:

Event ID: 18456
Source: MSSQLSERVER

Login failed for user 'ComputerNameOfSubscriber\SQLaccount.' [CLIENT: <Local
Machine>]

another entry reads:

Login failed for user 'ComputerNameOfSubscriber\SQLaccount.' [CLIENT: IP
ADDRESS of Distributor]

SQLaccount is the account used to run SQLServer.

I also noticed in the both entries Hex the word 'Master' as in the Master
DB?. Not sure whats going on, all I know is the replication works but then
for some reason starts bulk copying all the data and locks my subscription
server from updating records (due to the Bulk copying).
Paul Ibison
7/7/2006 12:00:00 AM
There seem to be several factors here and we'll try to see what's occurring
:)
You have an account on the subscriber which is periodically connecting to
the distributor but failing is this another pull subscription?.
The normal synchronization works ok for your merge replication - pull or
push?.
There's a bulk-copy occuring at the subscriber - is there a reinitialization
being set off? Is there another publication involved?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Some_Ignorance_IN_Replication
7/7/2006 3:37:02 AM
Yes subscriber is pull subscription, and it works most of the time, but then
descides to bulk copy every so often. After i told it that the subscription
never expires the bulk copy only happens about 1 every 3 weeks.

There is only one publication involved and one subscription. I dont know if
there is a reinitalization being set off, but it seems like it. Is there
anyway to check?

[quoted text, click to view]
Paul Ibison
7/8/2006 12:00:00 AM
There's a distribution retention period, a publication retention period and
a history retention period. You could set all three values to something high
eg 4 weeks and then see if the reinitialization still occurs during a 3 week
period.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)



Some_Ignorance_IN_Replication
7/10/2006 7:31:02 AM
Found out that all that was wrong was the snap shot agent was running on a 14
day schedule and the password for the publisher connection at the subcriber
was invalid so the publisher wasnt updating its records that the subscription
successfully replicated. Paul, thanks for your help.

[quoted text, click to view]
Paul Ibison
7/10/2006 4:55:12 PM
Thanks for the update - it was a really confusing one :).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)





AddThis Social Bookmark Button