P2P Replication - Issue in Manual Identity Management

I am using P2P replication between two server. I face a big issue in manual
identity management. I did as follows
Step 1: Built MyDB1 with data in server1.
Step 2:Set up Server1 MyDB1 as Publisher (Followed msdn white paper
p2ptranrepl.doc)
Step 3:Took backup of MyDB1 in Server1 and restored in server2 as MyDB1
(again as per steps in msdn white paper)
Step 4:Identity range for tablen in server1 is say 1-1000 (have put the
necessary check constraint on identity column. Also made Identity column NOT
FOR replication. Following Hillary Cotter's writeup in
http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/)
I reseeded the identity value in Server2 MyDB1 to have range as 1001 to
2000. Added necessary check constraints
Step 5: As per the msdn whitepaper setup using "Configure peer to peer
topology' wizard.
Step 6: Now say the identity range of server 1 is consumed and I want to
reseed the value to say 2001-3000. Server2 has max as only 1010 and hence
left as is.
I reseed server1 table1 and also change the check constraints accordiingly
from 2001-3000.
**********Now the issue is this change in constraint gets automatically
propogated to the subscriber. But i do not want the subscribers to move to
the new range. They should be in 1001-2000 only. I have verifed that 'Copy
Check Constraints' property is set to FALSE. But still the constraints are
getting propogated to subscribers****************************
This becomes a blocking issue for my manual identity management.
Kindly help to resolve the same.
Thank you,
Lalitha
remove the check constraints. They aren't necessary. I think you should be
using larger ranges as well.
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
"Lalitha" <Lalitha@.discussions.microsoft.com> wrote in message
news:AF09E8A4-90F8-4D4E-9429-94E2FAC3726F@.microsoft.com...
>I am using P2P replication between two server. I face a big issue in manual
> identity management. I did as follows
> Step 1: Built MyDB1 with data in server1.
> Step 2:Set up Server1 MyDB1 as Publisher (Followed msdn white paper
> p2ptranrepl.doc)
> Step 3:Took backup of MyDB1 in Server1 and restored in server2 as MyDB1
> (again as per steps in msdn white paper)
> Step 4:Identity range for tablen in server1 is say 1-1000 (have put the
> necessary check constraint on identity column. Also made Identity column
> NOT
> FOR replication. Following Hillary Cotter's writeup in
> http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/)
> I reseeded the identity value in Server2 MyDB1 to have range as 1001 to
> 2000. Added necessary check constraints
> Step 5: As per the msdn whitepaper setup using "Configure peer to peer
> topology' wizard.
> Step 6: Now say the identity range of server 1 is consumed and I want to
> reseed the value to say 2001-3000. Server2 has max as only 1010 and
> hence
> left as is.
> I reseed server1 table1 and also change the check constraints accordiingly
> from 2001-3000.
> **********Now the issue is this change in constraint gets automatically
> propogated to the subscriber. But i do not want the subscribers to move to
> the new range. They should be in 1001-2000 only. I have verifed that 'Copy
> Check Constraints' property is set to FALSE. But still the constraints are
> getting propogated to subscribers****************************
> This becomes a blocking issue for my manual identity management.
> Kindly help to resolve the same.
> Thank you,
> Lalitha
>
|||Hi,
Thanks for the response.
Though I use large ranges, having the check constraints is for making the
solution fool proof. This validation will ensure that no data gets inserted
other than my configured range.
When 'Copy Check Constraints' is set to FALSE, why is P2P behaviour different?
1) Is this a bug in P2P which will be fixed in future service packs?
2) Is this a limitation of P2P?
If this is the limitation of P2P then I intend to go for merge.
Please clarify.
Thanks for your inputs...
Lalitha
"Hilary Cotter" wrote:

> remove the check constraints. They aren't necessary. I think you should be
> using larger ranges as well.
> --
> 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
>
> "Lalitha" <Lalitha@.discussions.microsoft.com> wrote in message
> news:AF09E8A4-90F8-4D4E-9429-94E2FAC3726F@.microsoft.com...
>
>