Additional articles added ..

I was going through this link and had a question with regards to caveats
when new articles are added
The link is http://www.replicationanswers.com/No...alizations.asp
So Say i have nosync initially setup and everything is going on fine. I now
want to add new articles to the publication.
Can i not just do an sp_addarticle followed by a sp_addsubscription with
@.sync_type='automatic' and then run the snapshot agent.
Will that not apply the snapshot for just these new articles ?
Using Transactional Replication
Thanks
Yes, you're quite right. Section 2.1 is referring to the gui, but it is
possible to mix noinit and automatic in the same publication by scripts.
I've put this in as an interesting aside (attributed to yourself ),
although I maintain that this could lead to unmaintainable publications.
Rgds,
Paul Ibison
sql

Adding articles to existing subscription(s)

If I add an article to an existing publication that already has push
subscriptions, how can I get the new article to the subscriber databases
without running a full snapshot or reinitializing the subscription etc.?
I am running SQL Server 2K sp3 on Windows 2K/2K3.
Thank you.
For transactional replication, you just need to run the snapshot agent and
then synchronize (running the snapshot agent will only create a snapshot of
the new article).
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thank you for your answer. I have tried running the snapshot agent and I
get the following message:
A snapshot was not generated because no subscriptions needed initialization.
Maybe the question I should be asking is how to get the existing
subscriptions to realize that there is a new article and it should be part of
the subscription. The subscriptions report that not all articles are part of
the subscription after I add the new article.
Thanks.
"Paul Ibison" wrote:

> For transactional replication, you just need to run the snapshot agent and
> then synchronize (running the snapshot agent will only create a snapshot of
> the new article).
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||I have found the solution, you need to run sp_refreshsubscriptions in the
publisher database and the snapshot will only move the new article that was
added not the the entire publication.
Thanks for you assistance.
"Len Gustafson" wrote:

> If I add an article to an existing publication that already has push
> subscriptions, how can I get the new article to the subscriber databases
> without running a full snapshot or reinitializing the subscription etc.?
> I am running SQL Server 2K sp3 on Windows 2K/2K3.
> Thank you.
|||Len,
this type of script should be what you need:
exec sp_addarticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.source_table = 'tEmployees'
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
Rgds,
Paul Ibison

adding articles

