IMC Data Base Change

[Jannie Hanekom]

In case it’s of any use to anyone else ever stumbling on this discussion thread (as I did):

Take backups.  Of the *entire* system – not just databases.  Before, during and after making changes.  I can’t stress that enough.

Named instances are tricky to deal with.  Although the documentation makes mention of the SQL Browser service being required in order to locate a named instance, I was unable to get it to connect to the named instance until I manually set the TCP port the named instance operates on, and configured the installer to point to that port rather than to 1433/tcp.  Useful tip: The “ERRORLOG” log on the SQL server in the \LOG directory of each instance is invaluable in troubleshooting which instance is being connected to. **EDIT** I also found what appears to be a bug in the reporting component when dealing with named instances.  More at the end of this post. **/EDIT**

The best way to migrate the database is to use the procedure provided by HPE (install a clean instance of iMC on a third server that points to the SQL server and get that working, then copy the correct config files across.)  Manually hacking at configuration files is incredibly tedious and error-prone.  It also has the challenge that you need to manually create the SQL Logins and set their passwords, or use the sp_help_revlogin method.  Using the installer and copying the config files across eliminates all of that.  See https://support.hpe.com/hpsc/doc/public/display?docId=emr_na-c05162581 and https://support.hpe.com/hpsc/doc/public/display?docId=emr_na-c05162582

As I don’t use the sa user for the Deployment Utility, I also had to manually create a “deploy” login on SQL and set its password in the Deployment Utility, so that one is able to see database statistics in the “Environment” tab of the utility

I did find that – in my case at least – copying over the two config files in the documentation was insufficient.  I used the list that “kortemi” provided: **EDIT** For the reporting component, copying across the files does not matter.  The "dataSource.xml" and "<serverip>.xml" files get dynamically generated on iMC startup from server-addr.xml. **/EDIT**

C:\Windows\imc-reserved\instinfo.txt
..\IMC\common\conf\server-addr.xml
..\IMC\deploy\conf\component-env.xml
..\IMC\deploy\conf\dbconfig.xml
..\IMC\dbman\etc\dbman.conf
..\IMC\client\web\apps\imc\reports\iMCCRconfig.xml (did not exist in my installation)
..\IMC\client\web\apps\imc\reportsv2\dataSource.xml
..\IMC\client\web\apps\rptviewer\WEB-INF\repository\data\<serverip>.xml

 _{* (at present, my Reporting capability still does not work.  I'm guessing I missed something somewhere.) **EDIT** Reporting fixed, see end of post **/EDIT**}

For what it’s worth, below are some extracts of how a named instance is configured in the configuration files.  It’s clear that you have ZERO chance of guessing the format correctly.  Installing a temporary copy of iMC is the only way to get these right.

..\IMC\common\conf\server-addr.xml

  <component address="127.0.0.1" id="iMC-ICC">
    <db-config address="MYDBSERVER" dbname="icc_db" instance="IMCDB" password="-105-61-35-7-31-18-210-210-181-198-178-166-177-162-102-142-126" port="1435" type="SQLServer" username="imc_icc"/>
  </component>

 ..\IMC\deploy\conf\component-env.xml 

  <component id="iMC-PLAT">
    <var name="DATABASE_PORT" value="1435"/>
    <var name="DATABASE_ADDRESS" value="MYDBSERVER"/>
    <var name="DATABASE_TYPE" value="SQLServer"/>
    <var name="DATABASE_NAME" value="config_db"/>
    <var name="DATABASE_PASSWORD" value="{CT}|LtT2SCrxTkCb3k7X9q45dA=="/>
    <var name="DEPLOY_IP" value="127.0.0.1"/>
    <var name="JDBC_INSTANCE_NAME" value=";instance=IMCDB"/>
    <var name="DATABASE_INSTANCE" value="MYDBSERVER,1435\IMCDB"/>
    <var name="DATABASE_USER_NAME" value="imc_config"/>
    <var name="INSTANCE_NAME" value="IMCDB"/>
    <var name="VERSION" value="7.3-E0506"/>
    <var name="INNER_VERSION" value="V700R001B06D018"/>
  </component>

 ..\IMC\deploy\conf\dbconfig.xml

<item compId="iMC-PLAT" dbAddress="MYDBSERVER" dbName="config_db" dbType="SQLServer" dbUserName="imc_config" forceDropWhenRemove="false"/>

**EDIT** Fixing Reporting after migrating the database to a named instance: (and probably on a fresh install too!)

After migration, I found my reporting feature did not work.  At first, I thought I needed to add a separate data source in iMC.  I tried that, but compared it to a known-good iMC that was installed with an external database from the start, and found that it wasn't necessary: the "127.0.0.1" data source was quite acceptable.

What I did find was that the "datasource.xml" and "127.0.0.1.xml" files in the report folder contained only the SQL Server name and port number, NOT the instance name, like so:

...<url>jdbc:sqlserver://MYDBSERVER:1435;databaseName=report_db</url>...

Editing this file directly is futile, as it's automatically overwritten again when you restart iMC for the change to take effect.  I eventually found that it's getting the details from server-addr.xml. However, in that file, the instance name was indeed specified, as it was for all the other DB's (see "instance=" parameter.)  It seems the parser that builds the "datasource.xml" and "127.0.0.1.xml" ignores this parameter...

  <component address="127.0.0.1" id="iMC-REPORT">
    <db-config address="MYDBSERVER" dbname="reportplat_db" instance="IMCDB" password="-105-61-35-5-31-250-237-242-184-229-183-156-183-151-132-142-126" port="1435" type="SQLServer" username="reportplat"/>
    <db-config address="MYDBSERVER" dbname="report_db" instance="IMCDB" password="-105-61-35-38-247-15-18-245-240-223-210-196-159-153-141-115-95-83-79-61-62-46" port="1435" type="SQLServer" username="report"/>
  </component>

I then changed the server-addr.xml file to read as follows:

  <component address="127.0.0.1" id="iMC-REPORT">
    <db-config address="MYDBSERVER\IMCDB" dbname="reportplat_db" instance="IMCDB" password="-105-61-35-5-31-250-237-242-184-229-183-156-183-151-132-142-126" port="1435" type="SQLServer" username="reportplat"/>
    <db-config address="MYDBSERVER\IMCDB" dbname="report_db" instance="IMCDB" password="-105-61-35-38-247-15-18-245-240-223-210-196-159-153-141-115-95-83-79-61-62-46" port="1435" type="SQLServer" username="report"/>
  </component>

When I started iMC again, the "datasource.xml" and "127.0.0.1.xml" files were built "correctly", and my reporting started working again:

...<url>jdbc:sqlserver://MYDBSERVER\IMCDB:1435;databaseName=report_db</url>...