Versions Compared

Old Version 4

changes.mady.by.user user-1bc08

Saved on

compared with

New Version Current

changes.mady.by.user user-1bc08

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

You can configure your Perspectium DataSync Agent to share data from ServiceNow or Salesforce to a Microsoft SQL Server database or a Microsoft Azure SQL database by changing some additional configurations in your agent.xml file. Since Azure SQL is Microsoft's cloud database service of SQL Server, the DataSync Agent configurations are the same as configuring to connect to a SQL Server database you host.

(info) NOTE: A DataSync Agent configured to share to a MS SQL Server database or a Microsoft Azure SQL Server database will create large fields (fields defined with a size of 251+ characters) with a searchable nvarchar(max) data type.

AnchorTOPTOP
Panel
titleWhat's on this page?

Table of Contents
absoluteUrltrue


Prerequisites

(warning) First, you will need to install DataSync Agent.

(warning) You will also need to create a ServiceNow dynamic share/bulk share.

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

(warning) Finally, you will need to create a database in the server you choose, MS SQL Server or MS Azure SQL Server, that will store data replicated via the DataSync Agent.

Procedure

To set up your DataSync Agent to share application data to a MS SQL Server database or MS Azure SQL Server database, follow these steps:

UI Steps
sizesmall


UI Step

Follow the steps to install the DataSync Agent for ServiceNow or the DataSync for Salesforce Agent and make sure that your agent.xml file is configured correctly.


UI Step

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


UI Step

Open your agent.xml file in a text editing application. Then, locate the <task> directive(s) within your <subscribe> directive, and update the following database-related directives:

DirectiveValue to enter
<database_type>sqlserver
<database_server>

URL for your MS SQL Server database. If MS SQL Server is installed locally, enter localhost

<database_port>

1433 

The agent also supports using an instance name when the SQL server is using dynamic ports. For example you can enter the value \MSSQLSERVER here

<database_user>Username used to access your MS SQL Server database or MS Azure SQL Server database
<database_password>Password used to access your MS SQL Server database or MS Azure SQL Server database
<database_parms>

loginTimeout=30

(info) NOTE: This is optional, but entering this parameter will force the DataSync Agent to wait for 30 seconds after a connection error before attempting to reconnect to MS SQL Server or MS Azure SQL Server



UI Step

Locate the <task> directive(s) in your <subscribe> directive, nest the following directive:

DirectiveDescription
<skip_database_creation/>

Bypasses the dynamic creation of the replicated database

(info) NOTE: This directive is added for a MS SQL Server replication scenario with the DataSync Agent, as you must create the database used for replication in MS SQL Server before configuring your DataSync Agent

Your agent.xml file should look similar to the example shown below:

Code Block
languagexml
themeEclipse
<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
   <agent>
      <share/>
      <subscribe>
         <task instances="1">
            <task_name>servicenow_to_mssqlserver</task_name>
            <message_connection password="encrypted:vlOtU71yu8N/EFIJH85SSBtaIt7qEEfvqiqft9VZyYE=" queue="psp.out.replicator.test" use_basic_consume="false" user="exampleuser">https://example.perspectium.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>sqlserver</database_type>
            <database_server>localhost</database_server>
            <database_port>1433</database_port>
            <database_user>perspectium</database_user>
            <database_password>perspectium123</database_password>
            <database_parms/>
            <database_column_max_size>251</database_column_max_size>
            <database>psp_repl</database>
			<skip_database_creation/>
         </task>
      </subscribe>
      <max_reads_per_connect>4000</max_reads_per_connect>
      <polling_interval>5</polling_interval>
      <skip_message_set_processing/>
   </agent>
</config>



UI Step

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


UI Step

After configuring your agent.xml file to support replication to your MS SQL Server database, start running your DataSync Agent again.

↑ Go to top of page

Optional Configurations

The following table reflects the available optional configuration directives:

DirectiveExampleUseDefaultamqp_uri<amqp_uri>amqp://localhost</amqp_uri>Primary Message Bus LocationOptionalamqp_user<amqp_user>admin</amqp_user>User for logging into primary busOptionalamqp_password<amqp_password>adminadmin</amqp_password>User's password for message busOptionalpolling_interval<polling_interval>20</polling_interval>Interval between each runOptionalmax_reads_per_connect<max_reads_per_connect>1500</max_reads_per_connect>Messages to process per intervalOptionalmax_writes_per_connect<max_writes_per_connect>1500</max_writes_per_connect>Messages to process per intervalOptionalschedule<schedule>* * * * *</schedule>Process messages every minutesOptionalschemas_directory<schemas_directory>directory name</schemas_directory>directory holding the schema definitionsOptionalcant_access_directory<cant_access_directory>directory name</cant_access_directory>directory holding the name of table which can't be accessed.Optionaldate_format<date_format>MM/dd/yyyy</date_format>format of the date for all tables.Optionaldate_time_format<date_time_format>MM/dd/yyyy HH:mm:ss</date_time_format>format of the date and time for all tables.Optionalkey<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

.

0Optional

The following table provides the optional target database configuration options.

DirectiveExampleUseDefaultdatabase_type<database_type>mysql|sqlserver|oracle|postgres|etc</database_type>The type of database replicated toRequireddatabase<database>psp_repl</database>Name of the target databaseOptionaldatabase_server<database_server>IP or FQDN of the database server</database_server>IP or FQDN of the database serverRequireddatabase_user<database_user>database user name</database_user>User for logging into the databaseRequireddatabase_password<database_password>database user password</database_password>Password for logging into the databaseRequireddatabase_port<database_port>port used by the server</database_port>Port used by the serverOptionaldatabase_parms<database_parms>parms passed to the database server</database_parms>Parameters passed to the database serverOptionaldatabase_sid<database_sid>SID (Oracle Only) </database_sid>SID used for Oracle databaseOptional↑ Go to top of page