Skip to Content

How to Move / Migrate Derby Monitoring Db to Auditing Db

August 25, 2014 | 2,548 Views |
 4.0bi 4.0bi platformbi4bi4 upgradebi4.0bo 4.0
Follow

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.

 
MograteDerby_03.png

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:

MograteDerby_04.png

AFTER EXPORT:

MograteDerby_05.png

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

  MograteDerby_06.png

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\

MograteDerby_04.png

\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

MograteDerby_07.png

  • 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:

  MograteDerby_08.png

AFTER:

  MograteDerby_09.png

 

Step 5: – Switch Monitoring to Auditing Database

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

  MograteDerby_10a.png

  •   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

  MograteDerby_11a.png

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

  MograteDerby_05.png

MograteDerby_11.png

To report this post you need to login first.

9 Comments

You must be Logged on to comment or reply to a post.

    1. Ajay Gupta Post author

      Juliet,

           You need update the sqlsrv.sbo file for ALL MS SQL Server related db, that includes MS SQL 2008 / 2012 / 2014.

      Regards,

      Ajay


      (0) 
      1. Brian Kudera

        Thank you, yes… it does seem that you do indeed need to update the alias for  all SQL Server entries in the sbo file. I tripped over this for quite awhile as I only made the changes per the screenshot above included in this document (was only highlighting changes made to the SQL Server 2008 section).

        To prevent others from making this same mistake, perhaps your “After” screenshot in Step 4 could be updated?

        Thank you!!

        (0) 
  4. Brian Kudera

    We’ve recently moved from SQL Server 2012 to SQL Server 2014.

    Needless to say, my monitoring application has seem to become fubared!

    Before I submit an incident to SAP, any recommendations? Has anyone got this working on SQL Server 2014 and if so, what does your sqlsrv.sbo config file look like?

    I’ve confirmed that the alias as mentioned above is listed in ALL databases, including the section for SQL Server 2014.

    (0) 

Leave a Reply