Archiving Settings Screen (archive database disabled - default)
Archiving Settings Screen (archive database enabled)
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/or Pruning is very important for two reasons:
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:
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.
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.
Warning: if you disable or enable the Patriot archive database at any point you must disable and re-enable database pruning/archiving from this screen.
|
Archiving Settings Screen (archive database disabled - default)
|
Archiving Settings Screen (archive database enabled)
|
The following settings can be configured on this screen.
| Maximum Number of Signals 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). The special value 0 can be used to allow an unlimited number of signals to be stored, however it is not recommended to leave this value as 0 as it means no signals are ever pruned (or archived). Its recommended this value is set to keep less than 12 months worth of signal history in the current database. |
|---|---|
| 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 Number of Signals 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. The special value 0 can be used to allow an unlimited number of signals to be kept in the archived database, however it is not recommended to leave this value as 0 as you will eventually run into issues with database backups, diskspace limitations etc. |
| 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. |
Click on the Enable Pruning/Archiving button to enable pruning/archiving.
If you need to make changes to any of these settings, make a note of the current settings, click on Disable Pruning/Archiving, re-enter the corrected settings, then press Enable Pruning/Archiving.
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 config setting. The setting is called SignalArchivingRateLimit. The default value is 10. The maximum value which can be used is 100. Please consult Patriot support before adjusting this value.
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.
Its 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 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
Note: 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.
Note: 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.