Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Replication - Performance Problem Expand / Collapse
Author
Message
Posted Wednesday, August 25, 2010 3:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 7, 2014 3:23 AM
Points: 4, Visits: 73
Hi,

we are having a performance problem and we are trying to tune the database.

Decisions were made long ago and here is what I found when I got here:

Current system consists of a SQL Server 2005 Enterprise sp3 Cluster installation with logical disks residing in SANS and 1000 remote users with SQL Server 2005 Express.

It has 4 merge publications and 1000 subscribers to each of them. We usually have between 10 and 40 concurrent users, (the ones with the sql server express), replicating between 08:00 and 19:00 Monday to Friday, as they usually work remotely and may be replicating from 500 kms away or even more.

Steps we have taken include:
- Checking disks --> they seem to have problems coping with the workload but they perform quite well otherwise.
- Checking indexes on server side--> they were causing problems but they are fixed now.
- Improved performance of tempb by icreasing the number of files.
- Reduced the time for the subscription to expire.
- ...

Any suggestion would be welcome

Thanks,
Gerardo
Post #974706
Posted Wednesday, August 25, 2010 1:11 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, April 13, 2014 11:32 AM
Points: 939, Visits: 778
What sort of performance issues are you having exactly?

Is it the actual process of the merging that's running slowly? Are you using push or pull subscribers? Is the Distribution db on the publisher server or seperate?
Post #975115
Posted Thursday, August 26, 2010 2:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 7, 2014 3:23 AM
Points: 4, Visits: 73
Hi

we have the distribution database on the same server, and we have performance problems when the merge is done, even when there are no changes, more especifically when having more than 20 concurrent replicating subscribers and whenever we do an initial replica.

In fact our main concern now is how to change anything as doing changes to the tables, (published articles), has become impossible as it would require to much time.

(BTW Subscribers are using pull subscriptions and we are using filters).


Thank you
Post #975450
Posted Thursday, August 26, 2010 3:45 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, April 13, 2014 11:32 AM
Points: 939, Visits: 778
How are your subscribers connected - over the internet?

How big is your filtered database?
Post #975488
Posted Thursday, August 26, 2010 4:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 7, 2014 3:23 AM
Points: 4, Visits: 73
They connect over the internet, using a VPN and the quality of connection varies, some have cable, some use 3G.

One of the tables has about 17M records, but the filtering reduces it to a max of 5000

The other tables have only about 50m records maximum.

Some data is shared, but only about 10%

Thank you
Post #975501
Posted Thursday, August 26, 2010 4:11 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:48 AM
Points: 1,552, Visits: 1,754
Not sure if merge allows it, but in transactional replication you can test the latency of replication with a "trace toker". Just google'd and it isnt. Doh

With a varying number of connection speeds I would personally add from warning within the replication publication.


MSDN has some info within the link below

http://msdn.microsoft.com/en-us/library/ms152768%28v=SQL.90%29.aspx

Post #975505
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse