CA Service Management

Is it possible to replicate just the contact tables (ca_contact, usp_contact) from Prod to QA using SQL for CA Service Desk Manager 14.1.x release?

I have tried exporting the data using Tasks > Generate Scripts > Select specific tables > Selecting both Data and Schema into a SQL script. Then dropped SQL constraints (reference link shown below) followed by truncate ca_contact table that fails to run due to the following error.

Cannot truncate table 'mdb.dbo.ca_contact' because it is being referenced by a FOREIGN KEY constraint.

https://docops.ca.com/ca-service-management/14-1/en/administering/configure-ca-service-desk-manager/managing-your-database/drop-and-restore-constraints

No, this not a good idea.

If you bring back the entire table your are also bringing the UUID wich may not "fit" with the other data in your MDB.

The error message is generated by at least 35 billions differents tables that reference ca_contact

Almost every object has a last_mod_by attribute.

Simply configure your QA env to connect to LDAP.

The reason for my ask is that we do a replication of entire MDB from Prod to QA then test run some custom SQL scripts to automate the contacts updates from other native source databases (HR appls), which is messing up the contacts table. In order to reduce the efforts of copying entire MDB each time, I'm looking for suggestions or ideas on how to just replicate contact tables from Prod to QA.

And btw, contact update should be done using webservices, not sql queries.

I guess every organization has their custom solution approach towards contacts updates and agreed, webservices is a better approach.

Is there creation of contact or only a sync of existing contact?

For both cases, new contacts creation and existing contact updates as well..

You can create a linked server on your QA with an account that has readonly access to prod and type a query that insert line from PROD to QA if not present and a query that update rows that are present on both env.

BnL, the custom automation solution works except for few edge cases which messes up the contact table, so trying to replicate the problem in QA to resolve these edge case issues and in the process, we need to restore just the contacts table back to previous good state.

why not take a backup of the QA DB on a clean state?

It is way faster to restore than copying Prod on QA.

Yes, this is what I have been doing so far and need to wait on DBA's availability and other internal formalities to get the restore done, so thinking of much shorter and quickest way to just deal with couple of tables rather full restore of cleaned state of QA DB.

If your custom script to sync contacts is modifiable, i would personnaly create a duplicate of ca_contact and usp_contact and test the script against those two tables.

Select * into ca_contact2  from  ca_contact
Select * into usp_contact2  from  usp_contact

good idea will give it a try though custom script does include contacts sync and other bunch of contact linked reference tables. Thank you!

Have a look at this Microsoft article and decide which replication method (between Transnational or Merge) would work best for your requirements to sync contacts from Prod to QA:

Types of Replication | Microsoft Docs 

===

Kind Regards,

Brian

vchinni 

Do you have any additional questions regarding this topic?

If not, please mark one of the provided answers as Correct so that this thread can be closed.