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
>
Showing posts with label subscriber. Show all posts
Showing posts with label subscriber. Show all posts
Thursday, March 8, 2012
Thursday, February 16, 2012
Adding data from disconnected subscriber
Abdul,
what type of replication are you using?
Rgds,
Paul Ibison, SQL Server MVP, WWW.Replicationanswers.Com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thank you Paul, I'm using merge replication
"Paul Ibison" wrote:
> Abdul,
> what type of replication are you using?
> Rgds,
> Paul Ibison, SQL Server MVP, WWW.Replicationanswers.Com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Abdul,
in your first post you mention "Then I recreate the subscription but I find
that the record does not replicate on the other subscriber and publisher. ".
Merge replication is not normally continuous, rather the merge agent is run
on a schedule. You could have a schedule of once a minute and enter records
on the subscriber between synchronizations. To simulate a broken connection,
you could disconnect the network card, or set one of the databases to
read-only, then synchronize. Dropping the subscription as you have done is
something else entirely and is a simulation of a subscriber exceeding the
maximum retention period. In this case you could run sp_removedbreplication,
resubscribe as a noinit subscription and do a dummy update. However, I think
that this isn't what you really require.
Rgds,
Paul Ibison, SQL Server MVP, WWW.Replicationanswers.Com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
what type of replication are you using?
Rgds,
Paul Ibison, SQL Server MVP, WWW.Replicationanswers.Com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thank you Paul, I'm using merge replication
"Paul Ibison" wrote:
> Abdul,
> what type of replication are you using?
> Rgds,
> Paul Ibison, SQL Server MVP, WWW.Replicationanswers.Com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Abdul,
in your first post you mention "Then I recreate the subscription but I find
that the record does not replicate on the other subscriber and publisher. ".
Merge replication is not normally continuous, rather the merge agent is run
on a schedule. You could have a schedule of once a minute and enter records
on the subscriber between synchronizations. To simulate a broken connection,
you could disconnect the network card, or set one of the databases to
read-only, then synchronize. Dropping the subscription as you have done is
something else entirely and is a simulation of a subscriber exceeding the
maximum retention period. In this case you could run sp_removedbreplication,
resubscribe as a noinit subscription and do a dummy update. However, I think
that this isn't what you really require.
Rgds,
Paul Ibison, SQL Server MVP, WWW.Replicationanswers.Com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Sunday, February 12, 2012
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
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 article in merge replication
Hi,
our configuration is SQL 2000 (publisher and subscribers as well), merge
replication, pull subscription.
From time to time we add new subscriber.
I need to add new table or stored procedure into current replication
schema. What to do exactly if I want to avoid reinit with total snapshot
transfer for existing subscriptions and any new subscription should be
created with new articles already?
Is following sequence correct?
EXEC sp_addmergearticle
@.publication = @.our_publication,
@.article = N'newarticle',
... @.force_invalidate_snapshot = 1
EXEC sp_refreshsubscriptions
@.publication = @.our_publication
Thanks for an answer or suggestion.
Lenka
Adding a new merge article to the publication will result in an entire
snapshot being created but NOT a reinitialization. Just the new article will
be transferred. Once the article has been added to the publication, you'll
need to also run the snapshot agent to have it propagated.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
our configuration is SQL 2000 (publisher and subscribers as well), merge
replication, pull subscription.
From time to time we add new subscriber.
I need to add new table or stored procedure into current replication
schema. What to do exactly if I want to avoid reinit with total snapshot
transfer for existing subscriptions and any new subscription should be
created with new articles already?
Is following sequence correct?
EXEC sp_addmergearticle
@.publication = @.our_publication,
@.article = N'newarticle',
... @.force_invalidate_snapshot = 1
EXEC sp_refreshsubscriptions
@.publication = @.our_publication
Thanks for an answer or suggestion.
Lenka
Adding a new merge article to the publication will result in an entire
snapshot being created but NOT a reinitialization. Just the new article will
be transferred. Once the article has been added to the publication, you'll
need to also run the snapshot agent to have it propagated.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Thursday, February 9, 2012
Adding an additional Subscription to Merge Replication
I have two servers in a merge replication setup (linked every night using an ISDN line). I wish to add a new subscriber to this but I am not sure if I need to do a new snapshot.
I was thinking of using the latest backup from the publisher and recover this on the new subscriber - if I do this will i need
to run the snapshot again ? or will they all simply carry on with the merge...
Thanks
Gerry
you have to create a no-sync subcription which will run the snapshot only
for necessary merge replication metadata.
"Gerry" <anonymous@.discussions.microsoft.com> wrote in message
news:3490FE65-DF3F-401D-A6EE-78236E508677@.microsoft.com...
> I have two servers in a merge replication setup (linked every night using
an ISDN line). I wish to add a new subscriber to this but I am not sure if
I need to do a new snapshot.
> I was thinking of using the latest backup from the publisher and recover
this on the new subscriber - if I do this will i need
> to run the snapshot again ? or will they all simply carry on with the
merge...
> Thanks
> Gerry
|||How do I create/define a no-sync subscription ? Does it get defined when I run the
Push Subscription Wizard in the usual way?
Thanks
Gerry
|||You have to select an option that no data needs to be synchronized
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Gerry" <anonymous@.discussions.microsoft.com> wrote in message
news:0709ECB3-8396-4593-A423-93761B4137EE@.microsoft.com...
> How do I create/define a no-sync subscription ? Does it get defined when I
run the
> Push Subscription Wizard in the usual way?
> Thanks
> Gerry
>
I was thinking of using the latest backup from the publisher and recover this on the new subscriber - if I do this will i need
to run the snapshot again ? or will they all simply carry on with the merge...
Thanks
Gerry
you have to create a no-sync subcription which will run the snapshot only
for necessary merge replication metadata.
"Gerry" <anonymous@.discussions.microsoft.com> wrote in message
news:3490FE65-DF3F-401D-A6EE-78236E508677@.microsoft.com...
> I have two servers in a merge replication setup (linked every night using
an ISDN line). I wish to add a new subscriber to this but I am not sure if
I need to do a new snapshot.
> I was thinking of using the latest backup from the publisher and recover
this on the new subscriber - if I do this will i need
> to run the snapshot again ? or will they all simply carry on with the
merge...
> Thanks
> Gerry
|||How do I create/define a no-sync subscription ? Does it get defined when I run the
Push Subscription Wizard in the usual way?
Thanks
Gerry
|||You have to select an option that no data needs to be synchronized
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Gerry" <anonymous@.discussions.microsoft.com> wrote in message
news:0709ECB3-8396-4593-A423-93761B4137EE@.microsoft.com...
> How do I create/define a no-sync subscription ? Does it get defined when I
run the
> Push Subscription Wizard in the usual way?
> Thanks
> Gerry
>
Adding addition indexes on the subscriber tables
We have snapshot tables which are regenerated each night. Is there a way to
create additional indexes on the subscriber side since the vendor won't allow
us to create the indexes on the publisher?
Tom,
you could use a post-snapshot script for this.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||other than the excellent advice Paul has given you, you can use
sp_addscriptexec to create them on all subscribers deployed through a UNC.
Keep in mind that you want to minimize the indexes on your subscriber(s) to
improve performance of your replication solution.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uSx7mh6PFHA.3336@.TK2MSFTNGP09.phx.gbl...
> Tom,
> you could use a post-snapshot script for this.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Sp_addscriptexec works for transactional and merge, but not snapshot
Rgds,
Paul
|||stop making me look bad, Paul. I am perfectly capable of doing that on my
own.
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
create additional indexes on the subscriber side since the vendor won't allow
us to create the indexes on the publisher?
Tom,
you could use a post-snapshot script for this.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||other than the excellent advice Paul has given you, you can use
sp_addscriptexec to create them on all subscribers deployed through a UNC.
Keep in mind that you want to minimize the indexes on your subscriber(s) to
improve performance of your replication solution.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uSx7mh6PFHA.3336@.TK2MSFTNGP09.phx.gbl...
> Tom,
> you could use a post-snapshot script for this.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Sp_addscriptexec works for transactional and merge, but not snapshot
Rgds,
Paul
|||stop making me look bad, Paul. I am perfectly capable of doing that on my
own.
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
Subscribe to:
Posts (Atom)