Showing posts with label publisher. Show all posts
Showing posts with label publisher. Show all posts

Monday, March 19, 2012

Identity Ranges

I'm using Merge replication on a database that was designed using integer identity columns for primary keys. When I create a publisher it's great because Sql Server will create rowguid columns for me on most of the tables; actually all but one table.

The problem comes when I try and use identity ranges for the subscribers. As a test I set up a range on one table, it only allowed for 10 in the range with an 80% threshold. I wanted to see what would happen when say my publisher db inserts 11 new rows. Well, I found out that after the 8th new row it wouldn't insert anymore as the range was exceeded and it gave me an error message saying
The identity range managed by replication is full and must be updated by a replication agent.

The question is; which agent needs to run in order for the new range to be assigned to the publisher? I have seen some people talk about an sp_ that can be run, but in a production environment I wont this to be automatic.

My altenative to ranged identies is using guid uniqueidentifiers. See my other post on this!!

regards

GrahamI assume this is SQL Server 2000.

To answer your questions:
Maybe the table on which merge is not adding the rowguid col is because the table already has a rowguid column?

And since you have 80% threashold, it is failing after the 8th row and I assume you have pub_idrange and range values each set to 10.
You can get by this situation by increasing the numbers, so that the probablity of them running out of numbers is less. Say like 10,000 or 100,000.

And the agent to be run when the id range is full is the merge agent. Running the merge agent will refresh the ranges on publisher and subscriber.
On the publisher, you can also run the sp_adjustpublisheridentityrange to refresh the range and that way you dont need to run the merge agent.

But typically in a production scenario, it is recommended that:
1. You have a decent sized value for the publisher and subscriber id range values
2. Merge agent to run frequently. That way the ranges will be refreshed (if needed) when merge agent runs.

Identity ranges

Using merge replication, I've decided to change over to identity ranges
(versus uniqueidentifiers). At the publisher, I'm setting a range on the
tables that would be consistent with the records expected not to be exceeded
in the database. Since these values are for an entire state of consumers, I
set the publisher range to 500,000 for that consumer table.
Three questions:
1. Does the publisher range sound excessive and would I be better off using
the auto-range to up that value or should I err on the high side initially?
2. On the subscriber side (all PPC), the normal range of records that would
be pushed would probably be 3000-5000 and the actual changes made would
probably only amount to about 500 per day per PPC. What would be a safe
range on the subscriber side?
3. Does it really matter how WIDE the range is set?
You really need to consider how long the PPC can go without a sync. Can
they stay disconnected for 5 days, 10, longer? Do some calculating and make
sure that not only the range is wide enough, but that the threshold is low
enough so they receive a new range to cover the next worst case senario.
One thing you can guarantee is that the user are not forced to sync
everyday...they probably will not.
"Earl" wrote:

