Rebuilding the system merge repl indexes

Hi,

We have a client that has a large (5Gb) database replicated to 13 subscribers, the publisher is Sql 2005, the subscribers are Sql Express. The publication has as few filtered articles too. I have found that after several months of continuous running Replication Monitor is taking a long time to report history on each subscriber.

Do people tend to rebuild the indexes on the system merge replication tables on production servers, or should the standard replication jobs take care of this?

Thanks for your help

Graham

1. What is the retention period?

2. Was the cleanup job run?

|||

The retention period is 14 days for all subscribers.

The Agent history clean uo: Distributor job (publisher and distributor are on the same box), was last run yesterday successfully.

The Distribution Clean up: Distributor job has never been run and it not enabled.

The Replication monitoring refresher for Distributor job has never been run and is not enabled.

|||

1. Did the replication Monitor running all the time (on the subscriber)?

2. If you stop and re-start to launch it, does it still take a long time to refresh "sync status"?

3. if the repl monitor still takes a long time to refresh the sync status, can you turn on the profiler to see which RPC call takes unexpected long execution time? (I suspect the SQL Agent job history continuous to grow)

This posting is provided "AS IS" with no warranties, and confers no rights

|||

Replication does not do any index rebuilding or any defragmentation. It may be a good idea to create a job that does this for you during off-peak hours.

You may also want to monitor what's going on in the background while you're refreshing, replmonitor does make use of temp tables as well and it may have some contention with existing replication metadata tables that we're trying to improve for the next release of Katmai.

|||

Things took at turn for the worse yesterday. I had to reinitialize one of the subscribers because for some reason the publisher had decided to delete its merge meta data (the thing that normally happens when the subscriber does not sync for more than the retention period) dispite it sync'ing the day before. When I did the reinit, it then told me the snapshot was obsolete and I had to re-generate it. When I re-generated the snapshot it failed with a timeout after being stuck at 48% for 30minutes. Then all my other subscribers failed because of the same reason - the snapshot was obsolete.

In the end I tore down replication and re-created the publication and added all the subscribers again (this took all day too).

Now replication monitor is a lot more responsive.

Thanks for everyones help.

Rebuilding a merge publication & adding a subscriber

Assume the following scenario: I have an existing publication using merge
replication with ServerA as the distributor and publisher and ServerB as a
push subscriber. I need to add ServerC as a push subscriber. In addition, the
publication has been in service for several years, and I understand that it
is desirable to start from scratch by deleting and rebuilding it. My plan is
to create an entirely new publication involving a few minor schema changes
from the existing one, again with ServerA as the distributor and publisher
and with ServerC as the sole subscriber. Once this setup proves functional, I
then plan to delete the original publication and add ServerB as a subscriber
to the new one. Here are my questions.
1. First of all, is this a workable scenario? Are there practical gotchas
that I will run into? Is there any problem with having two publications –
running side-by-side for just a few days – that are nearly identical?
2. Is there any concern that during this interim period of a few days – with
ServerA hosting both publications – the processing load on ServerA would be
excessive?
3. Can the existing distribution database serve for both publications, or is
it necessary or desirable to create a new distribution database for the new
publication?
4. All the publication articles already exist on ServerB and ServerC, but I
want to remove them from these subscribers and push them back out from
ServerA when the new subscription is established, so that a number of minor
schema changes can be included. What is the best way to remove the articles
from the subscriber servers?
Sorry for the monster posting, but I thought it best to get all this out
there at once. TIA...
You cannot publish the same table more than once. But if I understand
correctly, You are going to create a new set of articles that have
slight changes so that should not be a problem. The distributor can
handle multiple publications without any problems and many publications
can run on the same server, so long as the same tables are not
published more than once. The load would mostly be IO and network
traffic but without knowing the kind of data and server details it's
hard to say how it will impact your server. If you have all the data at
the publisher you can drop the subscriptions at the subscribers, then
drop the tables. Script the tables form the publisher and create them
on the subscriber. The merge snapshot will sync the tables and you
should be good.

Rebuild replicated indexes

I have a number of databases that are being transactionally replicated from SQL 2000 Enterprise edition publisher to SQL 2005 Enterprise edition subscriber. I have included indexes in the replication. The subscriber database is then accessed and the data de-normalised and aggregated for reporting purposes.

My question is this: I want to periodically re-build the indexes on the publisher and subscriber via an automated task. If I rebuild the indexes on the publisher, will that automatically replicate to the subscriber? Will there be a problem with the "snapshot being out of date", and therefore replication stopping? I run a new snapshot once a day in the small hours of the morning. If there is likely to be a problem with the rebuild throwing the replication out, would it be wise to have the rebuild job running just before the new snapshot is taken?

Any help/advice appreciated.

No, the index rebuild isn't replicated. You would have to rebuild the indexes on the publisher and subscriber separately.|||

Or there is another option is to use @.pre/post_snapshot_script. You can use subscriber index rebuilding actions into a script and set the script as @.pre/post_snapshot_script in publication properties page -->Snapshot or through sql (sp_changepublication/sp_changemergepublication).

For example, as for post snapshot script, Distribution agent/merge agent will run the pre-snapshot script before any of the replicated object scripts when applying the snapshot at a Subscriber. The script is executed in the security context used by the Merge Agent when connecting to the subscription database. Pre-snapshot scripts are not run on SQL Server Mobile Subscribers.

Refer to: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/28a629a1-7374-4614-9b04-279d290a942a.htm

Therefore, once you rebuild index in publisher side, reinitialize sub, sub side index will be rebult through pre/post snapshot script as well.

Hope the above information will help.

-Yunjing