MSSQL Server and Client Configuration Recommendations and Requirements

Cultura Technologies invests in testing using the latest supportable version of Microsoft SQL with the AGRIS Product.

When specific instances or conflicts in capabilities or functionality arise, Cultura Technologies recommends to validate/confirm function in the then current MSSQL version.

MSSQL Server Recommendations for success

• MSSQL datasets on average are 4-6 times larger than PSQL 

  • (20 user, 5 location systems should expect approximately a 10 GB production dataset)

• All datasets are managed within the same MSSQL "Multi-Tenant" database

• Minimum AGRIS hardware requirements will vary based on the size of your organization and datasets.   

  • Minimum recommendation is 12 GB RAM  

  • Minimum recommended processor speed of 3GHz

    • See also MSSQL Hardware and Software Requirements for Installing SQL Server

• Running SQL Server on a virtual machine will be slower than running natively because of the overhead of virtualization. 

• The MSSQL database engine server should be setup and configured as a single task server, focused on MSSQL database operation.  Other applications and AGRIS should NOT be installed on the same server as the MSSQL database engine server for optimum performance. 

• The use of Azure is not currently supported nor has testing for optimum configuration been completed for AGRIS using MSSQL.

• The MSSQL, AGRIS Application Servers, Terminal servers should be installed and operating within the same server network and cluster for optimum performance. 

• A test environment should be configured and reviewed by key members prior to going live with AGRIS on MSSQL to understand / ensure server and network performance is optimized for "live" use within your network.

• Network communication speeds may impact end user experience. 

• Review the following details for planning your install.

  • Planning a MSSQL Server Installation.

MSSQL Client expectations and sizing.

1. Greenstone has observed the following AGRIS on MSSQL client resource consumption in our testing environments:

   1. The memory consumption for one AGRIS on MSSQL client fluctuates between 128 MB and 480 MB.

   2. The memory consumption depends on the areas of the system you are running. 

   3. The highest memory consumption was observed when running large reports (100,000+ records).

   4. The memory consumption averages 240 MB when not executing large reports.

2. The CPU load while running one client was between 1% and 4%.

3. Peak CPU consumption was achieved when navigating a data grid with a large number of rows (5000+).•

4. We have experienced a 3% increment of CPU load per additional instance of AGRIS running on the same machine performing the same operation concurrently.

•  

  •  

    • PC Model Asus G10AC

    • Processor Intel i7 - 4770 CPU @ 3.40 GHz (4 cores)

    • Memory 32 GB

    • OS 64-bit Windows Server 2012 R2 Standard Edition

    • Hard Drive 1TB SATA hard drive (7200 RPM)

    • Network RJ45 LAN (10/100/1000)

NOTE:  If you foresee the need to perform audits of the database, consider using MSSQL 2016 Service Pack 1 Standard/Enterprise Editions

Other Resources:

1. SQL Server Storage Best Practices

2. MSSQL Backup Best Practices

3. MSSQL Server Maximum Capacity Specifications.

Related articles