I am using trasactional push replication on 2005.
So, first I do this:
EXEC sp_addarticle
@.publication = N'azDSS', -- Change value with the name of the
publication we wish to add to
@.article = N'tbPhxSrvrpf', -- Change value with the name of the
article we are adding
@.source_owner = N'ICOMS', -- Change value with the correct schema
name
@.source_object = N'tbPhxSrvrpf', -- Change value to the name of the
table
@.destination_table = N'tbPhxSrvrpf', -- Change value to the name of
the table
@.type = N'logbased',
@.creation_script = N'',
@.description = null,
@.pre_creation_cmd = N'truncate',
@.schema_option = 0x000000000807509F,
@.status = 16,
@.vertical_partition = N'false',
@.ins_cmd = N'CALL [sp_MSins_tbPhxSrvrpf]',-- Update the section
between the {} (and removed the {})
@.del_cmd = N'CALL [sp_MSdel_tbPhxSrvrpf]', -- Update the section
between the {} (and removed the {})
@.upd_cmd = N'SCALL [sp_MSupd_tbPhxSrvrpf]', -- Update the section
between the {} (and removed the {})
@.filter = null,
@.sync_object = null
GO
Then I do this:
exec sp_addsubscription
@.publication = N'azDSS', -- Change value with the name of the
publication
@.article = N'tbPhxSrvrpf', -- Change value with the name of the
article
@.subscriber = N'CARZ0DB13\ARZSQL13', -- Change value with the name
of the subscribing server
@.destination_db = N'azDSS', -- Change value with the name of the
subscribing db
@.sync_type = N'automatic',
@.update_mode = N'read only'
GO
and what I get is:
Msg 14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line
533
Specify all articles when subscribing to a publication using concurrent
snapshot processing.
Is there a work around for this. I need to be able to add an article
to an existing publication without snapshoting the entire thing
Saw this on Vyas's blog some time ago in SQL Server 2000
(http://vyaskn.tripod.com/sqlblog/).
As far as i know, you'll have to use a workaround eg have a different
publication publish the table.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul Ibison wrote:
> Saw this on Vyas's blog some time ago in SQL Server 2000
> (http://vyaskn.tripod.com/sqlblog/).
> As far as i know, you'll have to use a workaround eg have a different
> publication publish the table.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Correct. I don't however see a work around posted here. I'm hoping
that someone might be able to clue me in to what I can do. I would
hate to think that MS would not have a way of adding the article to a
publication without having to do a total re-init.
-ms
|||Hi Michael, I posted an unofficial workaround in the following posting:
[url]http://groups.google.com/group/microsoft.public.sqlserver.replication/browse_frm/thread/ad9ad3d18f501332/447e9417f655bb1c?lnk=gst&q=Raymond+Mak&rnum=28#447 e9417f655bb1c[/url]
Other more official workarounds including changing the sync_method from
'concurrent' to either 'database snapshot' (enterprise edition only) and
'native' (which locks table during snapshot generation). Change the
sync_method will force a reinitialization of all your subscriptions at this
point.
-Raymond
"michael.swinarski@.cox.com" <mswinarski@.gmail.com> wrote in message
news:1162309836.937305.115900@.k70g2000cwa.googlegr oups.com...
> Paul Ibison wrote:
>
> Correct. I don't however see a work around posted here. I'm hoping
> that someone might be able to clue me in to what I can do. I would
> hate to think that MS would not have a way of adding the article to a
> publication without having to do a total re-init.
> -ms
>
|||This publication is over 200 GB. I would rather not have to
re-snapshot the entire publication just to add a table (wich we will be
doing more offtien then most). Can this be done?
Raymond Mak [MSFT] wrote:[vbcol=seagreen]
> Hi Michael, I posted an unofficial workaround in the following posting:
> [url]http://groups.google.com/group/microsoft.public.sqlserver.replication/browse_frm/thread/ad9ad3d18f501332/447e9417f655bb1c?lnk=gst&q=Raymond+Mak&rnum=28#447 e9417f655bb1c[/url]
> Other more official workarounds including changing the sync_method from
> 'concurrent' to either 'database snapshot' (enterprise edition only) and
> 'native' (which locks table during snapshot generation). Change the
> sync_method will force a reinitialization of all your subscriptions at this
> point.
> -Raymond
> "michael.swinarski@.cox.com" <mswinarski@.gmail.com> wrote in message
> news:1162309836.937305.115900@.k70g2000cwa.googlegr oups.com...
|||I am guessing that you don't want the snapshot agent to regenerate snapshot
data for all articles in your publication. If this is the case, please make
sure that the immediate_sync property in syspublications is set to 0 (see
sp_changepublication).
-Raymond
"michael.swinarski@.cox.com" <mswinarski@.gmail.com> wrote in message
news:1162316002.881015.11340@.m7g2000cwm.googlegrou ps.com...
> This publication is over 200 GB. I would rather not have to
> re-snapshot the entire publication just to add a table (wich we will be
> doing more offtien then most). Can this be done?
>
>
> Raymond Mak [MSFT] wrote:
>
|||Can you then generate a snapshot for the individual article? When the
article is added, how does the subscriber recieve it for the first
time?
-ms
Raymond Mak [MSFT] wrote:[vbcol=seagreen]
> I am guessing that you don't want the snapshot agent to regenerate snapshot
> data for all articles in your publication. If this is the case, please make
> sure that the immediate_sync property in syspublications is set to 0 (see
> sp_changepublication).
> -Raymond
> "michael.swinarski@.cox.com" <mswinarski@.gmail.com> wrote in message
> news:1162316002.881015.11340@.m7g2000cwm.googlegrou ps.com...
|||By setting the immediate_sync property to 0, the snapshot agent should only
generate files for articles with uninitialized subscriptions.
-Raymond
"michael.swinarski@.cox.com" <mswinarski@.gmail.com> wrote in message
news:1162327020.094451.305790@.k70g2000cwa.googlegr oups.com...
> Can you then generate a snapshot for the individual article? When the
> article is added, how does the subscriber recieve it for the first
> time?
> -ms
>
>
> Raymond Mak [MSFT] wrote:
>

Adding an Article to a Publication

Hi:
I have a publication with larger sql table articles. I want to add another
article (small table). To start replicating, I will need to reinitialize and
rerun the snaphot. I don't want to run the snapshot during the day time
because it may block other users because publication has some large tables in
it.
Is there anyway that I can replicate just that table to subsriber during the
day time?
Thanks a lot
Hi Mike,
You shouldn't need to reinitialize your existing subscription in order to
get snapshot data for the new article propagated to the subscriber. And if
you have a transactional publication with the immediate_sync property set to
false, the snapshot agent will only generate bcp data for the new article.
-Raymond
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:43347180-986E-474C-8C45-EB83BEB62DE5@.microsoft.com...
> Hi:
> I have a publication with larger sql table articles. I want to add
> another
> article (small table). To start replicating, I will need to reinitialize
> and
> rerun the snaphot. I don't want to run the snapshot during the day time
> because it may block other users because publication has some large tables
> in
> it.
> Is there anyway that I can replicate just that table to subsriber during
> the
> day time?
> Thanks a lot
>
|||Thanks Raymond. Where will I see immediate_sync property.
"Raymond Mak [MSFT]" wrote:

> Hi Mike,
> You shouldn't need to reinitialize your existing subscription in order to
> get snapshot data for the new article propagated to the subscriber. And if
> you have a transactional publication with the immediate_sync property set to
> false, the snapshot agent will only generate bcp data for the new article.
> -Raymond
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:43347180-986E-474C-8C45-EB83BEB62DE5@.microsoft.com...
>
>
|||Hi Mike,
You can select the value of the immediate_sync property directly from
syspublications at the publisher database.
-Raymond
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:E58CEE1E-B55D-4DB3-8E4F-1AB5387C36F2@.microsoft.com...[vbcol=seagreen]
> Thanks Raymond. Where will I see immediate_sync property.
> "Raymond Mak [MSFT]" wrote: