Merge seems to hang at conflict and conflict is not logged

We have a SQL2005 merge replication database with 8 subscribers. Three of the
subscribers have not successfully replicated. We would appreciate any
additional ideas for correcting this. So far we have some inconsistencies.
For example, the "View Synchronization Status" displays:
"Downloaded 100 change(s) in 'OurTable' (100 updates, 1 conflict)"
and the publisher's "Microsoft Replication Conflict Viewer" displays:
"There are no conflicts to view."
If you have any ideas why conflicts are causing the replication to fail
while the conflict viewer shows no conflicts, we would appreciate your input.
Thanx.
Were there any conflicts on the subscriber side?
You can run the conflict viewer there as well, or if that's not
convenient/possible there are sp_xxx procedures to help you view the
conflict tables on the client...

Merge Replication: sp_MSgetmetadatabatch Duration

I'm running SQL 2000 SP4 publisher with approximately 15 MSDE 2000
pull subscribers.
A few of my users are having problems with replication. This is all
done via VPN connections to our site (so they're not on our LAN.)
The merge agent fails with the error "The process could not query row
data at the 'Publisher'."
After running Profiler, it appears that the this is the longest
duration of anything run.
I can't find any real details on this stored procedure, so I'm not
real sure what its doing. I'm also NOT a replication expert. I know
enough to have gotten it running and its been working okay, but these
problems have slowly been creeping up. We can normally get the
synchronization to go through, but its frustrating to my users, and I
may be facing a mutiny! Help! I'll be glad to provide any more details
that anyone requests, and if anyone has any details on what this
stored procedure is doing or how I can find out, that would be most
appreciated!
Try setting querytimeout to a large value or using the slowlink
profile.
On Nov 15, 12:26 pm, dday...@.gmail.com wrote:
> I'm running SQL 2000 SP4 publisher with approximately 15 MSDE 2000
> pull subscribers.
> A few of my users are having problems with replication. This is all
> done via VPN connections to our site (so they're not on our LAN.)
> The merge agent fails with the error "The process could not query row
> data at the 'Publisher'."
> After running Profiler, it appears that the this is the longest
> duration of anything run.
> I can't find any real details on this stored procedure, so I'm not
> real sure what its doing. I'm also NOT a replication expert. I know
> enough to have gotten it running and its been working okay, but these
> problems have slowly been creeping up. We can normally get the
> synchronization to go through, but its frustrating to my users, and I
> may be facing a mutiny! Help! I'll be glad to provide any more details
> that anyone requests, and if anyone has any details on what this
> stored procedure is doing or how I can find out, that would be most
> appreciated!
|||On Nov 15, 1:06 pm, Hilary Cotter <hilary.cot...@.gmail.com> wrote:
> Try setting querytimeout to a large value or using the slowlink
> profile.
> On Nov 15, 12:26 pm, dday...@.gmail.com wrote:
>
>
>
>
> - Show quoted text -
I've done both of those (Set QueryTimeout = 6000) and it still occurs.
|||Index fragmentation could be cuase, you should be rebuilding your merge
system table indexes on a regular basis:
DBCC DBREINDEX (MSmerge_contents, '', 80)
DBCC DBREINDEX (MSmerge_genhistory, '', 80)
DBCC DBREINDEX (MSmerge_tombstone, '', 80)
DBCC DBREINDEX (MSmerge_current_partition_mappings, '', 80)
DBCC DBREINDEX (MSmerge_past_partition_mappings, '', 80)
ChrisB MCDBA
MSSQLConsulting.com
"dday515@.gmail.com" wrote:

> I'm running SQL 2000 SP4 publisher with approximately 15 MSDE 2000
> pull subscribers.
> A few of my users are having problems with replication. This is all
> done via VPN connections to our site (so they're not on our LAN.)
> The merge agent fails with the error "The process could not query row
> data at the 'Publisher'."
> After running Profiler, it appears that the this is the longest
> duration of anything run.
> I can't find any real details on this stored procedure, so I'm not
> real sure what its doing. I'm also NOT a replication expert. I know
> enough to have gotten it running and its been working okay, but these
> problems have slowly been creeping up. We can normally get the
> synchronization to go through, but its frustrating to my users, and I
> may be facing a mutiny! Help! I'll be glad to provide any more details
> that anyone requests, and if anyone has any details on what this
> stored procedure is doing or how I can find out, that would be most
> appreciated!
>
|||On Nov 15, 1:48 pm, Chris <Ch...@.discussions.microsoft.com> wrote:
> Index fragmentation could be cuase, you should be rebuilding your merge
> system table indexes on a regular basis:
> DBCC DBREINDEX (MSmerge_contents, '', 80)
> DBCC DBREINDEX (MSmerge_genhistory, '', 80)
> DBCC DBREINDEX (MSmerge_tombstone, '', 80)
> DBCC DBREINDEX (MSmerge_current_partition_mappings, '', 80)
> DBCC DBREINDEX (MSmerge_past_partition_mappings, '', 80)
> ChrisB MCDBA
> MSSQLConsulting.com
>
> "dday...@.gmail.com" wrote:
>
>
> - Show quoted text -
I've done a full reindex as well prior, still the same problem!
|||Did you try the slow link profile?
Is it possible also that your network link is going down during your sync?
Can you run a ping -t between the publisher and subscriber to verify that
the link stays up during the sync?
RelevantNoise.com - dedicated to mining blogs for business intelligence.
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
"Daniel Day" <dday515@.gmail.com> wrote in message
news:3d27eaf9-9105-47e0-96bc-c6afe7763da0@.v4g2000hsf.googlegroups.com...
> On Nov 15, 1:06 pm, Hilary Cotter <hilary.cot...@.gmail.com> wrote:
> I've done both of those (Set QueryTimeout = 6000) and it still occurs.

merge replication, help!

Hi,

I'm setting up a merge replication between one publisher(sql server 2005 standard sp2) and a couple of subscribers(sql server 2005 express sp2). They are connected with each other through VPN tunnels(1.5M adsl connections). I'm using pull replication, every 20 minutes. The initial snapshot replication was finished properly for each subscriber, but after several hours, the subscribers keep getting such kind of error message "Another merge agent for the subscription or subscriptions is running, or the server is working on a previous request by the same agent. (Source: MSSQLServer, Error number: 21036)". it looks like the vpn tunnel is not good sometimes, and the merge agent just sits there waiting. then another request comes in, it cannot get the handler of that agent which is held by the previous request. So how can i configure the server to release the agent when a new request comes in? or setup the timeout for each request?

Any idea would be appreciated.

Thank you very much!
Maybe you can consider -QueryTimeout parameter for the merge agent?|||thanks for reply.
but would u be able to tell me how to do that?
|||oh, sorry, i found it. the current timeout is 300, just 5 minutes. but i set the merge replication every 20 minutes. then how come this happens?
|||QueryTimeout is how long the merge agent will wait on a given query before timing out. The 20 minutes you specified sounds like the scheduled intervals at which the subscription will synchronize.sql

merge replication, help!

Hi,

I'm setting up a merge replication between one publisher(sql server 2005 standard sp2) and a couple of subscribers(sql server 2005 express sp2). They are connected with each other through VPN tunnels(1.5M adsl connections). I'm using pull replication, every 20 minutes. The initial snapshot replication was finished properly for each subscriber, but after several hours, the subscribers keep getting such kind of error message "Another merge agent for the subscription or subscriptions is running, or the server is working on a previous request by the same agent. (Source: MSSQLServer, Error number: 21036)". it looks like the vpn tunnel is not good sometimes, and the merge agent just sits there waiting. then another request comes in, it cannot get the handler of that agent which is held by the previous request. So how can i configure the server to release the agent when a new request comes in? or setup the timeout for each request?

Any idea would be appreciated.

Thank you very much!
Maybe you can consider -QueryTimeout parameter for the merge agent?|||thanks for reply.
but would u be able to tell me how to do that?
|||oh, sorry, i found it. the current timeout is 300, just 5 minutes. but i set the merge replication every 20 minutes. then how come this happens?
|||QueryTimeout is how long the merge agent will wait on a given query before timing out. The 20 minutes you specified sounds like the scheduled intervals at which the subscription will synchronize.

Merge Replication with different Service Packs

I have a Win 2003 server with SQL 2000 SP3 that is publisher and distributor for several merge replication publications. Subscribers are a mixture of SQL 2000 (running SP3 and SP4) and MSDE. I've run into a problem in creating publications that is fixed in SP4 but for various reasons I can't update all of the subscribers that are running SP3. I don't expect any issues updating just my server but will I have any issues running SP4 on my server and SP3 on the subscribers?

Thanks for the help.

GaryI have not experienced any issues related to mis-matched service packs in my development environment. On the other hand, I have not yet deployed to full scale production.

Regards,

hmscottsql

Merge replication with anonymous subscribers and identity columns

Hi,

I read the BOL on how the publisher will had out identity ranges to subscribers, but it was not clear if this was also the case for anonymous subscribers. Will merge replication with identity columns work with anonymous subscribers that sync via HTTPS?

Thanks,
Darrell Young
Yes, it should work. Please try and let us know if you encounter any issues.

merge replication where deletes are required

Hello all,
I am desperatly need help, our setup is a SQL 2005 server, with merge
replication.
Filtered rows and 120 pocket pcs (subscribers).
We need to delete 100000 rows each night, plus minor inserts, and every time
each pocketpc gets all deletes which kills me and them. I have searched the
net how to achive this without any luck.
I have tried to "reinitilize all" with upload changes, + create new snapshot
+ delete the subscriber and nothing help, all deletes get sent to the
subscriber.
I cannot believe that there are no solution for this common problem, and why
does all the deletes get out to all the subscribers when we have filtered
rows and it works fine with inserts or update?
Please help, anything is better than what we have, and the nightly deletes
are required.
Perhaps you could temporarily turn off the delete tracking:
http://msdn2.microsoft.com/en-us/library/ms146947.aspx?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Merge Replication Push using IP address fails? WTF?

I have a Merge publication that has multiple subscribers. They are all SQL Express, so it's all Push Subscriptions.

When I try to add a subscriber by it's IP address, the snapshot gets delivered. Schema created, data loaded, but I see the following error in Replication Monitor:

The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)

Cannot find the object 'MSmerge_ctsv_E0801EECCA824BB29D48D36D64D2BAEA', because it does not exist or you do not have permission. (Source: MSSQLServer, Error number: 15151)

When I change the IP address in the script to the name of the machine it works perfectly. The ONLY thing I changed in the script was the IP address to the name.

The reason for this is that I have several client machines that will have connectivity, but NO name resolution back at the Server.

Please, Please, Please help!

Sample follows:

Code Snippet

execsp_addmergesubscription

@.publication = N'TestPub',

@.subscriber = N'10.126.22.30',

@.subscriber_db = N'dbTest',

@.subscription_type = N'Push',

@.sync_type = N'Automatic',

@.subscriber_type = N'Local',

@.subscription_priority = 0,

@.description =null,

@.use_interactive_resolver = N'False'

execsp_addmergepushsubscription_agent

@.publication = N'TestPub',

@.subscriber = N'10.126.22.30',

@.subscriber_db = N'dbTest',

@.job_login =null, @.job_password =null,

@.subscriber_security_mode = 0,

@.subscriber_login = N'Valid SQL LOGIN',

@.subscriber_password = N'ValidPassword',

@.publisher_security_mode = 1,

@.frequency_type = 64,

@.frequency_interval = 0,

@.frequency_relative_interval = 0,

@.frequency_recurrence_factor = 0,

@.frequency_subday = 0,

@.frequency_subday_interval = 0,

@.active_start_time_of_day = 0,

@.active_end_time_of_day = 235959,

@.active_start_date = 20070607,

@.active_end_date = 99991231,

@.enabled_for_syncmgr = N'False'

Pushing to an IP address is not supported. No doubt that is why it is failing. You must push to the server name. However you can setup an Alias and push to that. I think the Alias must match the server name.

I did read somewhere that Aliases may not be supported either but I know that does work. You could also consider adding entries to your hosts file.

Martin

|||

DOH!

Thanks for the reply.

I really HATE having to edit the hosts file......

Merge Replication Pull Subscription Error

I have replication scenario using SQL SERVER 2000 as the Distributor/Publisher and multiple MSDE databases as the subscribers. When I set up the scenario to use push subscriptions the replication seems to work well. All changes flow correctly between al
l subscribers and the publisher.
When I try to run a pull subscription scenario the replication will fail. I think that the snapshot agent is failing because of some type of security problem. I get the error:
“SQL Server Agent could not access the replication agent. Use the DCOMCNFG utility to confirm that the SQL Server Agent Windows account has permissions to launch the replication agent. The step failed.”
The server is using windows authentication and has the sp3a on it. Again, when I run the replication using push subscribers it works. When I change it to pull subscribers, I get the error.
Any advice is greatly appreciated,
Phil
Are you using remove agent activation?
If so, you must use your Publisher, Susbcriber, or Distributor as the location of your remote agent.
If not, someone has messed with where your merge.exe program is running.
open up DCOMCnfg, locate Microsoft SQL Server Replication Merge Agent 8.0. click on properties Verify in the location tab, that the program runs locally, in the security tab, click on edit for launch permissions. Make sure the everyone group has special
access.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
-- Phil wrote: --
I have replication scenario using SQL SERVER 2000 as the Distributor/Publisher and multiple MSDE databases as the subscribers. When I set up the scenario to use push subscriptions the replication seems to work well. All changes flow correctly betwe
en all subscribers and the publisher.
When I try to run a pull subscription scenario the replication will fail. I think that the snapshot agent is failing because of some type of security problem. I get the error:
“SQL Server Agent could not access the replication agent. Use the DCOMCNFG utility to confirm that the SQL Server Agent Windows account has permissions to launch the replication agent. The step failed.”
The server is using windows authentication and has the sp3a on it. Again, when I run the replication using push subscribers it works. When I change it to pull subscribers, I get the error.
Any advice is greatly appreciated,
Phil
sql

Merge replication publisher subscriber

Hello
I have Publisher and 200 subscribers
I need to unload Publisher having added N intermediate Publishers
on 50 subscribers to everyone
And each intermediate publisher stores data only for it's owns
subscribers.
Is this possible?
Do I need for subscription that are created to be with ' global '
sync_typ?
Is there a particular order in witch a publications and a subscription
are created?
Thanks for your helps
ps : sql2000 SP4,8.00.2187
Yes this is called republishing and it is recommended when you have large
numbers of merge subscribers. It does require the global priority between
the republishers. You create the main publisher first and then the
downstream ones.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"maroucia" <maroucia@.yahoo.com> wrote in message
news:1161252010.072061.165570@.b28g2000cwb.googlegr oups.com...
> Hello
> I have Publisher and 200 subscribers
> I need to unload Publisher having added N intermediate Publishers
> on 50 subscribers to everyone
> And each intermediate publisher stores data only for it's owns
> subscribers.
> Is this possible?
> Do I need for subscription that are created to be with ' global '
> sync_typ?
> Is there a particular order in witch a publications and a subscription
> are created?
> Thanks for your helps
> ps : sql2000 SP4,8.00.2187
>
|||http://www.replicationanswers.com/Republishing2005.asp

Merge replication publisher subscriber

Hello
I have Publisher and 200 subscribers
I need to unload Publisher having added N intermediate Publishers
on 50 subscribers to everyone
And each intermediate publisher stores data only for it's owns
subscribers.
Is this possible?
Do I need for subscription that are created to be with ' global '
sync_typ?
Is there a particular order in witch a publications and a subscription
are created?
Thanks for your helps
ps : sql2000 SP4,8.00.2187Yes this is called republishing and it is recommended when you have large
numbers of merge subscribers. It does require the global priority between
the republishers. You create the main publisher first and then the
downstream ones.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"maroucia" <maroucia@.yahoo.com> wrote in message
news:1161252010.072061.165570@.b28g2000cwb.googlegroups.com...
> Hello
> I have Publisher and 200 subscribers
> I need to unload Publisher having added N intermediate Publishers
> on 50 subscribers to everyone
> And each intermediate publisher stores data only for it's owns
> subscribers.
> Is this possible?
> Do I need for subscription that are created to be with ' global '
> sync_typ?
> Is there a particular order in witch a publications and a subscription
> are created?
> Thanks for your helps
> ps : sql2000 SP4,8.00.2187
>|||http://www.replicationanswers.com/Republishing2005.asp

Merge replication publisher subscriber

Hello
I have Publisher and 200 subscribers
I need to unload Publisher having added N intermediate Publishers
on 50 subscribers to everyone
And each intermediate publisher stores data only for it's owns
subscribers.
Is this possible?
Do I need for subscription that are created to be with ' global '
sync_typ?
Is there a particular order in witch a publications and a subscription
are created?
Thanks for your helps
ps : sql2000 SP4,8.00.2187Yes this is called republishing and it is recommended when you have large
numbers of merge subscribers. It does require the global priority between
the republishers. You create the main publisher first and then the
downstream ones.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"maroucia" <maroucia@.yahoo.com> wrote in message
news:1161252010.072061.165570@.b28g2000cwb.googlegroups.com...
> Hello
> I have Publisher and 200 subscribers
> I need to unload Publisher having added N intermediate Publishers
> on 50 subscribers to everyone
> And each intermediate publisher stores data only for it's owns
> subscribers.
> Is this possible?
> Do I need for subscription that are created to be with ' global '
> sync_typ?
> Is there a particular order in witch a publications and a subscription
> are created?
> Thanks for your helps
> ps : sql2000 SP4,8.00.2187
>|||http://www.replicationanswers.com/Republishing2005.asp

merge replication problem ; very urgent

We have a publisher and 2 subsribers. The subscribers are spanning 2 transactions/sec. We have inplemented merge replication and it was scheduled to occur every 5 min. When we start the replication, the replications is going on without any problem for sometime(may be some hours), but after that it failes with the message, "the process could not deliver inserts at the publisher". We have SAN. When I searched for this error in google, it asked me to increase the queytimeout value in agent profile. So I increased it from 300 to 600, but in vain. If anybosy could help me in this, that would be great.

Thanks,

Preethi.

Hi Preethi,

Is this SQL 2000 or SQL 2005?
Is there a large amount of data yet to be propagated across when this failure occurs?
Are any other processes running on the Publisher machine that are taking up the CPU or memory and hence the merge agent timing out?
Are there any constraints on the publisher that are making the inserts to fail?
Can you post the complete error message from the Merge agent.
Also a higher verbosity log will help. Run the merge agent with the parameter -OutputVerboseLevel 2 or 3.|||sql 2000.
we have 4 merge agents running thats it.
i took an error log with the high verbosity level.
In job history, the error is "remote procedure call failed and it didn't execute".
In the log that I took, the above error message i gave occured. Actually when we started replication fresh, it ran for 4 days for transaction rate 75K/hr. Now even with 12K/hr it is failing in 3 min.

Regarding error i will post it.|||Are you using any agent profile to run the merges?
When you changed the Timeout value, where did you change it?
Try increasing the timeout and logintimeout values. Set -QueryTImeout 1000 -LoginTimeout 1000 and see if it helps.

Another thing you could try is setting the -UploadGenerationsPerBatch and -DownloadGenerationsPerBatch to a low value say 25 and run the merge agent.

Merge Replication Problem (Private,Public Data - Security Issue)

Hello,
We plan to establish a merge replication topology in which several servers
(subscribers) have "public" data – data that have to be propagated to the
other servers, and private data - data reside only at original servers.
Private and public data are marked with one table column (1/0). Those servers
are at local branches in different countries. We have one server on the
Internet, which can be publisher and hold all public data, besides its own
private data. Subscriptions will be anonymous pull subscriptions. Subscribers
would periodicaly make dialup connections and merge data. Article filter
would be something like "Public=1". The problem is that subscribers' private
data will go to the publisher in merge replication. As far as we understand,
filter is only checked at the publisher and not at the subscriber. Only
public data goes from publisher to the subscribers, but all data goes from
subscriber to the publisher.
Our client is to happy about the fact that data marked as private is
available on the Internet server.
Is there any possibility to prevent private data going to the publisher?
Many thanks in advance.
Mirza
The easiest solution is to partition the data - have 2 tables, one for
private and one for public. This way, the data is protected and can be
selectively replicated. To show the data to the user on teh subscriber you
could use partitioned views, or standard views with an instead-of trigger.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Merge replication problem

I have one corporate server that acts as both publisher and distributor.
Remote locations all have SQL server loaded and act as subscribers. This
setup has worked great until yesterday. Somehow, and it is still under
investigation, the corporate server "lost" all subscriptions and became
disconnected from the remote locations.
Each location is on a 5 minute schedule for the merge agent to run.
After recreating all the subscriptions and "pushing" them down to the remote
locations, there has been information lost during the down time. The
snapshot agent had to be run in order to recreate the subscriptions. I am
trying to figure out how I could have reconfigured the setup so that when
the subscription was made all the data in the remote locations would have
been preserved and "Merged" with the data from the server.
Thank you in advance for you input.
WB
Should this situation happen again I suggest:
1. Prevent further updates on remote stations until replication is re-setup
OR choose a time for the resync that least interferes with operations - the
longer you leave it the worse your problem becomes
2. Remove publication(s) and subscriptions
3. Use a tool like Red Gates "Data Compare" to see what data the publisher
is missing - it will generate the sql inserts for you, this sql may need to
be manually tweaked
4. Apply update scripts
5. Recreate publication(s) and subscriptions
6. Find the person who caused the problem and apply thumbscrews
Jim.
"WB" wrote:

> I have one corporate server that acts as both publisher and distributor.
> Remote locations all have SQL server loaded and act as subscribers. This
> setup has worked great until yesterday. Somehow, and it is still under
> investigation, the corporate server "lost" all subscriptions and became
> disconnected from the remote locations.
> Each location is on a 5 minute schedule for the merge agent to run.
> After recreating all the subscriptions and "pushing" them down to the remote
> locations, there has been information lost during the down time. The
> snapshot agent had to be run in order to recreate the subscriptions. I am
> trying to figure out how I could have reconfigured the setup so that when
> the subscription was made all the data in the remote locations would have
> been preserved and "Merged" with the data from the server.
> Thank you in advance for you input.
> WB
>
>
|||I don't know how successful I will be at applying the thumbscrews to myself,
as it appears to have happened on my watch. It appears that while trying to
create a new remote server (from the remote location) and then send down the
snapshot and merged replication data, that the SQL server at the main office
removed the subscriptions of all the remote locations. Not sure how the
subscriptions were removed or why SQL thought it needed to do that, but I
may never know....
I will definitely look into the data compare tool; that will probably come
in handy in the future.
"Jim Breffni" <JimBreffni@.discussions.microsoft.com> wrote in message
news:C3E8DA76-F717-4353-8E0B-FACDA058F3DB@.microsoft.com...
> Should this situation happen again I suggest:
> 1. Prevent further updates on remote stations until replication is
re-setup
> OR choose a time for the resync that least interferes with operations -
the
> longer you leave it the worse your problem becomes
> 2. Remove publication(s) and subscriptions
> 3. Use a tool like Red Gates "Data Compare" to see what data the
publisher
> is missing - it will generate the sql inserts for you, this sql may need
to[vbcol=seagreen]
> be manually tweaked
> 4. Apply update scripts
> 5. Recreate publication(s) and subscriptions
> 6. Find the person who caused the problem and apply thumbscrews
>
> Jim.
>
> "WB" wrote:
This[vbcol=seagreen]
remote[vbcol=seagreen]
am[vbcol=seagreen]
when[vbcol=seagreen]
have[vbcol=seagreen]

Merge Replication over Internet with MSDE and Sql Server (Urgent)

Hello,
I would like to setup Merge Replication between a central Sql Server as
publisher and a variety of MSDE clients as subscribers. The catch that I am
running into is that I want to be able to do this over the Internet. I have
set this up with SQL CE, so I assume that it is possible with MSDE, however,
I just can't seem to find any documentation on this. I would appreciate it
if someone could tell me if this is possible or not.
Other questions I have (but have not research a ton for yet are)
1) How can I programatically kick off a sync on the MSDE from a .NET
application?
2) If conflicts occur, how can I programatically get the conflicts back to
the client application to display in a form and allow the user to resolve
them?
It is frustrating that this is so easy with SQL CE and here I can't seem to
figure out how to do it with MSDE.
Thanks so much for your help.
Marie
Create your publication for anonymous pull. Use the merge ActiveX control on
the subscriber to pull the subscription.
You will need to specify the publisher name, network name (ie ipaddress or
FQDN), and transport mechanism using Publisher, PublisherAddress, and
PublisherNetwork properties respectively.
Check out this link for more info.
http://support.microsoft.com/default...&Product=sql2k
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Marie" <Marie@.discussions.microsoft.com> wrote in message
news:70B8068D-06BA-4036-9433-BF3B5FBB9714@.microsoft.com...
> Hello,
> I would like to setup Merge Replication between a central Sql Server as
> publisher and a variety of MSDE clients as subscribers. The catch that I
am
> running into is that I want to be able to do this over the Internet. I
have
> set this up with SQL CE, so I assume that it is possible with MSDE,
however,
> I just can't seem to find any documentation on this. I would appreciate
it
> if someone could tell me if this is possible or not.
> Other questions I have (but have not research a ton for yet are)
> 1) How can I programatically kick off a sync on the MSDE from a .NET
> application?
> 2) If conflicts occur, how can I programatically get the conflicts back to
> the client application to display in a form and allow the user to resolve
> them?
> It is frustrating that this is so easy with SQL CE and here I can't seem
to
> figure out how to do it with MSDE.
> Thanks so much for your help.
> Marie
>
>
>
|||Actually I am a little wrong on question 2. With Windows Synchronization
Manager, you click on your subscription, click on properties, click on
other, and select Resolve Conflicts Interactively.
You can also use the Microsoft SQL Conflict Resolver control for this.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23sqW9bYiEHA.3428@.TK2MSFTNGP11.phx.gbl...
> Create your publication for anonymous pull. Use the merge ActiveX control
on
> the subscriber to pull the subscription.
> You will need to specify the publisher name, network name (ie ipaddress or
> FQDN), and transport mechanism using Publisher, PublisherAddress, and
> PublisherNetwork properties respectively.
> Check out this link for more info.
>
http://support.microsoft.com/default...&Product=sql2k[vbcol=seagreen]
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Marie" <Marie@.discussions.microsoft.com> wrote in message
> news:70B8068D-06BA-4036-9433-BF3B5FBB9714@.microsoft.com...
I[vbcol=seagreen]
> am
> have
> however,
> it
to[vbcol=seagreen]
resolve
> to
>
|||BTW - your answer to number 2 is AFAIK - no. When you set up your merge
publication for interactive conflict resolution, pull it using Windows
Synchronization Manager, and a conflict occurs you get a dialog telling you
a conflict has occured and to contact your system administrator.
At this point you can educate your users to open the Conflict Resolver on
their own, or simply to contact you and you can resolve them.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23sqW9bYiEHA.3428@.TK2MSFTNGP11.phx.gbl...
> Create your publication for anonymous pull. Use the merge ActiveX control
on
> the subscriber to pull the subscription.
> You will need to specify the publisher name, network name (ie ipaddress or
> FQDN), and transport mechanism using Publisher, PublisherAddress, and
> PublisherNetwork properties respectively.
> Check out this link for more info.
>
http://support.microsoft.com/default...&Product=sql2k[vbcol=seagreen]
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Marie" <Marie@.discussions.microsoft.com> wrote in message
> news:70B8068D-06BA-4036-9433-BF3B5FBB9714@.microsoft.com...
I[vbcol=seagreen]
> am
> have
> however,
> it
to[vbcol=seagreen]
resolve
> to
>

Merge Replication limit running in continuous mode

Hi,
We have a customer site running merge replication with a
publisher/distributer and 12 subscribers. All the merge agents are running in
continuous mode. It seems that two of the agents are always failed, or don't
last long. Is this directly related to the "number of concurrent merge
processes" number? It looks like because there are 10 merge agents *always*
running, the other two don't get a look in. Is this correct?
We can increase this number to 20 and the site seems to run ok, but we've
been hearing rumours that any number past 10 concurrent merge processes gets
unstable. What's the MS position on this?
What's the recommended solution to this situation? Schedule the agents
instead of continuous mode?
What is the error message generated by these two always failing agents?
Do you have the "limit the number of concurrent merge processes to the
following" option checked and set to 10.
What happens is that for long running merge agents the agents will timeout
or deadlock themselves, so MS advised you to limit the number that run
concurrently.
If you do choose this option you have to configure your agents to wait a
longer time before timing out. To do this right click on your merge agents,
select agent properties, click on the Steps tab, and double click on Run
Agent. Place focus in the commands text box, press End, and press the space
bar. enter -StartQueueTimeout 120
This will have the effect of having these two failing merge agents of
waiting up to 2 minutes for the first 10 merge agents in the queue to finish
their work.
I am not sure what the rational for 10 was. The fact it seems to work fine
when you bump it up to 20 seems to indicate that this throttling is not
optimal for you. I suspect that you did not use StartQueueTimeout option.
Running the agents in continuous mode will have no effect. The agents poll
by default every minute, so after the agent completes it will go to sleep
for a minute, and any agents in the queue will have their timeslice.
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
"Sydney" <hsc@.newsgroup.nospam> wrote in message
news:5C49D704-C649-41FA-8686-3EA92151867F@.microsoft.com...
> Hi,
> We have a customer site running merge replication with a
> publisher/distributer and 12 subscribers. All the merge agents are running
> in
> continuous mode. It seems that two of the agents are always failed, or
> don't
> last long. Is this directly related to the "number of concurrent merge
> processes" number? It looks like because there are 10 merge agents
> *always*
> running, the other two don't get a look in. Is this correct?
> We can increase this number to 20 and the site seems to run ok, but we've
> been hearing rumours that any number past 10 concurrent merge processes
> gets
> unstable. What's the MS position on this?
> What's the recommended solution to this situation? Schedule the agents
> instead of continuous mode?
>

