x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 4 replies
  • Subscribers 13 subscribers
  • Views 623 views
  • Users 0 members are here
Browse Forums
Announcements
262
topics
29
replies
General Discussion
437
topics
872
replies
BRIAN R TERERA

SQL Server Requirements

Posted By

Hi Everyone,

I currently have a client with up to 70 concurrent users running Sage 300 2020 core modules including Norming asset management and Pacific workflow on desktop version. My challenge is on speed every time they dome month end procedure, it slows down the server nearly to non operational. They have one central SQL server (Virtualised) and 6 Sage 300 application servers connecting to it with both program and shared data files residing on these servers. Workstation then connect to these 6 servers which are in different geographical areas via odbc. 

Host Server;

Processor:          Intel (R) Xeon(R) Silver 4110 CPU @2.1GHz 2.10 GHz

RAM:                  112 GB (111 GB usable)

System Type:      64-bit OS

SQL Server Specs are as follows;

Processor:          Intel (R) Xeon(R) Silver 4110 CPU @2.1GHz 2.10 GHz

RAM:                  70 GB

System Type:     64-bit OS

Sage 300 App Application Servers

Processor:          Intel (R) Xeon(R) Silver 4110 CPU E3-1225 v5 @ 3.30GHz    3.30 GHz 

RAM:                  32 GB

System Type:     64-bit OS

Every time they have this problem I check the SQL Task Manager and Memory Usage will be above 95%.

WHAT COULD BE THE PROBLEM ? 

If there is a best structure and hardware specifications for such a setup, please share.

  • 0

    Hi

    Please advise wirth regards to the following below:

    1. How large is the Data, log file and .bak?

    2. Are the transaction log files being truncated?

    3. Is the a SQL Maintenance plan in place for the SQL Server?

    4. How often is the History cleared? 

  • 0

    Hello Brian,

    Refer to the "Sage 300 compatibility guides and system requirements", especially note the SQL server requirements when it comes to the RAID sets to use. SQL will easily look after your load if it is set up correctly. Don't focus on DB size or plans, that's not the problem.

    I think your problem though is "6 Sage 300 application servers connecting to it with both program and shared data files residing on these servers". You can't do that, you need one Shared Data path. What I think may be happening is the record locking which happens in the shared data path (not the DB) is not understood by SQL. Sage thinks that, say, a posting can happen as there is no lock within server 1's shared directory, but a user on server 2 is busy posting. SQL going to get confused, and will queue requests with its own locks. Execute "sp_who2 active" and if you have locks there, that's the challenge.

    Again refer to the guides, you will see that the shared data path has to be common (unless every server talks to a different Sage DB). If you used Citrix/RD, you would probably only need 2 servers, I wouldn't have a geographical server without RDP connections.

  • 0 in reply to Kevin Johnston

    Thanks Kevin for the response. I will change to one Shared Data path for all these application servers. Of late I have noticed that a few guys work via RDP and they seems not to have much problems, that's another point taken.

  • 0 in reply to Zakariyya Carrim

    1. - Data files 100GB

        - Log files 80GB

    2. Less often

    3. None

    4. Never

*Community Hub is the new name for Sage City