Appendix F. Advanced Monitoring configuration – Tables Partitioning
Sentinet offers deployment and upgrade options when its monitoring database tables are partitioned. Tables partitioning is a feature of Microsoft SQL Server that allows to physically split records in a single table into multiple partitions using a partition function. Tables partitioning may give performance and manageability benefits for those Sentinet database tables that can potentially have a lot of records. For example, deleting historical data from a single partition may take only seconds, rather than hours when data is not partitioned.
Records can be partitioned in many ways. The selection of a specific partition function depends on how table records are going to be used in bulk operations. Sentinet Repository Configuration Wizard and sample database scripts are shipped with the product to allow partitioning of the MonitoringTransactions, MonitoringRecords, MonitoringTransactionProperties and MonitoringActivities tables by the record's creation date with one table partition storing records for exactly one day. This gives the ability to quickly remove historical records with a one-day precision interval.
To enable monitoring tables partitioning check the Enable Monitoring tables partitioning checkbox.
Note
Exclamation Sign icon, located next to this checkbox, is just a clickable reminder to consult with an online version of this document‘s chapter.
With enabled Monitoring tables partitioning, the Repository Configuration Wizard will silently run the scripts that create the partition function, partition scheme, indexes, and archive tables for the tables listed above. It will also modify PurgeMonitoring stored procedure described in Appendix C to employ partition splitting and truncating for the fastest record removal. However, the wizard does not enable Purge Historical Data task of the Service Agent Windows Service. Running this task (or its alternative described in the Important insert below) is essential, as it makes sure that new partitions are timely created and old partitions are dropped when expired.
Important
After Repository Configuration Wizard completes its execution, make sure to uncomment Purge Historical Data task, review and provide its parameters according to the requirements of your specific environment, and restart Sentinet Agent Service.
Alternatively, ensure that PurgeConfiguration and PurgeMonitoring SQL stored procedures are called if you are using scheduled SQL Agent Job(s) (see more details in this document).
Note
Sentinet uses UTC date and time when creating SQL Server table partitions. However, the MonitoringTransactions table's clustered index is built on the StartDateTimeLocal column that stores values in the local reporting time zone. Sometimes, this may lead to few monitoring transactions not aligned with partitions’ boundaries, which is considered to be an insignificant deviation.
Consider the following example:
Suppose the MonitoringTransactions table has 3 partitions:
• Partition 1 <= 01/01 00:00:00 UTC
• Partition 2 <= 01/02 00:00:00 UTC
• Partition 3 <= 01/03 00:00:00 UTC
If a new monitoring transaction record with StartDateTimeLocal = 01/01 22:00:00 UTC-05:00 is inserted, it will be assigned to Partition 2 even though its UTC value of 01/02 03:00:00 UTC belongs to Partition 3. Similarly, if a new monitoring transaction record with StartDateTimeLocal = 01/02 01:00:00 UTC+02:00 is inserted, it will be assigned to Partition 3 even though its UTC value of 01/01 23:00:00 UTC belongs to Partition 2.
Important
If you upgrade (or reconfigure an existing) Sentinet version from unpartitioned to partitioned configuration, all existing monitoring data (regardless of how far back it goes) will be moved in one, very first partition. This very first partition will be deleted only when it's time to remove it according to the configured purge criteria. For example, if you used to keep 10 days of data in an unpartitioned Sentinet database, and upgraded to partitioned database, then all existing 10-days data will be moved in the very first partition, which will be removed only in 10 days after the upgrade. This will result in up to 20 days of data kept in the database before the database goes back to a 10-days storage.
Important
When Sentinet is configured with tables partitioning, Full-text search for the content of the messages’ body will not be available even if the Full-text search feature is installed with the SQL server. Search for the messages’ body content will be available only by using SQL Server LIKE predicate syntax.