Configuring vRealize Log Insight Agent on MS SQL Server 2012

There’s a lot of information available about the SQL Server Content Pack for Log Insight, but actually  trying to use it reveals a lot of gaps.  So, here’s what I’ve done to get my SQL Server 2012 R2 instances to correctly report their information to vRealize Log Insight and show up in the Content Pack filters.

To ensure the correct log directories are used and allow reuse of filelog sections, I suggest creating an agent group for each SQL Server.  I’m also a fan of the server-side agent configuration, so if the agent gets totally removed and reinstalled, it’ll acquire the correct configuration again.

Steps:

1. Install the Log Insight Agent for Windows on the SQL Server machine, be sure to use the FQDN of the Log Insight VIP
2. While logged on to the SQL Server, locate the Logs folder for each SQL server instance. Note that this is not the database transaction logs folder – if it contains *.ldf files, it’s the wrong folder.   Specifically, you want the folder that contains the ERRORLOG file.  To be certain follow these steps:
   • Launch SQL Server Configuration Manager
   • Select “SQL Server Services” from the left pane
   • Right-click “SQL Server (INSTANCENAME)” from the right page, choose “Properties”.
   • Select the “Startup Parameters” tab
   • In the Existing Parameters box, make a note of the parameter that starts with “-e”
   • The filepath in that parameter (minus “ERRORLOG”) is the folder we need.
   • DO NOT CHANGE ANY VALUES, Cancel Properties, Close Configuration Manager

3. Logon to Log Insight as an administrator
4. Check the Content Package area to be sure the “Microsoft – SQL Server” Content Pack is installed
5. In the next steps, we’ll configure the agent from the server-side. It is possible to do from the client-side.
6. In the Administration|Agents section of Log Insight select Microsoft – SQL Server from Available Templates drop-down list
7. Click the “Copy Template” button. In the Copy Agent Group box that appears, change the name to “Microsoft – SQL Server – SERVERNAME”.  (replace SERVERNAME with the actual server’s name).  Click “Copy”.
8. Now, the dropdown list should indicate that you are editing the new Agent Group
9. In the Filter box, edit the parameters to create a filter that identifies only the desired server. For example “Hostname | starts with | SERVERNAME”
10. In the Agent Configuration section
    • Update the section header from “[filelog|MSSQL]” to [filelog|MSSQL-SERVERNAME-INSTANCENAME]” replace SERVERNAME and INSTANCENAME with the correct, actual values. Be sure to keep “MSSQL” as the prefix, since the dashboard elements key on that.
    • Update the directory value to where the ERRORLOG file was found
    • Update the exclude value to read *.trc;*.xel;*.mdmp
    • Example:

[filelog|MSSQL-COREDB1-MSQLSERVER]
; IMPORTANT: Change the directory as per the environment
directory=C:\MSSQL\DATA\MSSQL12.MSSQLSERVER\MSSQL\Log
tags={“ms_product”:”mssql”}
charset=UTF-16LE
exclude=*.trc;*.xel;*.mdmp

11. If there are multiple instances of SQL server on the host server, copy the entire section and edit the section header with the Instance Name. Example of configuration for multiple instances:

[filelog|MSSQL-COREDB1-MSSQLSERVER]
; IMPORTANT: Change the directory as per the environment
directory=C:\MSSQL\DATA\MSSQL12.MSSQLSERVER\MSSQL\Log
tags={“ms_product”:”mssql”}
charset=UTF-16LE
exclude=*.trc;*.xel;*.mdmp

[filelog|MSSQL-COREDB1-WORKLOAD]
; IMPORTANT: Change the directory as per the environment
directory=C:\MSSQL\DATA\MSSQL12.WORKLOAD\MSSQL\Log
tags={“ms_product”:”mssql”}
charset=UTF-16LE
exclude=*.trc;*.xel;*.mdmp

12. Click Save New Group.
13. On the SQL Server, restart the “VMware vRealize Log Insight Agent”
14. Navigate to %ALLUSERSPROFILE%\VMware\Log Insight Agent and open liagent-effective.ini with notepad. Check that the sections added above appear in this file.
    • If they do not, you may have to adjust the filters on the Agent Group

Advertisement