Using, operating, and maintaining large databases requires special considerations when configuring and using data management policies. This article provides best practices for large databases.

Use Direct Upload and Export

Direct upload and export minimize the impact on the database by relying on the files and system configuration available on the Recording Serves, instead of running expensive database queries.

Ensuring Data Retention Policy order

The policy processing acquires locks on each subject conversation to support parallel execution by default, and then each policy is tested for each conversation record to make sure the policy priority order is kept. This makes the policy configuration error-proof but can put a high load on the database if it contains many conversations. In that case, it is recommended that this type of locking be turned off in the server configuration by setting the Storage Management / Data Retention / Check Policy Order on Call Basis to No. But then the order of the policies should be ensured by some additional policy configuration:

• Conversations older than: for example, if the conversations should not be deleted until they got exported, then for the Delete policy, set Conversations older than to one week later than the export policy. This is not a 100% solution though, because if the export process stopped for more than a week then it will not be effective anymore.
• CDR fields
  • example 1: if the Export should not happen until the files are uploaded/moved to specific storage, set up a new filter so the Storage Target Equal to the desired storage
  • example 2: if a policy should be executed depending on if the Transcode policy already transcoded a conversation, then one can use the Elapsed Time Since Transcoding (UTC) field to filter
• Execute Only After Another Policy Executed / Execute Only After This Export Executed: if the previous options cannot work, then use these options to ensure the order of policy processing.

Note that setting Check Policy Order on Call Basis to “No” will result in bulk conversation locking and processing (1000 conversations per round) and that could prevent simultaneous transcoding on multiple servers.

Recent Than filter to save SQL Server processing

Policy filters can be complicated and so the SQL Server may perform unnecessary processing. In order to avoid that in case of large databases, it is recommended to set up a Recent Than filter for such types of policies. More specifically, if there are records for a long period of time (years), and the majority of the records cannot be ignored based on the nature of the policy (export policy for example), then a Recent Than filter can narrow down the processed records significantly. Obviously, if the processing is stopped for a longer period, then the conversations that moved out from the window will not be processed unless the Recent Than filter is broadened temporarily.