> Using merge replication, I've decided to change over to identity ranges
> (versus uniqueidentifiers). At the publisher, I'm setting a range on the
> tables that would be consistent with the records expected not to be exceeded
> in the database. Since these values are for an entire state of consumers, I
> set the publisher range to 500,000 for that consumer table.
> Three questions:
> 1. Does the publisher range sound excessive and would I be better off using
> the auto-range to up that value or should I err on the high side initially?
> 2. On the subscriber side (all PPC), the normal range of records that would
> be pushed would probably be 3000-5000 and the actual changes made would
> probably only amount to about 500 per day per PPC. What would be a safe
> range on the subscriber side?
> 3. Does it really matter how WIDE the range is set?
>
>
|||Thanks Brian, those are good thoughts. A couple of followup questions: What
would the criteria be to ensure that the "range is wide enough"? I didn't
really understand about the threshold being "low enough".
"Brian Reuter" <BrianReuter@.discussions.microsoft.com> wrote in message
news:885F4F00-0793-478F-B363-7FCCF97C97C9@.microsoft.com...[vbcol=seagreen]
> You really need to consider how long the PPC can go without a sync. Can
> they stay disconnected for 5 days, 10, longer? Do some calculating and
> make
> sure that not only the range is wide enough, but that the threshold is low
> enough so they receive a new range to cover the next worst case senario.
> One thing you can guarantee is that the user are not forced to sync
> everyday...they probably will not.
>
>
> "Earl" wrote:
|||IMHO best way is to give range of 10.000.000 or even hundred million
and forget about ranges, tresholds etc. forever.
Max identity is something like 9223372036854775807, so why to be
worried about ranges.
Is there any drawback with this approach?
Pagus
On Fri, 12 Nov 2004 10:13:23 -0500, "Earl"
<brikshoe@.newsgroups.nospam> wrote:
[vbcol=seagreen]
>Thanks Brian, those are good thoughts. A couple of followup questions: What
>would the criteria be to ensure that the "range is wide enough"? I didn't
>really understand about the threshold being "low enough".
>"Brian Reuter" <BrianReuter@.discussions.microsoft.com> wrote in message
>news:885F4F00-0793-478F-B363-7FCCF97C97C9@.microsoft.com...
|||FAO Earl - Pagus is talking about BigInts (8 bytes) and
not Ints (4 bytes), where the range is +/-2billion or so.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I was thinking that range wouldn't work :=)
I want that replication book, can I get that as an ebook too (I didnt' see
anything on the site)?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:5e3f01c4c999$7e3be410$a601280a@.phx.gbl...
> FAO Earl - Pagus is talking about BigInts (8 bytes) and
> not Ints (4 bytes), where the range is +/-2billion or so.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Earl,
the book is now available - I've read a draft and it looks v.good.
Hilary mentioned something about internet updates, but I don't recall
hearing about it as an ebook. Hopefully he'll see this thread and reply to
you.
Rgds,
Paul Ibison, SQL Server MVP
|||No, ebook. The merge volume may be released as an ebook, but its future is
yet undecided.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Ot8sppbyEHA.2568@.TK2MSFTNGP11.phx.gbl...
> Earl,
> the book is now available - I've read a draft and it looks v.good.
> Hilary mentioned something about internet updates, but I don't recall
> hearing about it as an ebook. Hopefully he'll see this thread and reply to
> you.
> Rgds,
> Paul Ibison, SQL Server MVP
>

Identity Range reused by subscriber

We are running Windows 2000 server SP3a for the publisher/distributor and
MSDE with SP3a for the subscriber.
We have several tables that have identities for primary keys, and our
identity range handling has been working great, until now.
One of the tables decided to REUSE its existing range of 1000 (threshold is
set to 80%), so all the transactions are backing up at the subscriber since
the range
has already been used once before.
I tried to use the sp_adjustpublisheridentityrange, and it says it ran
successfully, but it is still doing the same thing.
Looking at the next available identity range on the table, it is showing
correctly for the next available block of 1000.
What could have happened, and how do I recover?
Thanks,
RS
It is hard to say what has caused this to happen. Obviously automatic identity range management has failed you for some reason.
I'd call up PSS on this problem.
Some people have had success my manually adjusting the constraint on the subscriber/publisher tables, and manually adjusting the values of MSrepl_identity_range in the distribution database.
And Mspub_identity_range.
Most DBA's will use the set it and forget approach to automatic identity range management. They will set their ranges once with what they feel are representative ranges for the lifecycle of the project. They don't have to worry about batch updates blowing
the range, or problems with automatic identity range management this way.
Of course if you are continually adding new susbcribers this might now work for you.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Identity Range not working for master

I have set up a publisher database and a subscriber database which is a replica of the publisher. For the identity fields I set it up so that they would have a range of 10 numbers with an 80% margin. I was testing this on the subscriber and replica at t
he same time. I disconnected the subscriber, used up 8 id's, then connected and sure enough it gave me a new range. However whilst using the publisher it used up all 10 in the range and then gave me an error message! Surely it should have automatically
given me a new range once I'd hit 80% of the previous range.
Any help?
Thanks
Adrian
that depends on how large the batch is. So if you update 20 records in a
batch, it won't get updated and you blow the range.
The idea is to pick ranges that are much larger than representative batches.
So if I were you, I'd try ranges in the 1000's or set ranges that will not
be exceeded in the life time of your replication solution.
Hilary
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:855D2D7D-63EF-40D2-9457-65C97E67A33F@.microsoft.com...
> I have set up a publisher database and a subscriber database which is a
replica of the publisher. For the identity fields I set it up so that they
would have a range of 10 numbers with an 80% margin. I was testing this on
the subscriber and replica at the same time. I disconnected the subscriber,
used up 8 id's, then connected and sure enough it gave me a new range.
However whilst using the publisher it used up all 10 in the range and then
gave me an error message! Surely it should have automatically given me a
new range once I'd hit 80% of the previous range.
> Any help?
> Thanks
> Adrian
|||Adrian,
as well as Hilary's reply, you could also consider manual range management
and use an algorithm that ensures no overlap in the ranges:
http://www.mssqlserver.com/replicati...h_identity.asp
HTH,
Paul Ibison
|||Hilary
I do intend to use much larger ranges, however I was just testing out the process on a smaller range to see it in action. It worked for the subscriber but not the publisher?
Any thoughts?
Regards
Adrian
"Hilary Cotter" wrote:

> that depends on how large the batch is. So if you update 20 records in a
> batch, it won't get updated and you blow the range.
> The idea is to pick ranges that are much larger than representative batches.
> So if I were you, I'd try ranges in the 1000's or set ranges that will not
> be exceeded in the life time of your replication solution.
> Hilary
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Adrian" <Adrian@.discussions.microsoft.com> wrote in message
> news:855D2D7D-63EF-40D2-9457-65C97E67A33F@.microsoft.com...
> replica of the publisher. For the identity fields I set it up so that they
> would have a range of 10 numbers with an 80% margin. I was testing this on
> the subscriber and replica at the same time. I disconnected the subscriber,
> used up 8 id's, then connected and sure enough it gave me a new range.
> However whilst using the publisher it used up all 10 in the range and then
> gave me an error message! Surely it should have automatically given me a
> new range once I'd hit 80% of the previous range.
>
>
|||There are a couple of issues here
1) are you running your agent continuously? Running is on a schedule,
every 5-10 minutes, or even less can help the adjustment, otherwise you
might want to manually execute the increment procedure
(sp_adjustpublisheridentityrange) to adjust everything
2) its not clear to me that the number of rows in the batch was close
enough to the threshold to kick off the indentity range adjustment. Was it?
3) The allottment of ranges is not always intuitive. For instance if I
set a range on the Publisher of 100, the Publisher may "own" 0-200, where
the Subscriber "owns" 200-300. You have to look at the check constaints on
the indentity range tables to figure this out.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:308EF6B0-3714-4D95-8BE6-4EBD352B1929@.microsoft.com...
> Hilary
> I do intend to use much larger ranges, however I was just testing out the
process on a smaller range to see it in action. It worked for the
subscriber but not the publisher?[vbcol=seagreen]
> Any thoughts?
> Regards
> Adrian
> "Hilary Cotter" wrote:
batches.[vbcol=seagreen]
not[vbcol=seagreen]
a[vbcol=seagreen]
they[vbcol=seagreen]
on[vbcol=seagreen]
subscriber,[vbcol=seagreen]
then[vbcol=seagreen]
a[vbcol=seagreen]

Identity range management

I apologize for the repeated post, but my previous posts were not answered
and I'm getting desperate.
I have a SQL2005 Standard publisher with 10 SQL2005 Express subscribers
using web synchronization (anonymous subscriptions). The identity ranges on
the subscribers are not properly updating in at least some cases. The
identity range is 10000 with an 80% threshold but the subscribers run out of
identity values even though they synchronize every hour and never have more
than 150 inserts per sync. Re-syncing after the identity range is full (and
the client sees error messages) fixes the problem.
Is there any reason why an anonymous subscription on SQL2005 Express would
not get a new identity range before filling the old range? Thanks.
No, there is no reason. I think you should open a support incident with PSS
on this one.
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
"John Van Vliet" <JohnVanVliet@.discussions.microsoft.com> wrote in message
news:815687F4-989A-4CAD-A43C-45A942B55037@.microsoft.com...
>I apologize for the repeated post, but my previous posts were not answered
> and I'm getting desperate.
> I have a SQL2005 Standard publisher with 10 SQL2005 Express subscribers
> using web synchronization (anonymous subscriptions). The identity ranges
> on
> the subscribers are not properly updating in at least some cases. The
> identity range is 10000 with an 80% threshold but the subscribers run out
> of
> identity values even though they synchronize every hour and never have
> more
> than 150 inserts per sync. Re-syncing after the identity range is full
> (and
> the client sees error messages) fixes the problem.
> Is there any reason why an anonymous subscription on SQL2005 Express would
> not get a new identity range before filling the old range? Thanks.
>
|||My problem is similar, but in this case the publisher is complaining about
the insertions, so the publication database is the one running out of
identities.
I had seen this happening when the subscriber hasn't replicated for a while
and makes more insertions than the available range. However I am getting the
errors in the publication database, when we insert data from another system:
The insert failed. It conflicted with an identity range check constraint in
database 'Appraisal', replicated table 'dbo.ApprRenExistingVAC', column
'ApprRenExistingVACId'. If the identity column is automatically managed by
replication, update the range as follows: for the Publisher, execute
sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution
Agent or the Merge Agent.
In this case our settings are:
@.pub_identity_range = 10000, @.identity_range = 1000, @.threshold = 80
we have 15 subscribers.
thanks in advance for any input.
"Hilary Cotter" wrote:

> No, there is no reason. I think you should open a support incident with PSS
> on this one.
> --
> 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
>
> "John Van Vliet" <JohnVanVliet@.discussions.microsoft.com> wrote in message
> news:815687F4-989A-4CAD-A43C-45A942B55037@.microsoft.com...
>
>

Monday, March 12, 2012

Identity paradox

We have a problem with replicated tables.

We have on publisher and one subscriber(using Merge-replication). In our database there is one table with an identity column. In our publication we are using identity ranges in order to make sure that no identical records are created on both the server and the clients.

So far everything works fine. it is possible to create new records on the server and the clients without any conflicts.

But, when we tries to insert a record that is out of range on either the server or the clients we got an error; "The identity range managed by replication is full and must be updated by a replication agent. The INSERT conflict occurred in database 'Client1', table 'W_Person', column 'ID'. Sp_adjustpublisheridentityrange can be called to get a new identity range."

When not using replication it is possible to use SET IDENTITY_INSERT <table> ON. Is there any workaround without using the stored procedure Sp_adjustpublisheridentityrange?

//John & PeterThere is a check constraint created on the published table which prevents an value that doesn't meet the criteria of the check constraint from being entered into the identity column.

hth,
Shahin

Identity Not for Replication

Hi,
I am beginner in replication, I use a merge type replication
I have a Publisher on a Sql2000 Server
I have a Distributor on an other Sql2000 Server
And I have subscriber with laptop
I use a pull subscription
My question is I f my database have 1000 tables and I use only 60 tables in
my replication
Do I have to use Not for replication on all Identity column of the 60 tables
or all the 100 tables?
Thank in advance!
You only need to use it on the ones which are truly bi-directional, or where
the inserts will occur on both sides between syncs. For example if all of
your inserts occur on your publisher you will not need it. To be on the safe
side I would put it everywhere.
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
"GC" <GC@.discussions.microsoft.com> wrote in message
news:78448474-E3B8-4449-8561-63B13AA33025@.microsoft.com...
> Hi,
> I am beginner in replication, I use a merge type replication
> I have a Publisher on a Sql2000 Server
> I have a Distributor on an other Sql2000 Server
> And I have subscriber with laptop
> I use a pull subscription
> My question is I f my database have 1000 tables and I use only 60 tables
> in
> my replication
> Do I have to use Not for replication on all Identity column of the 60
> tables
> or all the 100 tables?
> Thank in advance!
>
>
>

