|
|
You're in the
sql server replication
group:Identity Range Management
sql server replication:
-- 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... [quoted text, click to view] > 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... > > > 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... > > > > > 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 > > > > > > > > > > > > > > > > > >
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... [quoted text, click to view] > 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... > > > 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... > > > > > 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 > > > > > > > > > > > > > > > > > >
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
What do you think if i go for ROWGUID instead?
[quoted text, click to view] "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... > > 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 > >
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 [quoted text, click to view] "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... > > 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... > > > > 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... [quoted text, click to view] > 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
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... [quoted text, click to view] > 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... > > > 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 > > > > > >
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |