Hi,
I have setup merge replication to run between two SQL 2k (SP3) across the internet. But while selecting the articles via EM I forgot to select "Identity Resolver" for a particular table. And this is creating havoc in that table.
I have to solve this by dropping the article. Or is there any other way?
But if I try to drop the article it says that there is an active subscription for this publication. Would this mean that I will have to drop my entire publication and create the entire thing again?
Can someone help me with this?
Thank you.
Regards,
Karthik
Karthik,
it is not mandatory to reinitialize - you can drop the subscription to a particular article using sp_dropsubscription then drop the article using sp_droparticle, then sp_addarticle to readd then sp_refreshsubscriptions.
Incidentally, what is the error message you see?
Regards,
Paul Ibison
|||Hi Paul,
Thank you for the quick response.
I see a primary key violation for a column that has IDENTITY property set and marked as "YES NOT FOR REPLICATION".
So according to you I do the following -
1. Run sp_dropsubscription at the publisher.
2. Run sp_droparticle
3. Run sp_addarticle
4. Run sp_refreshsubscriptions
I am not all that experienced in Replication. So I still have a few queries on what you have said. I would like to be really clear before I do something as this is the production database.
If I drop a subscription from the publisher, will it not mean that I would need to initialize via the snapshots again?
And what does sp_refreshsubscriptions do actually? I did look at BOL, but didn't understand too much.
Regards,
Karthik.
|||Karthik,
apologies - I just reread your post and noticed that you are using merge replication. In this case my route won't work - it only works for snapshot and transactional. What you can do though is to script out your publication (inc subscriptions),delete the
publication (and prevent users from editing data on the subscriber), make necessary changes to the table, then recreate the publication. For each of the other articles you set @.sync_type to 'none' to establish that the table already exists on the subscrib
er. For the table that you want the identity value, you'll need to have @.sync_type set to 'automatic'.
Alternatively you can have several publications to separate the articles if this is a common issue.
As another point, I'd mention that you can be sure to avoid these PK violations if you set the increment of the identity column in such a way as there can never be an overlap eg publisher uses even numbers and subscriber odd ones. There is an article by M
ichael Hotek on this (http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison
|||Hi Paul,
I did generate a script of both the publisher and subscribers. But nowhere could I see a @.sync_type. I searched the entire text file.
May be I forgot to specify that I have a pull subscription.
Do let me know if my posting part of the script file would help.
Thank you once again.
Regards,
Karthik
|||Karthik,
it's a parameter off sp_addmergepullsubscription or sp_addmergesubscription.
However, unfortunately it is not valid on a per article basis
- hadn't
appreciated this subtle difference between transactional and merge, so you'd
have to set @.sync_type set to 'automatic' for the complete subscription. In
your case you might be able to make the required table modification to the
table on the publisher and subscriber then run this. As it involves identity
column changes whose ranges are saved to system tables I suspect this is not
viable and would advise another route:
(a) drop the publication! Add 2 new publications - one with the old tables
apart from the problemmatic one, one with the problemmatic table. For the
first one have @.sync_type set to 'none' (subscriber has schema) and have
@.sync_type set to 'automatic' for the second.
(b) if this is not feasible (due to PK-FK constraints between articles),
then go down the initial route of a complete recreation of the publication.
Sorry it's not easier advice.
Regards,
Paul Ibison
|||Hi Paul,
What you seemed to have mentioned seems quite complicated. I would like to stall it as much as possible. Anyways, may be I am trying to go about this the wrong way as I didn't specify why I want to drop the article.
Is there any way I can specify idenity ranges for an article after it has been published? What I need to do basically is, specify that my table "FTPFile" has a range of 1-1000 from my publisher with a threshold of 80%.
Is there any way of doing this?
Thank you once again!
Regards,
Karthik.
|||Karthik,
the only sp wich might be relevant is:
sp_adjustpublisheridentityrange
From BOL: For a publication which has the auto identity range enabled, the
Distribution Agent or Merge Agent is responsible for automatically adjusting
the identity range in a publication based on its threshold value. However,
if for some reason the Distribution Agent or Merge Agent has not been run
for a period of time, and identity range resource have been consumed heavily
to the point of threshold, users can call sp_adjustpublisheridentityrange to
allocate a new range of values for a Publisher.
The identity ranges are held in a system table and the infrastructure is
also implemented using check constraints, but I would not advise you to
change these manually.
My recommendation would be to set it up again and ensure there can't be any
problems by following Michael Hotek's example: eg publisher uses even
numbers and subscriber odd ones. There is an article on this
(http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison
|||Hi Paul,
Thank you for all the help.
I was able to fix the problem as suggested in the link.
Thank you once again.
Regards,
Karthik
