Constraint causing replication problem

Hi all,

we receive an errors today about an insert action:

conflict
when I check the structure of table I see this:

ALTER TABLE [packing].[yyyyyyyy]  WITH NOCHECK ADD  CONSTRAINT [repl_identity_range_DE7B0399_B451_49D9_A83E_016FB6EDDDE9] CHECK NOT FOR REPLICATION (([yyyyyyyyy]>(151000001) AND [yyyyyyyyy]<=(152000001) OR [yyyyyyyyy]>(152000001) AND [yyyyyyyyy]<=(153000001)))
GO

any suggestion on this ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jose TorresCertified Database AdministratorCommented:
I am guessing you have replication set up.
This could be caused by the current identity value on the publisher is not within the range of the identity range check constraint.
marrowyungSenior Technical architecture (Data)Author Commented:
yeah, another reason ?

what will be the suggestion ?
Jose TorresCertified Database AdministratorCommented:
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

marrowyungSenior Technical architecture (Data)Author Commented:
"How to: Manage Identity Columns (Replication Transact-SQL Programming) "

any UI based instead of programming approach ?
Jose TorresCertified Database AdministratorCommented:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
I heard there are auto range manager on SQL merge replication, right?

can I do that without impacting the whole ongoing merge replication ?
marrowyungSenior Technical architecture (Data)Author Commented:
by this link:

https://technet.microsoft.com/en-us/library/ms151736(v=sql.105).aspx

"To modify identity ranges and thresholds after a table is published


1.On the Articles page of the Publication Properties - <Publication> dialog box, select a table with an identity column.


2.Click Article Properties, and then click Set Properties of Highlighted Table Article.


3.On the Properties tab of the Article Properties - <Article> dialog box, in the Identity Range Management section, enter values for one or more of the following properties: Publisher range size, Subscriber range size, and Range threshold percentage.


4.Click OK.


5.Click OK on the Publication Properties - <Publication> dialog box.

"

it seems that I don't see the Identity Range Management section, see screenshot.

it also seems that we are on automatically manage identity range, right? why still have this kind of problem.

but not all articles has this "automatically manage identity range" option, can you share why ?

if later on if we CAN enable this automate thing, any impact on our merge replication ?
marrowyungSenior Technical architecture (Data)Author Commented:
oh, is here, but not for all article(Table), why ?
identity-range-mangement.jpg
Anthony PerkinsCommented:
but not all articles has this "automatically manage identity range" option, can you share why ?
Perhaps the article does not contain an IDENTITY column ...
marrowyungSenior Technical architecture (Data)Author Commented:
I am watching not training note for replication for my company, it is funny that they are using automated identity range management BUT ALSO need to enlarge the publisher and subscriber range from 10000 to 1000000 and from 1000 to 1000000 respectively.

automated range management should already handle that, right?

you can see that this properties page do not offer a range for me to adjust/change, any idea?

article-properties.jpg
Jose TorresCertified Database AdministratorCommented:
Although the SQL Server GUI allows you to do many things it does not expose everything.
But the heavy duty stuff is still the old fashioned t-sql.  Running stored procedures designed to configure or modify certain features. Replication is one of those.  Out of the box you can set up basic replication but if you want more customizing you need to rely on the stored procedures that it has.
marrowyungSenior Technical architecture (Data)Author Commented:
"But the heavy duty stuff is still the old fashioned t-sql.  Running stored procedures designed to configure or modify certain features. "

I hate that, T-SQL again !

what is the SP and the REAL example on changing that ?
Anthony PerkinsCommented:
automated range management should already handle that, right?
Nope.  That is not correct.
Anthony PerkinsCommented:
what is the SP and the REAL example on changing that ?
You are talking about the sp_addarticle (Transact-SQL).  Unless you have a DBA in your company then I would suggest you limit yourself to the simple tasks you can accomplish with the GUI.
marrowyungSenior Technical architecture (Data)Author Commented:
"Unless you have a DBA in your company then I would suggest you limit yourself to the simple tasks you can accomplish with the GUI. "

by using GUI in this case can't help on this, right?

"automated range management should already handle that, right?
 Nope.  That is not correct."

so we can ignore about the automated range management as it is by default, but we still have a lot of conflict in range issue, we MUST adjust the publisher and subscriber range, right?
Anthony PerkinsCommented:
>>so we can ignore about the automated range management as it is by default,<<
No I did not say that.  You will need to set an appropriate range.
marrowyungSenior Technical architecture (Data)Author Commented:
so we STILL need to adjust the range even automate range management is by default, in use?

we always adjust:

1)      publisher range size: from 10000 to 1000000,
2)      Subscriber range size from 1000 to 1000000

it still not enough, so must change both to 2000000, but what is the side effect on increasing that much on publisher? it use more RAM ? our RAM on publisher already very little.

and what is range threshold percentage = 80 ? what is that for ?
Anthony PerkinsCommented:
I think it is time you closed this question and started a new one.
marrowyungSenior Technical architecture (Data)Author Commented:
but it is related to the same thing, identity range problem ?
marrowyungSenior Technical architecture (Data)Author Commented:
Anthony Perkins,

"Perhaps the article does not contain an IDENTITY column ... "

I think the replication will add that identity column for us, it seem it is not! the identity column is added by us on the table which is GOING to replicate, right?

then that's why not all articles has the range to adjust.
Anthony PerkinsCommented:
I think the replication will add that identity column for us
Why in the world would Replication add an IDENTITY column?

then that's why not all articles has the range to adjust.
Bingo!
marrowyungSenior Technical architecture (Data)Author Commented:
"Why in the world would Replication add an IDENTITY column?"

noo. I don't know replication as much as you guy.

if we have 2x subscribers and each one update with the same value and with identities column we have to make sure that they don't conflict with each other, so we need a range to manage it.

but if it is not replication we don't care about that so much as we are not going to update both subscriber at nearly the same time. so I think replication add this column for us.

"then that's why not all articles has the range to adjust.
 Bingo! "

I just learn that if we seem conflict in range we only need to/ can only increase the range and no more than that, right?
marrowyungSenior Technical architecture (Data)Author Commented:
any update on this  ? tks.
marrowyungSenior Technical architecture (Data)Author Commented:
administrator, please add this post to more group , tks.
Vitor MontalvãoMSSQL Senior EngineerCommented:
You need to click on "Request Attention" button at the end of the main question.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.