Optional SQL Server configuration settings for ICA

Products

• Information Centric Analytics

Issue/Introduction

Minimum server resource specifications and baseline configuration settings are provided in the Information Centric Analytics (ICA) Administrator Guide. This article covers additional configuration settings that may improve both the connectivity and performance of the server hosting Microsoft SQL Server and ICA's RiskFabric database and ancillary data warehouses.

Environment

Version : 6.x

Component : Microsoft SQL Server

Resolution

The following are recommended configuration modifications:

Server Memory Options

The minimum and maximum server memory settings are used to configure the amount of memory (in megabytes) granted to SQL Server in order to establish upper and lower limits of memory used by SQL Server's buffer pool. The SQL Server engine process starts with only the memory required to initialize (the minimum server memory value). As the workload on the engine increases, it acquires additional system memory to support the workload but never acquires more than the level specified by the maximum server memory setting. The default setting for minimum server memory is 0 and the default setting for maximum server memory is 2147483647 MB (or unlimited).  A general rule to follow when configuring these settings is to leave the operating system 20% of total system memory. 

1. Open SQL Server Management Studio (SSMS)
2. Connect to the Database Engine hosting the RiskFabric database
3. In Object Explorer, right-click the server hostname and select Properties
   The Server Properties window will open
4. Select the Memory page
5. Enter the appropriate memory settings for your server
6. Click OK to save changes and close the Server Properties window

Remote Server Connections

Per Microsoft, the remote query timeout option specifies "how long, in seconds, a remote operation can take before SQL Server times out. The default value for this option is 600, which is a 10-minute wait. Setting this value to 0 disables the time-out. This value applies to an outgoing connection initiated by the Database Engine as a remote query. This value has no effect on queries received by the Database Engine. A query will wait until it completes."

This setting affects queries executed during ICA's RiskFabric processing jobs as OPENQUERY calls against linked servers, such as RiskFabric_ASDB, ActiveDirectoryDW, and the Symantec DLP database.

1. Open SQL Server Management Studio (SSMS)
2. Connect to the Database Engine hosting the RiskFabric database
3. In Object Explorer, right-click the server hostname and select Properties
   The Server Properties window will open
4. Select the Connections page
5. Check the box labelled Allow remote connections to this server and set the Remote query timeout value to 0 (no timeout)
6. Click OK to save changes and close the Server Properties window

Additional Information

Microsoft Books Online: Server memory configuration options

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver16

(URL last checked May 10, 2023)

Microsoft Books Online: Configure the remote query timeout Server Configuration Option

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-query-timeout-server-configuration-option?view=sql-server-ver16

 (URL last checked May 10, 2023)