Add additional Subscribers

Hi all,
Have Transactional Replication on SQL 2000 EE SP4 for some time now as
follows:
Publisher/Distributor is an 8 way Server
8GB RAM, RAID 1 (ldf), RAID 10 (mdf), RAID 0 (snapshot folder)
Dist Agents run at Distributor as PUSH to existing Subscribers as follows
Subscriber1 - ServerA
Subscriber2 - ServerB
Subscriber3 - ServerC
This works great, and I now want to ADD:
Subscriber4 - ServerD
Subscriber5 - ServerE
What I want to do is add Server D and E as Subscribers (these are all
separate physical servers) without having to re-initialize any data on
Servers A, B, or C which have been running just fine.
I can prevent access to the PUBLISHER database (~20GB) and quiesce the
Publisher and Subscribers 1/2/3 on Servers A, B, C for several hours if need
be.
What is the best way overall to approach this ?
When the Replication was first setup, I set the Snapshot Agent to use 8 BCP
Threads and it took about 30 mins to generate the Initial Snapshot, and
about 1.5 hours to apply to each Subscriber A, B, C across the network
(these servers are all well connected with dedicated nics on a private LAN
solely for Server to Server Replication traffic).
If I go through the Publication Properties dialog and ADD my two new
Subscribers 4/5 (Servers D/E) using the Wizards...my questions are as
follows:
1 - Will the addition of the two NEW Subscription 4/5 for Servers D/E cause
the Snapshot Agent to actually create a fresh snapshot ? I assume it will
"invalidate", but there is no existing snapshot currently available, and the
SS Agent has only been run manually on occasion since the intitial setup to
publish new articles to the subscribers which has worked fine. Otherwise the
SS Agent remains disabled.
2 - Will the Snapshot attempt to be applied to, or mess up anything for the
existing (already in-synch) Subscriptions on Servers A, B, C or should it
*only* apply itself to the NEW Subscribers 4/5 on Servers D/E ?
I hope the above makes sense and ariticulates what I'm trying to do. Thanks
in advance for any advice, or hints/tips you can provide.
Thanks,
Frank
Just as a follow up question, if I have the Publisher DB and Subscriber DB's
all in synch, and not accessible to users can I:
1 - Take a FULL Backup of one of the Subscription DB on any of the existing
Subscriber Servers (A/B or C), then restore it to Servers D/E
2 - Use the Publication Wizard to PUSH a new Subscription to Servers D/E
using the No Synch option ?
Does that do all the necessary behind the scenes processing correctly ? I
assume because I've restored a backup of an existing Subscriber DB to the
NEW Subscriber DB that it will have all the INS, UPD, DEL StoredProcs in
place as well ?
I read you can do a backup/restore of the Publication DB on a Subscriber for
a no-synch initialization, but the Subscription DB's have auto created
ClusteredIndexes for the PriKeys that exist on the Publication so would that
be an issue ?
In addition, there are some different non-clustered indexes on the
Subscribers as they are used for read-only queries, and mostly reporting
purposes that are offloaded from running on the Publisher DB.
Thanks,
Frank
"Frank Conte" <Frank.Conte@.discussions.ms.com> wrote in message
news:%23GPqOqpwHHA.4640@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> Have Transactional Replication on SQL 2000 EE SP4 for some time now as
> follows:
> Publisher/Distributor is an 8 way Server
> 8GB RAM, RAID 1 (ldf), RAID 10 (mdf), RAID 0 (snapshot folder)
> Dist Agents run at Distributor as PUSH to existing Subscribers as follows
> Subscriber1 - ServerA
> Subscriber2 - ServerB
> Subscriber3 - ServerC
> This works great, and I now want to ADD:
> Subscriber4 - ServerD
> Subscriber5 - ServerE
> What I want to do is add Server D and E as Subscribers (these are all
> separate physical servers) without having to re-initialize any data on
> Servers A, B, or C which have been running just fine.
> I can prevent access to the PUBLISHER database (~20GB) and quiesce the
> Publisher and Subscribers 1/2/3 on Servers A, B, C for several hours if
> need be.
> What is the best way overall to approach this ?
> When the Replication was first setup, I set the Snapshot Agent to use 8
> BCP Threads and it took about 30 mins to generate the Initial Snapshot,
> and about 1.5 hours to apply to each Subscriber A, B, C across the network
> (these servers are all well connected with dedicated nics on a private LAN
> solely for Server to Server Replication traffic).
> If I go through the Publication Properties dialog and ADD my two new
> Subscribers 4/5 (Servers D/E) using the Wizards...my questions are as
> follows:
> 1 - Will the addition of the two NEW Subscription 4/5 for Servers D/E
> cause the Snapshot Agent to actually create a fresh snapshot ? I assume it
> will "invalidate", but there is no existing snapshot currently available,
> and the SS Agent has only been run manually on occasion since the intitial
> setup to publish new articles to the subscribers which has worked fine.
> Otherwise the SS Agent remains disabled.
> 2 - Will the Snapshot attempt to be applied to, or mess up anything for
> the existing (already in-synch) Subscriptions on Servers A, B, C or should
> it *only* apply itself to the NEW Subscribers 4/5 on Servers D/E ?
> I hope the above makes sense and ariticulates what I'm trying to do.
> Thanks in advance for any advice, or hints/tips you can provide.
> Thanks,
> Frank
>
|||(1) for an initialisation you'll need to create a new snapshot.
(2) initialization of the 2 new subscriptions won't affect existing
subscriptions.
HTH,
Paul Ibison
|||You can use a backup of the subscriber - it is much less work than taking the
publisher backup. You don't have to be concerned about identity columns,
deleteing data from filtred articles, removing redundant tables and so on.
There are more details which might be relevant here:
http://www.replicationanswers.com/NoSyncInitializations.asp
HTH,
Paul Ibison
|||Hi Paul,
Thanks for the quick reply, and helpful info.
So, according to section 2.1 "Adding Additional Articles" in the link you
provided; if I do this by way of backup an existing Subscriber, restore it
to a new Subscriber(s) than do a no-sych Subscription through the wizard I
LOSE the ability to run the snapshot agent thereafter where it would
ordinarily:
1 - Generate schema, index and data files for a NEWLY added article on the
publisher. For example, I add a new table on the Publisher, then go to the
Publication Properties and check off the new table to be added in the
Article tab. Normally I would run the Snapshot Agent manually and it
generated the files for just that newly added article to the Publication.
2 - Have the Distribution Agent pickup the new articles and push them to my
new Subscribers, like it would for my existing ones that were originally
initialized via Snapshot (sync_type=automatic).
If that is the case, I think I should take the other approach as per my
other POST ?
That is...add the two new Subscribers, generate a fresh snapshot, and let
it apply it to the two new Subscribers all while the Publication DB, and
existing Subscription DB's are quiesced and offline (as in no changes
allowed) ?
Hassan's workaround you noted doesn't apply if adding articles via the
Publication Properties, correct ? It would always require using
sp_addsubscription going forward, and as you mentioned make for a less
maintainable setup than I would like.
Thanks,
Frank
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:5C31C7A4-96EE-4DC5-963E-0D8751CA04B3@.microsoft.com...
> You can use a backup of the subscriber - it is much less work than taking
> the
> publisher backup. You don't have to be concerned about identity columns,
> deleteing data from filtred articles, removing redundant tables and so on.
> There are more details which might be relevant here:
> http://www.replicationanswers.com/NoSyncInitializations.asp
> HTH,
> Paul Ibison
>
|||Hi Paul,
I think this is the better approach for me (see my other reply to yours) in
this thread.
So provided I do not delete and re-add my existing Subscribers in the
Publication Properties they will remain completely uneffected by the new
snapshot files that I do want applied to my new Subscribers.
That sounds like the way to go to keep the maintainable setup going forward.
Is there any other known workaround similiar to Hassan's that doesn't
require using scripting after using the backup/restore method instead of the
initialize with snapshot files ?
Many thanks for you insight, and on going assistance you provide in these
NG's and via your web site.
Regards,
Frank
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:971720D2-2205-43B4-96B9-91FC56D37051@.microsoft.com...
> (1) for an initialisation you'll need to create a new snapshot.
> (2) initialization of the 2 new subscriptions won't affect existing
> subscriptions.
> HTH,
> Paul Ibison
|||Hi Frank - this is correct. For addition of new 'automatic' articles, you'd
need to do this in scripts. Therefore adding new subscriptions normally will
be more suitable. Actually I do the nosync method primarily when the
bandwidth is small and the snapshot is huge - in which case I zip it up
before FTPing over - quite different to your case as far as I can determine.
Cheers,
Paul Ibison
|||What I've seen some people do is to add the new articles to a new publication
in order for the process to be automatic (after having already done a nosync
initialization). This is fine for a one-off provided the articles aren't
related.
HTH,
Paul Ibison