MERGE REPLICATION TUTORIAL

Please, I bought the Book of Hillary Cotter about Transaccional and Snapshot
but... I need to know about Merge. He still is not publishing his book.
So, anyone know where can i download, read or anything about Merge
replication?
Thanks in advance
Jose,
until Hilary's book is ready, BOL is the best resource available. For a more
graphical initial article, this may help :
http://www.mssqlcity.com/Articles/Re...MR/SetupMR.htm
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul.
I found also very good information on Microsoft website. (links that i found
at the end of that article)
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23u0AngVLGHA.2756@.TK2MSFTNGP10.phx.gbl...
> Jose,
> until Hilary's book is ready, BOL is the best resource available. For a
> more graphical initial article, this may help :
> http://www.mssqlcity.com/Articles/Re...MR/SetupMR.htm
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
sql

Merge Replication Problem Missing rows on subscriber

I have set up Mege replication where a publishing subscriber
subscribes to a central publisher. The publishing subscriber publishes
to other regional subscribers. All Subscriptions are set up as pull
subscriptions using dynamic snapshots. The problem however is that
when the associated application is used and merge replication running
across all machines at 1 minute intervals occurrs randomly some rows
within some of the articles are not recieved at the leaf node
subscribers (No errors are received or conflicts generated) . The
changes always make it out to the publishing subscriber. This
inconsistant state can be corrected by inititaing a reinitialisation of
the subscription for all subscrptions relating to the publishing
subscriber. When this occurrs the original shapshot is reapplied and
all the changes since the last snap shot are applied. The result is
all the records are now transferred. In addition if i use associated
web application to create records within the database and then manually
run each merge agent the data is successfully transferred.
I have tried a lot of differnet things to correct this problem.
(Monitored locks / deadlocks etc). Has anyone else experienced this
sort of behavior?
Look to see if these missing rows show up in the conflict viewer. If so you
may be able to roll these deletes back.
Are you using join filters? This could be a factor. If a parent row falls
out of the filter on the subscriber, it will take all rows in the child
tables out of the subscriber if keep_partition_changes is set to false.
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
"Davec" <dcowie@.ballsolutions.com> wrote in message
news:1138756934.548687.176410@.g47g2000cwa.googlegr oups.com...
>I have set up Mege replication where a publishing subscriber
> subscribes to a central publisher. The publishing subscriber publishes
> to other regional subscribers. All Subscriptions are set up as pull
> subscriptions using dynamic snapshots. The problem however is that
> when the associated application is used and merge replication running
> across all machines at 1 minute intervals occurrs randomly some rows
> within some of the articles are not recieved at the leaf node
> subscribers (No errors are received or conflicts generated) . The
> changes always make it out to the publishing subscriber. This
> inconsistant state can be corrected by inititaing a reinitialisation of
> the subscription for all subscrptions relating to the publishing
> subscriber. When this occurrs the original shapshot is reapplied and
> all the changes since the last snap shot are applied. The result is
> all the records are now transferred. In addition if i use associated
> web application to create records within the database and then manually
> run each merge agent the data is successfully transferred.
> I have tried a lot of differnet things to correct this problem.
> (Monitored locks / deadlocks etc). Has anyone else experienced this
> sort of behavior?
>
|||Thanks for the reply Hilary I really appreciate your help,
I am using Join filters with @.keep_partition_changes = N'true'.
There are no conflict tables
The problem seems to be that when synchronization bewteen my lowest
level leafnode subsciber and regional publisher occurr
the merge agent when enumerating pending changes does not pick up the
full set of data that should be delivered to the subscriber based on
the join filter.
I have managed to narrow down the sequence of events that cause the
problem i am experiencing
This is the Replcation topology i have used
CentralPublication ->Regional Publisher 1 (Global Subscription) ->
Subscriber 1 (Local Subscription)
In Step 1 My web application runs against the Central Publisher
database effectively creating rows of data in a number of tables (all
articles of the publication)
Step 2 - Replication takes place between central publisher and the
regional publisher via a global pull subscription. Rows are filtered
via Join filter to the regional Publisher. This seems to be operating
as i would expect
Step 3 - Replication then takes place between the regional Publisher
and the Regional Subscriber. (The regional Publisher republishes a
filtered set to the regional subscriber) Investigation of the
replicated data at the Central publisher is also correct at this stage
Step 4 - Perform mode transactions via web application such that when
synchronisation between central publisher and regional publisher take
place the join filter now includes some of the rows created in step 1
but not part of the filtred data in step 2
Step 5 - Merge Agent executes synchronizing between central publisher
and Regional Publisher. Inspection of the regional Publisher database
also is shown to be correct
Step 6 - Merge Agent executes on regional Subscriber to sync Regional
subscriber with Regional Publisher. And not all rows are transferred
Interestingly the missing rows seem to be the ones that were created in
step 1 and only satisfy the join filters at step 4 and 5.
Actually this problem i have just noticed seems very similar to one you
have been discussing on Developers Dex
http://www.codecomments.com/sql/mess...4712070&Page=1
with regards to Join Filters and GenerationIDs.
|||As part of my ivestigation i have noticed that the msMerge_GenHistory
table at the leafnode subscriber is populated with entries that have
not been necessaily delivered to the the subscriber. Does this sound
like expected operation. Could this account for the missing records in
that the merge process believes they have already been delivered.

Merge Replication on SQL Server 2000 -- too slow?

I'm setting up a Merge replication over a very high bandwidth line, and I'm
running into many performance problems.
1. I'm publishing the data one-way from the Publisher to one Subscriber at
this time. Once the data are synchronized, it's taking forever to push delta
data since the snapshot. I changed to high volume Merge profile, and it's
still way behind. Millions of transactions are waiting at the Publisher.
2. After the initial data synchronization, MSMerge_contents at the
Subscriber contains millions of rows. Why is that? There's no activity at
the Subscriber. Oddly enough, if I were to turn on two ways merge, I'd get
UPDATE data pushing back to the Publisher from the Subscriber. I don't know
why what needs to be updated? There's no application or query running at the
Subscriber. This scares me.
3. Because there's millions of entries in MSMerge_contents table, add/drop
articles from replication causes major problem. What's the best way to
handle this data? I'm replicating 200+ tables and some tables have 10+
millions rows.
Thanks,
Hung
Hi. if you have to do ONE way replication, go for transactional replication
instead; it's way faster than merge replication.
Since it's merge replication, the subscriber db's msmerge_contents table
would grow as every single dml would be recorded into it and would remain
depending upon the retention period u have specified.
Also schedule to defrag the msmerge_contents, msmerge_gen_history,
msmerge_tombstone tables as they are likely to get large.
"Hung" wrote:

> I'm setting up a Merge replication over a very high bandwidth line, and I'm
> running into many performance problems.
> 1. I'm publishing the data one-way from the Publisher to one Subscriber at
> this time. Once the data are synchronized, it's taking forever to push delta
> data since the snapshot. I changed to high volume Merge profile, and it's
> still way behind. Millions of transactions are waiting at the Publisher.
> 2. After the initial data synchronization, MSMerge_contents at the
> Subscriber contains millions of rows. Why is that? There's no activity at
> the Subscriber. Oddly enough, if I were to turn on two ways merge, I'd get
> UPDATE data pushing back to the Publisher from the Subscriber. I don't know
> why what needs to be updated? There's no application or query running at the
> Subscriber. This scares me.
> 3. Because there's millions of entries in MSMerge_contents table, add/drop
> articles from replication causes major problem. What's the best way to
> handle this data? I'm replicating 200+ tables and some tables have 10+
> millions rows.
> Thanks,
> Hung
>
>
|||well, my goal is to get Merge Repl to work. I'm doing a testbed right now by
pushing it one-way from Production server only. Are you saying that
MSMerge_Contents table at the Subscriber contains all rows from the intial
snapshot as well? If so, can I clear it after the intial snapshot synch
because data are pushing one-way right now from the Publisher? I don't want
or expect to see data merging back from the Subscriber at this point. The
one time I saw data propagating back from the Subscriber, I was scared at
the least and didn't understand why that would be possible.
After the inital data synching from the snapshot, ongoing data synching just
can't seem to keep up. We have a big pipe open between the two servers.
Another scare I had was that I got duplicated data at the Publisher itself.
I have an on Insert trigger on TableA to _move_ data from TableB to TableC.
For that one day, for every row from TableB, there were two identical rows
in TableC. How could this be possible? If the trigger fired on TableA fired
twice for some reason, data from TableB should already be cleared from the
first trigger fire. Merge Replication trigger doesn't move data from TableB
to TableC. I couldn't explain that behavior, and I'm scare to turn
replication back on now. Any idea?
Thanks,
Hung
"T" <T@.discussions.microsoft.com> wrote in message
news:18A1F015-179D-405D-B48A-A95F56C777B7@.microsoft.com...[vbcol=seagreen]
> Hi. if you have to do ONE way replication, go for transactional
> replication
> instead; it's way faster than merge replication.
> Since it's merge replication, the subscriber db's msmerge_contents table
> would grow as every single dml would be recorded into it and would remain
> depending upon the retention period u have specified.
> Also schedule to defrag the msmerge_contents, msmerge_gen_history,
> msmerge_tombstone tables as they are likely to get large.
> "Hung" wrote:
|||Hi.
The subscriber's msmerge_contents should not contain data from the snapshot
file, it contains transactions after the snapshot.
data being pushed back from subscriber is likely the case with merge
replication and u can't stop this beahvious.
for second prob., you would be getting duplicate records in TableC if it
were also published and trigger also exist at subscriber for TableA.
When record gets inserted in publisher tableC through TableA delete, same
event will fire at subscriber's TableA and will cause the trigger at
Subscriber also insert into TableC.
now when u replicate changes, publisher TableC record merges with
Subscriber's TableC record and you get two records at both sides.
Disable that trigger at subscriber, you will get TableC updated at
subscriber if it's included in replication.
"Hung" wrote:

> well, my goal is to get Merge Repl to work. I'm doing a testbed right now by
> pushing it one-way from Production server only. Are you saying that
> MSMerge_Contents table at the Subscriber contains all rows from the intial
> snapshot as well? If so, can I clear it after the intial snapshot synch
> because data are pushing one-way right now from the Publisher? I don't want
> or expect to see data merging back from the Subscriber at this point. The
> one time I saw data propagating back from the Subscriber, I was scared at
> the least and didn't understand why that would be possible.
> After the inital data synching from the snapshot, ongoing data synching just
> can't seem to keep up. We have a big pipe open between the two servers.
> Another scare I had was that I got duplicated data at the Publisher itself.
> I have an on Insert trigger on TableA to _move_ data from TableB to TableC.
> For that one day, for every row from TableB, there were two identical rows
> in TableC. How could this be possible? If the trigger fired on TableA fired
> twice for some reason, data from TableB should already be cleared from the
> first trigger fire. Merge Replication trigger doesn't move data from TableB
> to TableC. I couldn't explain that behavior, and I'm scare to turn
> replication back on now. Any idea?
> Thanks,
> Hung
> "T" <T@.discussions.microsoft.com> wrote in message
> news:18A1F015-179D-405D-B48A-A95F56C777B7@.microsoft.com...
>
>
|||"T" <T@.discussions.microsoft.com> wrote in message
news:0AAC406D-BD4B-4F8C-A034-3C454A22AFA5@.microsoft.com...
> Hi.
> The subscriber's msmerge_contents should not contain data from the
> snapshot
> file, it contains transactions after the snapshot.
> data being pushed back from subscriber is likely the case with merge
> replication and u can't stop this beahvious.
>
I'd expect the same thing that the subscriber's msmerge_contents table
should be empty since there's no activity at the Subscriber. But I do get
millions of rows in the Subscriber's msmerge_contents table after the first
initialization. I should have queried back to the source tables to figure
out where these rows in msmerge_contents belong.
I understand about data pushing back from the Subscriber is part of
Merge Replication. In my case, the Subscriber isn't taking any live traffic
from the web or query, so I don't expect the Subscriber to have any data
change to push back to the Publisher. Because after the initialization, the
Subscriber's msmerge_contents contains millions, those entries probably get
pushed back? Again, I don't understand why subscriber's msmerge_contents
would have data. I've tried to clear and reinitialize many times. The one
time where I allowed the Subscriber to push data back, it had millions
UPDATE to upload and with 100 entries batch, that seemed to be eternity.

> for second prob., you would be getting duplicate records in TableC if it
> were also published and trigger also exist at subscriber for TableA.
> When record gets inserted in publisher tableC through TableA delete, same
> event will fire at subscriber's TableA and will cause the trigger at
> Subscriber also insert into TableC.
> now when u replicate changes, publisher TableC record merges with
> Subscriber's TableC record and you get two records at both sides.
> Disable that trigger at subscriber, you will get TableC updated at
> subscriber if it's included in replication.
This is a logical explanation. I don't remember if I allowed Subscriber
to push data back during this time or not. I probably did. Otherwise, it
shouldn't happen. Disabling trigger at the Subscriber would create problem
when both Publisher and Subscriber are Live at the same time. For example,
there's web orders inserting into the Subscriber when it's in production,
then I do want to subscriber's trigger on TableA to move data from TableB to
TableC and upload all data to the Publisher. So, if I disable Subscriber's
trigger, this would create a problem. I thought Merge Replication are
triggers-aware and wouldn't fire twice? It knows that data are pushing from
the Publisher to the Subscriber, therefore, Subscriber's trigger won't fire?
or I misunderstand it. How do I avoid this without disabling Subscriber's
triggers because both servers might be in Production at the same time.
Thanks.
[vbcol=seagreen]
> "Hung" wrote:

Merge Replication Hangs at Connecting To Subscriber

This has been working great up until last night when I manuall stopped the
merge publication while it was publishing. When I tried to start it again
the merge agent pushing the data to remote SQL 2k does not get past
'Connecting to Subscriber: XXX'
Below I have pasted the log file and the agent startup parameters.
Agent Config:
-Publisher [SQL_PUBLISHER] -PublisherDB [DB_Source] -Publication
[Pub_Products] -Subscriber [SQL_SUBSCRIBER] -SubscriberDB [DB_Target]
-Distributor [SQL_PUBLISHER] -DistributorSecurityMode 1 -Continuous -Output
c:\mergeDebug.txt -OutputVerboseLevel 2
Debug Log (ran for 30 min):
**** Start Of Log ****
Microsoft SQL Server Merge Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent:
SQL_PUBLISHER-DB_Source-Pub_Products-SQL_SUBSCRIBER-6
Percent Complete: 0
Connecting to Distributor 'SQL_PUBLISHER'
Connecting to Distributor 'SQL_PUBLISHER.'
Server: SQL_PUBLISHER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 10:31:37 AM]SQL_PUBLISHER.: {call sp_MSgetversion }
[4/14/2005 10:31:37 AM]SQL_PUBLISHER.: {call sp_helpdistpublisher
(N'SQL_PUBLISHER') }
[4/14/2005 10:31:37 AM]SQL_PUBLISHER.BackOfficeDistribution: select
datasource, srvid from master..sysservers where upper(srvname) =
upper(N'SQL_PUBLISHER')
[4/14/2005 10:31:37 AM]SQL_PUBLISHER.BackOfficeDistribution: select
datasource, srvid from master..sysservers where upper(srvname) =
upper(N'SQL_SUBSCRIBER')
[4/14/2005 10:31:37 AM]SQL_PUBLISHER.BackOfficeDistribution: {call
sp_MShelp_merge_agentid (0, N'DB_Source', N'Pub_Products', 1, N'DB_Target')}
[4/14/2005 10:31:37 AM]SQL_PUBLISHER.BackOfficeDistribution: {call
sp_MShelp_profile (6, 4, N'')}
Percent Complete: 0
Connecting to Publisher 'SQL_PUBLISHER.DB_Source'
Initializing
Server: SQL_PUBLISHER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 10:31:38 AM]SQL_PUBLISHER.DB_Source: set nocount on declare
@.dbname sysname select @.dbname = db_name() declare @.collation nvarchar(255)
select @.collation = convert(nvarchar(255), databasepropertyex(@.dbname,
N'COLLATION')) select collationproperty(@.collation, N'CODEPAGE') as
'CodePage', collationproperty(@.collation, N'LCID') as 'LCID',
collationproperty(@.collation, N'COMPARISONSTYLE') as 'ComparisonStyle'
Connecting to Publisher 'SQL_PUBLISHER.DB_Source'
Server: SQL_PUBLISHER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 10:31:38 AM]SQL_PUBLISHER.DB_Source: {call sp_MSgetversion }
Percent Complete: 1
Connecting to Publisher 'SQL_PUBLISHER'
[4/14/2005 10:31:38 AM]SQL_PUBLISHER.BackOfficeDistribution: {call
sp_MShelp_subscriber_info (N'SQL_PUBLISHER', N'SQL_SUBSCRIBER')}
Connecting to Subscriber 'SQL_SUBSCRIBER.DB_Target'
Server: SQL_SUBSCRIBER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 10:31:38 AM]SQL_SUBSCRIBER.DB_Target: {call sp_MSgetversion }
Percent Complete: 2
Connecting to Subscriber 'SQL_SUBSCRIBER'
Percent Complete: 3
Retrieving publication information
Percent Complete: 4
Retrieving subscription information
Percent Complete: 4
The merge process is cleaning up meta data in database 'DB_Source'.
Percent Complete: 4
The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s) in
MSmerge_contents, and 0 row(s) in MSmerge_tombstone.
Percent Complete: 4
The merge process is cleaning up meta data in database 'DB_Target'.
Percent Complete: 4
The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s) in
MSmerge_contents, and 0 row(s) in MSmerge_tombstone.
Percent Complete: 4
Uploading data changes to the Publisher
Connecting to Subscriber 'SQL_SUBSCRIBER.DB_Target'
Server: SQL_SUBSCRIBER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 10:31:40 AM]SQL_SUBSCRIBER.DB_Target: {call sp_MSgetversion }
Connecting to Publisher 'SQL_PUBLISHER.DB_Source'
Server: SQL_PUBLISHER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 10:31:40 AM]SQL_PUBLISHER.DB_Source: {call sp_MSgetversion }
Connecting to Subscriber 'SQL_SUBSCRIBER.DB_Target'
Server: SQL_SUBSCRIBER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 10:31:40 AM]SQL_SUBSCRIBER.DB_Target: {call sp_MSgetversion }
Connecting to Publisher 'SQL_PUBLISHER.DB_Source'
Server: SQL_PUBLISHER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 10:31:41 AM]SQL_PUBLISHER.DB_Source: {call sp_MSgetversion }
Connecting to Subscriber 'SQL_SUBSCRIBER.DB_Target'
Server: SQL_SUBSCRIBER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 10:31:41 AM]SQL_SUBSCRIBER.DB_Target: {call sp_MSgetversion }
Connecting to Publisher 'SQL_PUBLISHER.DB_Source'
Server: SQL_PUBLISHER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 10:31:41 AM]SQL_PUBLISHER.DB_Source: {call sp_MSgetversion }
Connecting to Subscriber 'SQL_SUBSCRIBER.DB_Target'
Server: SQL_SUBSCRIBER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 10:31:41 AM]SQL_SUBSCRIBER.DB_Target: {call sp_MSgetversion }
Connecting to Publisher 'SQL_PUBLISHER.DB_Source'
Server: SQL_PUBLISHER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 10:31:41 AM]SQL_PUBLISHER.DB_Source: {call sp_MSgetversion }
**** End Of Log ****
there is a condition where resources are depleted on the publisher or
subcriber which could show up as this. Stop and start SQL Server to see if
it clears it.
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
"thejoneser" <thejoneser@.discussions.microsoft.com> wrote in message
news:C6C25879-5B53-43EE-9ACE-BB12C77FF7A4@.microsoft.com...
> This has been working great up until last night when I manuall stopped the
> merge publication while it was publishing. When I tried to start it again
> the merge agent pushing the data to remote SQL 2k does not get past
> 'Connecting to Subscriber: XXX'
> Below I have pasted the log file and the agent startup parameters.
> Agent Config:
> -Publisher [SQL_PUBLISHER] -PublisherDB [DB_Source] -Publication
> [Pub_Products] -Subscriber [SQL_SUBSCRIBER] -SubscriberDB [DB_Target]
> -Distributor [SQL_PUBLISHER] -DistributorSecurityMode
1 -Continuous -Output
> c:\mergeDebug.txt -OutputVerboseLevel 2
> Debug Log (ran for 30 min):
> **** Start Of Log ****
> Microsoft SQL Server Merge Agent 8.00.760
> Copyright (c) 2000 Microsoft Corporation
> Microsoft SQL Server Replication Agent:
> SQL_PUBLISHER-DB_Source-Pub_Products-SQL_SUBSCRIBER-6
> Percent Complete: 0
> Connecting to Distributor 'SQL_PUBLISHER'
> Connecting to Distributor 'SQL_PUBLISHER.'
> Server: SQL_PUBLISHER
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4/14/2005 10:31:37 AM]SQL_PUBLISHER.: {call sp_MSgetversion }
> [4/14/2005 10:31:37 AM]SQL_PUBLISHER.: {call sp_helpdistpublisher
> (N'SQL_PUBLISHER') }
> [4/14/2005 10:31:37 AM]SQL_PUBLISHER.BackOfficeDistribution: select
> datasource, srvid from master..sysservers where upper(srvname) =
> upper(N'SQL_PUBLISHER')
> [4/14/2005 10:31:37 AM]SQL_PUBLISHER.BackOfficeDistribution: select
> datasource, srvid from master..sysservers where upper(srvname) =
> upper(N'SQL_SUBSCRIBER')
> [4/14/2005 10:31:37 AM]SQL_PUBLISHER.BackOfficeDistribution: {call
> sp_MShelp_merge_agentid (0, N'DB_Source', N'Pub_Products', 1,
N'DB_Target')}
> [4/14/2005 10:31:37 AM]SQL_PUBLISHER.BackOfficeDistribution: {call
> sp_MShelp_profile (6, 4, N'')}
> Percent Complete: 0
> Connecting to Publisher 'SQL_PUBLISHER.DB_Source'
> Initializing
> Server: SQL_PUBLISHER
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4/14/2005 10:31:38 AM]SQL_PUBLISHER.DB_Source: set nocount on declare
> @.dbname sysname select @.dbname = db_name() declare @.collation
nvarchar(255)
> select @.collation = convert(nvarchar(255), databasepropertyex(@.dbname,
> N'COLLATION')) select collationproperty(@.collation, N'CODEPAGE') as
> 'CodePage', collationproperty(@.collation, N'LCID') as 'LCID',
> collationproperty(@.collation, N'COMPARISONSTYLE') as 'ComparisonStyle'
> Connecting to Publisher 'SQL_PUBLISHER.DB_Source'
> Server: SQL_PUBLISHER
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4/14/2005 10:31:38 AM]SQL_PUBLISHER.DB_Source: {call sp_MSgetversion }
> Percent Complete: 1
> Connecting to Publisher 'SQL_PUBLISHER'
> [4/14/2005 10:31:38 AM]SQL_PUBLISHER.BackOfficeDistribution: {call
> sp_MShelp_subscriber_info (N'SQL_PUBLISHER', N'SQL_SUBSCRIBER')}
> Connecting to Subscriber 'SQL_SUBSCRIBER.DB_Target'
> Server: SQL_SUBSCRIBER
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4/14/2005 10:31:38 AM]SQL_SUBSCRIBER.DB_Target: {call sp_MSgetversion }
> Percent Complete: 2
> Connecting to Subscriber 'SQL_SUBSCRIBER'
> Percent Complete: 3
> Retrieving publication information
> Percent Complete: 4
> Retrieving subscription information
> Percent Complete: 4
> The merge process is cleaning up meta data in database 'DB_Source'.
> Percent Complete: 4
> The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s) in
> MSmerge_contents, and 0 row(s) in MSmerge_tombstone.
> Percent Complete: 4
> The merge process is cleaning up meta data in database 'DB_Target'.
> Percent Complete: 4
> The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s) in
> MSmerge_contents, and 0 row(s) in MSmerge_tombstone.
> Percent Complete: 4
> Uploading data changes to the Publisher
> Connecting to Subscriber 'SQL_SUBSCRIBER.DB_Target'
> Server: SQL_SUBSCRIBER
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4/14/2005 10:31:40 AM]SQL_SUBSCRIBER.DB_Target: {call sp_MSgetversion }
> Connecting to Publisher 'SQL_PUBLISHER.DB_Source'
> Server: SQL_PUBLISHER
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4/14/2005 10:31:40 AM]SQL_PUBLISHER.DB_Source: {call sp_MSgetversion }
> Connecting to Subscriber 'SQL_SUBSCRIBER.DB_Target'
> Server: SQL_SUBSCRIBER
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4/14/2005 10:31:40 AM]SQL_SUBSCRIBER.DB_Target: {call sp_MSgetversion }
> Connecting to Publisher 'SQL_PUBLISHER.DB_Source'
> Server: SQL_PUBLISHER
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4/14/2005 10:31:41 AM]SQL_PUBLISHER.DB_Source: {call sp_MSgetversion }
> Connecting to Subscriber 'SQL_SUBSCRIBER.DB_Target'
> Server: SQL_SUBSCRIBER
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4/14/2005 10:31:41 AM]SQL_SUBSCRIBER.DB_Target: {call sp_MSgetversion }
> Connecting to Publisher 'SQL_PUBLISHER.DB_Source'
> Server: SQL_PUBLISHER
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4/14/2005 10:31:41 AM]SQL_PUBLISHER.DB_Source: {call sp_MSgetversion }
> Connecting to Subscriber 'SQL_SUBSCRIBER.DB_Target'
> Server: SQL_SUBSCRIBER
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4/14/2005 10:31:41 AM]SQL_SUBSCRIBER.DB_Target: {call sp_MSgetversion }
> Connecting to Publisher 'SQL_PUBLISHER.DB_Source'
> Server: SQL_PUBLISHER
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4/14/2005 10:31:41 AM]SQL_PUBLISHER.DB_Source: {call sp_MSgetversion }
> **** End Of Log ****
>
|||Thanks Hilary.
I had to get this fixed yesterday so I deleted the subscription. Did a
manual sync using a scripting tool, then recreated the subscription.
I'm going to try and get my hands on the current hotfix baseline build since
several similar merge replication issues seem to be fixed in it.
-- Chris
"Hilary Cotter" wrote:

> there is a condition where resources are depleted on the publisher or
> subcriber which could show up as this. Stop and start SQL Server to see if
> it clears it.
> --
> 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
> "thejoneser" <thejoneser@.discussions.microsoft.com> wrote in message
> news:C6C25879-5B53-43EE-9ACE-BB12C77FF7A4@.microsoft.com...
> 1 -Continuous -Output
> N'DB_Target')}
> nvarchar(255)
>
>

merge replication and simple recovery model

Hi All,
Is it possible or okay for setting the publishing DB in a merge
replication setup to Simple recovery model instead of the current
Bulk-logged model?
Will this have an impact to the subscribers?
Thanks.
Aramid
Merge doesn't use the transaction log in the same way as transactional, but
even in this case it is a common misconception that FULL recovery mode is
necessary, so to answer your question 'Yes' - it has no effect as far as the
correct workings of replication are concerned.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)