Click here to monitor SSC
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-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 505
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
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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
SSC Eights!
SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)

Group: General Forum Members
Points: 855 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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1294 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
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4909 Visits: 7365
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
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1998 Visits: 846
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