September 8, 2009 at 4:33 am
I want to automatically manage identity. Using SQL Server 2005 SQL Server Management Studio I have configured Merge replication and when i tried to add data from subscriber it won't coz i already setup identity manually. DBCC CheckIdent (table, reseed, 500000)
i wanted to do it work manually whatever i setup but there is a constraint in which i can't update identity my own provided.
ALTER TABLE [dbo].[MasterTable] WITH NOCHECK ADD CONSTRAINT [repl_identity_range_DCBED27C_001E_4A1B_9E55_D7C3974C32C2] CHECK NOT FOR REPLICATION (([autoID]>(24016) AND [autoID]<=(25016) OR [autoID]>(25016) AND [autoID]<=(26016)))
GO
ALTER TABLE [dbo].[MasterTable] CHECK CONSTRAINT [repl_identity_range_DCBED27C_001E_4A1B_9E55_D7C3974C32C2]
How to i fix it when configuring replication via replication wizard? or i need to make script and then make change in it ?
Shamshad Ali.
September 9, 2009 at 8:02 am
Hi
You have Two options:
1. Configuring merge replication by wizard:
After selecting the replication "articles" / "tables" choose "Article Properties" button, then on the Properties tab find "Automatically Manage identity ranges" and chose "Manual"
2. From saved/generated replication configuration script file, if it's available:
for every "exec " change "@identityrangemanagementoption = N'auto'" with "@identityrangemanagementoption = N'manual'"
Bye.
September 18, 2009 at 11:39 pm
1. Configuring merge replication by wizard:
After selecting the replication "articles" / "tables" choose "Article Properties" button, then on the Properties tab find "Automatically Manage identity ranges" and chose "Manual"
If the replication is alreay configured than this option is disabled. That means i have to drop publication again?
for other option of saved script: usually i don't save script just run replication wizard - so it would require me to run publication and subscription from scratch. right?
Shamshad Ali.
September 19, 2009 at 2:36 am
In Your case my step by step suggestion would bee:
1. Delete all subscribers from the affected publication. ( Make sure that when you delete the subscription the subscriber sql server can be accessed by the publisher. It's very important. )
2. Generate the publication T-SQL script. ( It's very easy and time saver )
3. Drop / Delete the publication.
4. In the generated publication script, make the suggested modification "@identityrangemanagementoption = N'manual'"
5. Run the modified script on the publisher sql server.
6. Verify the newly created publication articles if have the desired properties set.
7. Push or Pull the subscribers ( With initialization or not, depends on operation environment )
What I mean by operation environment:
- If you have a maintenance window when you can be sure that nobody can connect to the publisher and the affected subscriber databases to change/create date, you can create the subscribers without initialization.
- If one or more databases form the affected replication topology publisher or subscriber db is accessed 24 from 24 hours you have to create the subscribers with initialization.
And one important thing: Make sure that the IDENTITY column from all articles/tables participating in replication at the publisher and at the subscribers have the "NOT FOR REPLICATION" property set.
Istvan.
September 19, 2009 at 1:16 pm
Your given step by step solution is already i knew and this is what i don't want to repeat like delete subscription etc. what if the setup is already done? now I want to modify it. that was my actual question. Thanks anyway for your detailed reply. i will do it from scratch.
Shamshad Ali
September 20, 2009 at 11:48 am
You're right!
Please accept my apologies by not responding straight to your question.
The only safe way to change the identity management property for a merge publication is by deleting and recreating the affected publication.
That's because, the database objects created by this option ( like constraints added to the articles ) and other dependencies must be removed.
From my experience this is the only safe way to change the identity management property.
I hope it helps my proposed step by step description.
Good Luck
Istvan.
November 21, 2011 at 2:49 am
Thanks Istvan, you saved me from a lot clicking as sql novice. Therefor I started looking for a more efficient solution:-)
I would like to add that you also need to change the pub_identity_range, identity_range & threshold to null
So
@identityrangemanagementoption = N'manual',
@pub_identity_range = null,
@identity_range = null,
@threshold = null,
Jeroen
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply