For optional advance configurations for your DataSync Agent, contact Perspectium Support if you have any questions.


(warning) First, you will need to install a DataSync Agent configured to your use case.

(warning) You will also need to create a ServiceNow dynamic share with an update trigger or create a ServiceNow bulk share.

(warning) Finally, make sure to stop running your DataSync Agent before making any Agent configuration changes.







Agent Column Case Configuration

The DataSync Agent supports forcing of the column name to a configured case. This configuration must be done prior to the Agent creating the table. Two directives are available for inclusion within the agent.xml configuration file. The chosen directive must be placed within the <task> tag.

You would use either of these directives in your agent.xml: 


<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
    <agent>
        <subscribe>
            <task>
                <task_name>example_subscribe</task_name>
                <message_connection password="encrypt:XXXX" user="XXXX" queue="..." >amqp://example.perspectium.net</message_connection>
                <instance_connection password="encrypt:XXXX" user="XXXX">http://example.service-now.com</instance_connection>
                <handler>com.perspectium.replicator.sql.SQLSubscriber</handler>
                <decryption_key>The cow jumped over the moon</decryption_key>
                <database_type>sqlserver</database_type>
                <database_server>MyAddress</database_server>
                <database_port>1433</database_port>
                <database_user>XXXX</database_user>
                <database_password>XXXX</database_password>
                <database_parms></database_parms>
                <database>psp_repl</database>
 
                <uppercase_columns/>
 
            </task>            
        </subscribe>
        <max_reads_per_connect>2000</max_reads_per_connect>
        <polling_interval>5</polling_interval>    
    </agent>
</config>


The default (none of these directives) simply uses the original case of the column. If you happen to specify both, uppercase will be used.

(info) NOTE: This directive affects column names only. It does not affect table names. 


↑ Go to top of page



Change data type

To ensure multibyte or foreign characters are saved properly for string column types, the Agent creates column types that can store unicode string data such as NVARCHAR (MySQL, MS SQL Server, etc). You can use other data types other than N type fields by editing the databases.xml.

(warning)  Before you begin, request the databases.xml file for your DataSync Agent by contacting Perspectium Support.


  1. In the databases.xml file, open the file in a text-editing application and locate the configurations for your database type <database_type><your database></database_type>.

  2. Change the data types to your configuration inside the <mapping> tag.

    For example, if you have an Oracle database and you want to use CLOB instead of NCLOB, change

    <mapping sn_type="2011" db_type="2011">NCLOB</mapping>

    to

    <mapping sn_type="2011" db_type="2011">CLOB</mapping>


  3. Save the databases.xml file and put the file within the conf directory that was created upon installation of your DataSync Agent.



↑ Go to top of page



DataSync Agent field type mappings

To ensure data is formatted correctly, the DataSync agent will map ServiceNow field types to Database field types. Consult the table below to see how ServiceNow field types are mapped for supported databases.


ServiceNow Field TypeDatabase Field Type
address

NVARCHAR

auto_increment

BIGINT

auto_number

NVARCHAR

boolean

BIT

catalog_preview

NVARCHAR

char

NVARCHAR

choice

NVARCHAR

collection

NVARCHAR

color

NVARCHAR

color_display

NVARCHAR

composite_name

NVARCHAR

compressed

NVARCHAR

condition_string

NVARCHAR

conditions

NVARCHAR

counter

NVARCHAR

currency

FLOAT

date

DATE

datetime

DATETIME2

day_of_week

BIGINT

days_of_week

NVARCHAR

decimal

FLOAT

default

NVARCHAR

document_id

NVARCHAR

documentation_fieldNVARCHAR
domain_idNVARCHAR
domain_number

FLOAT

domain_path

NVARCHAR

double

FLOAT

due_date

DATETIME2

email

NVARCHAR

email_script

NVARCHAR

external_names

NVARCHAR

field_list

NVARCHAR

field_name

NVARCHAR

float

FLOAT

glide_action_list

NVARCHAR

glide_date

DATE

glide_date_time

DATETIME2

glide_duration

DATETIME2

glide_encrypted

NVARCHAR

glide_list

NVARCHAR

glide_percise_time

NVARCHAR

glide_time

DATETIME2

glide_var

NVARCHAR

GUIDNVARCHAR
html

NVARCHAR

html_script

NVARCHAR

id

NVARCHAR

image

NVARCHAR

index_name

NVARCHAR

int

NVARCHAR

integer

BIGINT

integer_date

BIGINT

integer_time

BIGINT

internal_type

NVARCHAR

ip_address

NVARCHAR

journal

NVARCHAR

journal_input

NVARCHAR

journal_list

NVARCHAR

long

NVARCHAR

mask_code

NVARCHAR

metric_absolute

FLOAT

metric_counter

FLOAT

metric_derive

FLOAT

metric_gauge

FLOAT

mid_config

NVARCHAR

month_of_yearBIGINT
multi_small

NVARCHAR

multi_two_lines

NVARCHAR

name_values

NVARCHAR

nl_task_init

BIGINT

number

BIGINT

numeric

BIGINT

order_index

BIGINT

password2

NVARCHAR

password

NVARCHAR

percent_complete

FLOAT

phone

NVARCHAR

ph_number

NVARCHAR

phone_number

NVARCHAR

phone_number_e164

NVARCHAR

picklist

NVARCHAR

price

FLOAT

radio

NVARCHAR



↑ Go to top of page



Enable upsert to a database

For .bulk messages, the DataSync Agent will query for a record to see if it exists and if so, update that record.  Otherwise if the record doesn't exist, an insert will occur. However for databases that support it, the Agent can perform an upsert action where updating or inserting is done in one action to help improve performance on large tables that have hundreds of millions or billions of records. To enable this, you will need to use the <enable_upsert/> directive.

Enabling upsert is only available for SAP Hana.



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

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

  3. 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>
	<truncate_utf8/>
      <share/>
      <subscribe>
         <task instances="1">
            <task_name>test_hana2_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>hana</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>
            <skip_alter/>
			<enable_upsert/>
         </task>
      </subscribe>
      <max_reads_per_connect>4000</max_reads_per_connect>
      <polling_interval>5</polling_interval>
      <skip_message_set_processing/>
   </agent>
</config>


  4. Start running your DataSync Agent again.




↑ Go to top of page



Excluding column DataSync Agent configuration

The DataSync Agent supports excluding columns from replication. Configuring columns to be excluded should be done prior to the Agent creating the table, which contains the excluded column for clarity. However, it can be done after with no ill affect. That column will no longer be populated.

You can configure multiple columns to be excluded. To set this up you will have one <exclude_columns>…</exclude_columns> set of tags. Within the tag, you will define each column to be excluded by individual <exclude_column>…</exclude_column> tags. This will be placed within the <subscribe> element located in the agent.xml configuration file.

(info) NOTE: The preferred approach for excluding columns from being replicated is to leverage capabilities of the producer, such as Perspectium Views, which provides the ability to define which columns should be replicated.

In the example below, the necessary configuration is added to exclude the columns:

  • fx_price

  • sys_tags

  • sys_translated_text

Currently, when a column is excluded, it is applied to every table which may contain the column.


<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
    <agent>
        <subscribe>
            <task>
                <task_name>example_subscribe</task_name>
                <message_connection>amqps://example.perspectium.net</message_connection>
                .
                .
                .
            </task>
  
            <exclude_columns>
                <exclude_column>sys_translated_text</exclude_column>
                <exclude_column>fx_price</exclude_column>
                <exclude_column>sys_tags</exclude_column>
            </exclude_columns>
  
        </subscribe>
  
        <max_reads_per_connect>4000</max_reads_per_connect>
        <polling_interval>5</polling_interval>
    </agent>
</config>



↑ Go to top of page



Set up a Highly Available Agent

Perspectium DataSync Agents support an “exclusive consumer” mode that provides a Highly Available Agent solution while maintaining the order of messages in a queue. This active-passive Highly Available approach will exclusively allow only one DataSync Agent to actively read messages from a queue. Other DataSync Agents that passively try to connect to the queue will only be given access to the queue if another DataSync Agent is no longer connected.

DataSync Agents use the queue in the message connection and locks that queue for exclusive use. If multiple tasks are made using the same message connection, only one subscriber task will be created and used. When running another DataSync Agent, a warning will be logged in the perspectium.log file indicating that the queue is in exclusive use and that consumer wait time will be increased (default is to increase to 30 seconds). Once the first running DataSync Agent is stopped or fails, the backup DataSync Agent will start consuming messages when the queue becomes available and the consumer wait time will be reset.

(info) NOTE: The Highly Available Agent is only available when your DataSync Agent is set to connect to the Perspectium Mesh via the AMQP/AMQPS protocol (HTTP/HTTPS is not supported).


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

  2. Open your agent.xml file in a text editing application and locate the <message_connection> tag, which should be nested within <task>. To the <message_connection> tag, add an attribute named exclusive and set its value to true.

  3. Within the <task> tag, you can optionally nest a tag named <consumer_sleep_time> whose value will indicate the number of milliseconds that a passively connecting DataSync Agent should wait before attempting to connect again following a failed connection.

  4. Save the changes you've made to your agent.xml and close the file. An example agent.xml configuration for a Highly Available DataSync Agent is shown below:

    <?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
    <config>
     <agent>

        <max_reads_per_connect>2000</max_reads_per_connect>
       	<polling_interval>15</polling_interval>

        <subscribe>
            <task>
                <task_name>ticket_table_replication</task_name>
                <polling_interval>30</polling_interval>

                <handler>com.perspectium.replicator.sql.SQLSubscriber</handler>
                <decryption_key>The cow jumped over the moon</decryption_key>

                <message_connection queue="psp.out.replicator.example" password="encrypted:iTOCp4deP7rJNgFkf2AEMA==" user="exampleuser"  use_basic_consume="true" exclusive="true" >amqp://acme.perspectium.net</message_connection>
                <instance_connection user="example.user" password="examplepassword">https://myinstance.service-now.com</instance_connection>
                <consumer_sleep_time>50000</consumer_sleep_time>
                <database_type>mysql</database_type>
                <database_server>localhost</database_server>
                <database_port>3306</database_port>
                <database_user>root</database_user>
                <database_password>encrypt:mypassword</database_password>
                <database_params/>
                <database>mydatabase</database>
                <skip_database_creation/>
            </task>
      </subscribe>

   </agent>
  </config>


  5. Start running your DataSync Agent again.



↑ Go to top of page



Set up temporal data

To compile timestamped "snapshots" of your ServiceNow data, you can configure your DataSync Agent with the <temporal> directive. <temporal> will allow you to indicate times when your record's data is/was valid from and when the data is/was valid to.

(info) NOTE: To set up a temporal data, the table you are syncing data to must not contain any records (i.e., Temporal data cannot be captured for tables that are already being synced to a database with a DataSync Agent).

WARNING! If you have already configured an integration with a DataSync Agent and have been saving records in a database, but you would now like to enable or disable temporal replication, update the value within the agent.xml's <database> directive to a new database. Otherwise records will not be processed properly.

The following are a list of database the temporal agent supports: 

  • Oracle
  • MySQL
  • MSSQL
  • SAP Hana 2.0
  • PostGresSQL




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. To use the default configurations for syncing temporal data (columns will be named psp_valid_from and psp_valid_to, the maximum psp_valid_to value will be 9999-01-01 01:01:01, and the archived psp_valid_to value will be 8888-01-01 01:01:01), add the <temporal/> self-closing tag anywhere within the <task> tag. An example of this agent.xml is shown below. Otherwise, to create custom configurations for your temporal database, see Step #3.

Example agent.xml for default temporal data configurations:

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
    <agent>
        <share/>
        <subscribe>
            <task instances="1">
                <task_name>test_subscribe</task_name>
                <message_connection password="encrypted:vlOtU71yu8N/EFIJH85SSBtaIt7qEEfvqiqft9VZyYE=" queue="psp.out.replicator.test" use_basic_consume="true" user="admin">amqps://test.perspectium.net</message_connection>
                <instance_connection password="encrypted:vlOtU71yu8N/EFIJH85SSPN9aF0P5/YViVwPEVFcGW4=" user="admin">https://mycompany.service-now.com</instance_connection>
                <handler>com.perspectium.replicator.sql.SQLSubscriber</handler>
                <decryption_key>This is my decryption key for testing</decryption_key>
                <database_type>mysql</database_type>
                <database_server>localhost</database_server>
                <database_port>3306</database_port>
                <database_user>testuser</database_user>
                <database_password>testpassword</database_password>
                <database_parms>characterEncoding=UTF-8 & useSSL=false</database_parms>
                <database_column_max_size>251</database_column_max_size>
                <database>psp_repl</database>
                <temporal>
                    <columns>
                        <column column_type="93" column_size="32" type="from" primary_key="false">psp_from</column>
                        <column column_type="93" column_size="32" type="to">psp_to</column>
                    </columns>
                    <max_datetime>2030-12-31 00:00:00</max_datetime>
                    <timestamp_difference>5</timestamp_difference>
                </temporal>
				<archive_datetime>2050-12-31 00:00:00</archive_datetime>
            </task>
        </subscribe>
        <max_reads_per_connect>4000</max_reads_per_connect>
        <polling_interval>5</polling_interval>
        <skip_message_set_processing/>
    </agent>
</config>



Create custom configurations (optional)

Directive Attribute(s)Description
<max_datetime>

Customize your temporal database table's maximum date and time stamp in the psp_valid_to column. 

<max_datetime>2030-12-31 00:00:00</max_datetime>

Replace the date and time stamp with any value you would like to assign for custom configuration.

(info) NOTE: If records already exist in your temporal database table and then the <maxdatetime> value is changed, any records that were previously timestamped in the psp_valid_to column will not be updated to reflect the newly entered <max_datetime> value.

<archive_datetime/>

Customize your temporal database table's archive date and time stamp in the psp_valid_to column.

<archive_datetime>2050-12-31 00:00:00</archive_datetime>

Replace the date and time stamp with any value you would like to assign for custom configuration.

The date and time stamp is applied to temporal rows when the agent consumes .archive messages. This marks the row as “archived” and helps with querying for archived records in the database tables.

<disable_archive/>
This will disable <archive_datetime/> functionality and skip processing .archive messages
<column>

type="from"

type="to"

Customize the column names for the psp_valid_from and psp_valid_to columns by modifying the values within the <column type="from"> and <column type="to"> directives, respectively.

<column type="from">psp_valid_from</column>
<column type="to">psp_valid_to</column>


You can setup precision fractional seconds for your temporal columns timestamps. In your agent.xml in the temporal tag, add the attribute precision_size. For example, <precision_size="3"> will result in a timestamp similar to "2018-09-08 17:51:04.123" whereas <precision_size="4"> will result in a timestamp similar to "2018-09-08 17:51:04.1234".


<column>primary_key=”true”

Customize which temporal columns are primary keys by modifying the values within <column primary_key=”true”>. By default, both temporal columns are primary keys. However, they both cannot be non-primary keys, meaning both columns cannot have <column primary_key=”false”>. This is so that it can still insert temporal entries into the database and the database does not complain about duplicate entries.

<column primary_key="false">psp_from</column>


<timestamp_difference>

Customize a time period between temporal entries by adding <timestamp_difference> inside <temporal>. 

<temporal> 
	<timestamp_difference>1</timestamp_difference>
</temporal>

The value will be read as seconds, so if you want 1 microsecond, you would use 0.000001. The smallest it can go is nanoseconds. By default, the time period is set to 1 millisecond to keep times between entries in order.

(warning) WARNING: It is not recommended to input a large number in <timestamp_difference>. Doing so would cause the timestamped entries to not be in order as it relies on when the record is processed.

<skip_duplicates/>

Skip inserting duplicate entries by adding directive. <skip_duplicates/> inside <temporal>. 

(info) NOTE: This may affect the agent’s performance as it checks with the database and does a comparison every time a message comes in.

<temporal> 
	<skip_duplicates/>
</temporal>


<skip_duplicates/>

check_all_fields="true"

Check the fields values if the updated time is the same from the previous share. 

<temporal> 
	<skip_duplicates check_all_fields="true"/>
</temporal>


<update_on_bulk/>

Use to change the temporal behavior with .bulk messages. With this attribute set to true, the Agent will do the following when a .bulk message is received:

1) Check if the sys_updated_on value of the message is different than the latest temporal record in the table.

2) If the value is different, insert a new temporal record.

3) If the value is the same, update the latest temporal record.

This is useful for when new fields are added to a table with new default values but the record itself hasn't been changed otherwise. This feature only applies to .bulk messages as .insert and .update messages will always insert new temporal records.


The default value is false if this attribute is not specified in which case the Agent will have default temporal behavior and always insert new temporal records. 

(info) NOTE: Executing multiple shares for the same table at the same time resulting in multiple messages for the same record may create duplicates or discrepancies.

<temporal> 
	<update_on_bulk/>
</temporal>




Save the changes you made to your agent.xml file and close the file.


Log into the database that your DataSync Agent is sharing data to, and confirm that psp_valid_from and psp_valid_to timestamps are being created and that the psp_valid_to timestamp is updated whenever a change is made to the same record.

(info) NOTE: By default, the primary keys for the subscribed table your database will be a composite key comprised of the sys_id value, from column value, and to column value.





↑ Go to top of page



Share Table Schemas to a database

By default, table schemas are created by passing your ServiceNow instance username and password into the <instance_connection> tag of the agent.xml file created upon installation of the DataSync Agent. Alternatively, you can send ServiceNow table schemas directly to a database that your DataSync Agent replicates to.

(info) NOTE: Table schemas can be shared through bulk share only.


  1. Navigate to the directory in which your agent.xml file was saved upon installation of the DataSync Agent. Then, open the agent.xml file in a text editing application.

  2. Delete the <instance_connection> tag. Then, within the <task> tag, add a self-closing <use_cache/> tag.

    Your agent.xml file will look like the following example:

    <?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
  <agent>
    <subscribe>
      <task>
        <task_name>example_subscribe</task_name>
        <message_connection password="encrypted:XXXXX" queue="psp.out.replicator.example" use_basic_consume="true" user="exampleUser">amqps://example.perspectium.net</message_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>exampleHost</database_server>
        <database_port>3306</database_port>
        <database_user>exampleUser</database_user>
        <database_password>encrypted:XXXXX</database_password>
        <database_params>characterEncoding=UTF-8</database_params>
        <database>psp_repl</database>
 
        <use_cache/>
      </task>
    </subscribe>
    <max_reads_per_connect>4000</max_reads_per_connect>
    <polling_interval>5</polling_interval>
    <skip_message_set_processing/>
  </agent>
</config>


  3. Log into your ServiceNow instance. In ServiceNow's left side navigation window, navigate to Perspectium > Perspectium Core > Shares > Bulk Share > View Bulk Shares. Then, click the timestamp next to the name of the bulk share table schemas for or create a new one.

  4. Check Advanced. Once that option is checked, an Advanced tab will appear with the rest of the selection tabs. 

  5. Check Share Schema. Once that option is checked, the Target Application field will appear. In that field, select DataSync Agent.

  6. Click the Save or Update button at the bottom of the form to save the configurations for your bulk share.



↑ Go to top of page



Skip altering of database tables

By default, your Perspectium DataSync Agent will make changes to your database table(s) so that the table(s) in your application match with your database. For example, you DataSync Agent will add database columns and increase the size of database columns to ensure the complete syncing of data. You can, however, turn off this functionality by adding the <skip_alter/> directive within your agent.xml configuration file.


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

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

  3. Save the changes you've made to your agent.xml and close the file. 

    <?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>
			<skip_alter/>
         </task>
      </subscribe>
      <max_reads_per_connect>4000</max_reads_per_connect>
      <polling_interval>5</polling_interval>
      <skip_message_set_processing/>
   </agent>
</config>



  4. Start running your DataSync Agent again.



↑ Go to top of page



Specify Working Directories for Agent Files

By default the conf, logs and perspectium_data directories will be saved in the directory where the DataSync Agent is installed. However, you can specify a different “working directory” for these files/directories.

The directories that can be moved to a different working directory contain the following files:

  • conf - The configuration directory holds the agent.xml and log4j2.xml configuration files
  • logs - The directory that holds the Agent's log files
  • perspectium_data - The directory that holds temporary data while the Agent runs.  By default, the perspectium_data directory is created in the bin directory. The perspectium_data directory is generated automatically when the Agent starts.

(info) NOTE: You will still need to keep a conf directory in the directory where the Agent is installed. However, in the new working directory that you specify you must also create a conf directory containing your agent.xml and log4j2.xml files. The perspectium_data directory will be generated in the first level of the working directory alongside these two directories when the Agent starts.


  1. Locate the agent.bat file in the directory where the Agent was installed.

  2. Open the agent.bat file in your preferred text editing application and locate the following line in the file:

    SET JAVA_ARGS=-Xmx%2 -Dfile.encoding=UTF-8 -Dlog4j.configurationFile=“%~dp0..\conf\log4j2.xml” -Dcom.perspectium.working.directory=“DIRECTORY_PATH” -classpath “%~dp0..\jars*”;“%~dp0..\extlib*” com.perspectium.replicator.Replicator

    Replace this line with:

    SET JAVA_ARGS=-Xmx%2 -Dfile.encoding=UTF-8 -Dlog4j.configurationFile=“DIRECTORY_PATH\conf\log4j2.xml” -Dcom.perspectium.working.directory=“DIRECTORY_PATH” -classpath “%~dp0..\jars*”;“%~dp0..\extlib*” com.perspectium.replicator.Replicator

    where DIRECTORY_PATH is the full path for the working directory where you want to save these directories.

  3. Also within the agent.bat file, locate this line:

    SET JAVA_ARGS=-Dfile.encoding=UTF-8 -Dlog4j.configurationFile=“%~dp0..\conf\log4j2.xml” -Dcom.perspectium.working.directory=“DIRECTORY_PATH” -classpath “%~dp0..\jars*”;“%~dp0..\extlib*” com.perspectium.replicator.Replicator

    Replace this line with:

    SET JAVA_ARGS=-Dfile.encoding=UTF-8 -Dlog4j.configurationFile=“DIRECTORY_PATH\conf\log4j2.xml” -Dcom.perspectium.working.directory=“DIRECTORY_PATH” -classpath “%~dp0..\jars*”;“%~dp0..\extlib*” com.perspectium.replicator.Replicator

    where DIRECTORY_PATH is the full path for the working directory where you want to save these directories.

  4. (OPTIONAL) 

    Open up the the log4j2.xml file in the conf folder of your new working directory(i.e. DIRECTORY_PATH\conf\log4j2.xml) in your preferred text editing application and locate the following line in the file:

    <RollingRandomAccessFile name="FILE" fileName="../logs/perspectium.log"
    filePattern="../logs/perspectium-%d{MM-dd-yyyy}-%i.log.gz">

    Replace this line with:

    <RollingRandomAccessFile name="FILE" fileName="DIRECTORY_PATH/logs/perspectium.log"
    filePattern="DIRECTORY_PATH/logs/perspectium-%d{MM-dd-yyyy}-%i.log.gz">

    where DIRECTORY_PATH is the full path for the working directory where you want to save the Agent's log files. 




  1. Locate the wrapper.conf file within the conf directory of the directory where Agent is installed.

  2. Open the wrapper.conf file in your preferred text editing application and locate the following line in the file:

    wrapper.java.additional.2=-Dlog4j.configurationFile=file:../conf/log4j2.xml

    Replace this line with:

    wrapper.java.additional.2=-Dlog4j.configurationFile=file:DIRECTORY_PATH/conf/log4j2.xml

  3. Directly under the updated line in the previous step, add another line:

    wrapper.java.additional.3=-Dcom.perspectium.working.directory=DIRECTORY_PATH

    where DIRECTORY_PATH is the full path for the working directory where you want to save these folders.

  4. (OPTIONAL) 

    Open up the the log4j2.xml file in the conf folder of your new working directory(i.e. DIRECTORY_PATH\conf\log4j2.xml) in your preferred text editing application and locate the following line in the file:

    <RollingRandomAccessFile name="FILE" fileName="../logs/perspectium.log"
    filePattern="../logs/perspectium-%d{MM-dd-yyyy}-%i.log.gz">

    Replace this line with:

    <RollingRandomAccessFile name="FILE" fileName="DIRECTORY_PATH/logs/perspectium.log"
    filePattern="DIRECTORY_PATH/logs/perspectium-%d{MM-dd-yyyy}-%i.log.gz">

    where DIRECTORY_PATH is the full path for the working directory where you want to save the Agent's log files. 



↑ Go to top of page



Truncate string values

In the DataSync Agent, you can truncate string values if you have exceeded a column's max length (UTF-8). If you are using UTF-8 characters and prefer using VARCHAR or a string data type where the specified length is strictly the number of bytes, you will be able to truncate the string value as well. 

(info) NOTE: UTF-8 characters may have multiple bytes per character. 

If you use multiple UTF-8 characters and the number of bytes exceeds what is allowed in a column, an error will occur. You can prevent this by either adding <byte_padding/> or if you want to keep the original length and truncate the string values, add <truncate_utf8/> directive within your agent.xml configuration file.

