Dynamic Merge Replication slows down with more subscribers

Dynamic Merge Replication slows down with more subscribers

Post by Martin Conli » Fri, 25 Feb 2000 04:00:00



We are publishing a database with approx 180 tables using dynamic
replication based on the
SUSER_SNAME() function.

As we add subscribers, the history shows that the more subscribers that are
added, the more
times the agent seems to cycle through the articles when downloading the
data to the subscriber.

Eventually, we have say 200 subscribers, and the agent runs through the
article list 5 or 6 times. This
means the time required to replicate grows from +- 20 minutes (for the first
subscribers) to +- 120 minutes
and longer for the later subscribers. This is totally impractical, as the
release of the software will have more
than 2000 subscribers, and through our calculations, the 2000th subscriber
will take approx 2 days to initialise
the database.

This is now a mission critical factor that must be resolved before we
rollout, so any help will be greatly
appreciated.

 
 
 

Dynamic Merge Replication slows down with more subscribers

Post by Tim Graha » Tue, 29 Feb 2000 04:00:00


Hi martin

sounds like you need to kill the dynamic filter idea and make separate
publications for each static filter required. More overhead for you to
manage them but it will work much quicker for clients trying to synch.

Tim.

 
 
 

Dynamic Merge Replication slows down with more subscribers

Post by Michael Hote » Tue, 29 Feb 2000 04:00:00


Can you give us an exact config of what you are doing?  Are you using
dynamic or static memory management?  Are you using static or dynamic
database sizing?  What conflict resolution are you using?  How are you
performing the synch?

--
Please post all replies to the newsgroup unless stated otherwise.
Mike
DBA - AdOutlet.com
SQL-Programmer, TestBase, and Database Scanner reviews:
http://www.mssqlserver.com/products
Purging merge metadata: http://www.mssqlserver.com/replication
SQL Server resources: http://www.mssqlserver.com
SQL Server FAQ: http://www.mssqlserver.com/faq

"Aim high. You can never achieve more than you aspire to." - Al Reis

 
 
 

Dynamic Merge Replication slows down with more subscribers

Post by Martin Conli » Thu, 02 Mar 2000 04:00:00


Hi Tim

Thanks for the reply. Unfortunately as we have more than 2000 users to setup, the resources required to set up Static publications would be impractical.

* Sent from Devdex.com http://www.devdex.com The Web Developers Index *
The world's largest index site for Microsoft web technologies.

 
 
 

Dynamic Merge Replication slows down with more subscribers

Post by Martin Conli » Thu, 02 Mar 2000 04:00:00


Thanks for the reply.

We have actually tried various combinations of configurations on the server. Currently we are running using dynamic memory management. The problems however were encountered using static memory management as well.

We are also using dynamic database sizing, allowing the database to grow by 10%.

Our conflict resolution is centrally maintained on the server, ie a DBA resolves all conflicts on a daily basis, using the Conflict viewer in the Enterprise manager. As we have spent quite a while planning the structure, we actually do not get many conflicts. To give an idea, we had approx 20 conflicts after approx 10 million database changes, so we do not believe that conflicts would be the problem.

The synchronisation is carried out through use of the ActiveX Merge replication object that ships with SQL.
Again though, we have run tests using the Enterprise Manager, and still have the same problems.

We have had consultants in who have done very detailed analysis on the server and database configuration. The result of this was that our configuration was pretty close to being optimal.

Once again thank you for the reply. I would really value any further comments or suggestions....

* Sent from Devdex.com http://www.devdex.com The Web Developers Index *
The world's largest index site for Microsoft web technologies.

 
 
 

1. Replication slowing down - dynamic merge

Hi All

We are running dynamic merge replication with SQL7 and SP1 (SP2 has given us
numerous errors), and our concern is that the replication process is slowing
down (and often failing) as new subscribers are added.

We currently have approx 150 subscribers (with a total requirement of 2000).
When the first subscribers were initialized, this process would take approx
15-20 minutes. Now however, we are looking at 60-90 minutes for the same
process (if it succeeds at all).

Does anybody know the actual process that SQL uses to store changes under
dynamic replication. Are the changes stored in the distribution database
until all subscribers have synchronized. We believe this is the case, as the
distribution database grows rapidly.
Are expired or failed subscribers then cleared out of the distribution
database (by the Cleanup tasks associated with replication).

If this is the case (that data is sent to the distribution database), we
have a real problem, as our users are very often in remote areas with no
comms, and synchronizing is only done on a weekly or monthly basis. As a
result the distribution database will grow ridiculously large (which does
seem to be the case).

The nearest we have to a solution is to recreate the publication on a
regular basis, and re-initialize all subscribers, as this will then clear
the distribution database. This is unfortunately not practical, due to the
remote locations of our subscribers. A total re-initialization would take us
about 6 months! And by the time it's completed, we would need to start all
over again.

We are very concerned as to the stability of this product. We are running
enterprise solutions, with mission-critical data, and really cannot afford
to have the kind of issues currently being experienced.

Any assistance in this regard will be greatly appreciated.

--
Regards

Martin Conlin

2. Remote Connectivity

3. URGENT: Transactional replication slows down production server subscribers

4. WE NEED ORACLE DBA'S!!!! $$$$ Toronto, Ontario, Canada

5. Trying to push a dynamic merge subscription using subscriber's computer name

6. Stupid Question

7. Merge Replication - subscriber not uploading

8. delete from

9. Merge replication deleting records on subscriber after sync

10. Merge replication, without replicate changes on the subscriber

11. Central subscriber and merge replication

12. Merge replication and old data on subscriber

13. Merge replication:The row could not be updated at subscriber