Modify SCOM Data Warehouse Retention Policy
One of my clients wanted to know the default SCOM Data Warehouse data retention policy and wanted to modify the same according to their SCOM environment.
A Data Warehouse is often one of the largest databases supported by an organization. Large databases cost money and time to support. They are difficult to maintain and costs more to backup. They will take much more time to restore in case of a disaster. The larger they get, can impact how long the reports take to complete.
So, rather than keeping everything as per default values, it’s always advisable to adjust the SCOM Data Warehouse retention policy according to your environment.
There are two ways to check and modify the OpsMgr DW retention policy:
2: Using SQL
Data Warehouse Data Retention Policy tool is the safest and easiest way for those who are not familiar using SQL.
The tool can be downloaded from here: dwdatarp.exe
The server on which dwdatarp.exe is running must have access to the OperationsManagerDW along with the user running the tool.
It’s pretty simple to use. Just run it from a command prompt or a PowerShell and choose the parameters as per your requirement.
To check the DW retention policies, run the command with –s and –d parameters.
dwdatarp.exe -s SERVERNAME -d OperationsManagerDW
Here I have already modified most of the default values from 400 to 365 days.
If you want to modify the values you can use –ds –a and –m parameters.
Dwdatarp.exe -s SERVERNAME -d OperationsManagerDW – ds “Performance data set” –a “Hourly aggregations” –m 365
The modification will replicate immediately and data will be groomed during next grooming workflow.
Here we should be aware of the correct tables on OpsMgr DW.
We need to look at the StandardDatasetAggregation table.
1: Login on to OpsMgr DW server.
2: Open SSMS and execute below command:
FROM StandardDatasetAggregation sda
INNER JOIN dataset ds on ds.datasetid = sda.datasetid
ORDER BY DataSetDefaultName
The query will give us following required parameters:
- Dataset Id (GUID)
- AggregationTypeId – The Aggregation Type
- MaxDataAgeDays – The current maximum retention setting.
The AggregationTypeId values represents below:
0 = Raw
20 = Hourly
30 = Daily
So, here we want to modify the retention policy for below highlighted Data Set.
Below is the query which will modify the retention policy for above highlighted Data Set from 60 to 45 days:
update StandardDatasetAggregation set MaxDataAgeDays = 45
If we check the OpsMgr DW retention policy again, we can see the changes are done and data will be groomed during next grooming workflow.
You can select the appropriate option to modify the OpsMgr DW retention policy accordingly.
If you want more information on this you can refer below wonderful blog written by Kevin Holman:
Hope it will help.