Adding one more subscriber

Hi All
I have a transactional replication with one publisher/distributor and one
subscriber. I want to add one more subscriber for another location. What is
the best method to add one more subscriber? Expect expert's suggestions.
Regards,
Aris
Make sure the new subscriber is enabled (use Tools, Replication, Configure
Distributor, Publishers and Subscribers).
If it is indistinguishable from the other subscriber simple script out your
publication, note the sp_addsubscription statement at the bottom, copy this
and paste it into your publication database on your publisher. Edit this
statement for the new subscriber.
Run the script.
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
"Aris" <Aris@.discussions.microsoft.com> wrote in message
news:86D98B89-5F43-42F1-BF4B-BE9422F8827C@.microsoft.com...
> Hi All
> I have a transactional replication with one publisher/distributor and one
> subscriber. I want to add one more subscriber for another location. What
> is
> the best method to add one more subscriber? Expect expert's suggestions.
> Regards,
> Aris
>

Adding column to article on *susbcriber* side

Hi,
I've got a unidirectional transactional replication (on sql 2000) and
I want to add a column to a table on the subscriber's side, the column
allows nulls and is going to be updated on the subscriber.
I thought of creating the table, than adding the publication and when
I add this specific article, tell it to truncate the table if it
exists rather than to drop it.
Is there any flaw in this?
Thanks in advance,
R. Green
It should work. This is the normal way of carrying out what you are trying
to accomplish.
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
"Ronald Green" <zzzbla@.gmail.com> wrote in message
news:1172407317.380240.255030@.t69g2000cwt.googlegr oups.com...
> Hi,
> I've got a unidirectional transactional replication (on sql 2000) and
> I want to add a column to a table on the subscriber's side, the column
> allows nulls and is going to be updated on the subscriber.
> I thought of creating the table, than adding the publication and when
> I add this specific article, tell it to truncate the table if it
> exists rather than to drop it.
> Is there any flaw in this?
> Thanks in advance,
> R. Green
>
|||Thanks a lot!
On Feb 25, 3:01 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> It should work. This is the normal way of carrying out what you are trying
> to accomplish.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "Ronald Green" <zzz...@.gmail.com> wrote in message
> news:1172407317.380240.255030@.t69g2000cwt.googlegr oups.com...
>
>
>
>
> - Show quoted text -
|||Hi,
I ran a little test, and it failed on delivering the snapshot because
of that extra column on the subscriber side. So the moral of the story
is to add the column in a post snapshot script which is not always the
best idea (if you already have your schema deployed and something is
dependant on this column), OR you can change the SYNC view on the
publisher and add a blank column to it prior to running the snapshot
agent.
R. Green
On Feb 25, 3:01 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> It should work. This is the normal way of carrying out what you are trying
> to accomplish.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "Ronald Green" <zzz...@.gmail.com> wrote in message
> news:1172407317.380240.255030@.t69g2000cwt.googlegr oups.com...
>
>
>
>
> - Show quoted text -
|||Arghhh!!!!!!!!!!!! Somehow I was assuming you would be doing a no-sync.
Yes, this could be accomplished via a post snapshot script.
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
"Ronald Green" <zzzbla@.gmail.com> wrote in message
news:1172412186.310429.11430@.k78g2000cwa.googlegro ups.com...
> Hi,
> I ran a little test, and it failed on delivering the snapshot because
> of that extra column on the subscriber side. So the moral of the story
> is to add the column in a post snapshot script which is not always the
> best idea (if you already have your schema deployed and something is
> dependant on this column), OR you can change the SYNC view on the
> publisher and add a blank column to it prior to running the snapshot
> agent.
> R. Green
>
> On Feb 25, 3:01 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>

Adding Article/Table!

Hello All,
I'm using Transactional Replication and it's Push subscription.
Can you plz give me the complete steps to add a single table w/o starting
snapshot for all tables.
And another question,
BOL says sp_refreshsubscription for Pull and
sp_reinitsubscription is for Push Subscription.
But I saw newgroup an it says BOL is wrong sp_refreshsubscription works for
both.
What is the difference b/w sp_refreshsubscription and
sp_reinitsubscription?
Thanks in advance.
Naveed.
I just tried to do this:
sp_addarticle 'pubs','jobs','jobs'
I then generated my snapshot - and restarted my distribution agent. Only the
jobs table went over in the new snapshot.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Naveed" <nrehman@.marsonssoft.com> wrote in message
news:OqzXJ94qEHA.3900@.TK2MSFTNGP10.phx.gbl...
> Hello All,
> I'm using Transactional Replication and it's Push subscription.
> Can you plz give me the complete steps to add a single table w/o starting
> snapshot for all tables.
> And another question,
> BOL says sp_refreshsubscription for Pull and
> sp_reinitsubscription is for Push Subscription.
> But I saw newgroup an it says BOL is wrong sp_refreshsubscription works
for
> both.
> What is the difference b/w sp_refreshsubscription and
> sp_reinitsubscription?
> Thanks in advance.
> Naveed.
>
>

Adding an article with no-sync initialization

Hi,
I want to add a table/article that I have removed before some days because
of some problems.
Since I'm using transactional replication with "nosync" initialization I
don't know how can I add to the replication one article.
Thanks, BaniSQL
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
"BaniSQL" <BaniSQL@.discussions.microsoft.com> wrote in message
news:43C3C933-A02A-4CDE-BA94-5748AF588365@.microsoft.com...
> Hi,
> I want to add a table/article that I have removed before some days because
> of some problems.
> Since I'm using transactional replication with "nosync" initialization I
> don't know how can I add to the replication one article.
> Thanks, BaniSQL
>
|||What page number?
"Hilary Cotter" wrote:

>
> --
> 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
>
> "BaniSQL" <BaniSQL@.discussions.microsoft.com> wrote in message
> news:43C3C933-A02A-4CDE-BA94-5748AF588365@.microsoft.com...
>
>
|||Have a look at the sp_addsubscription stored procedure. You can pass a
single article name in the article parameter or use 'ALL' for all articles
in your publication.
"BaniSQL" <BaniSQL@.discussions.microsoft.com> wrote in message
news:4E2398B7-8664-472F-B264-30EB47043375@.microsoft.com...[vbcol=seagreen]
> What page number?
> "Hilary Cotter" wrote:

Adding a table to transactional repliaction

I am adding a table to an existing publication that has one pull subscription
using the steps listed below. When I run the snapshot agent, it does not
recognize the newly added article ("no subscriptions needed initialization").
I WANT TO RUN THE SNAPSHOT FOR JUST ONE TABLE. Do I need to drop and readd
the subscriber or subscription? If so, please let me know the steps.
ALSO, the steps below did work on a QA server but failed in production.
-- Thanks for your help.
exec sp_addarticle @.publication = N'my_PUBLICATION', @.article = N'my_TABLE',
@.source_owner = N'dbo', @.source_object = N'my_TABLE', @.destination_table =
N'my_TABLE', @.type = N'logbased', @.creation_script = null, @.description =
null, @.pre_creation_cmd = N'drop', @.schema_option = 0x00000000000000F3,
@.status = 16, @.vertical_partition = N'false', @.ins_cmd = N'CALL
sp_MSins_my_TABLE', @.del_cmd = N'CALL sp_MSdel_my_TABLE', @.upd_cmd = N'MCALL
sp_MSupd_my_TABLE', @.filter = null, @.sync_object = null, @.auto_identity_range
= N'false', @.force_invalidate_snapshot = 1
GO
select * from distribution..msArticles where article = 'my_TABLE'
go
EXEC sp_refreshsubscriptions
N'my_PUBLICATION'
go
exec sp_reinitsubscription
@.publication = 'my_PUBLICATION',
@.article = 'my_TABLE',
@.for_schema_change = 1,
@.subscriber = 'all'
GO
exec sp_addsubscription
@.publication = 'my_PUBLICATION',
@.article = 'my_TABLE',
@.subscriber = N'SUB_SERVER',
@.destination_db = N'pub_sub_database',
@.sync_type = N'automatic',
@.update_mode = N'read only'
I think you need to run sp_refreshpublications to get it to pick up the
changes.
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
"sbawa" <sbawa@.discussions.microsoft.com> wrote in message
news:F24C7DED-985E-4011-B4E7-F2C5E972F8EC@.microsoft.com...
>I am adding a table to an existing publication that has one pull
>subscription
> using the steps listed below. When I run the snapshot agent, it does not
> recognize the newly added article ("no subscriptions needed
> initialization").
>
> I WANT TO RUN THE SNAPSHOT FOR JUST ONE TABLE. Do I need to drop and
> readd
> the subscriber or subscription? If so, please let me know the steps.
> ALSO, the steps below did work on a QA server but failed in production.
> -- Thanks for your help.
>
> exec sp_addarticle @.publication = N'my_PUBLICATION', @.article =
> N'my_TABLE',
> @.source_owner = N'dbo', @.source_object = N'my_TABLE', @.destination_table =
> N'my_TABLE', @.type = N'logbased', @.creation_script = null, @.description =
> null, @.pre_creation_cmd = N'drop', @.schema_option = 0x00000000000000F3,
> @.status = 16, @.vertical_partition = N'false', @.ins_cmd = N'CALL
> sp_MSins_my_TABLE', @.del_cmd = N'CALL sp_MSdel_my_TABLE', @.upd_cmd =
> N'MCALL
> sp_MSupd_my_TABLE', @.filter = null, @.sync_object = null,
> @.auto_identity_range
> = N'false', @.force_invalidate_snapshot = 1
> GO
> select * from distribution..msArticles where article = 'my_TABLE'
> go
> EXEC sp_refreshsubscriptions
> N'my_PUBLICATION'
> go
> exec sp_reinitsubscription
> @.publication = 'my_PUBLICATION',
> @.article = 'my_TABLE',
> @.for_schema_change = 1,
> @.subscriber = 'all'
> GO
> exec sp_addsubscription
> @.publication = 'my_PUBLICATION',
> @.article = 'my_TABLE',
> @.subscriber = N'SUB_SERVER',
> @.destination_db = N'pub_sub_database',
> @.sync_type = N'automatic',
> @.update_mode = N'read only'
>
|||I tried using object search but unable to find this procedure in any of the
databases. Please help.
Your book on transactional replication is great. But you can probably see
that I haven't gotten too far into it yet.
"Hilary Cotter" wrote:

> I think you need to run sp_refreshpublications to get it to pick up the
> changes.
> --
> 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
> "sbawa" <sbawa@.discussions.microsoft.com> wrote in message
> news:F24C7DED-985E-4011-B4E7-F2C5E972F8EC@.microsoft.com...
>
>