Identity Range Management

Good morning All,
In the context of a merge replica:
Does this range serve as surrogate key (Primary Key) but after the sync, the
server overlooks it when inserting new rows in the published table and
generate a sequential one?
Here's what I am trying to achieve:
My disconnected users, have to issue unique File# to their customers.
Once a file number is given to a customer, the customer will use this file
number to reference his/her case for ever.
I want this number to be the unique identifier of his/her record in my table.
If I use this range model, then that would solve the problem provided that
the server will use this number as the primary key for the table where
customer cases are stored.
I read a few articles about the range model but it never read that this
number is kept or used as a primary key in a table.
What am I missing here?
Also
How can we reduce the large skipped numbers not used between sync?
Thanks
YOW
Yes. So the identity column which may or may not be the primary key or may
be one of the columns involved in the primary key could appear on two nodes
simultaneously if Automatic Identity Range Management is not working
correctly. This will lead to a conflict when the merge agent runs and can
cause one of the inserts to be rolled back and replaced by the winning
insert.
If it is working correctly or if there are other columns in the primary key,
or if the identity value is the only key in a primary key or unique index
and you are perfectly partitioned it should work.
Automatic identity range management promotes efficient use of the identity
ranges. If you notice that the ranges are not being used efficiently on the
publisher lower the publisher range. If it is not being used efficiently on
all subscribers, lower that range. If it is not being used efficiently on
one or the subscribers, but is on the other you are out of luck.
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
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:C2F1728A-0241-412E-AF9D-CAB3BE2CB04C@.microsoft.com...
> Good morning All,
> In the context of a merge replica:
> Does this range serve as surrogate key (Primary Key) but after the sync,
the
> server overlooks it when inserting new rows in the published table and
> generate a sequential one?
> Here's what I am trying to achieve:
> My disconnected users, have to issue unique File# to their customers.
> Once a file number is given to a customer, the customer will use this file
> number to reference his/her case for ever.
> I want this number to be the unique identifier of his/her record in my
table.
> If I use this range model, then that would solve the problem provided that
> the server will use this number as the primary key for the table where
> customer cases are stored.
> I read a few articles about the range model but it never read that this
> number is kept or used as a primary key in a table.
> What am I missing here?
> Also
> How can we reduce the large skipped numbers not used between sync?
> Thanks
> YOW
|||What do you think if i go for ROWGUID instead?
"Hilary Cotter" wrote:

> Yes. So the identity column which may or may not be the primary key or may
> be one of the columns involved in the primary key could appear on two nodes
> simultaneously if Automatic Identity Range Management is not working
> correctly. This will lead to a conflict when the merge agent runs and can
> cause one of the inserts to be rolled back and replaced by the winning
> insert.
> If it is working correctly or if there are other columns in the primary key,
> or if the identity value is the only key in a primary key or unique index
> and you are perfectly partitioned it should work.
> Automatic identity range management promotes efficient use of the identity
> ranges. If you notice that the ranges are not being used efficiently on the
> publisher lower the publisher range. If it is not being used efficiently on
> all subscribers, lower that range. If it is not being used efficiently on
> one or the subscribers, but is on the other you are out of luck.
> --
> 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
> "Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
> message news:C2F1728A-0241-412E-AF9D-CAB3BE2CB04C@.microsoft.com...
> the
> table.
>
>
|||They tend not to make good PK's. Have a look at this for more info on why
not.
http://www.aspfaq.com/show.asp?id=2504
Its in the bottom section.
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
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:8587BCD9-84AB-4719-BFFA-8A7BD607A847@.microsoft.com...[vbcol=seagreen]
> What do you think if i go for ROWGUID instead?
> "Hilary Cotter" wrote:
may[vbcol=seagreen]
nodes[vbcol=seagreen]
can[vbcol=seagreen]
key,[vbcol=seagreen]
index[vbcol=seagreen]
identity[vbcol=seagreen]
the[vbcol=seagreen]
on[vbcol=seagreen]
on[vbcol=seagreen]
in[vbcol=seagreen]
sync,[vbcol=seagreen]
file[vbcol=seagreen]
that[vbcol=seagreen]
this[vbcol=seagreen]
|||From your first answer:
if Automatic Identity Range Management is not working correctly. This will
lead to a conflict when the merge agent runs and can cause one of the
inserts to be rolled back and replaced by the winning insert"
What could make Automated IRM not work properly?
Thanks
"Hilary Cotter" wrote:

> They tend not to make good PK's. Have a look at this for more info on why
> not.
> http://www.aspfaq.com/show.asp?id=2504
> Its in the bottom section.
> --
> 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
> "Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
> message news:8587BCD9-84AB-4719-BFFA-8A7BD607A847@.microsoft.com...
> may
> nodes
> can
> key,
> index
> identity
> the
> on
> on
> in
> sync,
> file
> that
> this
>
>
|||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
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:37219273-4565-47B0-AB13-1D01132A0658@.microsoft.com...
> From your first answer:
> if Automatic Identity Range Management is not working correctly. This
will[vbcol=seagreen]
> lead to a conflict when the merge agent runs and can cause one of the
> inserts to be rolled back and replaced by the winning insert"
> What could make Automated IRM not work properly?
>
> Thanks
> "Hilary Cotter" wrote:
why[vbcol=seagreen]
in[vbcol=seagreen]
or[vbcol=seagreen]
two[vbcol=seagreen]
and[vbcol=seagreen]
winning[vbcol=seagreen]
primary[vbcol=seagreen]
on[vbcol=seagreen]
efficiently[vbcol=seagreen]
efficiently[vbcol=seagreen]
wrote[vbcol=seagreen]
and[vbcol=seagreen]
customers.[vbcol=seagreen]
this[vbcol=seagreen]
in my[vbcol=seagreen]
provided[vbcol=seagreen]
where[vbcol=seagreen]
|||I'd like to say bugs, but I am not really convinced that there are bugs with
this. Properly sized it does seem to work well - or at least it has worked
well for us on several installations.
If you don't size your data type or your ranges correctly or run the agent
in continuous mode, it might not update the ranges in time. This seems to be
the biggest problem with it.
It also seems that if you are monkeying around with the metadata that the
merge agent will not detect that an article is under automatic range
management when it runs and then check the local and remote ranges. It
normally does this check when it first runs. I have seen cases where the
detection proc never runs, but when I try to repro it on a clean database I
am unable to do so. So I think its something i have messed up along the way.
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
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:37219273-4565-47B0-AB13-1D01132A0658@.microsoft.com...
> From your first answer:
> if Automatic Identity Range Management is not working correctly. This
will[vbcol=seagreen]
> lead to a conflict when the merge agent runs and can cause one of the
> inserts to be rolled back and replaced by the winning insert"
> What could make Automated IRM not work properly?
>
> Thanks
> "Hilary Cotter" wrote:
why[vbcol=seagreen]
in[vbcol=seagreen]
or[vbcol=seagreen]
two[vbcol=seagreen]
and[vbcol=seagreen]
winning[vbcol=seagreen]
primary[vbcol=seagreen]
on[vbcol=seagreen]
efficiently[vbcol=seagreen]
efficiently[vbcol=seagreen]
wrote[vbcol=seagreen]
and[vbcol=seagreen]
customers.[vbcol=seagreen]
this[vbcol=seagreen]
in my[vbcol=seagreen]
provided[vbcol=seagreen]
where[vbcol=seagreen]