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 shipped with the product allow to partition 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.
Note
If you upgrade (or reconfigure existing) Sentinet version from unpartitioned to partitioned configuration, all existing monitoring data (regardless of how far back it goes) will be on one, first partition. All that data will be purged only when it's time to remove the entire first partition.