SQL Server Performance Forum – Threads Archive

Replication OOOOOOOOOOh!!

Hi folks! I’ve a merge replication setup b/w two servers.
Published tables have columns (INT IDENTITY SEED 1 INCREMENT[NOT FOR REPLICATION]).
Whenever i apply the SNAPSHOT, i have to run DBCC CHECKIDENT(‘table’ RESEED) for each table at the subscriber twice, for the values in the columns are almost always greater than the ID-Seed value. For example the last Identity value in the column is 999 but whenever i insert a new row; i get error; couldn’t insert duplicate value into the table. When i run the dbcc check i see the following message:
"Checking identity information: current identity value ‘1’, current column value ‘999’."
Why do i need to run this script each time. Is it normal or i am missing any option while setting up replication? Another thing:
Upon removing or altering a table requires a full snapshot. It’s painful especially when subscribers are over the WAN and and large size publishers. There’s an option while adding a Push-Subscriber(Don’tThe subscriber already has the schema, don’t initialize the schema).Means that we can drop a replication; delete and alter published tables at both the publisher and subscriber manually and then add our subscribers back using this option. I’ve tested this and seems to work. If this is the case, why the heck there’s any need to apply the snapshot? What are the conscequences if i use this strategy. I wanna know from the experts b4 i try this on my production db.
Thanx in advance.
Howdy!

DBCC CHECKIDENT corrects the current identity value for a column. The current identity value is not corrected, however, if the identity column was created with the NOT FOR REPLICATION clause (in either the CREATE TABLE or ALTER TABLE statement). Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi Satya! What’s the solution to this. Running this check for each table after each snapshot? And what about the 2nd question plz? Thanx.

When you use automatically managed identity ranges, the SQL server imposes constraints on the published table. Check SET IDENTITY_INSERT setting. You will need to go into your table design properties and under Check Constraints turn off the "Enforce constraint for Inserts and Updates". That is checked by default anytime replication puts Identity Range checking on the table.. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi Satya, thanx for helpin. The option "Enforce constraint for Inserts and Updates" and other options are disabled in the design view. Also there isn’t any constraint on the published tables. Thanx.

]]>