If you use a different character encoding, you can instead use <database_truncate_encoding> and set your specified encoding.

NOTE: Currently, the <database_truncate_encoding> directive only allow these encoding values:

  • ISO_8859_1
  • US_ASCII
  • UTF_16
  • UTF_16BE
  • UTF_16LE 
  • UTF_8

(warning) Request the databases.xml file for your DataSync Agent by contacting Perspectium Support.


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

  2. Open your agent.xml file in a text editing application. Then, locate the <agent> directive(s) and add the <truncate_utf8/> directive or add <database_truncate_encoding/> with the encoding value.

  3. 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>
	<truncate_utf8/>
	<!-- OR --> 
	<!-- <database_truncate_encoding>ISO_8859_1</database_truncate_encoding -->
	<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>
            <skip_alter/>
         </task>
      </subscribe>
      <max_reads_per_connect>4000</max_reads_per_connect>
      <polling_interval>5</polling_interval>
      <skip_message_set_processing/>
   </agent>
</config>


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

  5. Open your databases.xml in a text editing application. Then, locate your database type listed in <database_type> directive. Next, look for <can_alter_length/> and remove it.

  6. Start running your DataSync Agent again.



↑ Go to top of page





Enable multiple queues or database per Agent 

You can only configure the Agent to point to one queue or database. If you want to override that restriction, you will need to add the following under the <agent> directive: 

(info) NOTE: Using this option can lead to data discrepancies since the Agent may be reading the same records from the same tables in multiple queues and saving changes to the database in the incorrect order. It is only recommended to do this if you have configured dynamic and bulk shares to only share one table uniquely to a queue. For questions, contact Perspectium Support. 


  • <enable_multiple_queues/> to enable using multiple queues to one task
  • <enable_multiple_databases/> to enable using multiple databases to one task 


<?xml version="1.0" encoding="ISO-8859-1" ?>
<config>
    <agent>
        <enable_multiple_queues/>
		<enable_multiple_databases/>
        <subscribe>
            <task instances="1">
                <task_name>datasyncforservicenow_to_mysql_subscribe</task_name>
                <message_connection password="somePasswordHere" user="admin" queue="psp.out.replicator.testqueue">https://example.perspectium.net</message_connection>
                <instance_connection password="somePasswordHere" user="admin">https://example.service-now.com</instance_connection>           
                <handler>com.perspectium.replicator.sql.SQLSubscriber</handler>
            <decryption_key>Some decryption key here</decryption_key>
            <database_type>mysql</database_type>
            <database_server>localhost</database_server>
            <database_port>3306</database_port>
            <database_user>root</database_user>
            <database_parms>characterEncoding=UTF-8 & useSSL=false</database_parms>
            <database_column_max_size>251</database_column_max_size>
            <database>psp_repl</database>
            <receipt_send_interval>180</receipt_send_interval>
            <ack_batch_size>1</ack_batch_size>
         </task>
      </subscribe>
         <subscribe>
            <task instances="1">
                <task_name>datasyncforservicenow_to_mysql_subscribe</task_name>
                <message_connection password="somePasswordHere" user="admin" queue="psp.out.replicator.testqueue">https://example.perspectium.net</message_connection>
                <instance_connection password="somePasswordHere" user="admin">https://example.service-now.com</instance_connection>           
                <handler>com.perspectium.replicator.sql.SQLSubscriber</handler>
            <decryption_key>Some decryption key here</decryption_key>
            <database_type>mysql</database_type>
            <database_server>localhost</database_server>
            <database_port>3306</database_port>
            <database_user>root</database_user>
            <database_parms>characterEncoding=UTF-8 & useSSL=false</database_parms>
            <database_column_max_size>251</database_column_max_size>
            <database>psp_repl</database>
            <receipt_send_interval>180</receipt_send_interval>
            <ack_batch_size>1</ack_batch_size>
         </task>
      </subscribe>
      <max_reads_per_connect>4000</max_reads_per_connect>
      <polling_interval>5</polling_interval>
      <skip_message_set_processing/>
    </agent>
</config>



↑ Go to top of page





Key Set Processing for DataSync Agent

Key Set Processing is a capability of the DataSync Agent which can be enabled when replicating messages from a ServiceNow instance to a database. The Key Set capability optimizes throughput while further ensuring message ordering.

(info) NOTE: It's strongly recommended to enable this capability when replicating from a ServiceNow instance.

This is enabled on a task by task basis by adding the following configuration direction within the <task> definition.

<task>
	<enable_keyset_processing/>
	...
	...
</task>

The following directives can be added to the agent.xml when enabling the Key Set Processing: 

DirectiveDescriptionExample
<keyset_size>

Indicates the number of key set processor that will maintain the connection to the database.

(info) NOTE: Overriding the default key set size (8) may reduce overall throughput of message processing.


<keyset_size>4</keyset_size>


<queue_consumer_polling_interval>

Indicates the time interval the queue will be consumed. By default, the key set is scheduled to read from the queue every two seconds. 


<queue_consumer_polling_interval>nn</queue_consumer_polling_interval>

(info) NOTE: The value nn is in milliseconds.

<keyset_read_wait_time>

Indicates the time each key set processor will wait when reading from its queue. By default, the wait time is 200 milliseconds. 



<keyset_read_wait_time>nn</keyset_read_wait_time>




<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
    <agent>
        <share/>
        <subscribe>
			<task>
				<enable_keyset_processing/>
				<keyset_size>4</keyset_size>
				<queue_consumer_polling_interval>nn</queue_consumer_polling_interval>
				<keyset_read_wait_time>nn</keyset_read_wait_time>
				...
				...
			</task>
		</subscribe>
        <max_reads_per_connect>4000</max_reads_per_connect>
        <polling_interval>5</polling_interval>
        <skip_message_set_processing/>
    </agent>
</config>



↑ Go to top of page



Indexing Database tables

Database indexing is used to speed up data retrieval when querying for records from database tables. Enabling the Share Index option allows you to share the indexes of a table in ServiceNow through a bulk share so the same indexes can be created in the database where you've shared data to.

With this option enabled, the bulk share shares out an index message that contains the columns that are indexed in the table as its configured in ServiceNow. The DataSync Agent will then pick up this message and create indexes on the columns of the database table to match.

(info) NOTE: Indexing database tables is compatible with Oracle, SQL Server, MySQL, and Postgres databases, and is only used for DataSync. Snowflake does not support indexing, therefore, this feature is not compatible for Snowflake. 

