How to Move the MDB From a Local to a Remote Instance of SQL

Products

• CA Client Automation - IT Client Manager
• CA Client Automation

Issue/Introduction

This document describes the steps needed to move an MDB from one system to another without changing the Domain Manager's host name. 

Overview:

The moving of ITCM MDB SQL database involves the following steps

1. Backup  the MDB database from the source  server.

2. Restore the back to the SQL new server

3. Update the database settings to reflect the new SQL information

4. Associate the MDB users and logins

5. Update the comstore settings

6. Verify functionality of ITCM 

 

Prerequisites: 

You should know the following information before starting this procedure:

Make sure the Windows account you are logged in as is a member of the local administrators account and has the SQL Server Role of SYSADMIN. 

Ensure that you know the password for the ca_itrm account, nsmadmin etc.

Note the host name, IP address and FQDN of the SQL server source and target.

gather the SQL instance names and ports being utilized on each server.

Run the following queries and save the results so you can update the database settings

select  *  from  ca_settings

select  *  from  ca_agent

Select  *  from ca_n_tier

 

Always keep a backup of the last working comstore file which is stored under the CA\DSM\agent\ccnf\comstore.enc in case you need to revert comstore changes.

 

Environment

Client Automation - All versions

Resolution

Procedure to Move MDB:

Step 1.    Backup MDB Database from source machine

Do a complete SQL Backup of the existing MDB database

To do this, open SQL studio Management studio and expand databases and right click on MDB and select Tasks, Backup

 

Backup Configuration:

Key areas for the General tab are:

Back up component – This should be database

Destination - this is where the SQL backup file will be placed make it somewhere easy to find. 

 

Backup options

Key items to select for the General tab are:

Back up component – This should be database

Destination - this is where the SQL backup file will be placed make it somewhere easy to find. 

 

Click ok and backup runs

 

Step 2.      Restore mdb 

To start the restore go into SQL Management Studio

Expand databases and right click on , Databases and select Restore Database this opens the restore database screen

In the 'To Database' field, enter: mdb

For more details on SQL procedures for restore to a different server see this link

http://msdn.microsoft.com/en-us/library/ms186390.aspx#SSMSProcedure

 

MDB Restore Options

In the restore there are two tabs, General and  Options

You need to change the option Source for restore to “from device” on the general tab, click add and choose the location where you directed the back up to place the files.

Click on options tab in left hand pane.

  
 

In the options tab there are several very important sections to configure for a successful  restore to the new SQL server.

Note two arrows you must change the restore As field to reflect the new target server (have a drive mapped prior to doing this).

Click ok.

Restore runs 

Step 3.         Update the MDB Database entries on target server

Tables that need to be updated are

•   •     CA_Settings Table
•   •     CA_Agent Table
•   •     CA_N_Tier Table

Steps to update the MDB database via SQL Studio Manager:

Open SQL Studio Manager

Expand databases  -> select mdb

Click on New Query button

If you did not do the prerequisites steps of running the 3 select statements for the tables to update do this now as you will need them when you run the update queries below   The key items for each table that must be replaced are :

•  CA_Settings Table ---> set_val_text

•  CA_Agent Table  ---> agent_area

•  CA_N_Tier Table ---> db_host_name , db_server , db_instance , label

IMPORTANT NOTE: After running the queries make sure that when you run the update statements that you follow the same case as the column value.

Run the 1st Query

   use mdb

        update ca_settings

        set set_val_text = ‘< new SQL Server name >'

        where set_val_text = ‘< old SQL server name >'

Run the 2nd Query

  use mdb

      update ca_agent

      set agent_area = ‘<new SQL Server name in uppercase>'

      where upper(agent_area) = ‘<old SQL server name Uppercase>'

Run the 3rd Query

  use mdb

      update ca_n_tier

      set db_host_name = ‘< new SQL Server name >',

      db_server = ‘<FQDN new SQL Server name >',

      db_instance = ‘<MSSQLSERVER,1433>',

      label = ‘< new SQL Server name >'

      where domain_uuid in (select set_val_uuid from ca_settings where set_id = '1') 

Step 4.     Associate the MDB database users and logins

 

Explanation on users in MDB

NOTE ON USERS

 'ca_itrm'           - Main CA ITCM account to access mdb

 'ca_itrm_ams'   - AMS user

 'nsmadmin'       - Account used to access mdb for the CA Common Services

 'aiowner'          - Asset intelligence account

 'aiuser'             - Asset intelligence account

 ‘cicuser’          - Account Content download uses to update mdb

Associate Database Users and Logins

Confirm that the required logins and MDB database users exist

If not, manually add the users 

 

Login name add users from previous slide

Change to SQL Server Authentication

Set the CA_ITRM account password to: “NOT_changedR11”

Deselect enforce password policy

Change default database to mdb

Click OK

Repeat for all users you need to add.

Now you must associate the users to the database should be run even if the user exist already or if you had to manually recreate logins:

In SQL Enterprise Manager run these queries against MDB re-associate the database users with corresponding logins by issuing the following commands:

    use mdb

         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'

         exec sp_change_users_login 'AUTO_FIX', 'nsmadmin'

         exec sp_change_users_login 'AUTO_FIX', 'aiuser'

         exec sp_change_users_login 'AUTO_FIX', 'aiowner'

         

NOTES: nsamadmin account is needed only if using CCS or Unicenter NSM.

            Aiuser and aiowner are needed only if you are using Asset Intelligence. 

Step 5.     Domain Manager as part of an Enterprise Server

 If the Domain Manager is part of an Enterprise Manager, update the tables in the Enterprise  manager MDB  also:

          use mdb 

                update ca_n_tier

                set db_host_name = '<New DBMS Server Host Name>', where upper(db_host_name) = ‘ Original DBMS Server Host Name>'

                set db_server = '<New DBMS Server FQDN>', where upper(db_server) = '<Original DBMS Server FQDN>'

                set label = '<New DBMS Server Host Name>' where upper(label) = '<Original DBMS Server Host Name>'

If the MDB is not running on the default DBMS instance or the instance name is different on the new DBM server, then this DBMS server must also be updated. The following command updates the DBMS server name in the table:

            use mdb 

                    update ca_n_tier

                    set db_instance = '<New DBMS Server instance>', where upper(db_host_name) = '<New DBMS Host Name>' 

5a.    Enterprise Server having Domain Managers   

An Enterprise Manager, with Domain Managers connected to it, requires updating of the tables in the Domain Manager MDB:

         use mdb 

              update ca_n_tier

              set db_host_name = '<New DBMS Server Host Name>', where upper(db_host_name) = '<Original DBMS Server Host Name>'

              set db_server = '<New DBMS Server FQDN>', where upper(db_server) = '<Original DBMS Server FQDN>'

         set label = '<New DBMS Server Host Name>' where upper(label) = '<Original DBMS Server Host Name>'

If the MDB is not running on the default DBMS instance or the instance name is different on the new DBMS server,

then this DBMS server must also be updated.

The following command updates the DBMS server name in the table:

          use mdb 

                 update ca_n_tier

          set db_instance = '<New DBMS Server instance>', where upper(db_host_name) = '<New DBMS Server Host Name>' 

Step 6.    Update the comstore settings 

NOTE: Do Not Copy and Paste these commands due to the fact it will copy formatting and once copied on the command line will generate a syntax error.

To set the new DBMS server host name

             ccnfcmda -cmd SetParameterValue -ps itrm/database/default -pn dbmsServer -v "<New Host Name or FQDN>"

 To set the new MS SQL Server instance name and listening port (separated by a comma)

              ccnfcmda -cmd SetParameterValue -ps itrm/database/default -pn dbmsInstance -v "<New Instance Name, Instance Listening Port>"

  

To set the DBMS instance to the default instance (for MS SQL Server).

              ccnfcmda-cmd SetParameterValue -ps itrm/database/default -pn dbmsinstance -v ""

  

 To set the new dtsnos host name….

              ccnfcmda –cmd SetParameterValue –ps itrm/dts/dtsnos –pn repository_id –v "<New Host Name>" 

Notes on comstore changes:

The command “ccnfcmda -cmd GetParameterValue -ps itrm/database/default -pn dbmsServer” will show what the current setting is and you should run that prior to issuing the SetParameterValue .

Run the above commands, then verify the current setting and the change is successful.

Important! 

Verify that connectivity to the database with the new host name or fully qualified name (FQDN, server.domain.com) is established from the CA ITCM server,

using the SQL client tools or utilities and ca_itrm credentials.

Resolve all the connectivity issues before making any modifications to comstore settings.

Always keep a backup of the last working comstore file available at client automation root\agent\ccnf\comstore.enc.

How to tell where comstore is pointing to SQL Server hostname: 

          ccnfcmda -cmd GetParameterValue -ps itrm/database/default -pn dbmsServer

 

Step 7.     Update the CIC Settings

For the Content import client to update the signatures, reconfigure to the new MDB. Use the CIC configuration utility UTIL.BAT available in .......\CA\SC\CIC\Bin

1. Open the command prompt and go to the folder \CA\SC\CIC\Bin

2. Remove the old MDB server Configuration.

         util.bat –p <old_mdb_server>

3. Configure new MDB Server.

       util.bat -a <new_mdb_server> 1433 s cicuser <password> sa <sa_password> 

Note: Repository name is the new MDB Server Name.

 

Step 8.     Update DTS Settings

For CA ITCM to connect to the TNG Repository after an MDB name change, update the the repository name by executing the following command on the manager system:

 

Modp–r "<Repository_name>" -u nsmadmin –n "<nsmadmin password>" -q

Step 9.     Verify Functionality

 After doing all of the above you should ensure that the DSM Explorer is working correctly and having no connection issues to the database also ensure asset collect jobs are functional and Software Delivery jobs work. 

Additional Information

Additional notes on other CA products and options:

You may have other options installed like CA CCS, CA Asset Intelligence, Patch Manager that also require changing various settings. For more detail on this, please consult:

How to Move MDB