Reset replication status for specific tables

Products

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

Issue/Introduction

During Replication or SQL Bridge execution, you may find some data is missing on the database targeted by these tasks.

Many times this can easily be corrected by resetting the replication status on just the tables with the missing data, instead of unlinking the Domain Manager from the Enterprise Manager, or deleting and recreating the SQL bridge task.

Environment

Client Automation- All Versions

Cause

Data is replicated from the Domain Manager to the Enterprise and then marked as replicated.

 

That data will not be re-replicated until the data changes again.

 

In certain cases that data never gets replicated, due to Engine issues, loss of contact with the Enterprise or SQL and any other issue.

 

In these cases, the Domain manager thinks it sent it, so it won't get resent, but the Enterprise never got it, so the tables need to manually be told to resend the data.

Resolution

Below are sample queries you can use to reset the Replication status on a specific table for either Replication or a SQL Bridge:

**Please note, you should backup your MDB's before making any direct updates as a precaution.**

1.   To reset the Replication Status of a specific table you can use the query below on the Domain Manager's mdb,

       if the information is coming from the DM, and substitute the table name you want to reset in the mdb

 

          use mdb
            update ca_replication_status set modified_status=0x0 where
            replication_conf_uuid in ( select replication_conf_uuid from
            ca_replication_conf where direction != -1 and table_name='table_name')

For example, if you are missing information from the "ca_server" table, you would use the query below, substituting "ca_server" where it says table_name:

         use mdb

            update ca_replication_status set modified_status=0x0 where
            replication_conf_uuid in ( select replication_conf_uuid from
            ca_replication_conf where direction != -1 and table_name='ca_server')

 

2.  The concept is the same for the Database Synchronization task(SQL Bridge), except we use different tables.

     For SQL Bridge you can use the query below on the Source mdb to reset the status for just one table:

 

          use mdb
             update ca_synchronization_status set modified_status=0x0 where
             synchronization_conf_uuid in ( select synchronization_conf_uuid from
             ca_synchronization_conf where direction != -1 and  table_name='table_name')

For example if you want to reset the replication status for the "ca_agent" table you can use the query below:

         use mdb          

             update ca_synchronization_status set modified_status=0x0 where
             synchronization_conf_uuid in ( select synchronization_conf_uuid from
             ca_synchronization_conf where direction != -1 and   table_name='ca_agent')

3. If you want to reset the reset the replication task for all tables you can use the respective queries below.

    Please note this will reset the replication status for ALL tables and this will take some time.

    It should take about the same amount of time that your original Replication/SQL Bridge task took the first time it was run

• For Replication:
  
  update ca_replication_status set modified_status=0x0 where replication_conf_uuid in (select replication_conf_uuid from ca_replication_conf where direction != -1)

 

• For SQL Bridge:
  
  update ca_synchronization_status set modified_status=0x0 where synchronization_conf_uuid in (select synchronization_conf_uuid from ca_synchronization_conf where direction != -1)
  
  

4.  In some more severe cases it may be necessary to unlink the Domain Manager from the Enterprise and relink them together to force a full re-sync of the two database, or Delete the SQL Bridge task and recreate it.

     

However this can take time, as it will start replication over for all tables and this may be undesirable. It would be recommended to review the TRC_<EngineName>* logs in Detail mode, from the Engine that is running the replication or SQL bridge tasks to find any errors that are occurring during the Replication or SQL Bridge and address those errors with CA Support to determine the best course of action.