Friday, March 9, 2012

Identity Fields

Do ALL identity fields need to be changed to (Not for Replication) before I
create Publisher and Subscribers even if some tables will never be changed
outside of the Publisher? Thanks.
David
David,
this depends on what type of replication you are setting up (and actually
which version of SQL Server as Hilary pointed out to me). Please can you
post back with a little more info.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Sorry. We are planning to use Merge Replication with initially only
replicating to laptops with MSDE and not to a 2nd SQL Server. We are
using SQL Server 2000 with SP4.
Thanks.
David
*** Sent via Developersdex http://www.codecomments.com ***
|||David,
yes - in merge replication, this setting is needed.
Rgds,
Paul Ibison
|||They only need to be set for not for replication if
1) you have more than two nodes in your replication topology (i.e. more than
one subscriber)
2) Inserts occur on both sides between merges
This does capture the majority of merge implementations, but not all of
them.
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
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23tTlvK77FHA.1484@.tk2msftngp13.phx.gbl...
> David,
> yes - in merge replication, this setting is needed.
> Rgds,
> Paul Ibison
>

Identity Columns on replicated data

CREATE TABLE SALES
(sale_id INT IDENTITY (1,1),
sale_region CHAR(2) )
When replicated, the IDENTITY property is lost. If the Publisher crashed and
I wanted to restore the replicated data off the subscriber, I could manually
edit the table and turn on the IDENTITY property under Enterprise Manager. Is
there another way? I can not find a way to do this with the ALTER TABLE
statement. TIA
The best way is to use a custom script in sp_addarticle to put the identity
property with the NFR clause on the subscriber.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:95BE6E91-BDD0-4893-9FB8-47F6F92017B9@.microsoft.com...
> CREATE TABLE SALES
> (sale_id INT IDENTITY (1,1),
> sale_region CHAR(2) )
> When replicated, the IDENTITY property is lost. If the Publisher crashed
> and
> I wanted to restore the replicated data off the subscriber, I could
> manually
> edit the table and turn on the IDENTITY property under Enterprise Manager.
> Is
> there another way? I can not find a way to do this with the ALTER TABLE
> statement. TIA

Wednesday, March 7, 2012

Identity Column values getting corrupted

I have bi-directional replication setup between SQL7 and SQL2000 and I have defined range for each publisher. Every thing has been working just fine and over time I have added several distribution agents for new tables for 2 way replication. In order to be sure both the databases get to do insert in their assigned range, I have a CHECK constraint with "NOT FOR REPLICTION" setup on each replicated table.

Just recently, I have encountered 2 tables for which the check constraint throws an error saying the application is trying to insert row that violates the check constraint. Upon doing DBCC CHECKIDENTI with NORESEED option, I find out that the identity column value is incorrect even though this column has the property "NOT FOR REPLICATION".

I have fixed the problem by running DBCC CHECKIDENT with RESEED option, but after a few days the identity column value gets corrupted again. Any ideas?

I don't think the application is doing insert with "SET IDENTITY INSERT" because a) the identity column is set with NOT FOR REPLICATION property and b)I have check constraints to keep the application from inserting in the wrong range.

Any help will be deeply appreciated. Thanks!

-A

What version of SQL Server 2000 are you running? In earlier versions there was a bug in the SQL engine such that under certain conditions the identity seed can be corrupted. This bug is fixed in SP3 and after.