How can i add the article in existing publication

Question

Post reply

How can i add the article in existing publication

sram24_mca

SSCrazy

Points: 2633
More actions
January 29, 2008 at 5:51 am

#178729

I have one Publisher database and one Subscriber database,I started transactional replication for above database.
Now i want to add some more tables in the publisher database and that data has to be replicate it in to subscriber.
Is it possible?

Viewing 6 posts - 1 through 5 (of 5 total)

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

floyd99 Mr or Mrs. 500 Points: 571 More actions · Answer 1

i use merge replication, not transaction replication.. so forgive me if this doesn't work.

yes. adding articles is possible. removing them is not (not in merge.. maybe in trans you can).

just edit the publication and add the articles in the articles tab.

then you need to create a new snapshot

then the subscriber needs to be reinitialised to obtain the new snapshot.

like i said, this works for merge - i don't know how trans differs so do some reading first or test it on a test system.

Balaji L Hall of Fame Points: 3067 More actions · Answer 2

U can publish N number of articles in publisher and populate the data to different subscriber/s. If u r using transactional repl with pull subscription then do the following :

1) Define article in publisher db using sp_addarticle

2) Add the subscription in subscriper side using sp_addsubscription

3) Start the snapshot agent

Thanks,

Balaji L

Vivien Xing SSChampion Points: 12297 More actions · Answer 3

Yes, you can. After adding a new article, "The Distribution Agent or Merge Agent then copies the schema and data for the new article to the Subscriber (it does not reinitialize the entire publication").

check here: http://technet.microsoft.com/en-us/library/ms152493.aspx

MyDoggieJessie SSC-Forever Points: 44484 More actions · Answer 4

I've got a related question:

I've already created the publication which contains 31 articles, the next day a report developer said they forget they needed another table added to the mix so they could build a report off of the subscriber database...

I've read online that it should be a simple matter of adding the new article then starting the snapshot agent. This link (http://msdn.microsoft.com/en-us/library/ms152493.aspx) says that after adding an article to a publication, you must create a new snapshot for the publication (and all partitions if it is a merge publication with parameterized filters). The Distribution Agent or Merge Agent then copies the schema and data for the new article to the Subscriber (it does not reinitialize the entire publication).

HOWEVER, this does in fact REINITIALIZE the entire blodody publication. I'm now sitting at 15% of the 3rd article...waiting on 29 more to complete...just because I added one new table with 26,000 rows!

Have I set up the transactional replication incorrectly to begin with? I've checked the distributor and publisher for anything that would say to fetch an entirely new snapshot but have come up empty-handed.

Can anyone help?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

shaun.stuart SSCertifiable Points: 7031 More actions · Answer 5

Hmm. Yes, I think you did something wrong. I was in a similar situation. I had a database that had hundreds of tables replicated. The snapshot was 9 GB. I then needed to add one more table. I obviously did not want the whole database to be snapshotted again. I just added the table to the subscription and generated a snapshot. The snapshot agent ran and created a snapshot of only the new table and sent that over. FYI, this was on SQL 2000.

Did you stop replication before adding the additional table? If you did that, that might cause all the tables to be included in a new snapshot.

The link you gave specifically states a new snapshot of the entire publication will NOT be created: "After adding an article to a publication, you must create a new snapshot for the publication (and all partitions if it is a merge publication with parameterized filters). The Distribution Agent or Merge Agent then copies the schema and data for the new article to the Subscriber (it does not reinitialize the entire publication)."

In my case, I was also only using transactional replication. You never mentioned what kind of replication you were using, but you did mention the merge agent, so perhaps you are using merge replication?

In any event, you stated the whole snapshot was being sent, so I don't think there's anything we can do to stop that now. Hopefully it is completed and things are running smoothly again.

What I did in my case, was to set up a test using a second replication publication that was similar to my live publication, but with fewer tables and data, then tested adding a table to that publication and see what happened. Perhaps next time you can devise a similar test scenario and see what happens.