Merge Replication to multiple servers losing data

Hi,
We are using merge replication between four servers (1 publisher & 3
subscribers). The same articles (tables) are in each publication. I run the
application which changes data in some tables and adds records in another
table. The inserted data is immediately updated via trigger. After running
the application all expected data is present. If I manually force
replication to each subscriber sequentially, all expected data is present.
If I run replication between the servers at the same time, the table to
which data was added will lose some data. The data lost was not the data
that was just added. We are running SQL Server 2000 SP3a on all servers. Any
ideas?
tia,
Paul
Look at the 'view conflict' at replication monitor...
"PaulW" <MSNewsGroup@.Digi-Sol.com>
news:urv9zNxlHHA.1216@.TK2MSFTNGP03.phx.gbl...
> Hi,
> We are using merge replication between four servers (1 publisher & 3
> subscribers). The same articles (tables) are in each publication. I run
> the application which changes data in some tables and adds records in
> another table. The inserted data is immediately updated via trigger.
> After running the application all expected data is present. If I manually
> force replication to each subscriber sequentially, all expected data is
> present. If I run replication between the servers at the same time, the
> table to which data was added will lose some data. The data lost was not
> the data that was just added. We are running SQL Server 2000 SP3a on all
> servers. Any ideas?
> tia,
> Paul
>
|||There are no recorded conflicts. This table only has data inserted, then
updated through a trigger. We did view the transaction log. The only entries
with the table were the inserts we initiated followed by a delete/insert for
the trigger update.
Paul
"Grigoris Tsolakidis" <gcholakidis@.spam_remove.hotmail.com> wrote in message
news:uhRQsHGmHHA.4852@.TK2MSFTNGP03.phx.gbl...
> Look at the 'view conflict' at replication monitor...
> "PaulW" <MSNewsGroup@.Digi-Sol.com>
> news:urv9zNxlHHA.1216@.TK2MSFTNGP03.phx.gbl...
>
sql

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 Primary Key violations

I have a Merge Replication Problem and I'm not sure how to proceed.

Given the following:

Multiple clients have merge subscriptions to a central Server's DB.
DB contains a table tblUser
tblUser has a Varchar field; UserLoginName, which is Primary Key.
It also has an integer field, UserValue

The replication is configured to resolve to the greater integer value.

Client 1 creates a new User
INSERT tblUser
(UserLoginName, UserValue)
VALUES('Fred',1)
At the same time Client 2 does the same thing, but with a larger value
INSERT tblUSer
(UserLoginName, UserValue)
VALUES('Fred', 2)

One of the inserts will get to the Central Server first.
The other will form a conflict.

A row insert at 'Client2.dbUsers' could not be propagated to 'CentralServer.dbUsers'.
This failure can be caused by a constraint violation.
Violation of PRIMARY KEY constraint 'PK_tblUser_1'. Cannot insert duplicate key in object 'dbo.tblUser'.

So now I have two different value fields and a conflict that cannot be resolved.

Is this fixable?
Do I need to make changes at athe Architechture level?

Hi Brian,

if you need to store both records from all subscribers, You should add a column for client id .So once tables replicates from all subscribers each client has their own id.

Example: if client 1 has the code 100 and client 2 has the code 200

the table at subscriber will be : 100,'Fred',1 (Record comes from client 1)

200,'Fred',1 (Record comes from client 2)

if you need to resolve the conflict and to store 1 record only you should assign priority for each subscriber

For more details check the following URL : http://support.microsoft.com/kb/315514

Regards,

Tarek Ghazali

SQL Server MVP

http://www.sqlmvp.com

|||

I need to store only the higher of the two.

If the record already exists and both try to update the record, only the higher is stored, as desired.

But when the record does not exist, both subscribers do an insert, and it does not store only the higher.

Reading all the KB articles on merge replication, it appears that Updates and Inserts are supposed to be resolved the same.

I can't use subscriber priority, as I don't care WHERE the record came from, only the value stored.

Merge Replication and Multiple Record UPDATE statements

We have a database in which global updates must be periodically made to
multiple records, usually in the hundreds or thousands.
Is this a problem when using Merge replication? Because there are individual
merge triggers on each table, is it preferable to use a cursor to make global
changes?
Also, when using View Conflicts feature, is it safe to assume that record
listed as the winner has already been replicated to all locations? If so, why
does one have to click on either the Keep Winning Change or the Resolve With
This Data button?
Is there a way to globally accept all winning changes?
The global update will take far longer with merge replication. A cursor
solution might offer better performance.
Regarding the conflict viewer, it is safe to assume that the conflict has
been resolved with the publisher and one subscriber. It might then conflict
with another subscriber, so you can't assume it has been resolved
everywhere.
The keep winning change button and the resolve with this data buttons allow
you to clear the conflict (in other words accept it) or roll it back and let
the subscribers change win.
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
"elpepe" <elpepe@.discussions.microsoft.com> wrote in message
news:D5C598DA-05F0-4F98-A869-DA53D058D628@.microsoft.com...
> We have a database in which global updates must be periodically made to
> multiple records, usually in the hundreds or thousands.
> Is this a problem when using Merge replication? Because there are
> individual
> merge triggers on each table, is it preferable to use a cursor to make
> global
> changes?
> Also, when using View Conflicts feature, is it safe to assume that record
> listed as the winner has already been replicated to all locations? If so,
> why
> does one have to click on either the Keep Winning Change or the Resolve
> With
> This Data button?
> Is there a way to globally accept all winning changes?
|||Thanks -- that's very helpful.
There are hundreds of conflicts that remain. Is there a way to globally
accept the winner so that the conflicts are resolved everywhere?
Also, will you be writing a book on Merge replication anytime soon?
"Hilary Cotter" wrote:

> The global update will take far longer with merge replication. A cursor
> solution might offer better performance.
> Regarding the conflict viewer, it is safe to assume that the conflict has
> been resolved with the publisher and one subscriber. It might then conflict
> with another subscriber, so you can't assume it has been resolved
> everywhere.
> The keep winning change button and the resolve with this data buttons allow
> you to clear the conflict (in other words accept it) or roll it back and let
> the subscribers change win.
> --
> 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
>
> "elpepe" <elpepe@.discussions.microsoft.com> wrote in message
> news:D5C598DA-05F0-4F98-A869-DA53D058D628@.microsoft.com...
>
>