Foglight can now monitor the MS SQLServer BI stack – SSIS, SSAS, and SSRS.
Foglight supports monitoring SSIS for MS SQLServer 2012 and above. For this SSIS monitoring, Foglight needs to access the SSISDB catalog. This should be automatically created in versions 2014 and 2016. In SQLServer 2012, it needs to be manually created. These instructions also apply to the newer SQLServer versions if the SSISDB is not in the DB list within MS SQLServer Management Studio (SSMS) to allow Foglight to monitor it.
Follow these simple steps to configure using SSMS…
- Connect to the SQL Server Database Engine (on the same machine as the SSIS)
- In the Object Explorer, expand the server node, right-click theIntegration Services Catalogs node, and click Create Catalog.
You will then see this dialog….
- Check the box forEnable CLR Integration as the catalog uses CLR stored procedures.
- Check the box forEnable automatic execution of Integration Services stored procedure at SQL Server startup. This enables the startup stored procedure to run each time the SSIS server instance is restarted. The stored procedure performs operations maintenance on the SSISDB catalog. It repairs/resets the status of packages running if and when the SSIS server instance goes down.
- Provide a password and click Ok.
This password protects the database master key used for catalog data encryption.
You will want to securely save this password. This password will need to meet the windows password profile policy.
From here, you are ready to configure Foglight to monitor SSIS.
Install an agent to monitor the SQLServer database engine.
Lastly, install an agent to monitor the SSIS.
