Add article to existing Txnl Repl Publ w/out re-genning the entire snapshot

Question

Post reply

Add article to existing Txnl Repl Publ w/out re-genning the entire snapshot

Express12

SSCoach

Points: 18514
More actions
March 6, 2006 at 12:00 pm

#111871

SQL 2000 -- I have yet to figure out exactly how to add a single article to an existing publication (only 5 rows of data in the new article) then porting this new article's data to the subscriber - WITHOUT regenerating the entire snapshot of 20 Gb of data.
There's a couple of article's floating around on this subject - I've read them multiple times but cannot seem to prevent SQL from generating the entire Snapshot. If anyone's successfully accomplished this, I'd be interested in starting a dialogue for the solution - w/ specifics outlined.
Many thx in advance.
BT

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 1

The trick to this is in doing it by script.....If your using the wizard, it can't be done.......

look up sp_addarticle in books online.......and of course, if you still need help, I can provide examples....

Express12 SSCoach Points: 18514 More actions · Answer 2

Yes, as I originally mentioned, I read the few articles on this and built out the following solution. The subscription is not picking up CHANGED data at the subscriber side -- or it's not being pushed by the Distributor. Here's what I buil as per the instructions:

--Step 1: DTS your SOURCE table data to your Target table; this builds your target object (ensure you have a PK defined on it)

--Using Query Analyzer at the publisher on the publication database:

--

-- Add a new article to your Publication~

-- a) your_table_1

--

SQL did not execute STEP 2 until I added @force_invalidate_snapshot = 1 --- as a last parameter.

--STEP 2: From Publication Server:

use [Your_DBName]

GO

exec sp_addarticle @publication = N'Your_Pub_Name',

@article = N'your_table_1',

@source_owner = N'dbo',

@source_object = N'your_table_1',

@destination_table = N'your_table_1',

@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 sp_MSins_your_table_1',

@del_cmd = N'CALL sp_MSdel_your_table_1',

@upd_cmd = N'MCALL sp_MSupd_your_table_1',

@filter = null,

@sync_object = null,

@auto_identity_range = N'false',

@force_invalidate_snapshot = 1

GO

SQL DID NOT ALLOW THIS NEXT STEP TO COMPLETE --- SAID THERE's ALREADY A SUBSCRIPTION OUT THERE

--Step 3: For each push subscription that subscribes to the current publication, run the Transact-SQL

-- statements in SQL Query Analyzer at the publisher on the publication database.

use [Your_DBName]

GO

exec sp_addsubscription

@publication = N'Publication_Name',

@article = N'your_table_1',

@subscriber = N'your_server_name',

@destination_db = N'your_table_1',

@sync_type = N'none',

@update_mode = N'read only',

@offloadagent = 0,

@dts_package_location = N'distributor'

GO

--Step 4: Using Query Analyzer at the Publisher on the publication database, Render Ins/Upd/Del

-- Scripts for publication = 'Publication_Name'

-- Capture model sp's for:

-- sp_MSins_your_table_1, sp_MSupd_your_table_1, sp_MSdel_your_table_1

EXEC Your_DBName.dbo.sp_scriptpublicationcustomprocs @publication = N'Publication_Name'

--(the previous step worked fine -- but data is not being published via txn'l replication -- I created the 3

--stored proces on the target DB)

BT

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 3

the subscription has to be deleted....

You cannot do the

@pre_creation_cmd = N'drop',

Or the

@force_invalidate_snapshot = 1

as these will force a rebuild....

The structures have to already be built on both db's....

then you recreate the subscription....

also, once recreated, the subscription will only picked up data from that point forward, so you will need to verify the data is in sync manually before starting the subscription....

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 4

Let me get home tonight, and I'll post you a full set of scripts to do this, step by step, and with the correct params....

Express12 SSCoach Points: 18514 More actions · Answer 5

Scorpion - I'd really appreciate it. I owe you one -

-bt

BT

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 6

I apologize. I was out sick yesterday, but I have your scripts for ya. Where do you want them sent?

Express12 SSCoach Points: 18514 More actions · Answer 7

bill.turner@skippackridge.com would be great. THX!

BT