Replicate a new table without taking a snapshot

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 High Availability » Replicate a new table without taking a...

Replicate a new table without taking a snapshot

Rate Topic

Display Mode

Topic Options

Author

Message

sudonjn

Posted Friday, February 08, 2013 12:44 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 02, 2013 9:14 AM
Points: 19, Visits: 171

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

penu.v8

Posted Friday, February 08, 2013 1:38 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 02, 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

sudonjn

Posted Friday, February 08, 2013 1:58 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 02, 2013 9:14 AM
Points: 19, Visits: 171

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

happycat59

Posted Sunday, February 10, 2013 8:04 PM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:55 PM
Points: 2,471, Visits: 2,066

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

sudonjn

Posted Thursday, February 14, 2013 9:02 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 02, 2013 9:14 AM
Points: 19, Visits: 171

Thank you. I will try that.

Post #1420137

sudonjn

Posted Monday, February 18, 2013 10:26 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 02, 2013 9:14 AM
Points: 19, Visits: 171

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 »

Permissions