February 8, 2013 at 12:44 pm
I have a 350 GB table that is replicated, and I need to rename. This will require replication to be stopped on this table. Is it possible to add a table to a publication without requiring a snapshot? You can do this when setting up a new publication, but I cannot figure out how to do it when adding an article to an existing publication. I do not want to create another publication for only this table.
February 8, 2013 at 1:38 pm
you can try adding newtable/ article to the existing publication by recreating the snapshot for that single article if you using transactional replication
February 8, 2013 at 1:58 pm
You are correct in that if I remove the article from the publication and add it back once renamed, a snapshot of only that table will be taken. I am trying to avoid taking a 350GB snapshot, and more-so, I am trying to have as little downtime as possible. It will take too long to create the snapshot and move that data to the replicated table.
February 10, 2013 at 8:04 pm
Easy, peasy. When you create the article, specify "None" for @pre_creation_cmd (when using the sproc sp_addarticle). If you are using the GUI, when adding the article, go into "Article Properties" and scroll down to "Action if name is in use" and select "Keep existing object unchanged"
February 14, 2013 at 9:02 am
Thank you. I will try that.
February 18, 2013 at 10:26 am
I could not get it to work through using the GUI. It would still prompt that a snapshot was needed. However, I was able to script it. I did have to make @pre_creation_cmd = 'none', but I also had to run the command sp_addsubscription with @sync_type = 'replication support only'. Here are the scripts with generic names.
USE [PublicationDB]
EXEC sp_addarticle @publication = N'Publication', @article = N'Table', @source_owner = N'dbo',
@source_object = N'Table', @type = N'logbased', @description = N'', @creation_script = N'',
@pre_creation_cmd = N'none', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual',
@destination_table = N'Table', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false',
@ins_cmd = N'CALL [dbo].[sp_MSins_Table]', @del_cmd = N'CALL [dbo].[sp_MSdel_Table]',
@upd_cmd = N'SCALL [dbo].[sp_MSupd_Table]'
GO
USE [PublicationDB]
EXEC sp_addsubscription @publication = N'Publication', @subscriber = N'Subscriber',
@destination_db = N'SubscriberDB', @subscription_type = N'Push', @sync_type = N'replication support only',
@article = N'Table', @update_mode = N'read only', @subscriber_type = 0
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply