Version

Argent Advanced Technology 3.1A-1304-A and later

Date

Thursday, 5 Sep 2013 (Revised from original KBI of Thursday, 28 Mar 2013)

Summary

Often Argent Guardian Ultra monitors hundreds or thousands of servers and devices. This is normal, but this can generate a very large volume of Argent Predictor data

In a similar manner, Argent for Compliance’s archiving of Windows Event Logs can generate millions of rows of data per server per day. (A Security Log of typical Domain Controller can generate five million rows per day)

These are two examples of how the SQL Server backend can be burdened

Argent’s recommended approach in these cases is to use the SQL Bulk Insert to dramatically improve the SQL Server performance

When using the SQL Bulk Insert, instead of inserting one row at a time, the Argent AT Engine caches rows, writes out to a data file periodically, then calls SQL statement to insert all cached rows in one batch. Argent benchmarks show a 10 to 50 times improvement in performance

*** WARNING ***

Because the data path in SQL Bulk Insert is the path seen from SQL Server, it may not be the same as the path seen from AT Engine. It can become complicated when the SQL Server is not on the same machine as the Argent AT Engine

Scenario One – AT Engine Uses Local SQL Server

This is the simplest case; simply switch on the SQL Bulk Insert using:

Argent Guardian Ultra – Set registry HKLM\SOFTWARE\ARGENT\ARGENT_GUARDIAN_ULTRA\SQLBI_ENABLED to 1

Argent For Compliance – Turn on SQL Bulk Insert In GUI

Restart the service to allow the settings to take effect

Scenario Two – AT Engine Uses External SQL Server With A File Share

Turn on SQL Bulk Insert by

This is the simplest approach when an external SQL Server database is used

For example, assume the bulk insert directory is ‘C:\SQLBI_DROP_DATA‘. Do the following:

1. Edit registry
   HKLM\SOFTWARE\ARGENT_GUARDIAN_ULTRA\SQLBI_SQL_PATH to C:\SQLBI_DROP_DATA

   Note: The UNC path is not used here. The path is that what is seen from SQL Server. So it is the local path

2. Edit registry
   HKLM\SOFTWARE\ARGENT_GUARDIAN_ULTRA\SQLBI_DROP_PATH to \\{SQLSERVER}\C$\SQLBI_DROP_DATA
3. Turn on SQL Bulk Insert by doing setting changes in Scenario One

Scenario Three – AT Engine Uses External SQL Server Cluster

Similar to scenario two, in this case create a share on the same cluster drive as the Argent database

Update the registry keys as mentioned in Scenario One and Two, note that the UNC path should be in the format ‘\\{SQLCLUSTER}\DRIVE$\SQLBI_DROP_DATA’ instead of using the node name directly

This will avoid problems when cluster node is offline

Scenario Four – AT Engine Uses External SQL Server That Does Not Allow A Share

This case is used when the DBA does not want the Argent AT Engine to touch the SQL Server drives directly

*** WARNING ***

Consult with an Argent Engineer on Argent Instant Help prior to implementing as this approach can become quite complicated depending on the setup

1. It is necessary to check what service account the SQL Server is running under. If it is the Local System Account, then the DBA must open a share on the SQL Server machine, because a service with Local System Account does not have network access
2. Ensure the SQL Server service account has the access to the Argent AT Engine share. It can be verified by logging in to the SQL Server with an SQL service account, and checking if \\{AT Engine}\ARGENTGUARDIANULTRA is accessible

If the SQL Server allows a SQL Server account such as ‘sa‘, use this account instead of trusted connection in AT setup

Then edit:

SQLBI_DROP_PATH to C:\ARGENT\ARGENTGUARDIAN\BI_DATA_PATH

and
SQLBI_SQL_PATH to \\{AT Engine}\ARGENTGUARDIANULTRA\BI_DATA_PATH

If the SQL Server does not allow the SQL Server account, the case becomes complex

In this case the client Argent AT Engine authenticates with the SQL Server, and the SQL Server then impersonates the client and tries to access the UNC path specified in SQL Bulk Insert statement (SQLBI_SQL_PATH). This is referred to as ‘Delegation‘ and is implicitly forbidden. Explicit steps must be taken to enable constrained delegation for the SQL Server service account

See this article explaining the details:

http://msdn.microsoft.com/en-us/library/ms998355.aspx.

The article shows constrained delegation for an ASP service accessing the back end database, but in this case it would be the SQL Server in the middle accessing the back end UNC share

This can be done but can prove to be both brittle and fragile – Argent recommends to either have the DBA to open a share (Scenario 2 and 3) or make SQL Server authentication available for the Argent AT client

Scenario Five – AT Engines Configured As Non-Stop Monitors

Argent Non-Stop Motors configuration is stored in the database and is refreshed regularly whenever Argent services recycle

*** WARNING ***

Do not edit the Registry Keys for the Local Motors in the Non-Stop case because the central Argent database overwrites these registry settings

Do the following:

1. Stop Argent Guardian Ultra service on all Non-Stop Motors
2. Update SQLBI_SQL_PATH and SQLBI_DROP_PATH in central database table ARGSOFT_ARGENT_GUARDIAN_ULTRA_CONFIGURATION with the UNC path to the share
3. Restart Argent Guardian Ultra services on all Non-Stop Motors
4. Wait 120 seconds, then check the Registry Keys on all Non-Stop Motors to ensure they all show the correct settings

Technical Background

N/A

Resolution

N/A