2 - Configuring the reporting module to pull data from a shadow backup database
Saturday, December 3, 2016 4:49 AM

This article details the two available options to configure FocalScope's reporting module to pull data from a shadow backup copy of the FocalScope database.

You can download a PDF version of this article and the stand-alone diagrams in the attachments section at the end of this article.

Available Options

Please note, in both options there are only two SQL servers involved:

  1. An OLTP SQL server hosting the main application database
  2. A Backup SQL server hosting the shadow, read-only database (and the report cache database in 'Option 2')

Option 1: Report cache resides in the main database

Conditions to be aware of for option 1:

  1. Report data is pulled from the shadow database
  2. Report definitions and report cache (temporary data) are stored in the main transactional database

SQL server & databases layout for option 1 (2 SQL servers, 2 databases):

  • OLPT SQL
    • Main application database. Will also store report cached and temporary data
  • Backup SQL
    • Shadow, read-only database, which will be used to pull data for reporting 


Figure 1 - Option 1: Report cache resides in main database

Option 2: Report cache resides in a dedicated database

Conditions to be aware of for option 2:

  1. Report data is pulled from the shadow database
  2. Report definitions are stored in the main database
  3. The report cache (temporary data) is stored in a dedicated database

SQL server & databases layout for option 2 (2 SQL servers, 3 databases):

  • OLTP SQL
    • Main application database
  • Backup SQL

    • Shadow, read-only database, which will be used to pull data for reporting
    • Dedicated reporting database to store report cached and temporary data 
Please noteOption 2 is more difficult to deploy and maintain as it deals with three different databases: main, shadow, and cache. However, this option offers better performance for the main database as it allows offloaded managing of the report cached and temporary data to the backup SQL server, which reduces the amount of I/O on the OLTP SQL server during report generation. An additional instance of FocalScope (just the application) is required for Option 2. This is to provide the required additional process on the application server to keep schema of the dedicated reporting database up to date. Any overhead from this FocalScope instance on the application server is neglectable as it's activated only when the system is upgraded.


Figure 2 - Option 2: Report cache resides in dedicated database