Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2006
    Posts
    2

    Unanswered: SQL Transactional Replication Distribution Server Struggling

    We are attempting to rollout a name and address system to 10,000 users who will use an application connected to an MSDE database.

    We are using transactional replication to distribute data updates to them. Clients are connecting via the On-Idle feature of Synchronization Manager to grab transactions.

    Server spec:
    Network card: 1GB
    Processors: 2* Xeon 3.2Ghz
    Server spec: DL380 2Gb memory
    Concurrent connections set to: 600
    Disc: RAID 10 with 6400 controller

    We are not using hyper-threading.

    So far we have rolled the system out to 3500 subscribers, 500 per day.

    Each day a subsciber will receive at least 400 transactions and 5000 commands.

    Latency is 6 seconds, delivery rate 180 commands per second at less busy times.
    Latency is 14 seconds, delivery rate 127 commands a second at busy times.

    I have seen it get as slow as 0.04 commands a seconds at busy times.

    The server becomes incredibly slow when there are more than 50 concurrent connections.

    We are seeing 100 CPU for most of the day as clients connect to the distributor at various times. Lunchtime is particularly busy when people go to lunch, leaving their machines idle. We see lots of "time-outs" and "unable to connect to distributor" messages on the replication monitor during peak times.

    What can we do to improve the performance of the distribution server?

    Are we being over-ambitious by selection SQL Replication for this scenario?

    Thank you for any help!

    Best wishes

    Julian
    Last edited by JulianReed; 06-05-06 at 10:13.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Having 3500 remote MSDE databases trying to replicate to a single host server in an hour (over lunch time) seems unreasonable to me. That means that each database would need to replicate in about a second (60 minutes times 60 seconds only gives you 3600 seconds in an hour).

    Even if you spread this across several timezones, it still seems unlikely to me. While you might be able to build an infrastructure that would support this, it would take frightening resources to make it happen.

    First things first, I'm going to move this thread to the SQL Server forum. Your question is really about one of the tools used to implement Microsoft SQL Server, so it is much more likely to attract informed comment in the Microsoft forum than in the generic SQL forum (which is really for questions about the SQL languge in general).

    Second, I'm going to suggest that you think about the business need you are trying to address, not the technology you are using to address it. We can get back to the technology soon enough, but we (the resident geeks) need to understand what your business needs before we can offer a good suggestion for how to get there!

    While you are probably frustrated at the moment, your problem isn't likely to be life threatening in the short term, and I haven't seen many problems that we couldn't solve given a bit of "back and forth" with the real problem "owner".

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •