First, you will need to install a DataSync Agent configured to share data to an MS SQL Server database.
You will also need to create a ServiceNow dynamic share with an update trigger or create a ServiceNow bulk share.
Finally, make sure to stop running your DataSync Agent before making any Agent configuration changes.
Optional Configuration Directives
The following table reflects the available optional configuration directives:
| Directive | Example | Use | Default |
|---|---|---|---|
| amqp_uri | <amqp_uri>amqp://localhost</amqp_uri> | Primary Message Bus Location | Optional |
| amqp_user | <amqp_user>admin</amqp_user> | User for logging into primary bus | Optional |
| amqp_password | <amqp_password>adminadmin</amqp_password> | User's password for message bus | Optional |
| polling_interval | <polling_interval>20</polling_interval> | Interval between each run | Optional |
| max_reads_per_connect | <max_reads_per_connect>1500</max_reads_per_connect> | Messages to process per interval | Optional |
| max_writes_per_connect | <max_writes_per_connect>1500</max_writes_per_connect> | Messages to process per interval | Optional |
| schedule | <schedule>* * * * *</schedule> | Process messages every minutes | Optional |
| schemas_directory | <schemas_directory>directory name</schemas_directory> | directory holding the schema definitions | Optional |
| cant_access_directory | <cant_access_directory>directory name</cant_access_directory> | directory holding the name of table which can't be accessed. | Optional |
| date_format | <date_format>MM/dd/yyyy</date_format> | format of the date for all tables. | Optional |
| date_time_format | <date_time_format>MM/dd/yyyy HH:mm:ss</date_time_format> | format of the date and time for all tables. | Optional |
| key | <key>ven0001</key> | to specify a “key” or instance to subscribe to and ignore records from all other sources e.g. only records coming from the ServiceNow instance ven0001 will be subscribed to and all other messages will be skipped. Introduced in v3.8.0 | Optional |
The following table provides the optional target database configuration options.
| Directive | Example | Use | Default |
|---|---|---|---|
| database_type | <database_type>mysql|sqlserver|oracle|postgres|etc</database_type> | The type of database replicated to | Required |
| database | <database>psp_repl</database> | Name of the target database | Optional |
| database_server | <database_server>IP or FQDN of the database server</database_server> | IP or FQDN of the database server | Required |
| database_user | <database_user>database user name</database_user> | User for logging into the database | Required |
| database_password | <database_password>database user password</database_password> | Password for logging into the database | Required |
| database_port | <database_port>port used by the server</database_port> | Port used by the server | Optional |
| database_parms | <database_parms>parms passed to the database server</database_parms> | Parameters passed to the database server | Optional |
| database_sid | <database_sid>SID (Oracle Only) </database_sid> | SID used for Oracle database | Optional |
Dynamic Database Port Configuration
For DataSync Agent integrations using MSSQL Server, a configuration option is available to have dynamic database ports. The database_port directive can be nested within each task directive of the agent.xml configuration file for the Replicator Agent. Configuring your Replicator Agent in this way allows you to specify an instance name that will be compatible with dynamic MS SQL Server database ports.
To configure dynamic database ports for your Replicator Agent integration with MS SQL Server, follow these steps:
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.
Within each database_port tag, add \DATABASE_INSTANCE_NAME as this directive's value, where DATABASE_INSTANCE_NAME is the name of your MS SQL Server database instance. Your <database_port> directive will look like the following example:
<database_port>\PRODUCTION</database_port>
Save the agent.xml and start the agent.
Force reload a prepared SQL statement.
When running your Agent for a DataSync integration with MS SQL Server, a directive in the agent.xml configuration file named <force_preparedstatement_reload> has its value set to true by default so that SQL statements are batched together to prevent primary key violation exceptions. You can turn off this functionality, or stop reloading prepared SQL statements, by changing the value of this directive to false according to the procedure described below
To stop reloading prepared SQL statements for your DataSync integration to an MS SQL Server database, 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 <force_preparedstatement_reload> directive(s) nested within the <task> directive(s) and change each value to false per the example shown below:
<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
<agent>
<share/>
<subscribe>
<task instances="1">
<task_name>mssql_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="testuser">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>sqlserver</database_type>
<database_server>localhost</database_server>
<database_port>1433</database_port>
<database_user>testuser</database_user>
<database_password>testpassword</database_password>
<database_parms/>
<database_column_max_size>251</database_column_max_size>
<database>psp_repl</database>
<force_preparedstatement_reload>false</force_preparedstatement_reload>
</task>
</subscribe>
<max_reads_per_connect>4000</max_reads_per_connect>
<polling_interval>5</polling_interval>
<skip_message_set_processing/>
</agent>
</config>
Save the changes you made to your agent.xml file and close the file.
Secure Sockets Layer Error
If you get the following error when running the Agent and trying to connect to your SQL Server database:
2025-02-24 13:21:27.469 ERROR - main - SQLDataSource - Error: Cannot create PoolableConnectionFactory ("encrypt" property is set to "true" and "trustServerCertificate" property is set to "false" but the driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption: Error: (certificate_unknown) PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target. ClientConnectionId:bcdadb9e-4353-4d23-bb05-38eda104a036)
java.sql.SQLException: Cannot create PoolableConnectionFactory ("encrypt" property is set to "true" and "trustServerCertificate" property is set to "false" but the driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption: Error: (certificate_unknown) PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target. ClientConnectionId:bcdadb9e-4353-4d23-bb05-38eda104a036)
Then update the <database_parms> configuration to be:
<database_parms>encrypt=true;trustServerCertificate=true;</database_parms>
For example:
<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
<agent>
<share/>
<subscribe>
<task instances="1">
.
.
.
<database_parms>encrypt=true;trustServerCertificate=true;</database_parms>
</task>
</subscribe>
<max_reads_per_connect>4000</max_reads_per_connect>
<polling_interval>5</polling_interval>
<skip_message_set_processing/>
</agent>
</config>