Backing up a Merge Replication Subscriber database

Environment:
Central SQL 2000 db (with SP3a), employing 3 Merge Publications with column
level tracking
240 distributed/disconnected subscribers using pull subscriptions running
MSDE 2000
We're having a high volume of suspect databases appearing. Causes are varied
but the majority are torn pages. None are due to running out of disk space.
Currently our fix is to drop the database & rebuild it using replication.
However this is pretty slow. So alternatively, if we took daily backups of
each subscriber db, would a simple restore make everything work again?
If we take a backup at 1am, the user syncs at 9am and then subsequently the
database becomes suspect & we need to restore the backup. Will the
subscriptions continue to work and will the data that was sync'd at 9am be
retransmitted down to the subscriber?
Does anyone foresee any other problems?
thanks,
Jonathan
I think you should address your torn pages problem. For instance it could be
a disk, controller, or a power purity problem.
To address your question. If you restore the backup to the subscriber, the
publisher will fill in the missing data since 9:00 am.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jonathan Ainsworth" <JonathanAinsworth@.discussions.microsoft.com> wrote in
message news:55C49FD8-2BBC-406E-9815-0EFAD17268B6@.microsoft.com...
> Environment:
> Central SQL 2000 db (with SP3a), employing 3 Merge Publications with
column
> level tracking
> 240 distributed/disconnected subscribers using pull subscriptions running
> MSDE 2000
> We're having a high volume of suspect databases appearing. Causes are
varied
> but the majority are torn pages. None are due to running out of disk
space.
> Currently our fix is to drop the database & rebuild it using replication.
> However this is pretty slow. So alternatively, if we took daily backups of
> each subscriber db, would a simple restore make everything work again?
> If we take a backup at 1am, the user syncs at 9am and then subsequently
the
> database becomes suspect & we need to restore the backup. Will the
> subscriptions continue to work and will the data that was sync'd at 9am be
> retransmitted down to the subscriber?
> Does anyone foresee any other problems?
> thanks,
> Jonathan
>
|||Thanks for your help Hilary. Fixing the disk problem is harder than you'd
think as these torn pages are happening on 6 different models of IBM
Thinkpads and it is only happening in Production world. In over 1.5 years
we've yet to get a single one in development or test environments.
"Hilary Cotter" wrote:

> I think you should address your torn pages problem. For instance it could be
> a disk, controller, or a power purity problem.
> To address your question. If you restore the backup to the subscriber, the
> publisher will fill in the missing data since 9:00 am.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Jonathan Ainsworth" <JonathanAinsworth@.discussions.microsoft.com> wrote in
> message news:55C49FD8-2BBC-406E-9815-0EFAD17268B6@.microsoft.com...
> column
> varied
> space.
> the
>
>

Avoid a subscriber

Hi,
Is it possible to avoid a subcriber from having a table replicated on it?
Thanks
Vivek
sp_dropsubscription will allow you to do this, but it can lead to confusion
if you have other subscribers who subscribe to the same publication and
article.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Availability while applying a snapshot

In merge replication, if I select to re-initialize the subscriber. Is the database available to the end users while the snapshot is being applied?
The publisher requires a table lock to create the snapshot, while on the
subscriber it depends on the sync type. By default you'll drop the table on
the subscriber and recreate it, which would preclude user availability.
Regards,
Paul Ibison
"ReplGuy" <anonymous@.discussions.microsoft.com> wrote in message
news:E90EEDC1-D397-41D9-B413-D2B1FD10B6E0@.microsoft.com...
> In merge replication, if I select to re-initialize the subscriber. Is the
database available to the end users while the snapshot is being applied?
|||Thanks Paul,
So if I want the users to keep on working, then I should ellect not to re-initialize the database? Is this correct?
|||It depends why you are reinitializing. If it is because the schema has
modified substantially, then it is mandatory and your subscriber users will
not be able to access the table. If there is an addition of a column, then
sp_repladdcolumn is needed rather than reinitialization. As for the
reinitialization, it is usually treated as an out-of-hours procedure because
of this restriction.
HTH,
Paul Ibison
|||well it is available but merge replication by default deletes the data in the existing table and then refreshes it. So for a moment or two (depending on how long it takes to bcp the data in) the table is empty.
While the truncation and refreshing is going on there is some locking.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html