Command Shell Examples
Useful SQL Queries
How and when is data written (or synchronized) to the Data Warehouse? - Jonathan Almquist on Operations Manager - Site Home - TechNet Blogs

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

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

  • Comments 4
  • Likes

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.

I do not moderate this blog anymore. If you have a question regarding this post, send me a message.

Comments
  • 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.

  • 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

  • Hi Jonathan,

    what is the Time-Gap for performance Data. When will Perf-Data written to the DWH?

    Thanx,

    Mario

  • Hi Mario,

    Performance data is written immediately.  It is near real-time.  This doesn't mean you can run reports of this raw data immediately, however, because performance data needs to be aggregated before we can run any type of report that we deliver out of box.  In this case, you'll need to wait at least an hour before you'll see hourly aggregates display in a report.  You need to wait at least one day before your daily aggregate reports show data that is written today.

    Conversely, if you really wanted to report on real-time performance data, you could author a custom report to query the raw perf views.  Sometimes there is a good reason to do this, but usually this is not necessary.

    -Jonathan