By default, the Perspectium DataSync Agent uses a GUID type field as the primary key for a table. If you require a separate auto-incrementing primary key as the primary key, you can use the directive <custom_primary_key/> and set the name for this column inside the directive. 

For SQLServer, this will also create a non-clustered index for sys_id. To make sure this works, you can look up index ix_psp_old_primary and see if it is set to nonclustered.

(info) NOTE:

  • If a name is not specified, it will default to psp_id
  • If you also included the <primary_key> tag, it will not create the custom primary key (and index if using SQLServer)
  • This applies when creating new tables, existing tables are not affected


Prerequisites

(warning) First, you will need to set up one of the Perspectium DataSync Agents.

(warning) You should also stop running your DataSync Agent before making any Agent configuration changes.

Procedure

To set a custom primary key for your DataSync integration, follow these steps:

Navigate to the directory where you saved your agent.xml file when installing your DataSync Agent.

Open your agent.xml file in a text editing application. Then, locate the <task> directive(s) within your <subscribe> directive, and nest the <custom_primary_key/> directive.

Save the changes you've made to your agent.xml and close the file. Your agent.xml should look similar to the example shown below:

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
   <agent>
      <share/>
      <subscribe>
         <task instances="1">
            <task_name>datasyncforservicenow_to_mysql_subscribe</task_name>
            <message_connection password="encrypted:vlOtU71yu8N/EFIJH85SSBtaIt7qEEfvqiqft9VZyYE=" queue="psp.out.replicator.testqueue" user="admin">https://testperspectium.net</message_connection>
            <instance_connection password="encrypted:vlOtU71yu8N/EFIJH85SSPN9aF0P5/YViVwPEVFcGW4=" user="test.user">https://myinstance.service-now.com</instance_connection>
            <handler>com.perspectium.replicator.sql.SQLSubscriber</handler>
            <decryption_key>The cow jumped over the moon</decryption_key>
            <database_type>mysql</database_type>
            <database_server>localhost</database_server>
            <database_port>3306</database_port>
            <database_user>root</database_user>
            <database_password>encrypted:vlOtU71yu8N/EFIJH85SSMoilKLTeJHQrNZPJ7c5tFU=</database_password>
            <database_parms>characterEncoding=UTF-8 & useSSL=false</database_parms>
            <database_column_max_size>251</database_column_max_size>
            <database>psp_repl</database>
			<custom_primary_key/>
         </task>
      </subscribe>
      <max_reads_per_connect>4000</max_reads_per_connect>
      <polling_interval>5</polling_interval>
      <skip_message_set_processing/>
   </agent>
</config>

After configuring your agent.xml file to skip alteration of database tables, start running your DataSync Agent again.