Configuring the reporting module to pull data from backup
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:
- An OLTP SQL server hosting the main application database
- 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:
- Report data is pulled from the shadow database
- 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:
- Report data is pulled from the shadow database
- Report definitions are stored in the main database
- 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 note, Option 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