February 1, 2012 at 2:23 am
Hi,
Operating system on all servers: Windows Server 2008 R2.
Publisher: Sql Server 2008 R2 Standard
Distributor: SQL Server 2008 R2 Standard
Web Synchronization Agent: sqlce35.dll under IIS 7.5
Subscriber: Windows XP SP 3 or Windows 7 SP1
SQL CE Client 3.1
I have an issue where a merge replication stops updating subscriptions without knowing why.
Premises:
• 1 Publication
• 15 items filtered by hostname(). All set to download, None in bidirectional mode.
• 20 or 30 subscribers
You create a merge replication with several articles (15 tables) filtered by a HostName (). This replica push over 20 or 30 subscribers and the synchronization is done correctly. Data collection is done by request from the subscriber, it pull changes two or three times a day. And all the changes are received by the subscriber without any problem.
All this works fine until there comes a time, after a few days without problems the replica will no longer updating a few changes to the subscriber. We search on publicator and have the changes, check the subscriber and not have them. Returning to modify the changes in publicator, sometimes if they are updated in the subscriber and sometimes not.
The problem is that this replica is no longer reliable, we do not know that it is updated and what not.
Focusing on a single subscription-EVDBASD342232 '013243 ... 'and a single article' table1 'is not getting new data made the following verification steps
• Run "sp_showpendingchanges NULL, NULL, 'table1', 1" means the procedure returns me a series of rows that correspond to the data that should be replicated, I have found that the ID matches the subscription-EVDBASD342232 '013243 ... 'and the guid corresponds to the row on the 'table1' that should be replicated.
• We request subscription and we observe in the Replication Monitor to view subscription-EVDBASD342232 '013243 ... 'indicating 0 changes and everything is correct, shows no errors but will not say anything pending synchronization.
After analyzing all the data we do not understand is what is going wrong in the process.
Once a replica does not synchronize, the others do not either.
Please if anyone can help, thanks in advance.
If you need more clarification or details
February 1, 2012 at 7:54 am
February 1, 2012 at 8:56 am
I'm slightly confused. You are using a lot of pronouns, and using "replica" a lot and it's not clear what you mean. I assume you mean an individual node (subscriber), but I'm not sure. Or do you mean a publisher? There's no publicator. Not trying to be nit picky, but it becomes hard to discuss this is we don't have a common language.
Is there a central server of some sort where you are always making changes that are not pushed out to a) all subscribers or b) just those matching the hostname() filter? Or are these changes made at a subscriber being merged back to the publisher?
Is it always the same pair of nodes having issues (same hostname() filter) or does it vary?
February 1, 2012 at 10:00 am
Thanks steve,
When I said replica, I would like to say publisher. Sorry!
There is a central server where the people apply changes (transactions) over different tables. This is the publisher.
After that, the subscribers (20-30) obtain these changes with the pull function.
the hostname() filter is different for each subscription.
After a random number of days always the subscribers don't receive more changes.
February 1, 2012 at 10:18 am
I'm not a replication expert, but all subscribers? Or certain ones?
If I make changes to rows at the publisher with "red", "green" and "blue" values. Does the (or all) subscribers getting "red" changes miss them? Or are all changes (red, green, and blue) not going to all subscribers?
If it's some changes, then is it always the same? (Always red subscriber, or subscribers?)
I'm trying to narrow this down a bit. Have you looked into using Replication Monitor, or checking the status of the changes in the distributor?
February 1, 2012 at 12:39 pm
Thanks Steve,
I going to explain me with a example.
Table1
hostname()=Client.value
id | Client | price |ref
1 | 1| 25 |a
2 | 1| 32 |b
3 | 2| 25 |a
4 | 2| 30 |b
5 | 2| 29 |c
I have two subscriber:
Subscriber 1 hostname()=1 id '1-ACSDFADS'
Subscriber 2 hostname()=2 id '2-ERGS2343'
The fist day I change value to ref 'a' for all clients a 27
Table1 day 1
id | Client | price |ref
1 | 1| 27 |a
2 | 1| 32 |b
3 | 2| 27 |a
4 | 2| 30 |b
5 | 2| 29 |c
Table1 in subscriber 1 after changes day 1
id | Client | price |ref
1 | 1| 27 |a
2 | 1| 32 |b
Table1 in subscriber 2 after changes day 1
3 | 2| 27 |a
4 | 2| 30 |b
5 | 2| 29 |c
same days I change values and add new refs and the subscribers do push and all is fine
Table1 day n
id | Client | price |ref
1 | 1| 15 |a
2 | 1| 18 |b
3 | 2| 19 |a
4 | 2| 10 |b
5 | 2| 13 |c
6 | 2| 14 |d
Table1 in subscriber 1 after changes day n
id | Client | price |ref
1 | 1| 15 |a
2 | 1| 18 |b
Table1 in subscriber 2 after changes day n
3 | 2| 19 |a
4 | 2| 10 |b
5 | 2| 13 |c
6 | 2| 14 |d
the day n or n+1 something happens at the Publisher or subcriptor makes this stop working well
The following changes fail to replicate in subscribers
Table1 day n+1
id | Client | price |ref
1 | 1| 11 |a
2 | 1| 18 |b
3 | 2| 19 |a
4 | 2| 10 |b
5 | 2| 13 |c
6 | 2| 14 |d
Table1 in subscriber 1 after changes day n+1
id | Client | price |ref
1 | 1| 15 |a
2 | 1| 18 |b
Table1 in subscriber 2 after changes day n+1
3 | 2| 19 |a
4 | 2| 10 |b
5 | 2| 13 |c
6 | 2| 14 |d
As you can see I changed the value of 'a' for 11 and is not modified in the subscriber 1, the value for a is 15, any other change is not reflected either in subscriber 1 or subscriber 2.
if we look at the replica monitor shows that changes are not synchronized but also indicates that there are no errors and everything is ok.
And if we use the merge replication procedure sp_showpendingchanges NULL, NULL, 'table1', 1 shows the following results:
subscriber_id | table | is_deleted | rowguid
'1-ACSDFADS' |table1| 0 | ERFSASDFAS
the value ERFSASDFAS is the rowguid for the row:
id | Client | price |ref |rowguid
1 | 1| 11 |a | ERFSASDFAS
this is correct, the changes are pending to pull, but this change is not synchronized in subscriber 1.
next changes not will be synchronized.
I have use this type of replication 5 years in sql server 2005 and I have no problem(this type of problem), we have to do the migration to sql server 2008 R2 and this appears.
February 12, 2012 at 12:45 pm
I have found this update. I hope that solves my problem.
http://support.microsoft.com/kb/2591692/en-us
If anyone has any other solution would be appreciated.
thanks in advance.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply