Default conflict resolver in Sql 2000

Hi, I'm replicating a database between two instances of Sql 2000 using Merge Replication. I have no custom resolvers at present but I'm seeing something unexpected.

If I change the same record in both databases at the same time, but the changes affect different columns then I thought the changes would be merged and there would be no conflict because the changes were in different columns. What I actually see is a conflict and hence the publisher is winning and pushing it's changed row to the subscriber.

Have I got this wrong, will a conflict occur if the same row changes regardless of the columns updated?

Regards

GrahamCould it be the timestamp column that is causing the conflict? When I set up the publisher I simple published all tables and sp.

Is there any way in enterprise manager establishing what columns conflicted?

Thanks

Graham|||Merge replication can track conflict at row level or column level. It looks that you wanted to track conflicts at column level but set it up at row level.

You can launch conflict viewer to see the conflicts. From Enterprise Manager, if you right click the publication, there should be a menu called View Conflicts.|||Thanks for the reply.

I do have it tracking conflicts at column level. However, I did a little more digging, this is what I found...

I have a published table with two int columns, A and B. The initial data looks like:

A B
--
1 2

If on the publisher I update column A to a value 2 and on the Subscriber I update the same row but Column B to a value 3 the a merge happens and the result is that the pub and the subscriber looks like:

A B
--
2 3

If however, on the publisher I update both columns to A=3 and B=3 (net result only column A has changed) and on the subscriber I again update both columns to A=2 and B=4 (net result only column B changes), I then get a conflict as the agent thinks both A and B have been changed on the publisher and the subscriber.

I was expecting the net results to be sent when the merge agent runs, but it seems to send all the data regardless of whether any column data was changed or not.

Is this behaviour normal?

Regards

Graham
|||Hi Graham,
Yes this is the expected behavior.
Once a column is touched (to the same value or the new value) it will be sent to the other side.
However if you touch the same column multiple times, only the net effect is sent.

default clustered index when selecting primary key

Hi,
I have a list of table which I need to replicate to another database. First
of all, why do I need a primary key to achieve replication (sorry I am new
with replication)
Then I've noticed that if I just click the primary key button from EM on
Design table, then it defaults to a clustered index on the column I've
selected. Does anybody knwow why it defaults to clustered? I am not too kee
n
on clustered indexes for my database.
Thanks,
Panos.Panos Stavroulis. wrote:

> Hi,
> I have a list of table which I need to replicate to another database. Firs
t
> of all, why do I need a primary key to achieve replication (sorry I am new
> with replication)
Without a key the database can't uniquely identify a row in a table.
Every table should have a key. Why would you want a table without one?

> Then I've noticed that if I just click the primary key button from EM on
> Design table, then it defaults to a clustered index on the column I've
> selected. Does anybody knwow why it defaults to clustered? I am not too k
een
> on clustered indexes for my database.
I don't think there's a very good reason why it defaults to clustered.
You can easily change the setting so the default doesn't matter very
much. Also, I would not usually create indexes or keys in Enterprise
Manager. EM is very inefficient in the way it implements schema
changes. Usually it's better to write TSQL for your schema mods. EM
will even script the change for you to review if that helps you get
started.
Most of the time it does pay to have a clustered index on every table.
Tables without clustered indexes should be the exception rather than
the rule.
David Portas
SQL Server MVP
--|||> Does anybody knwow why it defaults to clustered?
A design decision MS made some 12 years ago. Perhaps because you can only ha
ve one CL IX on a table
and you can only have one PK for a table? Important to notice is that you ar
e free to override that
default.

> I am not too keen
> on clustered indexes for my database.
Why not? Clustered indexes are a very important performance tools, and most
DBAs avoid heap tables
and try to decide carefully which index to be the clustered index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in mes
sage
news:E07ADE7B-5424-43E1-AE34-936AF21D27AC@.microsoft.com...
> Hi,
> I have a list of table which I need to replicate to another database. Firs
t
> of all, why do I need a primary key to achieve replication (sorry I am new
> with replication)
> Then I've noticed that if I just click the primary key button from EM on
> Design table, then it defaults to a clustered index on the column I've
> selected. Does anybody knwow why it defaults to clustered? I am not too k
een
> on clustered indexes for my database.
> Thanks,
> Panos.|||You are opening a good subject here. Well the reason I don't use clustered
indexes for many tables is because I don't need to do a select statements
where col_id between 10 and 200 etc if column col_id was my primary key.
I normally select individual records. In fact which way do you think it's
faster? if I have a non clustered index and select where col_id = 100 or a
clustered index?
Thanks.
"Tibor Karaszi" wrote:

> A design decision MS made some 12 years ago. Perhaps because you can only
have one CL IX on a table
> and you can only have one PK for a table? Important to notice is that you
are free to override that
> default.
>
> Why not? Clustered indexes are a very important performance tools, and mos
t DBAs avoid heap tables
> and try to decide carefully which index to be the clustered index.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in m
essage
> news:E07ADE7B-5424-43E1-AE34-936AF21D27AC@.microsoft.com...
>|||> I normally select individual records. In fact which way do you think it's
> faster? if I have a non clustered index and select where col_id = 100 or a
> clustered index?
A clustered index will be marginally faster.
But are you really saying that you have no range queries (lower selectivity
search arguments) at all
in the database? What about joins? Grouping? Sorting? Clustered indexes can
beneficial for a wide
type of operations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in mes
sage
news:4AFF7C5A-C43F-4C27-AA5B-9A6F65DAAE6E@.microsoft.com...
> You are opening a good subject here. Well the reason I don't use clustered
> indexes for many tables is because I don't need to do a select statements
> where col_id between 10 and 200 etc if column col_id was my primary key.
> I normally select individual records. In fact which way do you think it's
> faster? if I have a non clustered index and select where col_id = 100 or a
> clustered index?
> Thanks.
> "Tibor Karaszi" wrote:
>|||I don't think you quite grasp the point of a clustered index.
I really don't feel like going into it right now, but let's look at your
example:
With a nonclustered index several index pages must be searched before
reaching the 100 value, while with a clustered index this is done in two
quick steps:
1) find the page where the 100 value resides; and
2) only go to that page.
ML
http://milambda.blogspot.com/|||The explanation that I remember best from an old DBA friend of mine is
that a clustered index is like page numbers in a book, whereas
non-clustered indexes are like the index in the back; although the
analogy is not a perfect fit, it does explain the relationship between
clustered and nonclustered indexes. Without page numbers in a
sequential order (clustering), it's tough to find the topic you're
looking for.
Stu|||Thanks for the answers. OK how about this.
We have a query that joins 2 tables, file & file_detail linked by column
col_id which is integer. If I was making a query to select all files and
their detail which are between 100 and 1000, then I would expect that this
query will be faster if I had a clustered index on the table.
However, I personally would expect if the query was "give me file 250" then
the non-clustered solution will be faster? Do you agree with this. How about
covering on non-clustered indexes, I would expect the non clustered option t
o
be faster. Thank you.
"Tibor Karaszi" wrote:

> A clustered index will be marginally faster.
> But are you really saying that you have no range queries (lower selectivit
y search arguments) at all
> in the database? What about joins? Grouping? Sorting? Clustered indexes ca
n beneficial for a wide
> type of operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in m
essage
> news:4AFF7C5A-C43F-4C27-AA5B-9A6F65DAAE6E@.microsoft.com...
>|||> We have a query that joins 2 tables, file & file_detail linked by column
> col_id which is integer. If I was making a query to select all files and
> their detail which are between 100 and 1000, then I would expect that this
> query will be faster if I had a clustered index on the table.
Cluster on which table and which column(s)? It is possible that a clustered
index o the fireign key
column in the file_detail table will improve that join, but that depends on
a lot of other factors.

> However, I personally would expect if the query was "give me file 250" the
n
> the non-clustered solution will be faster?
Non-clustered index on what? For a query with high-selectivity, a clustered
index on the search
column will still outperform a nonc-lustered (but the ihger selectivity, the
more marginally) as SQL
Server doesn't have to fetch each row in the datapage. Unless the non-cluste
red index covers the
query, of course.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in mes
sage
news:CE190BBB-A52C-4D06-9AA8-8E33D983B643@.microsoft.com...
> Thanks for the answers. OK how about this.
> We have a query that joins 2 tables, file & file_detail linked by column
> col_id which is integer. If I was making a query to select all files and
> their detail which are between 100 and 1000, then I would expect that this
> query will be faster if I had a clustered index on the table.
> However, I personally would expect if the query was "give me file 250" the
n
> the non-clustered solution will be faster? Do you agree with this. How abo
ut
> covering on non-clustered indexes, I would expect the non clustered option
to
> be faster. Thank you.
>
> "Tibor Karaszi" wrote:
>|||Sorry meant to say clustered index on col_id on both tables in one case and
nonclustered index on both tables in the second.
So basically basically since for most of the queries you need more
information (columns) than the columns contained in the index (covering
situation) then it's better to create a clustered index on the table as long
as the data arrive in the database in a sequential order and you don't get
page breaks.
Also do you have a view on clustered indexes on tables which are over 8K
long, ie maximum size of a page? Thanks.
"Tibor Karaszi" wrote:

