February 18, 2011 at 11:50 am
I have a VLDB in my environment. We have transaction replication configured.
Recently we added three tables in the publisher. What is the best way to generate the snapshot only for these three articles?
February 18, 2011 at 12:10 pm
If my memory serves me right, when you add a new object (Article) to an existing publication, it will create a snapshot for just that new article. It will not recreate snapshot for the other article that is already synched and being replicated. (Unless the other articles are out of Synch)
I do not have a set up to test it out now though.
-Roy
February 18, 2011 at 1:07 pm
Hey Roy,
I also thought the same thing but when I run the snapshot this started creating the files even for the tables which already existed. I just executed the snapshot agent job and nothing else. This was frustrating.
February 23, 2011 at 1:21 am
Please refer this link.
This article might have the answer. I didn't read it fully though.
Please revert back to forum if you are able to get the answer for this post.
M&M
March 1, 2011 at 10:17 pm
luckysql.kinda (2/18/2011)
Hey Roy,I also thought the same thing but when I run the snapshot this started creating the files even for the tables which already existed. I just executed the snapshot agent job and nothing else. This was frustrating.
Run the following, before generating the new snapshot. I have used this many times. It works.
EXEC sp_changepublication
@publication = 'MainPub',
@property = N'allow_anonymous',
@value = 'false'
GO
EXEC sp_changepublication
@publication = 'MainPub',
@property = N'immediate_sync',
@value = 'false'
GO
March 1, 2011 at 11:57 pm
Hi Guys , Let me clear your doubt here :
If you are working on SQL 2000 , then you ca use the following command to generate only newely added article snapshot. And I recommend it that you should set it on sql 2000 :
EXEC sp_changepublication
@publication = 'MainPub',
@property = N'allow_anonymous',
@value = 'false'
GO
EXEC sp_changepublication
@publication = 'MainPub',
@property = N'immediate_sync',
@value = 'false'
GO
Note : In SQL 2005 , this feature is bydefault. If you are working SQL 2005 , then no need to do anything . By default only newely added article snapshot will be generate.
Ashok
MCP|MCTS|MCITP|MBA IT|
March 2, 2011 at 2:15 am
ashok_a2004 (3/1/2011)
Note : In SQL 2005 , this feature is bydefault. If you are working SQL 2005 , then no need to do anything . By default only newely added article snapshot will be generate.Ashok
MCP|MCTS|MCITP|MBA IT|
Hi Ashok,
Are you 100% sure about this?
I just checked this in SQL 2008. It generated snapshot of new as well as old articles.
Suresh
March 5, 2011 at 11:02 am
We do this almost on a day to day basis.
1. Add the article using script
2 Add the subscription to the article
3. Generate snapshot (1 article will be generated)
Step 1 will only add the article to the publication, it will NOT force a subscriber to subscribe to it. Step 2 will subscribe the article.
You may have issues if you edit the publication through the GUI as it tends to do things in its "own way" and not the best way. We have a rule here, "Dont use the GUI except to generate scripts which can be validated".
exec sp_addarticle
@publication = N'', @article = N'',
@source_owner = N'', @source_object = N'',
@destination_table = N'',
@type = N'logbased', @creation_script = null, @description = null,
@pre_creation_cmd = N'drop',
@schema_option = 0x00000000000000F3, @status = 16, @vertical_partition = N'false',
@ins_cmd = N'CALL procname',
@del_cmd = N'CALL procname',
@upd_cmd = N'MCALL procname',
@filter = null, @sync_object = null
GO
exec sp_addsubscription @publication = N'',
@subscriber = N'',
@destination_db = N'',
@subscription_type = N'push',
@sync_type = N'automatic',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply