Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Replicate a new table without taking a snapshot Expand / Collapse
Author
Message
Posted Friday, February 8, 2013 12:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 11:02 AM
Points: 22, Visits: 201
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.
Post #1417878
Posted Friday, February 8, 2013 1:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 2, 2013 12:37 PM
Points: 19, Visits: 93
you can try adding newtable/ article to the existing publication by recreating the snapshot for that single article if you using transactional replication
Post #1417894
Posted Friday, February 8, 2013 1:58 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 11:02 AM
Points: 22, Visits: 201
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.
Post #1417901
Posted Sunday, February 10, 2013 8:04 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:46 PM
Points: 3,026, Visits: 2,630
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"


Post #1418191
Posted Thursday, February 14, 2013 9:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 11:02 AM
Points: 22, Visits: 201
Thank you. I will try that.
Post #1420137
Posted Monday, February 18, 2013 10:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 11:02 AM
Points: 22, Visits: 201
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
Post #1421295
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse