Transferring Spectrum Report Manager database from one host to another host

Products

• CA Spectrum
• DX NetOps

Issue/Introduction

Transferring Spectrum Report Manager (SRM) database from one host to another host

As hardware updates become available, there may be times you need to move your software products from an older system to a newer one.

Environment

Release: All Supported Releases
Component: SPCRPT - Reporting

Resolution

In order to move the database a few items need to remain the same and disk space will be needed:

1. SpectroSERVER/Landscape names MUST remain the same - otherwise SRM will treat them as new landscapes and report on them separately.
2. The new system must have ample disk space to handle the new database plus some for new data and in the event a repair needs to be done (2x the largest table - Ex. 100GB table, 200GB free space needed to repair)

To backup the database:

1. Shutdown Spectrum Tomcat Service (This will prevent any potential writes to the database while it's backed up)
2. Navigate a bash shell to $SPECROOT/mysql/bin
3. Type:
   
   mysqldump --defaults-file=../my-spectrum.cnf --opt --routines --ignore-table=reporting.v_active_user_model --ignore-table=reporting.v_alarm_activity --ignore-table=reporting.v_ncm_config_diff --ignore-table=reporting.v_security_string_accessibility_by_landscape--ignore-table=reporting.v_user_report_security --compress -uroot -p<passwd> --databases reporting > <dump_log>

This may take a while to complete based on the size of the database and system resources available. It is recommended the system do nothing else but the dump until it completes.

Once this completes you will have a file named <dump_log> (This is whatever you want to call it). This can be zipped up to make the transfer to the new system quicker, as it is just all text inside the file.

Once this file is copied to the new system with the exact version of Spectrum the old system had on it (including Spectrum patches), move it to the $SPECROOT/mysql/bin directory to make things easy.

Once there, follow these steps:

1. Shutdown Spectrum Tomcat Service
2. Navigate a bash shell to $SPECROOT/mysql/bin
3. Type:
   
   mysql --defaults-file=../my-spectrum.cnf -uroot -p<PASSWD> reporting < <dump_log>
   Example: mysql --defaults-file=../my-spectrum.cnf -uroot -proot reporting < reporting_db_backup_2012_10.sql

This will take time according to how much data you have since we are effectively rebuilding the entire database row by row. As above, it is recommended this system does nothing else but the import.

While on this topic, CA also recommends the following:

Anytime the Spectrum modeling database (SSdb) is backed up, the Archive Manager database (DDMDB) and Reporting databases should also be backed up to maintain the three databases synchronization. If an SSdb is reloaded, the other two databases should also be reloaded. This is not always possible because the reporting database takes up so much space, however it is recommended. If the SSdb is reloaded but the others are not you will have an SSdb that does not have certain data the Reporting database may have, like an outage or a newly created model. If you have any follow up questions regarding this process, please call CA Support.

A review should be done to see if the above migration process is the best thing for your business needs. For example, if you only leave 30 days worth of data in Spectrum Report Manager but leave 45 days worth in your Archive Manager DDMDB - moving the database is not recommended since Report Manager can process those 30 days from the DDM. Only if you keep more data than stored in the DDMDB's should you follow the above migration process.

If you do not copy the database over and want to setup Report Manager to process data from a migrated DDMDB follow this process on the SRM host:

1. Navigate to $SPECROOT/bin
2. Type:
   
   ./RpmgrInitializeLandscape <username> <PASSWD> [-skipInitialHistory] [-initHist <#of days>] [-slm] [-all] [<landscape1> <landscape2> ...]
   Example: ./RpmgrInitializeLandscape root root -initHist 45 -all

This will remove all SRM data currently existing in the SRM database and start processing new data from the DDMDBs.

Any questions on this knowledge article please contact CA Support.