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
>
>

Background replication

We are developing a embedded system based on Windows CE 5 and SQL Server CE 3 in C++ where we need to do merge replication in background while the front end application still need to perform operations on the database. A solution would be to run the merge replication on a separate thread while the application thread still can access the database to do inserts and updates. I have been researching if it is possible to access the database with queries during a merge replication, but I can not find an answere. Does anyone have experience of this and generally know if it is possible to implement with SQL Server CE 3 in the native API?

Best regards

Johan Johansson

This article may help you understand the implications of a user trying to access SQL Mobile while merge replication is happening.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5mobilesynch.asp

Darren

|||

Thanks Darren, this is exactly what I was looking for.

Best regards

Johan

AVOID merge joins

Hi,
Does anyone know of a way to AVOID merge joins?
I have a complex query that works fine on one system, but not on another.
After inspection, I found that the difference lies in different query plans generated by the query optimizer. On the failing system, a merge join is used, but since the total size of the columns is more than 8k, I get the error:
Server: Msg 1540, Level 16, State 1, Line 1
Cannot sort a row of size 8571, which is greater than the allowable maximum of 8094.
With query optimizer hints I can favour an optimization, but is there also a way to AVOID one?
(It should be a query optimizer hint on the complete SELECT, not on a specific table: the merge join is used on an intermediate step of the query, not on an identified table.)
Many thanksIn the mean time, I have found that I can use OPTION(ROBUST PLAN) in the select. Does anyone know the syntax when I would want to use it in a view?

Thanks,
Jan|||Basically it seems an issue that you have is not with type of join but with row size. When you use ROBUST PLAN option optimizer will try to use the max possible row size at the expense of actual query performance.
I would recommend to review query strategy to avoid of using huge row sizes.

But in a mean time you may try to use join type hint to prevent of using MERGE JOIN or use OPTION with particular JOIN type.

Regards.

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

aut-restart

Windows 2003 w/ SQL Server 2000 merge replication
Is there a way to have the synchrnoziation process restart after a set
amount of time when an error occurs - such as general network failure or
deadlock error?
Darin
*** Sent via Developersdex http://www.codecomments.com ***
I schedule the agent to run every 5 minutes. If this does not work for you,
you can have a 4 job step so if job step 3 fails job step 4 will run. It
will have a waitfor delay for 5 minutes and then loop back to job step 1.
RelevantNoise.com - dedicated to mining blogs for business intelligence.
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
"Darin" <darin_nospam@.nospamever> wrote in message
news:%23KckAxZHIHA.5360@.TK2MSFTNGP03.phx.gbl...
> Windows 2003 w/ SQL Server 2000 merge replication
> Is there a way to have the synchrnoziation process restart after a set
> amount of time when an error occurs - such as general network failure or
> deadlock error?
>
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***