Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How can i add the article in existing publication Expand / Collapse
Author
Message
Posted Tuesday, January 29, 2008 5:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 20, 2014 5:51 AM
Points: 91, Visits: 399
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?
Post #448781
Posted Monday, February 4, 2008 10:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, November 9, 2008 10:45 PM
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.
Post #451445
Posted Monday, February 4, 2008 11:30 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 3:13 AM
Points: 853, 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

Post #451457
Posted Wednesday, February 6, 2008 10:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 10, 2014 8:13 AM
Points: 1,278, Visits: 2,204
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
Post #452366
Posted Wednesday, September 22, 2010 10:52 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, November 7, 2014 9:34 AM
Points: 3,989, Visits: 7,171
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"
Post #991373
Posted Thursday, September 23, 2010 10:38 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 10:17 AM
Points: 1,420, Visits: 736
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.
Post #992182
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse