SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How can i add the article in existing publication


How can i add the article in existing publication

Author
Message
sram24_mca
sram24_mca
SSC Eights!
SSC Eights! (876 reputation)SSC Eights! (876 reputation)SSC Eights! (876 reputation)SSC Eights! (876 reputation)SSC Eights! (876 reputation)SSC Eights! (876 reputation)SSC Eights! (876 reputation)SSC Eights! (876 reputation)

Group: General Forum Members
Points: 876 Visits: 526
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?
floyd99
floyd99
SSC-Enthusiastic
SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)

Group: General Forum Members
Points: 179 Visits: 89
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
Balaji L
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1685 Visits: 271
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
Vivien Xing
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5626 Visits: 2204
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
MyDoggieJessie
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18883 Visits: 7448
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; They'll drag you down to their level and beat you with experience" ;-)
shaun.stuart
shaun.stuart
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3679 Visits: 895
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search