How to move the ITCM database (mdb) to a different server

Document ID : KB000107018
Last Modified Date : 18/07/2018
Show Technical Document Details
Question:
What are the steps to move the ITCM database to a different server, and reconfigure the ITCM application server for the new database location?
Environment:
Client Automation (ITCM) -- any version
Answer:
1- Run the following on the ITCM application server, in order to obtain the current values for the database configuration:
ccnfcmda -cmd GetParameterValue -ps itrm/database/default -pn dbmsserver
ccnfcmda -cmd GetParameterValue -ps itrm/database/default -pn dbmsinstance
ccnfcmda -cmd GetParameterValue -ps itrm/database/default -pn dbname
ccnfcmda -cmd GetParameterValue -ps itrm/database/default -pn dbuser


2- Before moving the database, stop the CAF service on the ITCM application server:
caf stop

3- Move the database server.  In the case this is not being done by a DBA, there are essentially two approaches:

a) Detach and attach.
These are Microsoft SQL server specific tasks.  You are "detaching" the database from the current server, which will allow you to move the associated files to the new database server, where you can "attach" the database.

b) Full backup and restore.
In this method, you run a full database backup, to file.  Copy the full database backup to the new server, where you restore it, effectively recreating the entire database on the new server.

Either method is fine-- whatever gets the database over to the new server.

Once the database is restored, you will need to re-link the database user accounts, to the SQL server level:
exec sp_change_users_login 'AUTO_FIX', 'ca_itrm';
exec sp_change_users_login 'AUTO_FIX', 'ca_itrm_ams';
exec sp_change_users_login 'AUTO_FIX', 'cicuser';


Note: You may have to manually recreate the database user accounts (e.g. ca_itrm, ca_itrm_ams, cicuser) on the SQL server.  Make them all public accounts, and you can leave the default database as "master".  Then run the above three statements, to re-link the server accounts, to the existing database accounts, which will merge their configurations and permissions automatically.

4- Update the ITCM application server for the new location of the database:
ccnfcmda -cmd SetParameterValue -ps itrm/database/default -pn dbmsserver -v "newValue"
ccnfcmda -cmd SetParameterValue -ps itrm/database/default -pn dbmsinstance -v "newValue"
ccnfcmda -cmd SetParameterValue -ps itrm/database/default -pn dbname -v "newValue"


Notes:
- The itrm/database/default/dbuser parameter should not be changing-- will be "ca_itrm" both before and after the move! 
- Be sure to match the formatting for the new values, with the formatting from the original values from step 1.

5- Update the dbo.ca_n_tier table in the ITCM database:

a) Check the current configuration values in the database:

select label, db_host_name, db_server
from ca_n_tier
where domain_uuid in (select set_val_uuid from ca_settings where set_id=1)


b) Update these configuration values, with the new values, following the same formatting:

update ca_n_tier
set label='newValue',
db_host_name='newValue',
db_server='newValue'
where domain_uuid in (select set_val_uuid from ca_settings where set_id=1)


6- Start the CAF service on the ITCM application server:
caf start