How to Move / Migrate Derby Monitoring Db to Auditing Db

[CdnConnection]

You have two options:

Options 1: (Exiting BI 4.0 w/ SP4+ Install, migrate Derby Database)

If you have existing data in your Derby trending database, you will need to migrate the Derby database to the auditing database, and then configure the BI platform to record monitoring information in the auditing database. These are the high-level steps you'll need to follow. For details, see the related topics.

• Migrate the Derby database.
• Verify that the auditing database is working, and auditing is running properly.
• Create the monitoring tables in the ADS (optional).
• Configure the SBO files and add alias names.
• Switch to the auditing database.
• Restart the Adaptive Processing Server that hosts the Monitoring Service.
• On the Monitoring Dashboard, ensure that everything works as expected. Verify that these monitoring tables have been created in the database:
  • MOT_MES_DETAILS
  • MOT_MES_METRICS
  • MOT_TREND_DATA
  • MOT_TREND_DETAILS
• Restore Derby Database

Option 2: (New BI 4.0 w/ SP4 Install, No Data in Derby Database)

If you do not have data in your trending database, that is, you have a fresh installation, you do not need to migrate the database; you only need to configure the BI platform to record monitoring information in the auditing database. These are the high-level steps you'll need to follow.

• Verify that the auditing database is working, and auditing is running properly.
• Create the monitoring tables in the ADS (optional).
• Configure the SBO files and add alias names.
• Switch to the auditing database.
• Restart the Adaptive Processing Server that hosts the Monitoring Service.
• On the Monitoring Dashboard, ensure that everything works as expected. Verify that these monitoring tables have been created in the database:
  • MOT_MES_DETAILS
  • MOT_MES_METRICS
  • MOT_TREND_DATA
  • MOT_TREND_DETAILS

Step 1: Migrate existing Derby Database

Logon into CMC > Application > Monitoring Application Properties > Export

For best results Export Data to CSV. If you a cluster BI Platform environment then export data from most heavy used server environment and you should only import the data from one Derby instance. Importing data from multiple Derby instances will result in data inconsistency and is therefore not recommended.

 

Date will be export in CSV format to the default location of the Derby Trending Database folder, located in \SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\Data\TrendingDB.  This is also the same location of all the database scripts to create the tables manually (optional).

BEFORE EXPORT:

AFTER EXPORT:

The following four CSV files are generated in the default Trending Database location; each CSV file is a representation of each table to be created in the monitoring DB:

• DerbyMot_Mes_Details.csv
• DerbyMot_Trend_Data.csv
• DerbyMot_Trend_Details.csv
• DerbyMot_Mes_Metrics.csv

Step 2: Verify Auditing Database Working Properly

Launch MS SQL Server Management Studio to validate Auditing database have been created properly

 

Step 3: - Create Monitoring Tables manually (Optional)

Use the SQL Scripts provided by BI Platform install to create the monitoring tables manually, location

\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\Data\TrendingDB\

\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\Data\TrendingDB

Step 4: - Configure SBO Files

Internally, the Monitoring application uses Connection Server libraries, and the SBO configuration is required for the Connection Server to establish connectivity to the database driver. You need to specify the database driver and its location in the SBO file to establish this connectivity, file location is <Install_Dir>\dataAccess\connectionServer\odbc\<dbType>.sbo

• If the database used for auditing is SAP HANA, the file where the driver needs to be configured is: newdb.sbo
• If the database used for auditing is MS SQL Server, the file where the driver needs to be configured is: sqlsrv.sbo
• For more details on other database type refer to The Platform Administration Guide, location http://help.sap.com/businessobject/product_guides/sbo41/en/sbo41sp4_bip_admin_en.pdf
• Below is an example of changes MUST be made to ALL MS SQL 2008 / 2012 / 2014 database

BEFORE:

 

AFTER:

 

Step 5: - Switch Monitoring to Auditing Database

Logon into CMC > Application > Monitoring Application Properties > Select Use Audit Database

 

•   Select Use Audit Database
• Save & Close

Step 6: - Restart the APS Server used by Monitoring or SIA

• Restart the APS Server being used by Monitoring
  • If you are NOT sure, restart the ALL servers using SIA

Step 7: - Validate the creation of NEW Monitoring Tables

• Validate the creation of Monitoring tables within Auditing Database, if you did NOT create the tables manually using the sql scripts
  • You have 4 – NEW tables created within Auditing Database schema
  • See below

 

Step 8: - Restore exported content to target Audit database

• Restore matching CSV file to matching tables crated in Audit database
• For more details please refer to your Database Administrator