KBI 310359 Enabling SQL Bulk Insert In Argent Guardian Ultra And Argent For Compliance
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:
- Edit registry
HKLM\SOFTWARE\ARGENT_GUARDIAN_ULTRA\SQLBI_SQL_PATH to C:\SQLBI_DROP_DATANote: The UNC path is not used here. The path is that what is seen from SQL Server. So it is the local path
- Edit registry
HKLM\SOFTWARE\ARGENT_GUARDIAN_ULTRA\SQLBI_DROP_PATH to \\{SQLSERVER}\C$\SQLBI_DROP_DATA - 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
- 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
- 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:
- Stop Argent Guardian Ultra service on all Non-Stop Motors
- Update SQLBI_SQL_PATH and SQLBI_DROP_PATH in central database table ARGSOFT_ARGENT_GUARDIAN_ULTRA_CONFIGURATION with the UNC path to the share
- Restart Argent Guardian Ultra services on all Non-Stop Motors
- 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