I am having problems when attempting to publish my database. When I try to
set the automatic identity range handler, the checkbox is dimmed out.
I am using SQL Server 2000 and my subscribers are using Windows CE. I have
set all the indentity fields to INT (2). Everything seems to be in order, but
the checkbox is still dimmed out.
All help will be greatly appreciated
Dale
Dale,
do you already have a subscription to this publication? If it is dimmed out
when looking at the publisher properties, you'll have to drop the
subscriptions, drop the article (apply) then readd the article and it will
then be possible to have automatic range management.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the reply.
I am very new to this, so could you please explain how I "drop" the
subscriptions, and "drop" the article.
thanks
Dale
"Paul Ibison" wrote:
> Dale,
> do you already have a subscription to this publication? If it is dimmed out
> when looking at the publisher properties, you'll have to drop the
> subscriptions, drop the article (apply) then readd the article and it will
> then be possible to have automatic range management.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Dale,
you can right-click on the publication and select the publisher properties.
On the subscriptions tab, delete each one. Apply. On hte articles tab,
uncheck the table in question. Apply. Next, check this article (table) and
on the article properties you'll be able to enable automatic range
management. After that, readd the subscriptions and run the snapshot agent
and merge agents.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for your help Paul!
I found the problem, one of the identity fields was accidently set to
cluster. Once fixed the publication went smoothly.
BTW your website is great!
Dale
"Paul Ibison" wrote:
> Dale,
> you can right-click on the publication and select the publisher properties.
> On the subscriptions tab, delete each one. Apply. On hte articles tab,
> uncheck the table in question. Apply. Next, check this article (table) and
> on the article properties you'll be able to enable automatic range
> management. After that, readd the subscriptions and run the snapshot agent
> and merge agents.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
Showing posts with label publish. Show all posts
Showing posts with label publish. Show all posts
Monday, March 19, 2012
Friday, February 10, 2012
Auto identity in republisher scenario
Hi to everyone,
Is auto identity range supported in publisher-subscriber scenario?
In example:
Server1 publish database1 with article Table1 with auto identity range.
Server1 reserve itself the range 1-100.
Server2 subscribe to Server1 and get and identity range 101-200.
Server2 is also a republisher for database1 so it allows Client1 to
subscribe and replicate with it.
Which identity range does Client1 get?
I suppose Client1 will get a 201-300 range from Server2, which will
eventually conflict if the same range is assigned to other clients directly
from Server1.
Am I right or am I wrong?
Kind regards,
Corrado
You are correct that it will eventually conflict. Auto-identity ranges is
something that I've played with and then never used. There are just too
many potential problems with them at this point. You can wind up with range
skipping and run out of values long before you would have. I've also had
cases where you generated duplicate values.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||are you using merge replication? it is unclear from your post.
Server 1 will have a check constraint on the identity column allowing a
range of identity values from 101-200. Server 2 from 201-300. Client 1 will
have a range from 320-330.
The ranges will be maintained between server 2 and client 1 with a different
set of ranges and thresholds than between Server 1 and Server 2.
There are some caveats with automatic identity range management. First you
must use a representative data type that will be good for your range of
values - ie smallint will probably be too small for most applications.
Secondly pick a range which is larger than your largest batch insert. The
automatic range adjustment is run after a transaction is distruted for
transactional publications or covergence is achieved if you are using merge.
Third most dba's will set the maxiumn ranges they expect their replication
solution to use in the life time of thier probject - sort of like set it and
forget it.
"Corrado Labinaz" <corradolab@.virgilio.it> wrote in message
news:uT2xsySMEHA.3668@.TK2MSFTNGP11.phx.gbl...
> Hi to everyone,
> Is auto identity range supported in publisher-subscriber scenario?
> In example:
> Server1 publish database1 with article Table1 with auto identity range.
> Server1 reserve itself the range 1-100.
> Server2 subscribe to Server1 and get and identity range 101-200.
> Server2 is also a republisher for database1 so it allows Client1 to
> subscribe and replicate with it.
> Which identity range does Client1 get?
> I suppose Client1 will get a 201-300 range from Server2, which will
> eventually conflict if the same range is assigned to other clients
directly
> from Server1.
> Am I right or am I wrong?
> Kind regards,
> Corrado
>
Is auto identity range supported in publisher-subscriber scenario?
In example:
Server1 publish database1 with article Table1 with auto identity range.
Server1 reserve itself the range 1-100.
Server2 subscribe to Server1 and get and identity range 101-200.
Server2 is also a republisher for database1 so it allows Client1 to
subscribe and replicate with it.
Which identity range does Client1 get?
I suppose Client1 will get a 201-300 range from Server2, which will
eventually conflict if the same range is assigned to other clients directly
from Server1.
Am I right or am I wrong?
Kind regards,
Corrado
You are correct that it will eventually conflict. Auto-identity ranges is
something that I've played with and then never used. There are just too
many potential problems with them at this point. You can wind up with range
skipping and run out of values long before you would have. I've also had
cases where you generated duplicate values.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||are you using merge replication? it is unclear from your post.
Server 1 will have a check constraint on the identity column allowing a
range of identity values from 101-200. Server 2 from 201-300. Client 1 will
have a range from 320-330.
The ranges will be maintained between server 2 and client 1 with a different
set of ranges and thresholds than between Server 1 and Server 2.
There are some caveats with automatic identity range management. First you
must use a representative data type that will be good for your range of
values - ie smallint will probably be too small for most applications.
Secondly pick a range which is larger than your largest batch insert. The
automatic range adjustment is run after a transaction is distruted for
transactional publications or covergence is achieved if you are using merge.
Third most dba's will set the maxiumn ranges they expect their replication
solution to use in the life time of thier probject - sort of like set it and
forget it.
"Corrado Labinaz" <corradolab@.virgilio.it> wrote in message
news:uT2xsySMEHA.3668@.TK2MSFTNGP11.phx.gbl...
> Hi to everyone,
> Is auto identity range supported in publisher-subscriber scenario?
> In example:
> Server1 publish database1 with article Table1 with auto identity range.
> Server1 reserve itself the range 1-100.
> Server2 subscribe to Server1 and get and identity range 101-200.
> Server2 is also a republisher for database1 so it allows Client1 to
> subscribe and replicate with it.
> Which identity range does Client1 get?
> I suppose Client1 will get a 201-300 range from Server2, which will
> eventually conflict if the same range is assigned to other clients
directly
> from Server1.
> Am I right or am I wrong?
> Kind regards,
> Corrado
>
Subscribe to:
Comments (Atom)