Moving the CA MDB Database on Microsoft SQL Server

If you have installed Microsoft SQL Server database in your system, perform the following steps:
casm171
If you have installed Microsoft SQL Server database in your system, perform the following steps:
  1. Review CA Service Management Tables to verify the tables that are impacted when you move the CA MDB database on Microsoft SQL Server.
  2. Stop the following
    CA Service Management
     services on both the source and target application servers:
    Some of these services might not be applicable if the associated products or components are not installed in your environment.
    1. Common Admin Service
      :
      • CA Service Management
         Admin
    2. CA SDM
      :
       If you have any shared UNC paths, ensure that you copy the paths from the source system to the target system.
      • CA Service Desk Manager ODBC Data Access
      • CA Service Desk Manager ODBC Agent
      • CA Service Desk Manager Server
    3. CA Service Catalog Services
      :
      • CA Service Accounting
      • CA Service Catalog
    4. CA Asset Portfolio Management Services
      :
      • IIS Admin Service
      • CA Asset Portfolio Management - Data Importer Engine
      • CA Asset Portfolio Management - Event Service
      • CA Asset Portfolio Management - Export Service
      • CA Asset Portfolio Management - HW Reconciliation Engine
      • CA Asset Portfolio Management - LDAP Import Service
      • CA Asset Portfolio Management - Registration Service
      • CA SM Server
    5. Unified Self-Service Services
      :
      • CA Unified Self-Service Jetty Server
      • CA Unified Self-Service Server
    6. CA 
      xFlow Interface
       Services
      :
      • CA 
        xFlow Interface
    7. CA Search Server Services
      :
      • CA Service Management
         Search Server
  3. On the source system, back up the
    mdb
    and
    uss_mdb
    database, and copy themdb back up files to the target system.
  4. (Optional) If you are using a named instance (non-default MDB name), ensure that you have selected the appropriate MDB instance name for the backup.
  5. On the target system, perform the following steps to back up the tables:
    1. Navigate to Microsoft SQL Management Studio, right-click
      MDB
      (or the appropriate MDB instance name applicable for your system), and select
      Tasks
      ,
      Generate Scripts
      .
    2. Click
      Select Specific database objects
      . Take a backup of the
      CA Service Management
       Tables. For more information, see CA Service Management Tables.
    3. Select the
      Tables
      option and choose the appropriate tables. 
    4. Click
      Next
      .
    5. Select
      Advanced
      and navigate to
      Types of Data
    6. Change it from
      Schema Only
      to
      Schema and Data
      , and click
      OK
      .
      By default, your selection is now saved to a file
      %user%\documents\script.sql
      .
    7. Specify different file names for MDB and USS_MDB.
    8. Perform steps
      a-f
       for uss_mdb.
      Two script files are generated.
  6. On the target system, perform the following steps to detach the existing
    mdb and uss_mdb:
    1. Navigate to Microsoft SQL Management Studio, right-click
      MDB
      (or appropriate MDB name),
      Tasks
      ,
      Detach
    2. On the Detach Database screen, select
      Drop Connections
      .
    3. Click
      OK
      .
  7. Restore
    mdb and uss_mdb
    backup on the target system that was copied from the source system. Select
    Overwrite the existing database
    (WITH REPLACE) from the
    Options
    tab.
    Ensure that the database name on the target system is retained.
    Provide read and execute privileges to the mdb.bak file that you copied from the source database to the SQLServerMSSQLUser$$MSSQLSERVER service account on the target MDB server. Failure to grant the read and execute privilege to the mdb.bak file may result in an error that the file folder cannot be located or it does not have the correct permissions. 
    Restore must complete without errors.
  8. Run the following Microsoft SQL auto_fix_user script for usermdbadmin andussdbadmin users:
    • sp_change_users_login 'AUTO_FIX','mdbadmin'
    • sp_change_users_login 'AUTO_FIX', 'apmdba'  (Applicable only if CA APM is installed and upgraded from previous release versions)
    • sp_change_users_login 'AUTO_FIX', 'uapmadmin' (Applicable only if CA APM is installed and upgraded from previous release versions)
    • sp_change_users_login 'AUTO_FIX', 'uapmbatch' (Applicable only if CA APM is installed and upgraded from previous release versions)
    • sp_change_users_login 'AUTO_FIX', 'uapmreporting' (Applicable only if CA APM is installed and upgraded from previous release versions)
    • sp_change_users_login 'AUTO_FIX', 'usmadmin' (Applicable only if CA Service Catalog is installed and upgraded from previous release versions)
    • sp_change_users_login 'AUTO_FIX', 'ServiceDesk' (Applicable only if CA SDM is installed and upgraded from previous release versions)
    • sp_change_users_login 'AUTO_FIX','ussdbadmin’ (Applicable only for USS)
  9. Verify that the mdbadminand ussdbadminusers are now mapped to
    mdb
    /
    uss_mdb
    and have the 
    bulkadmin
    ,
    public
    roles.
  10. On the target system, delete data from the following tables and commit the changes:
    1. Use
      mdb
      and run the following queries to delete data from the required tables:
      Common Tables:
      • delete from al_cdb_comp_installstate_bkup
      • delete from al_cdb_componentinstallstate
      • delete from al_cdb_config_params_backup
      • delete from al_cdb_configurationparameters
      • delete from al_cdb_files
      • delete from ca_application_registration
      CA SDM Tables
      :
      • delete from options
      • delete from usp_servers
      • delete fromsapolicy
      • delete from ci_mdr_provider
      • delete from usp_domsrvr
      • delete from usp_configuration
      • delete from usp_webeng_alias
      • delete from usp_webeng_domsrvr
      • delete from usp_webengine
      • delete from object_promotion (applicable only if
        CA Service Management
         14.1.02 is installed)
      • delete from promo_hist (applicable only if
        CA Service Management
         14.1.02 is installed)
      CA Service Catalog tables
      :
      • delete from usm_configuration
      CA APM tables:
      • delete from al_meta_binary_store
      • delete from al_process_account
      Delete the following CA APM tables, if you have
      CA Service Management
      14.1.02 installed in your system: 
      • delete from al_apmp_def
      •  delete from al_apmp_operation
      • delete from al_apmp_content_types
      • delete from al_apmp_progress_status
      • delete from al_apmp_record_filter
      • delete from al_apmp_run_stage
      CA 
      xFlow Interface
       tables
      :
      • Delete from l1admin_general_config
      CA  Search Server tables
      :
      • Delete from usp_es_servers
    2. Select
      USS_mdb
      and run the following queries to delete data from the required tables:
      • delete from PortalPreferences
      • delete from os_ExternalSource
      • delete from os_ExternalSourceProperties
      • delete from User_
  11. Execute the
    script.sql
    scripts that were created in
    Step 5
    .
    Ignore the warning message, There is already an object named ... in the Database.
  12. (Optional) To disable email notifications, modify the ca contact table query:
    Update ca_contact set email_address = ''
    .
    If USS is installed, disabling the email notifications may cause missing announcements in the target system. We recommend that you add the email addresses for required contacts before performing
    Step 13
    .
  13. Start the services on both the source and the target systems and on the target system, perform the following steps for each product:
    1. Common Admin Service
      :
      CA Service Management
       Admin
    2. CA SDM Services
      :
      Perform the following steps before you start the CA SDM services:
      1. If your environment has customized 
        CA Process Automation
        content, export all the relevant 
        CA Process Automation
        Content from the source 
        CA Process Automation
        server and import to the target 
        CA Process Automation
        server. Complete the required 
        CA Process Automation
        content configuration.
        For more information, see CA Process Automation.
      2. Copy the
        site/mods
        directory from the source CA SDM server to the target CA SDM server.
        This is applicable only if your application environment is customized. This is not applicable for the out-of-the-box (OOTB) content.
      3. Run the
        pdm_configure
        command without selecting the load default data option.
      4. Start the following CA SDM services:
        • CA Service Desk Manager ODBC Data Access
        • CA Service Desk Manager ODBC Agent
        • CA Service Desk Manager Server
      5. Execute the 
        pdm_k_reindex
         command on the CA SDM target system to index knowledge documents that are created and copied from the source system.
      6. Repositories.
        1. Log in to CA SDM as an administrator.
        2. Navigate to 
          Administration tab
          >
          Attachments Library
          >
          Repositories
          .
        3. Open the repositories one by one and update the target CA SDM server name in the Servlet Server and Repository Server fields.
          : Update the CA SDM server name in all the repositories(OOB and Created Repositories).
      7. Attachments.
        • Copy all the attachments using upload Path of all the repositories from a source server to the same path in a target server.
          For Example: ServiceDesk repository upload path is: <NX_ROOT>/site/attachments/default/servicedesk/file on source server. Copy the attachments from a source server to the target server.
    3. CA Service Catalog Services
      :
      Perform the following steps before you start the CA Service Catalog services:
      • If your source system has customized 
        CA Process Automation
        content, export all relevant 
        CA Process Automation
        content from the source 
        CA Process Automation
        server and import to the target 
        CA Process Automation
        server. Complete the required configurations for 
        CA Process Automation
        content.
        For more information, see CA Process Automation.
      • If your source system has customized CA EEM policies, export all the relevant CA EEM policies from the source CA EEM server and import to the target CA EEM server.
        For more information, see Installing Common Components
      • Copy filestore directory from the source CA Service Catalog server to the target CA Service Catalog server.
        This is applicable only if customization is carried out on the application environment. This is not applicable for the OOTB content.
      • Launch the 
        CA Process Automation
        server and access
        SLCM_Globaldataset
        . and validate that the domain and the CA Service Catalog root business unit names are same.
      • Start the following CA Service Catalog services:
        • CA Service Accounting
        • CA Service Catalog
    4. CA Asset Portfolio Management Services
      :
      • IIS Admin Service
      • CA Asset Portfolio Management - Data Importer Engine
      • CA Asset Portfolio Management - Event Service
      • CA Asset Portfolio Management - Export Service
      • CA Asset Portfolio Management - HW Reconciliation Engine
      • CA Asset Portfolio Management - LDAP Import Service
      • CA Asset Portfolio Management - Registration Service
      • CA SM Server
    5. Unified Self-Service Services
      :
      • CA Unified Self-Service Jetty Server
      • CA Unified Self-Service Server
    6. CA 
      xFlow Interface
       Services
      :
      • CA 
        xFlow Interface
    7. CA Search Server Services
      :
      • CA Service Management
         Search Server
      Run the rebuild index on Search Server.
      To Run the rebuild index, execute one of the files based on your operating system:
      • $ES_HOME/search/bin/pdm_es_rebuild_index.bat (Windows)
      • $ES_HOME/search/bin/pdm_es_rebuild_index.sh (Linux)
  14. Verify that
    CA Service Management
     applications are accessible. All applications must be up and running.
  15. Validate that all
    CA Service Management
     Integration scenarios are working seamlessly.