March 10, 2011 at 2:16 pm
In that situation getting the snapshot to recognize the changes can be sometimes problematic. The steps I sent should work IF you are ok with pushing a new copy of the table to the subscriber
I am not ok, I have to find out from management, I am new here:
I do't have access to that server (subscriber)and I don't think it is a good idea to do it
.
March 10, 2011 at 2:23 pm
Krasavita (3/10/2011)
I am not ok, I have to find out from management, I am new here:I do't have access to that server (subscriber)and I don't think it is a good idea to do it
.
The steps you were following earlier would have done that too. If you can't drop the table / recreate it at the subscriber then you would want to do the following;
1. Pull the table out of replication
2. Make the physical table change on the subscriber that you already ran on the publisher table.
3. Put the table in a new publication
4. Before adding the subscription right click on the new publication, select properties, under subscription options set independent agent (top option) to false. Click ok.
5. Create the subscription, and in the process of creating it set it NOT to initialize at the subscriber. (should be a checkbox you can deselect) Complete the subscription creation.
6. Right click on the subscription and select View Synchronization Status to ensure that you are seeing information there.
Being that you have had this table in and out of replication any changes made on the publisher will not be reflected on the subscriber and will have to be manually sync'd.
Out of curiosity, how large is this table? sp_spaceused 'tablename'
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 11, 2011 at 6:25 am
Can try this
1.Pull the table out of replication
2.Reinitialize all subcriptions
3.Put back my table to the publication original
4.Reinitialize all subcriptions
Originally, I Pull the table out of replication, made changes,put it back to replication and then Reinitialize all subcriptions
Also, what is validate subcription do?
Thank you
March 11, 2011 at 6:33 am
Validate is only going to look for data consistency - http://msdn.microsoft.com/en-us/library/ms151251.aspx.
If you can reinitialize all the subscriptions then do the first set of steps that I recommended and that will solve your problem AND you will only reinitialize the data for the one table in question.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 11, 2011 at 6:35 am
Can I put tabel to the same publication?
March 11, 2011 at 6:46 am
There is a way (which I have not tested yet - due to though) to put a table in the same publication and only force the snapshot for that table. I don't have all the steps to that established. Putting it in a new publication will not hurt anything especially if you follow the steps to set independent agent to false on the publication properties before adding the subscription. It will still share the same distribution agent in that case so there is no additional work being placed on the servers.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 11, 2011 at 8:31 am
Onestly, I am afraid to create a new publication, because this data goes to production, that we don't support. Another provider have access to production server and I ran replication from our development server to update production server. I still don't understand why this is happening. I thought making changes on the tabel and put it back to the original replication should be very easy and not give any problems
March 11, 2011 at 8:40 am
Altering a replicated tables structure DOES NOT require a full reinitialisation of all subscribers. Imagine doing this when a publication contained many gigabytes.
Ensure you do this in TSQL. The correct process would be:
1. Drop all subscriptions to the article
2. Remove the article from the publication
3. Alter the table normally.
4. Add the article to the publication
5. Add the subscriptions to the article
6. Generate a snapshot.
Done correctly, this will generate a small snapshot containing just the republished article and its BCP data. The settings should include the drop existing object from the subscriber and be rebuilt based on the schemaoption parameter.
March 11, 2011 at 8:48 am
MysteryJimbo (3/11/2011)
Altering a replicated tables structure DOES NOT require a full reinitialisation of all subscribers. Imagine doing this when a publication contained many gigabytes.Ensure you do this in TSQL. The correct process would be:
1. Drop all subscriptions to the article
2. Remove the article from the publication
3. Alter the table normally.
4. Add the article to the publication
5. Add the subscriptions to the article
6. Generate a snapshot.
Done correctly, this will generate a small snapshot containing just the republished article and its BCP data. The settings should include the drop existing object from the subscriber and be rebuilt based on the schemaoption parameter.
Correct me if I am wrong but I thought that this was ONLY true when immediate_sync was not set on and if it was it would generate a snapshot for all the articles, not just for the one added. Is that incorrect?
My point in recommending the new publication with a shared distribution agent was to avoid any caveats with the new article add to the existing publication to ensure that only the one table would be sent in the snapshot. Still seems safer to me in this situation.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 11, 2011 at 8:56 am
Check the syspublications table in Published database.
select replicate_ddl from <Published_DB>.dbo.syspublications Where name = (Select name from sysarticles where name = 'Your Table Name')
If the value returned = 0, you have an issue.
Can alter it for the publication but you will have to resync the publication.
So make sure its not too big and that you have a big enough Snapshot folder..
-- if you want to change the replicate_ddl property, use:
EXEC sp_changepublication
@publication = <<Publication_Name>>,
@property = N'replicate_ddl',
@value = N'1',
@force_invalidate_snapshot = 0,
@force_reinit_subscription = 1;
-- Good luck
March 11, 2011 at 9:13 am
David Benoit (3/11/2011)[hr
Correct me if I am wrong but I thought that this was ONLY true when immediate_sync was not set on and if it was it would generate a snapshot for all the articles, not just for the one added. Is that incorrect?
My point in recommending the new publication with a shared distribution agent was to avoid any caveats with the new article add to the existing publication to ensure that only the one table would be sent in the snapshot. Still seems safer to me in this situation.
We predominantly use immediate_sync and follow this process on a near weekly basis.
Some weeks we can make 2 or 3 changes to replication so this has been performed many times.
March 11, 2011 at 9:27 am
MysteryJimbo (3/11/2011)
David Benoit (3/11/2011)[hr
Correct me if I am wrong but I thought that this was ONLY true when immediate_sync was not set on and if it was it would generate a snapshot for all the articles, not just for the one added. Is that incorrect?
My point in recommending the new publication with a shared distribution agent was to avoid any caveats with the new article add to the existing publication to ensure that only the one table would be sent in the snapshot. Still seems safer to me in this situation.
We predominantly use immediate_sync and follow this process on a near weekly basis.
Some weeks we can make 2 or 3 changes to replication so this has been performed many times.
Thanks. Appreciate the feedback. I have a note to test this with some of the different situations in the near future.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 11, 2011 at 9:28 am
Krasavita - sounds like you should be ok going with MysteryJimbo's recommendation then and that will allow you to avoid the new publication.
Let us know how that goes.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 11, 2011 at 9:37 am
Guys, I just checked the schema and data was replicated to the other table, but they don't see it on the webside, so I think this is not replication problem. I am concern why for publication it is showing poor (status) and how this can be fixed.
Thank you
March 11, 2011 at 9:43 am
Krasavita (3/11/2011)
Guys, I just checked the schema and data was replicated to the other table, but they don't see it on the webside, so I think this is not replication problem. I am concern why for publication it is showing poor (status) and how this can be fixed.Thank you
So, you have verified that the schema change you made was replicated to the subscriber and that data is being replicated there as well? Great news if it is working. What steps did you use to fix it?
Not sure why it would be showing poor status. Is the table very active, i.e. a lot of transactions?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply