E10 load balanced SQL server

sql

(Michel Serry) #1

We have around 800 heavy users using Epicor 10.1.400.x. Our SQL server is having trouble to keep up and we are receiving complains that the system is responding slowly.

Have anyone used or currently have a load balanced SQL server scenario with Epicor 10?

Our current server specs are:
2 x 3.56 Ghz processors
128 GB or memory
10 GB fiber network connection
Disk is a Fusion-io Drive2 750 GB


(Bart Elia) #2

I don’t have the details but know of several instances where folks are using vanilla SQL Replication and pointing their reporting app server(s) against that read only replicated instance.
This takes the load off - especially folks who do a lot of reports.

I know we had a practice inside of Professional Services at one time for this - not sure state of the art currently.


(John Mitchell) #3

We just implemented multiple app servers and found the user experience improves dramatically. Are your 800 users MES or all using the client? John


(Randy Stulce) #4

We had a similar experience and noticed the SQL Server wasn’t getting pegged but the app server. So like @John_Mitchell, we too split up our users across multiple app servers and it worked for us.


(Michel Serry) #5

700 are using the client and 100 the MES.

We currently have 2 app servers load balanced using DNS.


(John Mitchell) #6

Wow, that’s a big install! I would check your SQL performance but you would probably get better performance scaling up rather than out. We are looking at redoing our architecture and the sweet spot seems to be around a single DB server, three app servers with task agents, and a dedicated SSRS server.

Keep in mind all of StackOverflow runs on a single MSSQL DB (1.5 TB of Ram) and 9 web servers so it can be done! https://stackexchange.com/performance


(Michel Serry) #7

Currently we are only running a single task agent. I know we can have up to 3. Anything special we need to do if we have 3 task agents running other then installing and configuring them? Is there a best practice?


(John Mitchell) #8

From my experience, so your mileage may vary, but nothing special is required. We created two new app servers on two new servers and installed and configured the Task Agents. They poll the db to see if there are any tasks to process, and if so, it is passed to IIS to process. You can monitor the Ice.SysRptLst to see which server processed which task and I believe the processing duration.

SELECT HostComputer, RptDescription, COUNT(SysTaskNum) AS Reports, AVG(DATEDIFF(s,CreatedOn,LastActionOn)) AS AvgDuration FROM Ice.SysRptLst GROUP BY HostComputer, RptDescription

There is a lot more information on this thread as well:

One last thing, you can also use a load balancing IIS extension called ARR, to route the messages between app servers if needed https://www.iis.net/downloads/microsoft/application-request-routing