Installation guide for Microsoft SQL Server and Oracle platforms
The dbWatch Agresso Module is based on the dbWatch Engine Framework and can be installed during the initial installation of the dbWatch Framework. If the MS SQL Server or Oracle instance where the agresso database/schema is defined, is already monitored by the dbWatch Server, (which means that there is a dbwatch database/schema defined on the same instance with a set of dbWatch Tasks/Alerts), you will only have to install individual Tasks and Alerts, and add the plug-in into the dbWatch Administration framework.
This installation guide explains how to add the dbWatch Agresso Module into an existing dbWatch Engine installation.
The dbWatch Agresso module consists of three main parts
- Agresso Tasks and Alerts templates
- Agresso Administration plugin
- Agresso report template
Agresso Tasks and Alerts templates
The Agresso Tasks and Alert templates is a set of procedures which can be installed into the dbWatch monitoring framework. Those procedures are responsible for collecting the information related to the agresso database/schema (performance, growth rate & usage pattern etc) and for checking the critical parts of the agresso database/schema (indexes, reports & connections etc). Procedures responsible for collecting data are called Tasks, while procedures checking data are called Alerts.
The Agresso Tasks and Alert procedures is the database monitoring package in the dbWatch context.
There are 14 Agresso Alerts/Tasks templates for the MS SQL Server platform, and 12 Agresso Alerts/Tasks templates for the Oracle platform:
- [database/schema name] – missing system indexes – Checks if there are any missing system indexes in the agresso database by comparing records in dbo.asysindex and sys.indexes tables (MS SQL Server and Oracle).
- [database/schema name] – missing user defined indexes – Checks if there are any missing system indexes in the agresso database by comparing records in dbo.asysindex and sys.indexes tables (MS SQL Server and Oracle)..
- [database/schema name] – not registered indexes – Checks if there are any indexes in the agresso database which are not registered in the dbo.asysindex or dbo.aagindex tables (MS SQL Server and Oracle).
- [database/schema name] Aplication Server – Checks if the Agresso Application Server is connected to the database (MS SQL Server and Oracle).
- [database/schema name] DWS shadow tables – Checks the internal and external fragmentation of all tables and indexes used by the DWS (Data Warehouse Server) process (MS SQL Server and Oracle).
- [database/schema name] TPS shadow tables – Checks the internal fragmentation of all tables and indexes used by the TPS process (MS SQL Server and Oracle).
- [database name] blob size collector – Collects information about size of blobs in agresso database (MS SQL Server).
- [database name] index usage statistics – This procedure collects statistics from sys.dm_db_index_usage_stats performance view which gives information on how an index (or a table – heap) has been used to resolve queries (MS SQL Server).
- [database name] internal fragmentation – Collects internal fragmentation statistics for tables and indexes. The information is extracted from the dynamic management function (view) sys.dm_db_index_physical_stats (MS SQL Server).
- [database/schema name] object size collector – Collects table and index size information for the largest objects in the system. The information is extracted from the dynamic management view sys.dm_db_index_physical_stats (MS SQL Server and Oracle).
- [database/schema name] report queue – Monitors and checks pending or running reports. Returns warning or alarm if the elapsed time exceeds defined thresholds (MS SQL Server and Oracle).
- [database name] session load – Collects the total number of sessions (inactive and active) connected to the agresso database (MS SQL Server).
- [database name] memory usage – Collects data cache memory usage for the agresso database (MS SQL Server).
- [database name] filegroups size collector – Collects size information for all filegroups defined in the agresso database (MS SQL Server).
- [schema name] print-blob check – Checks size of the Agresso ACRPRINTBLOB table BLOB column. (Oracle).
- [schema name] help and temp tables check – Collects information about help and temporary tables created in the agresso application schema. (Oracle).
- [schema name] CON$ system table check – Cheks the size of all indexes defined on SYS.CON$ system table. (Oracle).
Agresso Administration plugin
The Agresso Administration plugin is an extension to the dbWatch Administration Module (Advanced only) and is not dependent on the monitoring module (can be used without installing the Agresso Tasks and Alert procedures). This module enables you to administer parts of the agresso database/schema in a more efficient and structured way.
Agresso report template
The Agresso report template is based on information collected by the Agresso Tasks and Alerts, and some of the standard dbWatch Tasks and Alerts. Therefore cannot be used without installing the Agresso monitoring module. This report template can be adjusted and modified to create advanced reports for capacity planning or SLA related needs.
Installation of Agresso Tasks and Alerts
Every Agresso Task and Alert (like normal monitoring Tasks and Alerts) is an xml file which must be placed into the import_area directory where the dbWatch Server software is installed (default on windows: C:\Program Files (x86)\dbWatch\[version]\server\resources\import_area\).
To install Agresso monitoring, click on the “Manage tasks and alert” icon. Chose “Engine centric” view, then the group where the SQL Server or Oracle instance (with agresso database/schema) is defined, and then the dbWatch Engine name. On the right side of the “Manage tasks and alert” window, a set of packages will appear. Open the Agresso package folder, and select all the agresso templates.
Click “Install”, and then supply the agresso database (or schema) name (default “agresso”). For the “[Agresso database] Application Server” (on MS SQL Server platform) alert you will also have to specify the machine name where the Agresso Services are installed.
After sucsesful installation the Agresso Tasks and Alerts will appear in the dbWatch Monitor. All the alerts and tasks will be rescheduled, but you can also run all of them manually.
To uninstall an agresso task or alert, you have to open the template folder and then select the task/alert.
Installation of Agresso Administration plugin
To be able to use the Agresso Administration plugin, you have to copy the dbWatch_Agresso_MS.xml file into the “import_area” directory (default on windows: C:\Program Files (x86)\dbWatch\10.0\server\resources\import_area\). Then the Agresso plugin must be enabled through the “Instance configuration” panel.
When the Agresso plugin is enabled, the agresso folder will be visible in the dbWatch Administation module.
MS SQL Server
The Agresso Administration plugin is similar to the Agresso Management Console, but has extended functionality for index and blob management.
Installation of Agresso report template
To be able to use the Agresso report template, you have to copy the Agresso_Status_Report_mssql.xml file into the “report_templates” directory (default on windows: C:\Program Files (x86)\dbWatch\[version]\server\resources\import_area\). The Agresso report will then be available through the Report Manager wizard.
How to upgrade the Agresso module from dbWatch version 9.2 (or 9.1)
The early version of the Agresso module consisted of agresso tasks/alerts and the report template (no administration plugin).
Copy all the agresso xml files (file names which start with “dbwatch_sqlserver_agresso_… or dbwatch_oracle_agresso_…”) from the dbWatch 9.2 tasks directory (default on windows: C:\Program Files (x86)\dbWatch\9.2\server\engines\tasks\) into the import_area directory where dbWatch version 11 is installed. The same procedure for the agresso report(s) (default directory on windows: C:\Program Files (x86)\dbWatch\9.2\server\report_templates\). Remember, if you have made any changes to the agresso report templates, you should rename that xml file before copying it into the import_area directory where dbWatch 11 is installed. Otherwise the report template file will be replaced by the new template.
The “agresso voucher statistics” tasks and the “agresso temporary database” alert are no longer supported in dbWatch 11, but can still be used.
After the xml files are copied from the dbWatch 9.2 installation you have to place all the new Agresso Tasks and Alerts into the task directory (6 files will be replaced). Then, using the “Manage tasks and alert” panel install the remaining 7 tasks/alert into the dbWatch Engine.
After successful installation the new agresso tasks/alerts will appear in the dbWatch Monitor.