> Cluster on which table and which column(s)? It is possible that a clustere
d index o the fireign key
> column in the file_detail table will improve that join, but that depends o
n a lot of other factors.
>
> Non-clustered index on what? For a query with high-selectivity, a clustere
d index on the search
> column will still outperform a nonc-lustered (but the ihger selectivity, t
he more marginally) as SQL
> Server doesn't have to fetch each row in the datapage. Unless the non-clus
tered index covers the
> query, of course.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in m
essage
> news:CE190BBB-A52C-4D06-9AA8-8E33D983B643@.microsoft.com...
>

Decrising Subscription Expiry Date

Hi to all
I have a merge replication in SQL 2005 spk1 with 54 Subscribers that
are actually Mobile devices.
I set the Expiration date to 30 days and now I want to Reduce it lets
say to 10 or to 8 days.
My Question is this.
Will I get faster Synchs if I do that?
And
By just changing the number of days from 30 to 10 will the subscribers
need to Reinitialize in order to
get this change?
Thanks a lot
Savvas Christodoulou
IIRC for handhelds you will need to reinitialize. Your syncs should be
faster.
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
<savvaschr@.nodalsoft.com.cy> wrote in message
news:1173863832.116924.228550@.n76g2000hsh.googlegr oups.com...
> Hi to all
> I have a merge replication in SQL 2005 spk1 with 54 Subscribers that
> are actually Mobile devices.
> I set the Expiration date to 30 days and now I want to Reduce it lets
> say to 10 or to 8 days.
> My Question is this.
> Will I get faster Synchs if I do that?
> And
> By just changing the number of days from 30 to 10 will the subscribers
> need to Reinitialize in order to
> get this change?
>
> Thanks a lot
> Savvas Christodoulou
>
|||Thank you very mutch for your Overall help in Megre Replication

Debug DB Merge Replication

I setted a configuration with 2 SQL 2000 servers, 1 publisher and 1
subscriber, to manage a merge replication of a DB, with the wizard of
enterprise manager. It seems that some tables published as arcticles
are not synchronized correctly. I'm a newbie using SQL Server 2000 and
I don't know how to proceed for finding errors and resolve problems;
someone can give me any indication?
Thanks.
Marco - when you say they are not synchronized properly can you please give
more details. Do you have rown existing on the publisher but not subscriber
or vice versa or something different entirely. There are several
posibilities involving bulk loads or compensating changes or the merge agent
failing somehow or conflict resolution. If you provide some more details we
can help narrow down the cause.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul Ibison ha scritto:

> Marco - when you say they are not synchronized properly can you please give
> more details. Do you have rown existing on the publisher but not subscriber
> or vice versa or something different entirely. There are several
> posibilities involving bulk loads or compensating changes or the merge agent
> failing somehow or conflict resolution. If you provide some more details we
> can help narrow down the cause.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Hi Paul,
the situation is this: I have in a table 1 row which is different in
Subscriber from Publisher.
|||Assuming you have synchronized recently, there must have been a change
implemented without firing the merge triggers. Usuallt this is as a result
of a bulk insert, but in your case there seems to have been an update which
is odd, unless each record was added separately to the publisher and
subscriber as a part of a bulk process? Anyway, you could resort to
something like Lumigent's Log Explorer to see what is the root cause, and
use sp_mergedummyupdate to resync the row.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul Ibison ha scritto:

> Assuming you have synchronized recently, there must have been a change
> implemented without firing the merge triggers. Usuallt this is as a result
> of a bulk insert, but in your case there seems to have been an update which
> is odd, unless each record was added separately to the publisher and
> subscriber as a part of a bulk process? Anyway, you could resort to
> something like Lumigent's Log Explorer to see what is the root cause, and
> use sp_mergedummyupdate to resync the row.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Paul,
thanks for your suggestions.
Can you write the syntax to use the "sp_mergedummyupdate" ?
|||Hi Paul,
I'm not sure that there is only 1 row not updated. To be sure can I use
the sp_addtablecontents to force the update of all rows in my table?
Can you write an example of sintax for using this sp?
Thanks.
|||Marco,
the syntax is pretty straightforward:
exec sp_addtabletocontents
@.table_name ='yourtable',
@.owner_name = 'your owner eg dbo'
The @.owner_name is optional.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
last question.
Is the same thing to execute the sp in the Publisher or in the
Subscriber DB ?
|||Hi Marco,
I suppose it depends on what the required outcome is. If you have a row that
is different on the publisher and subscriber and which hasn't been logged as
a merge change, then there will be different results depending on where you
run the proc. By default, if you run it on the subscriber and publisher,
there will be a conflict and the publisher's change will win, otherwise
running it just on the pub will mean that the publisher's change gets
propagated and likewise for the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .