Merge reversed

Running Win 2000 & SQL 2000
I have a merge publication/subscription (rows are
filtered by location), replicating data between 16
servers (15 remote location & a main office). When data
is changed at the a remote location, the changes gets
propagated to the main office, but at some point, the
change gets reversed.
Any idea what is causing this?
Larry,
when you say it gets reversed, do you mena the row is deleted from the
subscriber? This could be caused if you are partitioning data and a row has
been added on a subscriber which doesn't accord with its filter.
HTH,
Paul Ibison
|||Look in the conflict table, for the article, on the publisher. This might
give you a clue as to what is going on.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Paul,
No the row is not deleted, but changed back to its
original state, prior to being updated at the
subscriber. This happens sporadically, but is causing
MAJOR problems.
Larry...
|||Rand,
There is nothing in the conflict tables for any article
in the publication.
Larry...
|||I saw your reply to Rand and if both filtering and conflicts are ruled out
(and no user on the publisher is altering the data!) then this is very
strange. I'd use profiler or an audit trigger to capture what is going on.
HTH,
Paul Ibison
|||Thanks Paul...will do.
|||Does the MSmerge_history table on the distributor offer any reason why
the updates were not applied?
Reinout Hillmann
SQL Server Product Unit
This posting is provided "AS IS" with no warranties, and confers no rights.
anonymous@.discussions.microsoft.com wrote:
> Rand,
> There is nothing in the conflict tables for any article
> in the publication.
> Larry...
>

Merge replication when new tables are created regularly

Hi all,
I am trying to replicate a database (sql server 2000) to a remote site. I
use merge replication and do almost continous replication.
My problem is I have one application which creates atleast 6 or 7 new tables
a day. Everytime it create a tables, snapshot agent restarts again and this
makes the entire server slow. Also when doing this snapshot agent fails most
often!
How can I get around this issue. Anybody with insight to this issue, plz
help me..
Regards,
Maani
The problem with the snapshot agent on merge publications is that it
snapshots the entire publication, even when only one article is added, and
there isn't an option of a concurrent snapshot unlike transactional. This is
probably causing the snapshot errors you are getting. It's not always very
practical, but you could potentially add the new tables to a new
publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Sounds to me like a design problem in the application. At that rate, you
would be adding approx. 2,500 tables a year! I'd hate to administer that
database.
David
"Maani" <Maani@.discussions.microsoft.com> wrote in message
news:709E0B34-622A-4E17-9996-5DCBC45D3C93@.microsoft.com...
> Hi all,
> I am trying to replicate a database (sql server 2000) to a remote site. I
> use merge replication and do almost continous replication.
> My problem is I have one application which creates atleast 6 or 7 new
> tables
> a day. Everytime it create a tables, snapshot agent restarts again and
> this
> makes the entire server slow. Also when doing this snapshot agent fails
> most
> often!
> How can I get around this issue. Anybody with insight to this issue, plz
> help me..
> Regards,
> Maani
|||So.. What should I do?
create a new publication periodically and and all newly created tables
should be added on to newest publication? How can i do that then? Somebody
plz help me with the scripts please as I am not professional DBA... :-)
"David" wrote:

> Sounds to me like a design problem in the application. At that rate, you
> would be adding approx. 2,500 tables a year! I'd hate to administer that
> database.
> David
> "Maani" <Maani@.discussions.microsoft.com> wrote in message
> news:709E0B34-622A-4E17-9996-5DCBC45D3C93@.microsoft.com...
>
>

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 Problem

We have setup merge replication on a production server/database that
is also referenced by our web application. The replication works with
a remote, disconnected application that uses MSDE as it's back-end.
The replication works correctly, but we had to set aside a range of
PKIDs for each table because we were getting duplicate primary key
errors during synchronization of data. Unfortunately, this causes
problems with the web application. After the range of IDs runs out,
the web application can no longer update the database.
Has anyone else run into this problem? Is there a way around using
this method that will still remove the chance of getting a duplicate
primary key error during synchronization? Is setting aside a range of
IDs the only method for avoiding this error?
Thanks in advance
Dan Broomall
Please see my previous answer.
Regards,
Paul
|||Hi,
I have recently implemented merge replication between 1 publisher and 1
subscriber, with push subscription for every 10 minute interval. the
replication went fine, but we are experiencing huge performance problems.
most application users are not able to work on the primary server. We have
temporarily disabled the merge agents, but still the performance is not
boosted. Both publisher and distributor are on one machine with log files on
different drives. What should be the counters to monitor performance?
Also, the clients connect using the http port. the webserver is also on the
same machine as the sql server.
"Paul Ibison" wrote:

> Please see my previous answer.
> Regards,
> Paul
>
>
|||ST,
as you have disabled your merge agents, this side of things is obviously not
the cause. However, the replication triggers will still fire so this is an
overhead. For a general slowness of the sql server, you could look at
processor, memory and disk counters in Performance Monitor. Several articles
list the counters you might start with, so I won't list them here, but this
is a good starting point:
http://www.sql-server-performance.co...ce_article.asp
HTH,
Paul Ibison
|||Thankyou for responding. I have 1 more question, Do we have to replicate the
views and stored procedures compulsarily. I decided not to in the end because
all we really need is the data to replicate. Now i am thinking if the
execution of the stored procedures is causing the performance overhead.
Please tell me if by replicating the stored procedures and views, will i have
a performance boost. I want to do it if it is absolutely necessary.
Thanks
S.T
"Paul Ibison" wrote:

> ST,
> as you have disabled your merge agents, this side of things is obviously not
> the cause. However, the replication triggers will still fire so this is an
> overhead. For a general slowness of the sql server, you could look at
> processor, memory and disk counters in Performance Monitor. Several articles
> list the counters you might start with, so I won't list them here, but this
> is a good starting point:
> http://www.sql-server-performance.co...ce_article.asp
> HTH,
> Paul Ibison
>
>
|||ST,
I'm not sure which stored procedures and views you are referring to. In
merge you can replicate them, but if you use transactional you can not only
replicate the TSQL - entries in sysobjects, syscomments, syscolumns etc -
you can also choose to replicate the execution of stored procedures. Maybe
this is what you are looking for? I'm not too sure how this relates to your
performance issues however. Please can you expand a little.
TIA,
Paul Ibison
|||When i setup the merge replication, i just selected the Tables to be
published, i didnot select the user stored procedures and views that are used
by the appliaction database. i just wanted to publish as little as possible
since i was doing the no sync option and the subscriber was already having
the stored procedures and views associated with the tables. Now, I am
thinking if in anyway the merge process was not having any information about
the stored procedure execution, it might have made the performance slowdown.
Also, i noticed that the I/O reads and write have increased heavily and the
data drive has got highly fragmented. Please advise
Thanks
ST
"Paul Ibison" wrote:

> ST,
> I'm not sure which stored procedures and views you are referring to. In
> merge you can replicate them, but if you use transactional you can not only
> replicate the TSQL - entries in sysobjects, syscomments, syscolumns etc -
> you can also choose to replicate the execution of stored procedures. Maybe
> this is what you are looking for? I'm not too sure how this relates to your
> performance issues however. Please can you expand a little.
> TIA,
> Paul Ibison
>
>
|||ST,
merge replication doesn't replicate the execution of stored procedures -
just the TSQL on initialization, (and not when the definitions change
either).
If you have disk fragmentation, then (out of hours) I'd reindex all the
tables then defragment the disk. Backup everything before doing this to an
external device. It sounds like you have some benchmark comparisons so
running the same counters afterwards should show a noticeable difference.
HTH,
Paul Ibison

Merge Replication Issues

I was trying to test out how well my merge replication is working, so I
created a new table on my main database and on my remote computer I created
the same table but with data in it. I then tried to merge the two up and the
data from my remote computer did not appear on the table on the main
database. What am I doing wrong? Please let me know something...
Corey
Hey --
Well, without knowing all the details...if this was SQL 2K, then you need to
create the table and then add it as an article to the publication...then to
the subscription. So, if you did that already, and started the merge agents
and it STILL isn't working...slap an output file on the merge agent and lets
see what's going on.
Also, try looking in sp_helparticle and see if it even shows up as an
article (sysmergearticles).
Donna
"panacorey" wrote:

> I was trying to test out how well my merge replication is working, so I
> created a new table on my main database and on my remote computer I created
> the same table but with data in it. I then tried to merge the two up and the
> data from my remote computer did not appear on the table on the main
> database. What am I doing wrong? Please let me know something...
> Corey
|||Yes I am using SQL 2k, how would I add the output file to the merge agent?
Sorry, but I am learning as I go. I tried to run the sp_helparticle and i
got the error "Invalid object name 'syspublications'
"Donna W" wrote:
[vbcol=seagreen]
> Hey --
> Well, without knowing all the details...if this was SQL 2K, then you need to
> create the table and then add it as an article to the publication...then to
> the subscription. So, if you did that already, and started the merge agents
> and it STILL isn't working...slap an output file on the merge agent and lets
> see what's going on.
> Also, try looking in sp_helparticle and see if it even shows up as an
> article (sysmergearticles).
> Donna
> "panacorey" wrote:
|||Hmmm...were you in the db that is published in Query Analyzer when you ran
sp_helparticle?
Cause if there's no syspublications, then replication isn't set up properly.
My guess is that you were in master when you ran it...
to put an output file on the agent, follow this article:
http://support.microsoft.com/kb/312292/en-us
For OutputVerboselevel put 3 (undocumented).
Remember to take this off later, as it will very seriously slow down
replicaton.
Look in Books Online for information on sp_addmergearticle, as well as
DEFINITELY read this in Books Online:
Schema Changes on Publication Databases
It's important that you are FULLY aware of what you can and can't do to a
replicated db.
Let us know what you find!
Donna
"panacorey" wrote:
[vbcol=seagreen]
> Yes I am using SQL 2k, how would I add the output file to the merge agent?
> Sorry, but I am learning as I go. I tried to run the sp_helparticle and i
> got the error "Invalid object name 'syspublications'
> "Donna W" wrote:
|||Oh hey, that might be my fault...
sp_helpMERGEarticle...
I always want to forget that...
:D
Donna
"panacorey" wrote:
[vbcol=seagreen]
> Yes I am using SQL 2k, how would I add the output file to the merge agent?
> Sorry, but I am learning as I go. I tried to run the sp_helparticle and i
> got the error "Invalid object name 'syspublications'
> "Donna W" wrote:
|||By default merge replication will whack the table and its data on the
subscriber. While it is possible to put the table in place on the
subscriber, add the rowguid column and the required metadata, in general
this is not recommended.
To make life easier for your self, have merge replication deploy the
subscription itself.
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
"panacorey" <panacorey@.discussions.microsoft.com> wrote in message
news:1C31DDA1-2696-4470-A929-705C26127870@.microsoft.com...
>I was trying to test out how well my merge replication is working, so I
> created a new table on my main database and on my remote computer I
> created
> the same table but with data in it. I then tried to merge the two up and
> the
> data from my remote computer did not appear on the table on the main
> database. What am I doing wrong? Please let me know something...
> Corey

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

Merge replication and spids left behind

We are using Merge replication with clients from remote offices (SQL
2000, sp3). Recently, I have had a problem with users who are
replicating, and they shut down their laptops. The connection never
dies, and I end up with major blocking issues related to the
"orphaned" spid. The tables that are blocked are used to filter data
on each client. Since the orphaned spid is blocking, backups will run
forever, and have to be killed, and a SQL management job that
inserts/updates data in these tables has to be killed.

If I kill the spid, it shows a rollback at 0% and the status never
changes. The user has disconnected, and there is really nothing to
roll back. How can I get rid of this spid with out restarting SQL
server, or rebooting my server?

Any help would be greatly appreciated.

Thanks,

Amy Mamarshall@.rhtc.net (Amy M) wrote in message news:<119d3885.0408101552.7fe7cd72@.posting.google.com>...
> We are using Merge replication with clients from remote offices (SQL
> 2000, sp3). Recently, I have had a problem with users who are
> replicating, and they shut down their laptops. The connection never
> dies, and I end up with major blocking issues related to the
> "orphaned" spid. The tables that are blocked are used to filter data
> on each client. Since the orphaned spid is blocking, backups will run
> forever, and have to be killed, and a SQL management job that
> inserts/updates data in these tables has to be killed.
> If I kill the spid, it shows a rollback at 0% and the status never
> changes. The user has disconnected, and there is really nothing to
> roll back. How can I get rid of this spid with out restarting SQL
> server, or rebooting my server?
> Any help would be greatly appreciated.
> Thanks,
> Amy M

This KB article might be useful:

http://support.microsoft.com/defaul...kb;en-us;818552

If this doesn't help, you may want to post in
microsoft.public.sqlserver.replication, as your problem seems to be
quite specific.

Simon