Unlink a Domain Manager and Enterprise Server via SQL queries

Products

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

Issue/Introduction

Sometimes there are errors when trying to remove a Domain Manager from an Enterprise Server via the DSM Explorer in Client Automation.

Although doing so through the DSM Explorer is the preferred method this document explains the steps needed to remove via SQL queries.

In such cases, a manual removal via SQL scripts can be helpful reestablish the connection between the Domain Manager and the Enterprise server.

Environment

Client Automation - All Versions

All Supported versions of Microsoft SQL Server

Resolution

NOTE: This is a very safe procedure when run properly.

However as with any procedure that modifies the product database, extreme caution must be taken to ensure no data is

lost or compromised that could result in an unintended issue in your environment.

Please read the below very carefully and test before using in a production setting:

 

Procedure to manually Unlink the Domain Manager (DM) from the Enterprise Server (ES):

1. Open DSM Explorer on the DM(s) and unlink all DM replication tasks for DM(s) not involved in this procedure*
2. Please stop CAF on the Enterprise Server and Domain Manager**
3. Open SQL Enterprise Manager and point to the MDB MS SQL server for the ES.
4. A separate connection will need to be made to the DM as well.** Either within the same Manager or via SQL Enterprise Manager on the other machine.
5. Browse to the MDB database in each case, right-click on it and select "New Query"
6. Perform the below for the ES and DM** separately:
   
   *Do this on the DM to be removed as well if that server still exists.

**Only if the DM being removed still exists.

IMPORTANT: Do not attempt the below without a full and validated MDB backup on both(all) servers involved.

If there is a failure, a complete restore of the MDB should be done.

Always have CAF stopped on all Servers involved AND unlink the replication task on all Domains belonging to the Enterprise,

even if they are not involved in this procedure. This is to ensure against MDB writes to the ES MDB being attempted during the process.

Also it will keep your ES MDB backup current as further updates will not be made.

PERFORM THIS STEP ON THE DOMAIN MANAGER IF IT STILL EXISTS:

This is the actual SQL Script that will remove data from your Domain Manager (DM) MDB***:

***NOTE:  Before the below, you can run the query "Select * from CA_N_TIER".

Only one row for the Domain Database server should be listed (and likely one for the ES).

If more than one listing for the DM is returned, contact support immediately

USE MDB
Go
DECLARE @domain_id int
DECLARE domain_id_cursor CURSOR FOR
select domain_id from ca_n_tier
where domain_uuid NOT in
(select set_val_uuid from ca_settings s
where s.set_id = 1)
OPEN domain_id_cursor
FETCH NEXT FROM domain_id_cursor INTO @domain_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
delete from bckfile where object_domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from UNITTYPE where domainid = @domain_id
delete from NCOVERVW where domainid = @domain_id
delete from urc_ab_computer where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from statmod where object_domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from statjob where object_domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from ca_manager_component where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from ca_manager where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from ca_engine where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from ca_discovered_software where sw_def_uuid in
(select sw_def_uuid from ca_software_def where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id))
delete from inv_externaldevice_tree where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from inv_externaldevice_item where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from inv_generalinventory_tree where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from inv_generalinventory_item where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from inv_table_map where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from inv_root_map where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from inv_tree_name_id where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from inv_item_name_id where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from ca_agent_component where server_uuid in (select server_uuid from ca_server where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id))
delete from ca_agent where domain_id = @domain_id
delete from ca_link_dis_hw_user where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from am_external_device where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from ca_discovered_user where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from ca_server_component where server_uuid IN (select server_uuid from ca_server where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id))
delete from ca_server where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from ca_discovered_hardware_network where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from ca_discovered_hardware where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from ca_group_member where group_domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from ca_replication_status
delete from ca_replication_conf
update ca_n_tier set parent_domain_uuid = NULL where domain_uuid IN
(select set_val_uuid from ca_settings s where s.set_id = 1)
delete from csm_property where name like '%repl%'
delete from ca_n_tier where domain_id = @domain_id
FETCH NEXT FROM domain_id_cursor INTO @domain_id
END
delete from LINKJOB where jobid IN (select jobid from NCJOBCFG where joname like '%Replication%')
CLOSE domain_id_cursor
DEALLOCATE domain_id_cursor
GO

PERFORM THIS STEP AGAINST THE ENTERPRISE SERVER MDB DATABASE:

This is the actual SQL Script that will remove data from your Enterprise Server (ES) MDB****:

****NOTE: Replace the one instance of <Enter the name of domain here> below with the name of the Domain Manager being removed from this Enterprise.

USE MDB
DECLARE @domain_id int

-- Please modify the cursor to enter the name of the domain to be unlinked from enterprise

DECLARE domain_id_cursor CURSOR FOR
select domain_id from ca_n_tier
where domain_uuid NOT in
(select set_val_uuid from ca_settings s
where s.set_id = 1) and label like '%<Enter the name of domain here>%'   

OPEN domain_id_cursor

FETCH NEXT FROM domain_id_cursor INTO @domain_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- Do the following with results
-- Delete the information from domain

delete from bckfile where object_domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from UNITTYPE where domainid = @domain_id
delete from NCOVERVW where domainid = @domain_id
delete from urc_ab_computer where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from statmod where object_domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from statjob where object_domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from ca_manager_component where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from ca_manager where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)

update ca_engine set dis_hw_uuid=null where domain_uuid in (select domain_uuid from ca_manager

where domain_uuid in (select domain_uuid from ca_n_tier where parent_domain_uuid is NULL))
delete from ca_engine where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from ca_discovered_software where sw_def_uuid in
(select sw_def_uuid from ca_software_def where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id))
delete from inv_externaldevice_tree where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from inv_externaldevice_item where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from inv_generalinventory_tree where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from inv_generalinventory_item where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from inv_table_map where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from inv_root_map where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from inv_tree_name_id where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from inv_item_name_id where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from ca_agent_component where server_uuid IN (select server_uuid from ca_server where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id))
delete from ca_agent where domain_id = @domain_id
delete from ca_link_dis_hw_user where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from am_external_device where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from ca_discovered_user where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from ca_server_component where server_uuid not in (select server_uuid from ca_server where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id))
delete from ca_server where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from ca_discovered_hardware_network where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from ca_discovered_hardware where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from ca_group_member where group_domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from ca_group_member where member_domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from ca_replication_status where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)
delete from ca_replication_conf where domain_uuid IN (select domain_uuid from ca_n_tier where domain_id = @domain_id)

-- FINAL QUERY
delete from ca_n_tier where domain_id = @domain_id

FETCH NEXT FROM domain_id_cursor INTO @domain_id
END

CLOSE domain_id_cursor
DEALLOCATE domain_id_cursor
GO
 

POST PROCEDURE STEPS:

1. Start CAF on the ES (and DM if valid to do so)
    
2. Open DSM Explorer pointing to the Enterprise Server (You can run a command like "dsmgui manager:<ES Server Name>" if the explorer is not pointed to the ES by default, to save time)
    
3. Browse to the 'Control Panel'=> 'Domains' section and then to 'All Domains'
    
4. Confirm the Domain in question is no longer listed (If for some reason it still is, right click on it and remove it again, there should be no error now*****)
   
   ***** NOTE: If there is any error, take a screenshot and contact support immediately.
    
5. Open DSM Explorer pointing to the/all Domain Manager(s) and re-link their replication tasks, one at a time.
    
6. Verify that no errors are seen. If any problems occur, please contact support.

Additional Information

For additional information on either the supported version of Client Automation or Microsoft SQL Server

Please consult the Client Automation's Compatibility Matrix found at this link:

Supportability Matrix