July 12, 2011 at 12:22 pm
The publisher is on one instance running SQL 2005 SP3 (9.00.4053.00). The distributor and subscriber are on a second instance running SQL 2005 SP2 (9.00.3080.00). We're talking about Transactional replication with push subscription types.
I am a replication newbie and I need to drop an article from an existing publication.
According to this technet article http://technet.microsoft.com/en-us/library/ms152493(SQL.90).aspx, dropping an article from a publication "invalidates the current snapshot; therefore a new snapshot must be created". This can be a very lengthy and disruptive process in the environment depending on the size of the tables in the publication.
Steps from the article:
1. Dropping an article from a publication involves dropping the article and creating a new snapshot for the publication. Dropping an article invalidates the current snapshot; therefore a new snapshot must be created.
2. After dropping an article from a publication, you must create a new snapshot for the publication (and all partitions if it is a merge publication with parameterized filters).
That said, I have seen various posts on this forum and others claiming that to drop an article from an existing (transaction replication) publication you can simply drop the subscription using sp_dropsubscription and then drop the article using sp_droparticle, and that's it.
Any insight into the topic would be much appreciated. If a re-initialization of the snapshot can be avoided it would be ideal. Thanks for reading.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 12, 2011 at 12:30 pm
You can drop the article with the subscriptions in place and yes it will invalidate the snapshot. However, if you were going to use the snapshot again later for a new subscriber I would recommend creating the new snapshot again then.
If you want to remove the subscription and then the article it won't give you the invalid snapshot warning but really the snapshot will be invalid as it will contain the article you just pulled AND you will have introduced another issue in that any transactions that would need to be replicated while the subscription was dropped would be lost. Don't do this.
So, you can just open the publication, remove the article (deselect) and then save. Acknowledge the warning about the snapshot and things will continue working normally.
I can provide further details if needed.
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
July 12, 2011 at 12:39 pm
Thanks for the reply David. I would like to do this in T-SQL if possible. Would running this on the publisher instance be equivalent to the steps you provided for the GUI?
EXEC PublisherDatabase.sys.sp_dropsubscription
@publication = N'Pub_Name',
@article = N'article_name',
@subscriber = N'instance2_name',
@destination_db = N'db_name' ;
GO
EXEC PublisherDatabase.sys.sp_droparticle
@publication = N'Pub_Name',
@article = N'article_name',
@force_invalidate_snapshot = 1 ;
GO
According to this article http://technet.microsoft.com/en-us/library/ms173832(SQL.90).aspx it says that setting @force_invalidate_snapshot = 1 will allow the proc to complete even though the action will invalidate the existing snapshot. That sounds a lot like accepting the warning generated by the GUI. Do you know if they're related?
[ @force_invalidate_snapshot = ] force_invalidate_snapshot
Acknowledges that the action taken by this stored procedure may invalidate an existing snapshot. force_invalidate_snapshot is a bit, with a default of 0.
0 specifies that changes to the article do not cause the snapshot to be invalid. If the stored procedure detects that the change does require a new snapshot, an error occurs and no changes are made.
1 specifies that changes to the article may cause the snapshot to be invalid, and if there are existing subscriptions that would require a new snapshot, gives permission for the existing snapshot to be marked as obsolete and a new snapshot generated.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 12, 2011 at 12:51 pm
Yes, force_invalidate_snapshot = 1 is identical to the warning in the GUI. Only run the sp_droparticle statement though not the sp_dropsubscription statement that you have. You really don't want to drop the subscription unless you need to and you don't to remove an article. By removing the article from the publication with sp_droparticle you will immediately stop replicating inserts / updates / deletes / ddl for that table / article.
Let me know if you would like me to elaborate on why you don't need to (or want to really) drop the subscription to remove the article from the publication and I will do that. It will probably be a bit later though.
For now, removing the article is very safe.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
July 12, 2011 at 1:17 pm
David Benoit (7/12/2011)
Yes, force_invalidate_snapshot = 1 is identical to the warning in the GUI. Only run the sp_droparticle statement though not the sp_dropsubscription statement that you have. You really don't want to drop the subscription unless you need to and you don't to remove an article. By removing the article from the publication with sp_droparticle you will immediately stop replicating inserts / updates / deletes / ddl for that table / article.Let me know if you would like me to elaborate on why you don't need to (or want to really) drop the subscription to remove the article from the publication and I will do that. It will probably be a bit later though.
I took the drop commands from the SSMS script created by right-clicking the Publication, choosing Generate Scripts and select "drop or disable the components" option but that's not me saying it was the proper starting point for my scenario, I was just looking around for a starting point, any starting point. This article http://technet.microsoft.com/en-us/library/ms146908(SQL.90).aspx makes no mention of dropping a subscription when dropping an article. That said, logically it seems correct to me to drop any subscriptions to an article before dropping the article itself, but like I said I am new at this. Thanks for your help here. I would love to hear the explanation as to why it's not necessary. No worries on timing, whenever you can get to it, I appreciate the info.
For now, removing the article is very safe.
Thanks, this is what I was looking to know for now.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 12, 2011 at 1:20 pm
Great. I will follow-up later with a bit on subscriptions and the impact of removing them.
Let me know if you have any problems with the article drop. You shouldn't. I have had to remove MANY using both sp_droparticle and the GUI and it works every time.
More to follow.....
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
July 19, 2011 at 2:48 pm
I am also interested in knowing the reason for why subscription is not dropped.
July 20, 2011 at 9:23 am
It seems like the OP wants the replication to continue, but doesn't require the one specific article any longer to be replicated. Therefore, they want the rest of the tables still replicated. Dropping the subscription would halt the replication.
To re-subscribe, you may or may not need to reinitialize from a snapshot, in which case the snapshot would need to be re-created in order to have a snapshot without the article being dropped.
Hope that helps,
Steve
July 20, 2011 at 9:36 am
Thanks for the reply Steve.
S. Kusen (7/20/2011)
It seems like the OP wants the replication to continue, but doesn't require the one specific article any longer to be replicated.
Correct.
Therefore, they want the rest of the tables still replicated. Dropping the subscription would halt the replication.
The non-intuitive part is that sys.sp_dropsubscription accepts the article name. I was hoping to know from a practitioner's perspective...why would I not drop the subscription?
To re-subscribe, you may or may not need to reinitialize from a snapshot, in which case the snapshot would need to be re-created in order to have a snapshot without the article being dropped.
I am not expecting to need to re-subscribe to this article...but I am curious. Which context is this in? After only dropping the article? Or after dropping the article and the subscription?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 20, 2011 at 9:51 am
Therefore, they want the rest of the tables still replicated. Dropping the subscription would halt the replication.
The non-intuitive part is that sys.sp_dropsubscription accepts the article name. I was hoping to know from a practitioner's perspective...why would I not drop the subscription?
This is a good question, I haven't worked directly with that proc and didn't know it had the article option. Apologies for that.
To re-subscribe, you may or may not need to reinitialize from a snapshot, in which case the snapshot would need to be re-created in order to have a snapshot without the article being dropped.
I am not expecting to need to re-subscribe to this article...but I am curious. Which context is this in? After only dropping the article? Or after dropping the article and the subscription?
The context there was that if the entire subscription was dropped, you would certainly need to re-subscribe to the publication that no longer included the article you were looking to drop, which would then need a fresh snapshot.
Sorry, I didn't provide that much insight, was attempting to clarify the other poster's response about not dropping the subscription and forgot to quote their response.
Steve
July 27, 2011 at 11:37 am
David Benoit (7/12/2011)
Great. I will follow-up later with a bit on subscriptions and the impact of removing them.Let me know if you have any problems with the article drop. You shouldn't. I have had to remove MANY using both sp_droparticle and the GUI and it works every time.
More to follow.....
Unfortunately issuing a call to sys.sp_droparticle failed with this error:
Could not drop article. A subscription exists on it.
So, I dropped the article from the publication using the GUI :sick: and it worked fine.
I [assume] that the GUI issued a call to sys.sp_dropsubscription in the background, before dropping the article, but I did not run a trace so cannot confirm it.
I am going to setup a test environment so I can get to the bottom of this but I wanted to post back the state of affairs.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 28, 2011 at 1:27 am
The two procedure calls you mentioned in the original question are the ones you need to call (and in that order). This is exactly what the GUI does in the background.
In order to remove an article from a publication, you have to make sure no one is subscribing to it (hence the error message saying you can't drop the article from the publication).
It is a little easier to understand if you try an envisage replication by seeing it in terms of magazines/newspapers (I guess this is the reason for the replication nomenclature).
Imagine your replication publication is a newspaper producer, the articles are the different newspapers (e.g. daily, mid-week and sunday editions) and the subscriptions are the actual customers who have a subscription.
It is decided the mid-week is too expensive to produce and is to be dropped from the program. You first have to drop the subscriptions to this article (inform the customer and also stop billing them), before you can stop sending them their newspaper. If you don't, you have all sorts of problems! 🙂 Luckily, SQL Server knows this rule and enforces it, whereas some companies can still send bills to their customers after the newspaper stops being delivered.
I hope this convoluted example helped explain the reasoning behind your problem.
Regards,
WilliamD
July 28, 2011 at 9:00 am
I generally use this to generate the TSQL.
select 'exec sp_dropsubscription @publication = N''' + sp.name + '''' + ', @article = N''' + sa.name + '''' + ', @subscriber = N''' + ss.srvname + '''' + char(13) +'GO'
from syssubscriptions ss
inner join sysarticles sa on sa.artid = ss.artid
inner join syspublications sp on sp.pubid = sa.pubid
where sa.name in ('')
select distinct 'exec sp_droparticle @publication = N''' + sp.name + '''' + ', @article = N''' + sa.name + '''' + ', @force_invalidate_snapshot = 1' + char(10) + 'GO' + char(10)
from syssubscriptions ss
inner join sysarticles sa on sa.artid = ss.artid
inner join syspublications sp on sp.pubid = sa.pubid
where sa.name in ('')
July 28, 2011 at 9:43 am
WilliamD- (7/28/2011)
The two procedure calls you mentioned in the original question are the ones you need to call (and in that order). This is exactly what the GUI does in the background.In order to remove an article from a publication, you have to make sure no one is subscribing to it (hence the error message saying you can't drop the article from the publication).
This made sense to me from the get-go. Thanks for confirming!
It is a little easier to understand if you try an envisage replication by seeing it in terms of magazines/newspapers (I guess this is the reason for the replication nomenclature).
Imagine your replication publication is a newspaper producer, the articles are the different newspapers (e.g. daily, mid-week and sunday editions) and the subscriptions are the actual customers who have a subscription.
It is decided the mid-week is too expensive to produce and is to be dropped from the program. You first have to drop the subscriptions to this article (inform the customer and also stop billing them), before you can stop sending them their newspaper. If you don't, you have all sorts of problems! 🙂 Luckily, SQL Server knows this rule and enforces it, whereas some companies can still send bills to their customers after the newspaper stops being delivered.
I hope this convoluted example helped explain the reasoning behind your problem.
Your splendid explanation makes perfect sense and is what I logically expected SQL Server to do but could not get wrap my head around it from just reading the documentation. Thanks for taking the time, it was exactly what I was looking to get from posting!
I prefer to do things via T-SQL scripting so having to use the GUI was not something I was happy about. All of this said, the other articles in the publication are happily still replicating...so what is all this business about there now being an invalid snapshot in play now that articles have been dropped from the pub since the initial setup? If this is too far off topic, or to general of a topic to carry on in this thread just say so and I can go hit the books some more or make a new thread.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 28, 2011 at 9:44 am
MysteryJimbo (7/28/2011)
I generally use this to generate the TSQL.
select 'exec sp_dropsubscription @publication = N''' + sp.name + '''' + ', @article = N''' + sa.name + '''' + ', @subscriber = N''' + ss.srvname + '''' + char(13) +'GO'
from syssubscriptions ss
inner join sysarticles sa on sa.artid = ss.artid
inner join syspublications sp on sp.pubid = sa.pubid
where sa.name in ('')
select distinct 'exec sp_droparticle @publication = N''' + sp.name + '''' + ', @article = N''' + sa.name + '''' + ', @force_invalidate_snapshot = 1' + char(10) + 'GO' + char(10)
from syssubscriptions ss
inner join sysarticles sa on sa.artid = ss.artid
inner join syspublications sp on sp.pubid = sa.pubid
where sa.name in ('')
I always welcome a T-SQL approach first. I just added this to the toolkit. Thanks for sharing this!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 47 total)
You must be logged in to reply to this topic. Login to reply