Some databases, such as SQL Server and Oracle, do not support the creation of indexes on columns that are of large object (LOB) data types. To learn more about CLOB columns in DataSync Agent configurations, see CLOB Configuration for DataSync Agent.



  1. Log into your ServiceNow instance. Then, follow the steps in this link on creating table indexes. 

  2. In ServiceNow's left side navigation window, navigate to Perspectium > Perspectium Core > Shares > Bulk Shares > Create a new Bulk Share.

  3. Check Advanced. Once that option is checked, an Advanced tab will appear with the rest of the selection tabs. 

  4. Check Share Index.

  5. Fill the rest of the required fields. 

  6. Click the Save button at the bottom of the form to save the configurations for your bulk share. Once your bulk share configurations have been saved, you can click back into the bulk share record and click the Execute Now button at the bottom of the form to bulk share out the specified records immediately.

    Upon execution of the bulk share, you should see that an index message has been shared out in addition to the other records.

    An index message will be shared out for each table shared by the bulk share. The index message will contain all the ServiceNow indexes for the table in the message's value field. Once the index message is received by the DataSync Agent, it will create indexes on the columns of the database table.

    (info) NOTE:

    • The index message will only be shared out once per bulk share.
    • In order to re-share the index message, you will need to clone the bulk share by clicking on the option under Related Links and click Clone bulk share. Doing so will copy the current bulk share configuration, including the Share Index option activated. 



↑ Go to top of page



Match Field Lengths

By default, whenever a new text field is added to an existing table, the DataSync Agent will use 40 for lengths that are less than 40. For text fields greater than 40 in length, it will use the field length from the schema. To enable matching the length of the text fields, add the <match_field_lengths/> directive within your agent.xml configuration file.

(info) NOTE: This directive will work for new fields only.


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

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

  3. 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>
            <match_field_lengths/>
         </task>
      </subscribe>
      <max_reads_per_connect>4000</max_reads_per_connect>
      <polling_interval>5</polling_interval>
      <skip_message_set_processing/>
   </agent>
</config>




↑ Go to top of page



Batch Processing 

With batch processing, records are inserted or updated in batches versus being executed one by one. When there are no more records received in the given flush interval, <batch_flush_interval>, or when the number of records reaches the maximum batch size, <max_batch_size> (whichever happens first), then the DataSync Agent will insert or update all the records from the current batch with the use of <batch_insert/> and <batch_update/>.

Using batch processing can improve the DataSync Agent's overall performance. 

To use batch processing with DataSync Agent, configure your agent.xml with the directives below:

Directive

Example

Description

<batch_insert/>
Self closing tag that configures your Agent to batch SQL INSERT statements.
<batch_update/>
Self closing tag that configures your Agent to batch SQL UPDATE statements.
<batch_flush_interval><batch_flush_interval>20</batch_flush_interval>

Number of seconds that will trigger a batch SQL statement execution. Acceptable values are integers from 1 to 30 (seconds).

<max_batch_size><max_batch_size>200</max_batch_size>Number of SQL statements that will trigger a batch SQL statement execution.  A larger suggested value is 200. 



<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
    <agent>
        <subscribe>
            <task>
                <task_name>example_subscribe</task_name>
                <message_connection password="encrypt:MbsPassword" user="MbsUser">amqps://example.perspectium.net</message_connection>
                <instance_connection password="encrypt:MbsPassword" user="SnUser">https://example.service-now.com</instance_connection>
                <handler>com.perspectium.replicator.sql.SQLSubscriber</handler>
                <decryption_key>Some decryption key here</decryption_key>
                <database_type>mysql</database_type>
                <database_server>localhost</database_server>
                <database_port>3306</database_port>
                <database_user>perspectium</database_user>
                <database_password>DbPassword</database_password>
                <database_parms>characterEncoding=UTF-8</database_parms>
                <database>psp_repl</database>
				<batch_insert/>
				<max_batch_size>200</max_batch_size>
            </task>
        </subscribe>
        <max_reads_per_connect>4000</max_reads_per_connect>
        <polling_interval>5</polling_interval>
        <skip_message_set_processing/>
    </agent>
</config>




↑ Go to top of page



Truncate Before Share

In the DataSync Agent, you can truncate and delete all the records for a selected table in your database before bulk sharing. This is best for when you want to avoid using business rules to capture your deletes. Instead, you can use bulk share with Truncate Before Share enabled to send a message to the agent that will indicate a delete in all the records in the specified table. 

(warning) This feature is NOT compatible with custom schemas. 


For example, 

  • For tables cmdb_rel_ci, they get rebuilt nightly through discovery, you can use this feature to keep track
  • For tables like that where we cant capture deletes since they’re done silently, it allows you to be up to date
  • Runs discovery -> clears out table and recreates relations in the table -> so now the database needs to get in sync

This feature requires configuration in both the DataSync Agent and the DataSync application in ServiceNow. 

(info) NOTE: This is an advanced feature that will delete data from your database and should be used with caution. Incorrect use of this feature may result in data being lost that cannot be recovered. Contact Perspectium Support with any questions before enabling this feature.


To truncate records for your DataSync integration, configure your agent.xml with the directive below:

(warning) This will be added within the <task> directive(s).

Directive

Example

Description

<truncate_table>

<truncate_table>true</truncate_table>


Enables truncate and deleting all records for a selected table in your database before bulk sharing. 
AttributesDescription
delete_only

To issue a DELETE command instead of TRUNCATE.

<truncate_table delete_only="true">true</truncate_table>



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

  2. Open your agent.xml file in a text editing application. Then, locate the <task> directive(s) and add <truncate_table>true</truncate_table>. 

  3. 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>
            <truncate_table>true</truncate_table>
         </task>
      </subscribe>
      <max_reads_per_connect>4000</max_reads_per_connect>
      <polling_interval>5</polling_interval>
      <skip_message_set_processing/>
   </agent>
</config>


  4. Start running your DataSync Agent again.

  5. Log into your ServiceNow instance. Then,  navigate to Perspectium > Perspectium Core > Shares > Bulk Shares > Create a new Bulk Share.

  6. Check Advanced. Once that option is checked, an Advanced tab will appear with the rest of the selection tabs. 

  7. Check Truncate Before Share. 

  8. Click the Save button at the bottom of the form to save the configurations for your bulk share. Once your bulk share configurations have been saved, you can click back into the bulk share record and click the Execute Now button at the bottom of the form to bulk share out the specified records immediately. 



↑ Go to top of page



Dynamic Columns Configuration

Dynamic columns provides the ability to configure one or more columns that will be added for each destination table. For example use Dynamic columns when you want to track the exact time in which a table entry has been made. You can create the desired column using the Dynamic columns feature and then manage the content of the column via the Plugin Framework.

  <dynamic_columns>
         <dynamic_column column_name="my_column" column_type="93" column_size="255"></dynamic_column>
  </dynamic_columns>

The column_name must contain a valid column name per the requirements of the target database type. The column_type must be a valid Java SQL type. The column_size field is required but will only be used when your column type is character oriented.


<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
    <agent>
        <subscribe>
            <task>
                <task_name>example_subscribe</task_name>
                <message_connection password="encrypt:XXXX" user="XXXX" queue="..." >amqp://example.perspectium.net</message_connection>
                <instance_connection password="encrypt:XXXX" user="XXXX">http://example.service-now.com</instance_connection>
                <handler>com.perspectium.replicator.sql.SQLSubscriber</handler>
                <decryption_key>The cow jumped over the moon</decryption_key>
                <database_type>sqlserver</database_type>
                <database_server>MyAddress</database_server>
                <database_port>1433</database_port>
                <database_user>XXXX</database_user>
                <database_password>XXXX</database_password>
                <database_parms></database_parms>
                <database>psp_repl</database>
  				<dynamic_columns>
         			<dynamic_column column_name="my_column" column_type="93" column_size="255"></dynamic_column>
  				</dynamic_columns> 
            </task>           
        </subscribe>
        <max_reads_per_connect>2000</max_reads_per_connect>
        <polling_interval>5</polling_interval>   
    </agent>
</config>



↑ Go to top of page



Installing Third-party Libraries

After installing your agent, you may need to download and install third party libraries that you have licenses to in order to add extra functionality to your agent.

For example, if you are running the agent on Windows and want to support DPAPI to encrypt values, you will need to provide the DPAPI library to the agent as follows:

  1. Start by opening up the directory where your agent is installed. In this directory you will see the extlib directory. 

  2. Next, open the extlib directory. Drag and drop the desired library, or libraries, into the directory. Make sure that you obtain the version of the library that's right for the target architecture of the machine that's running the agent, e.g. 64 bit versus 32 bit.

↑ Go to top of page



Configuring Multiple Agents

Although you can run multiple tasks within a single DataSync Agent, you can also run multiple DataSync Agents as services on the same machine. A common strategy is to have one DataSync Agent for production work and another one for testing/development work. 

Running multiple DataSync Agents requires modifying entries within each Agent's wrapper.conf file (under ../conf).

The wrapper.ntservice.name entry is required to be unique for each DataSync Agent on the same machine.

The other entries (wrapper.ntservice.displayname and wrapper.ntservice.description) listed below are not required to be unique but can be changed to help make it easier to distinguish the different DataSync Agents when looking at your operating system's services console.

(info) NOTE: When installing the DataSync Agent through the Installer, the Agent Name you enter will be used in the wrapper.ntservice.name entry. You can run the Installer multiple times to install multiple Agents and must specify different installation paths and different Agent Name values each time you run the installer. 

For example, the wrapper.conf for one DataSync Agent will look as follows:

# Name of the service
wrapper.ntservice.name=agent1
 
# Display name of the service
wrapper.ntservice.displayname=Perspectium Replicator Agent1
 
# Description of the service
wrapper.ntservice.description=Perspectium Replicator Agent1

A second Agent would have entries such as the following:

# Name of the service
wrapper.ntservice.name=agent2
 
# Display name of the service
wrapper.ntservice.displayname=Perspectium Replicator Agent2
 
# Description of the service
wrapper.ntservice.description=Perspectium Replicator Agent2


↑ Go to top of page



Configuring the DataSync Agent to Leverage a Proxy

The DataSync Agent supports accessing the Perspectium Integration Mesh and replication sources such as ServiceNow through a Proxy. Several organizations use such a proxy to provide a more secure or controlled method in which to access servers within the Internet.

Access to the Proxy can be configured to use either the HTTP or HTTPS protocol. The destination server, such as a ServiceNow instance, can also be configured to leverage either protocol. Furthermore, the DataSync Agent supports the ability to authenticate itself to the proxy using either the Basic Authentication or NTLM protocols. Additionally, you can configure authentication support for the destination server as well.


  1. Locate the agent's base directory and change it into the conf configuration folder where the agent.xml file is located.

  2. Edit agent.xml and save this file following the instructions below:

The following agent.xml directives can optionally be configured to access the defined URI via a proxy:

Connection directiveExample
Message connection<message_connection attributes>https://acme.perspectium.net</message_connection>
Reporting connection<reporting_connection attributes>https://acme.perspectium.net</reporting_connection>
Management connection<management_connection attributes>https://acme.perspectium.net</management_connection>


The attributes for configuring a proxy are:

AttributeDescription and ExampleRequired
proxy

The URL of the proxy server to connect to.

proxy="http://ourproxy.acme.com"

Yes
proxy_user

The username to authenticate with the proxy server

proxy_user=“proxyConfiguredUser”

No
proxy_password

The password to authenticate with the proxy server

proxy_password=“proxyConfiguredUserPassword”

No


If you specify a proxy_user then you must also configure the proxy_password. In the examples below, when we say target we mean the true intended destination that we want to reach beyond the proxy.

Proxy protocol, AuthenticationExample
HTTP<message_connection proxy=“http://proxy.acme.net”>https://acme.service-now.com</message_connection>
HTTPS<message_connection proxy=“https://proxy.acme.net”>https://acme.service-now.</message_connection>
HTTP, proxy authentication<message_connection proxy=“http://proxy.acme.net” proxy_user=“proxyUser” proxy_password=“password”>https://acme.service-now.com</message_connection>
HTTPS, proxy authentication<message_connection proxy=“https://proxy.acme.net” proxy_user=“proxyUser” proxy_password=“password”>https://acme.service-now.com</message_connection>
HTTP, target authentication<message_connection user=“mark” password=“password” proxy=“http://proxy.acme.net”>https://acme.service-now.com</message_connection>
HTTPS, target authentication<message_connection user=“mark” password=“password” proxy=“https://proxy.acme.net”>https://acme.service-now.com</message_connection>
HTTP, proxy and target authentication<message_connection user=“mark” password=“password” proxy=“http://proxy.acme.net” proxy_user=“perspectium” proxy_password=“password”>https://acme.service-now.com</message_connection>
HTTPS, proxy and target authentication<message_connection user=“mark” password=“password” proxy=“https://proxy.acme.net” proxy_user=“perspectium” proxy_password=“password”>https://acme.service-now.com</message_connection>


In most cases, the HTTP protocol is used when talking to the Proxy since that communication is taking place inside the firewall. 

3. Start running your DataSync Agent again.



↑ Go to top of page



Set a custom primary key

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


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

  2. 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.

  3. 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>


  4. Start running your DataSync Agent again.



↑ Go to top of page



Heartbeat 

The Heartbeat configuration also requires information on how to contact the Perspectium Message Bus. The directives used are exactly those used by the core configuration file config.xml but it’s possible that a completely different Message Bus or user credentials will be used. It’s important to note that the Heartbeat configuration is not intended to be modified by the customer. 

Directive

Example

Description

Required

heartbeat_queue<heartbeat_queue>psp.in.heartbeat</heartbeat_queue>heartbeat message bus queueYes
update_interval<update_interval>60</update_interval>heartbeat intervalYes
amqp_uri<amqp_uri>amqp://localhost</amqp_uri>Primary Message Bus LocationNo
amqp_user<amqp_user>georvi</amqp_user>User for logging into primary busNo
amqp_password<amqp_password>greetingsFromGeorvi</amqp_password>User's password for message busNo


<heartbeat_configuration>
   <amqp_uri>amqp://localhost</amqp_uri>
   <amqp_user>admin</amqp_user>
   <amqp_password>adminadmin</amqp_password>
   <heartbeat_queue>psp.in.heartbeat</heartbeat_queue>
   <update_interval>60</update_interval>
</heartbeat_configuration>

↑ Go to top of page



Encrypting Fields

Encrypting sensitive field values is supported in the config.xml and agent.xml files. The agent will utilize encrypted fields based on the encrypted: and encrypt: prefixes.

When the Agent (or the Validate Configuration) starts, any configuration values prefixed with encrypt: will be encrypted and the configuration file will be rewritten with the prefixes changed to encrypted:.

You can encrypt passwords attributes and elements, as well as your decryption keys if desired.

The following is an example of adding encrypt: in the agent.xml: 


<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
    <agent>
        <subscribe>
            <task>
                <task_name>example_subscribe</task_name>
                <message_connection password="encrypt:MbsPassword" user="MbsUser">amqps://example.perspectium.net</message_connection>
                <instance_connection password="encrypt:MbsPassword" user="SnUser">https://example.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>perspectium</database_user>
                <database_password>encrypt:DbPassword</database_password>
                <database_parms>characterEncoding=UTF-8</database_parms>
                <database>psp_repl</database>
            </task>
        </subscribe>
 
        <max_reads_per_connect>4000</max_reads_per_connect>
        <polling_interval>5</polling_interval>
        <skip_message_set_processing/>
    </agent>
</config>


The following is an example of the agent.xml after startup:


<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
    <agent>
        <subscribe>
            <task>
                <task_name>example_subscribe</task_name>
                <message_connection password="encrypted:vlOtU71yu8N/EFIJH85SauokjluHg3zcxvdAmB0=" user="MbsUser">amqps://example.perspectium.net</message_connection>
                <instance_connection password="encrypted:vlOtU71yu8N/EFIJH85SSJMF8Q5huHg3zc804oGAmB0=" user="SnUser">https://example.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>perspectium</database_user>
                <database_password>encrypted:vlOtU71asdfsadf/EFIJHasdfasfjlj=</database_password>
                <database_parms>characterEncoding=UTF-8</database_parms>
                <database>psp_repl</database>
            </task>
        </subscribe>
 
        <max_reads_per_connect>4000</max_reads_per_connect>
        <polling_interval>5</polling_interval>
        <skip_message_set_processing/>
    </agent>
</config>



↑ Go to top of page



Encrypting Fields using DP API

To leverage the Data Protection (DP) API for encrypting fields within your agent.xml configuration file, the Agent will utilize encrypted fields based on dpapi: prefixes. This approach ensures no 3rd party software by itself can decrypt fields that you have encrypted.


<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
    <agent>
        <subscribe>
            <task>
                <task_name>example_subscribe</task_name>
                <message_connection password="dpapi:MbsPassword" user="MbsUser">amqps://example.perspectium.net</message_connection>
                <instance_connection password="dpapi:MbsPassword" user="SnUser">https://example.service-now.com</instance_connection>
                <handler>com.perspectium.replicator.sql.SQLSubscriber</handler>
                <decryption_key>Some decryption key here</decryption_key>
 
                <database_type>mysql</database_type>
                <database_server>localhost</database_server>
                <database_port>3306</database_port>
                <database_user>perspectium</database_user>
                <database_password>encrypt:DbPassword</database_password>
                <database_parms>characterEncoding=UTF-8</database_parms>
                <database>psp_repl</database>
            </task>
        </subscribe>
 
        <max_reads_per_connect>4000</max_reads_per_connect>
        <polling_interval>5</polling_interval>
        <skip_message_set_processing/>
    </agent>
</config>


The following is an example of the agent.xml after startup:


<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
    <agent>
        <subscribe>
            <task>
                <task_name>example_subscribe</task_name>
                <message_connection password="dpapied:vlOtU71yu8N/EFIJH85SauokjluHg3zcxvdAmB0=" user="MbsUser">amqps://example.perspectium.net</message_connection>
                <instance_connection password="dpapied:vlOtU71yu8N/EFIJH85SSJMF8Q5huHg3zc804oGAmB0=" user="SnUser">https://example.service-now.com</instance_connection>
                <handler>com.perspectium.replicator.sql.SQLSubscriber</handler>
                <decryption_key>Some decryption key here</decryption_key>
 
                <database_type>mysql</database_type>
                <database_server>localhost</database_server>
                <database_port>3306</database_port>
                <database_user>perspectium</database_user>
                <database_password>encrypted:vlOtU71asdfsadf/EFIJHasdfasfjlj=</database_password>
                <database_parms>characterEncoding=UTF-8</database_parms>
                <database>psp_repl</database>
            </task>
        </subscribe>
 
        <max_reads_per_connect>4000</max_reads_per_connect>
        <polling_interval>5</polling_interval>
        <skip_message_set_processing/>
    </agent>
</config>



↑ Go to top of page