Database Pruning/Archiving Settings
Database Archiving is the process of moving historic signals (client history), activation notes, work orders, and operator maintenance logs from the main Patriot database to a separate Patriot Archive Database.
Database Pruning is the process of deleting this same historic data from the Patriot database(s).
Once your data has been pruned it will no longer be able to be accessed by Patriot. But, provided that you make and store automatic database backups at an interval shorter than the maximum age of your Patriot database(s) - no data is truly lost and may be manually accessed via the backups at any time.
By default new installations of Patriot do not have the Patriot archive database enabled and therefore only Database Pruning settings are available by default.
Database Archiving and Pruning is very important for two reasons:
- Database efficiency and performance
- Database Size restrictions
As a guide line, we recommended setting Pruning and/or Archiving at a maximum of 12 months or less of historic data in the main database. This is just a guideline, and it depends on many factors, such as:
- The volume of signals received
- The reporting demands on the system
- The server hardware
- The Microsoft SQL server version - free / express versions have limited database size and performance restrictions.
If you wish to (or are required to) keep more than 12 months of history in your active Patriot database(s) then it's recommended to enable the archive database, and setup archiving to keep 12 months history in your current database and then limit the archive database as required.
As data builds up in the database, system performance will be reduced, especially when taking backups or running scheduled reports. More powerful server hardware can postpone this effect, but not eliminate it.
If you are using a full version of SQL Server, your only restriction is physical disk space. However if you are using a free / Express edition there is a maximum limit on the database size. If this limit is reached, no new data can be added, which will cause Patriot to stop functioning. Archiving and/Pruning is essential if a free or Express edition of SQL is being used.
Automated Archiving Setup
The archiving settings screen can be accessed from System > Database > Archiving Settings. From this screen, database archiving and/or pruning can be configured and analysed.
If you disable or enable the Patriot archive database at any point you must disable and re-enable database pruning/archiving from this screen.
For archiving purposes Patriot breaks down its historic data into three types: Signal, Operator Log/Work Order and Recording Videos. Note that these data types do not correspond directly to individual database tables - they may include many related tables and data stored outside the SQL database (e.g. Video recording files). Archiving/pruning of each of these data types is controlled by separate settings each of which can be independently enabled/disabled.
Disabling archiving or pruning of any data type is not recommended long term as you will eventually run into issues with storing and moving database backups, diskspace limitations, history query performance etc.
Settings
The following settings can be configured on this screen.
Maximum Signal Count in Current Database
For efficiency reasons, this setting is set to a number of signals, not number of days/months. A good estimate can be calculated by multiplying (the number of clients in the database) x (the average signals per client per day) x (number of days of current log). For example 2500 clients x 3 signals per day x 180 days = 1,350,000. Enter this value into the Maximum Number of Signals in Current Database field. Once the database contains more than this number of signals, the oldest signals are pruned (or moved into the archive database if enabled). It is typical that this value is set to keep less than 12 months worth of signal history in the current database.
If you set a very small value (with respect to the size of your client database) then this setting may be ignored and Patriot will use a default minimum value of around 90 signals per client instead.
Maximum Days of Current Operator Log/Work Order
Specifies how many days old the operator maintenance log, and work orders should be before they are pruned (or moved into the archive database if enabled). This value should be set to as small a value as possible to cover the most common search requirements.
Maximum Signal Count in Archive Database
Only available when Archive Database in use.
Calculate this value in the same way as Maximum Number of Signals in Current Database. This specifies how many signals are keep in the archive database before they are permanently removed from the system. For example, if 1,000,000 signals in the Current Database and 2,000,000 signals in the archive database are specified, 3,000,000 total signals will be kept in the system. It is common practice to set the archived signal value to a larger value than the current database value. Performance of the system will not be affected so much by having a larger archived database than the current database. A typical value might be 1-2 years worth of signals kept in the archived database.
If you set a very small value (with respect to the size of your client database) then this setting may be ignored and Patriot will use a default minimum value of around 180 signals per client instead.
Maximum Days of Archived Operator Log/Work Order
Only available when Archive Database in use.
Specifies how many days old the operator maintenance log, and work orders are before they are removed from the archive database. this value is commonly set to a larger value than the 'Maximum Days of Current Operator Log/Work Order'.
Number of Months to Store Recording Videos
Specifies how many months worth of operator camera action recordings to store in the primary Video Storage directory. Older recordings will be automatically transferred, daily, to the Archived Video Storage directory.
Prune/Archive Every
Decides how often during the day pruning (or archiving if archive database is enabled) should be run, setting this to 30 minutes will have minimal impact on performance of the system during the day as smaller more frequent archives run much quicker. The pruning/archiving process is gradual to help maintain good system performance. Only small amounts of data being will be pruned/archived off at a time so it is recommended to prune/archive every 30 minutes to frequently reduce the size of the current database.
Time of Day to perform Pruning/Archiving
Set this early in the morning, use 1:00am unless this conflicts with another automatic procedure, such as backups.
Advanced Settings
The archiving procedure will calculate the amount of data to archive each time it is run. There are limits imposed to prevent an excessive amount of data from being archived at one time. This is designed to protect the system from heavy workload caused by the archiving procedure. If you have entered archiving levels that are much lower than existing data this can result in the archiving procedure taking some time to catch up to these values. In some situations these calculations result in inadequate levels of data being archived, and new signals received continue to outpace archived data. This can be caused by systems experiencing higher than normal signal volume. This maximum level of data to be archived can be adjusted using a Data Service Configuration Setting. The setting is SignalArchivingRateLimit, default value is 10. The maximum value which can be used is 100. Please consult Patriot support before adjusting this value.
Signal Statistics
To view the current status of the system, the Signal Statistics section of the Pruning/Archiving Settings screen allows you to calculate the total number and age of signals in both the main database (and archive database if enabled). To view this data, select the Refresh icon. On larger databases, this may take a few minutes to complete. This section can be used to verify that pruning/archiving is maintaining the database correctly.
It is important to regularly check that database pruning/archiving is working correctly, and maintaining your historic data at the correct levels. If you substantially increase or decrease the number of clients in your database, then its likely the pruning/archiving settings will need to be adjusted to keep the amount of data at the correct levels.
Manual Pruning/Archiving
Manual pruning/archiving can also be performed using the Patriot Utilities program, which can be found in the Patriot Version 6 Client installation folder.
This is typically: C:\Program Files (x86)\Patriot Systems\Patriot Version 6 Client\
Simply run the UtilitiesProgram.exe application, and select Maintenance > Database > Manual Archiving.
This program can be run while Patriot is offline, so the SQL login details must be entered before running the database prune/archive process. You must select your database mode - if you are using an archive database choose "Archive" else choose "Prune".
With this tool the Signal Statistics can be viewed and manual one-off pruning/archiving can be performed. This can be useful if the Patriot database ever reaches the database size limits or if a large number of signals is added (for example an unchecked runaway alarm) to the system to the point that performance is degraded.
Total Signal Count:
The Total Signal Count specifies the amount of signals you would like kept in the database before archiving.
Total Archive Signal Count:
Set this to the amount of signal you want to keep in the Archive database
If you have 4million signals in the database and you set the Total Signal Count to 3million Patriot will not archive off 1million signals right away. You will need to run the manual archive progressively until it reduces. This basically means it will continue archiving until 3million signals is reached in the main database.
When the automated archive function runs it leaves the Total Signal Count and the Total Archive Signal Count as the max entered for each setting in the archive settings.
Average Signals Per Day:
Average sigs per day is an estimate of how many signals each account logs on average. Increasing this will cause more signals to be archived or deleted.
Times per Day:
Times per day indicates how much times to archive each day. Setting this to a low number will archive more signals.