Determine tables replicated during DM to EM Replication and/or via SQL Bridge

Products

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

Issue/Introduction

Client Automation use Replication to replicate data from the Domain Manager to a Enterprise Manager and from an Enterprise back down to a Domain Manager.

SQL Bridge is used to replicate data to a CA product on another server.  

How can I determine what tables are replicated during DM to EM Replication and/or via SQL Bridge, and in what order are these tables replicated?

Environment

CA Client Automation - All Versions

Resolution

For Replication, the tables that will be replicated from one server to another is determined by the information in the ca_replication_conf table of the 'mdb' database. Anything in this table set to have a value in the 'direction' column not equal to -1 will be replicated. If you check this table on the DM and the EM, you will see different values in the 'direction' column for the same tables, as some tables replicate 'down' to the Domain Manager (DM) and some replicate 'up' to the Enterprise Manager (EM).

For SQL Bridge the tables that will be replicated are controlled by a similar table, the ca_syncronization_conf table*.

 

This table also uses the 'direction' column to determine what will be replicated, in the same manner with replication above.

When either of these tasks run, it will first process deletions, meaning anything that has been removed from the source mdb since the last replication run will be marked for deletion on the target mdb during the next run. Once either task completes its deletions, it will then process the Inserts to update the target database.

*For the SQL Bridge, there is an additional, external, portion of the task that invokes 'ContentUtility.exe' in the \CA\DSM\bin\ folder to synchronize the following additional tables:

bit_support
chip_set
ca_source_type
ca_software_type
ca_class_def
ca_class_hierarchy
ca_hierarchy
ca_link_type
ca_contact
ca_location
ca_company_type
ca_company
ca_language
signature_os_group
ca_software_def
ca_software_def_class_def_matrix
ca_category_def
ca_category_member
ca_software_def_types
ca_software_signature
ca_link_sw_def
am_approved_licenses
ca_acme_checkpoint

These tables can be found in the file called 'Content_Table.xml' in the same folder. This external synchronization task is run BEFORE the actual engine SQL Bridge task will run its synchronization. If the Content Utility portion fails, the remaining task will not be started and no replication will occur on the engine side for the tables defined in ca_synchronization_conf table as defined above.

 

The SQL queries below can be run against the source MDB's to determine what tables will be replicated and in what order.

1. Replication
   
   Order of Deletes for Replication
   select * from ca_replication_conf where direction <> -1 order by replication_order desc
   
   Order of Inserts for Replication
   select * from ca_replication_conf where direction <> -1 order by replication_order asc
   
   
2. SQL Bridge
   
   Order of Deletes for SQL Bridge
   select * from ca_synchronization_conf where direction <> -1 order by synchronization_order desc
   
   Order of Inserts for SQL Bridge
   select * from ca_synchronization_conf where direction <> -1 order by synchronization_order asc

As you can see the order for deletions is exactly the reverse of the order of the inserts;

 

This is done logically to prevent violation of any Foreign Key constraints during deletion and insertion.