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.

Reboot

I'm trying to install MSDE through a custom installation program. However, we all know that Microsofts Merge Modules are complete crap so the next best way of doing such a thing is to just call the "setup.exe" for MSDE itself.

My problem is that the MSDE setup prompts the user for a reboot. How can I supress this reboot so that my user will only have to reboot the machine after my program is installed?

I was told that the command /qb would work, but for whatever reason, it did not. Any ideas?

Any help would be greatly appreciated for this is the last "problem" with the current project.I've found this question in a few diffrent places but I could never find a clear cut answer. Hopefully the next person with this problem will find my post...

The answer is just send the command line "REBOOT=ReallySupress" when calling the setup.exe. I've been told this is case sensative.