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.
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?
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".