Reinitializing transactional subscribers

In SQL2000, SP3, We want to periodically refresh our read-only
subscribers with the latest schema and data (including FK's, indexes,
etc) whenever we role out a new software release. The database is
relatively small and we can currently disable publishing\remove
subscribers, and add them back in about 15 minutes. As the database
grows, I'm not sure if this would be the best method of synchronizing
the subscribers, but management likes the idea of a complete snapshot.
Is there a typical approach to updating subscribers with all
differences that makes more sense? The subscribers are primarily for
Disaster Recovery, but are also used to reduce the need for our
European customers to come to the US for reads. When I tried the
"start synchronizing" option in the EM gui, it has difficulty
dropping/replacing tables because it can't drop tables w FK's.
Also, can anyone explain the difference between reinitializing and
synchronizing?
thx much!
Gary,
if you have a need to drop FKs then it is possible to apply a prescript
before the snapshot is applied (botom of snapshot tab on publication
properties).
Some schema changes can be carried out using the 'plumbing' of replication,
eg sp_repladdcolumn and sp_repldropcolumn. Some other changes eg addition of
an index, can be done using sp_addscriptexec.
As for the difference between initialization and synchronization, right at
the start when the snapshot is sent to the subscriber there isn't really
one. Once the snapshot has been applied, sending down transactions in the
case of transactional replication is referred to as synchronization, and
reinitialization would refer to a new snapshot being created and propagated.
HTH,
Paul Ibison
|||Thanks Paul... But rather than running a pre-script, and individual
system sp's, do you know if this methodology - dropping/re-establishing
replication as new releases are rolled out is a common approach to
keeping things in-sync?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||I have seen it used, but sp_droparticle and sp_addarticle, along with
sp_repladdcolumn and sp_repldropcolumn are more common for small changes.
Regards,
Paul