Merge replication issue

I have merge replication set up running between a central database and
multiple subscribers. I am noticing that data that being sent from the
subscriber is not being applied to the publisher with conflicts. I am also
seeing that data that was in the subscriber yesterday is not in the
subscriber today. Some of the tables are set to use timestamp (latest wins)
as the conflict resolver while others are set to use the "default" conflict
resolver. What is the default? Why is data being removed on the subscriber
when it originated from there to begin with? If I change the conflict
resolver on the tables to be "subscriber always wins" will this help solve
the problem of missing data?
Cmoss,
do you have filters? If the rows aren't allowed through the filter clause,
this could explain why records added at the subscriber are removed on
synchronization.
The default resolver is publisher always wins, and if 2 subscribers
conflict, the first attains the priority of the publisher.
HTH,
Paul Ibison
|||Paul,
Thanks for the response. I do have filters on the articles, but the data
that is being removed is from the same tables that data was successfully
synchronized from before. I have changed the conflict type on some of my
crucial articles to be "subscriber always wins" becuase that is where the
data originates from. It is my understanding that I can change this
parameter at any time, correct?
"Paul Ibison" wrote:

> Cmoss,
> do you have filters? If the rows aren't allowed through the filter clause,
> this could explain why records added at the subscriber are removed on
> synchronization.
> The default resolver is publisher always wins, and if 2 subscribers
> conflict, the first attains the priority of the publisher.
> HTH,
> Paul Ibison
>
>
|||Yes - in my experience the resolver can be changed
dynamically. This'll mean that publisher deletes will be
discarded if the records are updated on the subscriber.
HTH,
Paul Ibison
|||Paul,
Thanks for the info. I an reviewing my subscriptions an have noticed
something odd. I have one subscription that synchronized last evening and
the results are as follows:
Merged 0 data changes ( 0 inserts, 0 updates, 0 deletes, 2 resolved conflicts)
How can I have conflicts if no data is synchronized between the local and
remote databases? Is there a way I can see what these conflicts are? I can
look at the data in the msmerge_delete_conflicts table but there were no
deletes performed.
"Paul Ibison" wrote:

> Yes - in my experience the resolver can be changed
> dynamically. This'll mean that publisher deletes will be
> discarded if the records are updated on the subscriber.
> HTH,
> Paul Ibison
>
|||Cmoss,
yes , you can right-click the publication and select 'View Conflicts'. The
conflict has already been resolved, but the details are logged and in the
conflict viewer you can reverse the selected winner/loser.
HTH,
Paul Ibison
|||I actually faces the same problem too. I used the default resolver for all
the conflict tables. Somehow I realize that there's conflict even though the
server did not do any changes and only the client do the update of a row. I
wonder why there could be conflicts even though only the subscriber do the
update. I have no choice but to switch to 'subscriber wins over publisher'
conflict resolver to solve the problem though it is not the ideal solution.
Yimmy

Merge Replication Indentity Semi-Automatic Range allocation

I have a publisher and 2 subscribers on the network and the table I am merge
replicating has an identity field, with NOT FOR REPLICATION set, to allow me
to use identiy ranges which it does very well, however I have 1 problem and 1
question.
Problem 1
When I reach my threshold (80%) it stops that server from creating new
records and an error is produced until the merge agent has run and the best
that can be is once a minute. How can I configure the system to not wait for
the next time the merge agent runs, as this system will be creating 100's of
transactions a minute.
Question 1
If the threshold is set to 80% and I have a range of 1000, then when it gets
to 800 it stops and sets the next id number to the first in the next block
meaning that the remaining 200 are redundant.
I would have expected that when the threshold is reached that until the new
numbers are received then the server would use the range after the threshold
until it receives the next range otherwise why not always set the threshold
to 100% as it just errors when it hits the threshold anyway? Looking at BOL
it says for transactional then the agent runs continously, but with merge no
such statement, and as its merge based the subscribers could be offline for
some time, so shouldn't there be a more controlled process when they reach
their threshold.
I think I am missing an option on the publication maybe
thanks in advance.
Neil.
Thanks for the kb's Paul but the threshold value seems to be completely
pointless to me
In KB 322910 it says
Before you implement ranged identity management with merge replication, you
must first consider how many inserts will be performed by users and how
frequently they will be merging their changes. The primary goal when defining
the identity options for an article is to make the ranges large enough that
the Subscriber will not run out of values before the next merge.
Which suggests to me that the subscribers should get a new block of identity
values when they merge, however in my testing, a new range is only produced
when the subscriber has reached the threshold level and an error is produced
and they are not allowed to create any new records until the merge which does
mean that the threshold value is pointless and should be set to 99% or 100%
I hope this makes sense.
Neil.
"Paul Ibison" wrote:

> Neil,
> there are some problems with what you are trying to do
> that are documented:
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;304706&Product=sql2k
> http://support.microsoft.com/?kbid=310540
> I would suggest one of 2 options:
> (a) set the range size so large that it will never need
> to reseed
> or
> (b) use manual range management and a simple algorithm eg
> in the simplest case odd and even values for the case
> where there is just a publisher and subscriber.
> Regards,
> Paul Ibison
> (The ONLY sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Neil,
if you are regularly synchronizing, a new range will be
requested as a part of the synchronization process. This
is before the check constraint that controls the upper
boundary reports an error when a subscriber who performs
an insert reports an error. So although there will be
unused identity values, this should lead to a smooth
running of the subscriber.
HTH,
Paul Ibison
(The ONLY sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I think your problem is because you are over running your threshold in the
batch. The way it is designed is that you pick a range which is
representative of the largest batch which could occur on your
publisher/subscriber and then adjust it upwards so that the threshold lies
within this batch.
Here are some examples
1) update 10,000 rows in a batch, max range is 10,000 threshold is 80% -
results range exceeded transaction rolled back
2) update 10,000 rows in a batch, max range is 20,000 threshold is 50% -
threshold exceeded, range adjusted on publisher and subscriber
3) update 10,000 rows in a batch, max range is 20,000 threshold is 80% -
threshold not exceeded, with first batch. range not adjusted on publisher.
Second batch runs, range exceeded, transaction rolled back.
4) update 10,000 rows in a batch, max range is 100,000, threshold is 80%,
threshold not exceeded until the 8th run, range adjusted on publisher and
subscriber, AND the publisher can still accept to more 10,000 batches before
blowing up.
The way most dba implement this is the set it and forget it philosophy. They
look at their publisher and subscriber and they parcel out the ranges that
they believe will not be exceeded during the lifetime of their replication
solution, and they don't have to worry about it.
One more point when you run into problems with automatic identity range
management you can use the following proc to fix things.
sp_mScheckidentityrange with the @.checkonly parameter having a value of 1
Some people have complained to me that the @.checkonly parameter is ignored
and the range is not updated. I havent' been able to repro this yet, but you
can always sp_MSadjustmergeidentity directly.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Neil" <Neil@.discussions.microsoft.com> wrote in message
news:7A350532-D333-4436-9035-87A376B935BC@.microsoft.com...
> Thanks for the kb's Paul but the threshold value seems to be completely
> pointless to me
> In KB 322910 it says
> Before you implement ranged identity management with merge replication,
you
> must first consider how many inserts will be performed by users and how
> frequently they will be merging their changes. The primary goal when
defining
> the identity options for an article is to make the ranges large enough
that
> the Subscriber will not run out of values before the next merge.
> Which suggests to me that the subscribers should get a new block of
identity
> values when they merge, however in my testing, a new range is only
produced
> when the subscriber has reached the threshold level and an error is
produced
> and they are not allowed to create any new records until the merge which
does
> mean that the threshold value is pointless and should be set to 99% or
100%[vbcol=seagreen]
> I hope this makes sense.
> Neil.
> "Paul Ibison" wrote:

Merge Replication Identity Range Clash

We are using SQL Server 2000 merge replication and have a publisher with
three remote subscribers. Everything has been working well for a year but
there is a problem with a newly published table article. The table has a
primary key with the Not For Replication option set. However, the publisher
and two of the three subscribers are using the same identity range and
causing primary key violations. We are not sure as to why this has occurred
but it could be fixed by reseeding the identity range for the table at each
subscriber. Does anyone know of a way to do this?
Dropping the article and recreating will not be possible as we do not want
to drop the existing subscriptions.
Any suggestions would be appreciated
Adam
Adam,
you could synchronize, drop the publication then reseed the identity
columns, recreate the publication with manual control of the identity ranges
and add subscribers with the nosync option.
Regards,
Paul Ibison