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?

Document ID : KB000011010
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

CA Client Automation use CA 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.  

Question:

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
Answer:

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.