Showing posts with label agent. Show all posts
Showing posts with label agent. Show all posts

Tuesday, March 27, 2012

Adding a new article to Transactional replication

Hi,
I added a new article to an already existing publication via EM,
and reintialized it. When the snapshot agent ran the change was
replicated, but the snapshot agent took the snapshot of all the
articles in the publication about 85 of them, instead of just
replicating the change.
IS there a way to reintialize an article in the publication, so
snapshot agent would only snap that one article instead of the whole
publication?
Does this also apply to adding a new column or dropping a column from a
publication?
Thanks for your help.
GG
for sql 2000 replicating to sql 2000 subscribers, only the article should be
replicated. I am not sure what happened in your case.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"GG" <gdabbara@.gmail.com> wrote in message
news:1117574970.901699.85980@.f14g2000cwb.googlegro ups.com...
> Hi,
> I added a new article to an already existing publication via EM,
> and reintialized it. When the snapshot agent ran the change was
> replicated, but the snapshot agent took the snapshot of all the
> articles in the publication about 85 of them, instead of just
> replicating the change.
> IS there a way to reintialize an article in the publication, so
> snapshot agent would only snap that one article instead of the whole
> publication?
> Does this also apply to adding a new column or dropping a column from a
> publication?
>
> Thanks for your help.
> GG
>
|||You mention that you reinitialized it - this is the reason why all articles
were created by the snapshot agent. After adding the article, you just need
to run the snapshot agent and then the distribution agent to get the single
article.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for your responses. I will try just starting the snapshot agent
instead of reintializing the whole publication. I was under the
impression that snapshot agent doesn't know about the change unless I
reintialize.
Thanks
GG
|||Adding an article is normally taken care of when running the snapshot. There
are some exceptions - if the initial publication was a nosync one, then the
snapshot of the new article won't generate anything by default. However
assuming this is niot the case you'll be fine.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Thursday, March 8, 2012

Add new table to transactional replication with push subscriber

Hi there,

With SQL Server 2005 after adding a new table to replication, the entire snapshot is redone when initializing the snapshot agent. Ive read that with the pull subsciption there is a proc you can run to only snap the new object. Is there any way (besides creating a new publication) to stop the whole snapshot being redone for a push subscription?

Thanks

It seems there is no this kind of proc to only add snap new object, no matter for pull or push subscription. New added schema in publication will cause subscription to reinitialized , unless you manually add the new added objects into subscriber side and choose @.sync_type = 'none' to skip the initial snapshot.

Hope it will help.

Thanks

Yunjing

|||

Thanks for reply.

but surely this cant be right. in SS2000 only new objcts added were initialized. we have a database with over 40 GB replicated data and cant redo the whole snap every time another article is added.

|||A new snapshot is generated such that any new subscriber that is added can reference an up to date snapshot. The entire snapshot is NOT sent to the subscribers. Only the new objects are sent to each subscriber. So, you only get an incremental initialize when you add new articles to the publication.

Add new table to sql server 2005 transaction replication

I am trying to add a new table to tansaction replication on sql server 2005.
The problem I am having is that if I restrt the snapshot agent, it is doing
all the tables in the publisher. Ho can I make the snapshot agent do only the
new table?
Steps I have taken:
1. Add new table to publisher and the publication.
2. Star the snapshot agent
This is all done using the SSMS Gui.
Thanks,
George Gopie
George,
can you check to see if you have enabled anonymous subscribers. If you have,
then this behaviour is normal and you could reset it (using
sp_changepublication) to create a snapshot of only the one table.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Use sp_addarticle for this. If it is a pull subscription you will need to do
a sp_refreshsubscriptions
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"georgeg" <ggg@.hotamil.com> wrote in message
news:5D774662-3B4D-4B58-9091-36F05F8CDB46@.microsoft.com...
>I am trying to add a new table to tansaction replication on sql server
>2005.
> The problem I am having is that if I restrt the snapshot agent, it is
> doing
> all the tables in the publisher. Ho can I make the snapshot agent do only
> the
> new table?
> Steps I have taken:
> 1. Add new table to publisher and the publication.
> 2. Star the snapshot agent
> This is all done using the SSMS Gui.
> Thanks,
> --
> George Gopie
>
|||Hilary,
I have used the sp_addarticle, and sp_refreshSubscriptions succesfully.
sp_addarticle
@.publication = RYS_SPRS77_PUBL,
@.article = InstrumentCheckResult,
@.source_object = InstrumentCheckResult
sp_refreshsubscriptions
@.publication = RYS_SPRS77_PUBL
However, when I checked the ReplicationMonitor
I see message: The initial snapshot for article InstrumrntCheckResult is not
yet available
Do I still need to run the snapshot agent again?
Thanks,
George Gopie
"Hilary Cotter" wrote:

> Use sp_addarticle for this. If it is a pull subscription you will need to do
> a sp_refreshsubscriptions
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "georgeg" <ggg@.hotamil.com> wrote in message
> news:5D774662-3B4D-4B58-9091-36F05F8CDB46@.microsoft.com...
>
>
|||Yes, it should only generate a snapshot for the new article.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"georgeg" <ggg@.hotamil.com> wrote in message
news:D26B8402-F678-43AA-9144-98C9A5172991@.microsoft.com...[vbcol=seagreen]
> Hilary,
> I have used the sp_addarticle, and sp_refreshSubscriptions succesfully.
> sp_addarticle
> @.publication = RYS_SPRS77_PUBL,
> @.article = InstrumentCheckResult,
> @.source_object = InstrumentCheckResult
> sp_refreshsubscriptions
> @.publication = RYS_SPRS77_PUBL
> However, when I checked the ReplicationMonitor
> I see message: The initial snapshot for article InstrumrntCheckResult is
> not
> yet available
> Do I still need to run the snapshot agent again?
> Thanks,
> --
> George Gopie
>
> "Hilary Cotter" wrote: