MSSQL Server and Client Configuration Recommendations and Requirements

MSSQL 2019

AGRIS supports MSSQL 2019 as of version 20.1.0. The specific Cumulative Update tested and supported is Update 4.

(Note: MSSQL 2019 requires use of Windows Server 2016 or higher to operate)

AGRIS supports MSSQL 2017 as of version 18.4.0. The specific Cumulative Update tested and supported is Update 11.

(Note: MSSQL 2016 requires use of Windows Server 2012 or higher to operate)

MSSQL datasets on average are 4-6 times larger than PSQL
- (20 user, 5 location systems should expect aprox 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 recommended 12 GB Ram
- Minimum processor speed 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.

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.
The CPU load while running one client was between 1% and 4%.
Peak CPU consumption was achieved when navigating a data grid with a large number of rows (5000+).•
We have experienced a 3% increment of CPU load per additional instance of AGRIS running on the same machine performing the same operation concurrently.

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

News you can use...

What should I expect the size per dataset to bet when migrating from PSSQL to MSSQL? Typically the database size will increase by roughly ~4-6X the current size of the pervasive files located in each dataset folder.
What about using MSSQL Express? In most cases, the typical AGRIS database exceeds the capacity for SQL Server Express.
- SQL Server 2017 Express - Limitations: Microsoft SQL Server Express supports up to 4 core processors, 1410 MB of memory, and 10 GB of data (per https://www.microsoft.com/en-us/sql-server/sql-server-2017-editions).