Nonconvergence issue after upgrading MSDE to SQL 2005 Express

I'm experiencing an intermittent issue with merge replication after
upgrading one of the subscribers to SQL 2005 Express from MSDE 2000.
Here are a few details about the environment before I describe the
issue
1. Originally was SQL 2000 Standard Publisher with MSDE 2000
Subscribers all SP4
2. Upgraded (in place) Publisher to SQL 2005 Standard SP1 - no issues
3. Upgraded 1 of 3 subscribers to SQL 2005 Express SP1 encountered the
following issue only on the upgraded Express installation
4. Merge replication is running continuously as a pull subscription
with column-level tracking
5. Replication is running as a scheduled task on the subscriber (pull)
by executing replmerg.exe
Issue:
Background: A contacts table has addresses stored in a separate table
with the address_id in the contacts table referencing the address_id
in the addresses table
1. Contact created on subscriber without an address (null address_id)
2. Replication runs (contact uploaded to publisher)
3. Contact updated at subscriber to have an address (address_id
populated)
4. Replication uploads changes to publisher
4a. The address is uploaded to the publisher
4b. The address_id update to the contact record appears to be uploaded
to the publisher (confirmed by running SQL profiler)
4c. After the replication session is complete the address_id update to
the contact record appears to have not been made or is rolled back
5. Replication downloads changes to the subscriber and determines that
the address is not part of the partition for the subscriber (because
the address_id is not present in the contact record) and removes it
from the subscriber (the address is still present on publisher)
If I were to bet on a theory about why the address_id update to the
contact record is getting lost I would put my money on a deadlock (or
similar issue) and rollback but I would be interested to hear if
anyone has run into similar issues. I'd also be interested to know why
SQL 2005 Express would behave differently when replicating than MSDE
2000 (this setup has been running for almost 2 years on the MSDE with
no issues). If it is a deadlock/rollback then does the MSDE subscriber
handle the rollback differently than the Express subscriber? Is there
a re-try step that is not running in Express that does run in MSDE?
Have you logged to an output file or run profiler to determine exactly what
is happening?
If it is a deadlock the merge agent is transactional in nature and will put
the error in a retry loop at the end of each batch.
So this doesn't really explain what you are seeing.
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
"Brian Fell" <brianfell@.gmail.com> wrote in message
news:1182807685.623799.115710@.u2g2000hsc.googlegro ups.com...
> I'm experiencing an intermittent issue with merge replication after
> upgrading one of the subscribers to SQL 2005 Express from MSDE 2000.
> Here are a few details about the environment before I describe the
> issue
> 1. Originally was SQL 2000 Standard Publisher with MSDE 2000
> Subscribers all SP4
> 2. Upgraded (in place) Publisher to SQL 2005 Standard SP1 - no issues
> 3. Upgraded 1 of 3 subscribers to SQL 2005 Express SP1 encountered the
> following issue only on the upgraded Express installation
> 4. Merge replication is running continuously as a pull subscription
> with column-level tracking
> 5. Replication is running as a scheduled task on the subscriber (pull)
> by executing replmerg.exe
>
> Issue:
> Background: A contacts table has addresses stored in a separate table
> with the address_id in the contacts table referencing the address_id
> in the addresses table
> 1. Contact created on subscriber without an address (null address_id)
> 2. Replication runs (contact uploaded to publisher)
> 3. Contact updated at subscriber to have an address (address_id
> populated)
> 4. Replication uploads changes to publisher
> 4a. The address is uploaded to the publisher
> 4b. The address_id update to the contact record appears to be uploaded
> to the publisher (confirmed by running SQL profiler)
> 4c. After the replication session is complete the address_id update to
> the contact record appears to have not been made or is rolled back
> 5. Replication downloads changes to the subscriber and determines that
> the address is not part of the partition for the subscriber (because
> the address_id is not present in the contact record) and removes it
> from the subscriber (the address is still present on publisher)
>
> If I were to bet on a theory about why the address_id update to the
> contact record is getting lost I would put my money on a deadlock (or
> similar issue) and rollback but I would be interested to hear if
> anyone has run into similar issues. I'd also be interested to know why
> SQL 2005 Express would behave differently when replicating than MSDE
> 2000 (this setup has been running for almost 2 years on the MSDE with
> no issues). If it is a deadlock/rollback then does the MSDE subscriber
> handle the rollback differently than the Express subscriber? Is there
> a re-try step that is not running in Express that does run in MSDE?
>
|||On Jun 25, 10:06 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Have you logged to an output file or run profiler to determine exactly what
> is happening?
> If it is a deadlock the merge agent is transactional in nature and will put
> the error in a retry loop at the end of each batch.
> So this doesn't really explain what you are seeing.
> --
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com"Brian Fell" <brianf...@.gmail.com> wrote in message
> news:1182807685.623799.115710@.u2g2000hsc.googlegro ups.com...
>
>
>
>
> - Show quoted text -
I ran profiler and saw no evidence of any problem however I do see the
update with the address_id in it (and an entry that indicated that
replication thought the upload was successful (Uploaded 2 change(s) in
''contacts'' (1 insert, 1 update) - one of those for the address entry
as well). Unfortunately, it took me three weeks to catch this thing
with profiler and I didn't have the replication logging to a file when
I started it up that day (was switching back and forth between
watching the console and reviewing a log). Any ideas about something
to look at would be greatly appreciated.
Thanks,
Brian Fell
|||You will probably have to try to repro the problem using the specific set of
commands you outline in your original post.
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
"Brian Fell" <brianfell@.gmail.com> wrote in message
news:1182824624.031896.140300@.o61g2000hsh.googlegr oups.com...
> On Jun 25, 10:06 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> I ran profiler and saw no evidence of any problem however I do see the
> update with the address_id in it (and an entry that indicated that
> replication thought the upload was successful (Uploaded 2 change(s) in
> ''contacts'' (1 insert, 1 update) - one of those for the address entry
> as well). Unfortunately, it took me three weeks to catch this thing
> with profiler and I didn't have the replication logging to a file when
> I started it up that day (was switching back and forth between
> watching the console and reviewing a log). Any ideas about something
> to look at would be greatly appreciated.
> Thanks,
> Brian Fell
>

Nomore snapshot without locking tables?

HI There

After upgrading my publishers to 2005 i noticed that i cannot specify not to lock tables during snapshot during publication creation, also not on publication properties, and i see sp_addpublication has no such parameter, is there no longer an option not to lock publication tables during snapshot?

Thanx

Hi Dietz,

You can specify the @.sync_method parameter of sp_addpublication to be concurrent or concurrent_c.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_repl_4s32.asp

[ @.sync_method=] 'sync_method'

Is the synchronization mode. sync_method is nvarchar(13), and can be one of the following values.

Value

Description

native

Produces native-mode bulk copy program output of all tables. Not supported for Oracle Publishers.

character

Produces character-mode bulk copy program output of all tables. For an Oracle Publisher, character is valid only for snapshot replication.

concurrent

Produces native-mode bulk copy program output of all tables but does not lock tables during the snapshot. Only supported for transactional publications. Not supported for Oracle Publishers.

concurrent_c

Produces character-mode bulk copy program output of all tables but does not lock tables during the snapshot. Only supported for transactional publications.

NULL (default)

Defaults to native for Microsoft SQL Server Publishers. For non-SQL Server Publishers, defaults to character when the value of repl_freq is Snapshot and to concurrent_c for all other cases.

Regards,

Gary

|||

Thanx Gary

As far as i can see this option is not available though management studio when creating a publication or viewing publication properties after creation, correct ? Only through TSQL.