The Call Detail Record (CDR) and Quality of Experience (QoE) data available through the OCS 2007 R2 Monitoring Server role enables very good usage and quality of experience reporting.Â This blog post covers how to estimate the size of the database, andÂ how to manage the database data rentention and purge time.
1) QoE and CDR Database Sizing
Based on a sample Microsoft OCS feature usage model (the same one used for the OCS 2007 R2 Capacity Planning), the CDRÂ database grows 8.8 KB per day, and the QoE database grows 16.8 KB per day.Â Once we know this, we can estimate the database size using this formula:
> Database size (KB) = (DB growth per user per day) * (Number of users) * (Number of days)
For example, the CDR and QoE database in a 3500 OCS user organization would consume a total of 5.3 GB in 60 days ((8.8+16.8) * 3500 * 60).
2) Setting the Data Retention
The default data retention for both CDR and QoE data is 60 days. Every night the purge process will delete any recordsÂ which are older thanÂ this value. The retention settingÂ can be viewed and set in the OCS Administrator Console by doing a right-click | Properties on particular monitoring server in the left-hand tree. The property page looks like the following:
Set the Maximum Days Logged to the number of days that you wish to retain the respective data sets.
- To purge everything, use a value of zero.
- Leave the checkbox unchecked to turn-off purging all together.
- You can also manage these settings through WMI.
3) Setting the Purge Time
Specifying the time of day that the SQL database purges the data cannot be set through the OCS Administrative Console. You need to use WMI to make this setting. The WMI classes to set the QoE and CDR purge times are shown below:
- QoE:Â use the MSFT_SIPQoESetting class and set the PurgeJobStartTime property.Â The value is an integer representing the number of hours after midnight to start the purging (i.e the hour based on a 24 hour clock).Â For example, a value of â€ś1â€ť will start the purging at 1am.
- CDR: Â use the MSFT_SIPCDRServiceSetting class and set the PurgeTime property. This also is an integer value representing the number of hours after midnight to start the purging of outdated records.
These classes are only installed and available on the Monitoring Server. However, you can use the â€ś-computernameâ€ť parameter in WMI provider to remotely view and set value.
For example, the following two lines of Powershell, can be used to remotely set the Purge Time for the CDR database to be 4am:> $CDRObject = Get-WmiObject -class MSFT_SIPCDRServiceSetting -computername <monitoring_server_hostname> | Select-Object > $CDRObject.PurgeTime = 4 > $CDRObject.Put()
Note: if you are using multiple monitoring servers connected to one monitoring database, it is important to not start the purging on each server simultaneously. Â See the TechNet article Microsoft Office Communications Server 2007 R2 Configuring Retention of QoE and CDR Data for more information
- Microsoft Office Communications Server 2007 R2 Configuring Retention of QoE and CDR Data (TechNet)
- Key high-level information about the OCS 2007 R2 Monitoring role: OCS 2007 R2 Monitoring Role FAQ (InsideOCS post)
- Microsoft Office Communications Server 2007 R2Monitoring Support (TechNet)
- MSDN MSFT_SIPQoESetting (New)
- MSDN MSFT_SIPCDRServiceSetting (New)