Share via

How and when is data written (or synchronized) to the Data Warehouse?

  • Article

Back in the MOM days the data was synchronized to the Data Warehouse by means of DTS jobs.  Data would be synchronized from the operational database to the Data Warehouse database on a schedule.  Using the scheduled DTS method of synchronizing data means the Data Warehouse is never really in sync with the operational database.

Well, those days are over.  The synchronization process was redesigned and greatly improved in SCOM, and now happens via workflows which are defined in the core management packs.  If you’re sharp with XML and SQL, you can reverse engineer these workflows to gain a better understanding.

Not all data is synchronized between the operational and Data Warehouse databases.  In fact, only Alert and Discovery data are synchronized.  All other data types are written in parallel to both databases.

If data cannot change once it is created, it is written in parallel to both database.  Since discovery and alert data can change throughout its lifetime, we know that these types of data need to be synchronized from the operations database to the Data Warehouse.

Conversely, event (including state change event) and performance data will never change after it is collected, so these types of data will be written to both databases in parallel.

Note: Not all event and performance data need to be stored in both databases.  These collection rules have two write actions; one for the operational database and one for the Data Warehouse.  If we wanted to collect this data only for reporting purposes, we could save on resources by configuring the collection rule to only write the data collected to the Data Warehouse.

So how and when does synchronization of alert data actually happen?

Alert synchronization is scheduled to run every 3 minutes, which is managed by the Root Management Server.  This rule has an override parameter for interval, but I cannot think of any reason we would need to change this.  Given the default interval, we know there could be a delta of up to 3 minutes between the operational database and Data Warehouse for alert data.

Comments

  • Anonymous
    January 01, 2003
    Hi Sam, As mentioned in the post, the alert synchronization workflow runs every 3 minutes by default.  This means that an alert generated will take up to 3 minutes to reach the Data Warehouse, but should not take any longer.  The alert is not "moved", it is "synchronized".  Grooming dictates how long the alert will stay in the operational and DW databases. By default, alert data will be kept in the Data Warehouse for 400 days.  By default, resolved alerts stay in the operational database for 7 days. -Jonathan

  • Anonymous
    January 01, 2003
    The comment has been removed

  • Anonymous
    February 21, 2010
    Hi, How can I find this workflows in SCOM? I have been looking for an answer on this for quite sometime.eg. when alert created-first detected in console, how long does the same alert kept in OpsDB before moving into DW and how long the same alert will kept in DW by system default. Thanks.

  • Anonymous
    June 14, 2010
    Hi Jonathan, what is the Time-Gap for performance Data. When will Perf-Data written to the DWH? Thanx, Mario