You can configure your Perspectium DataSync Agent to share data from ServiceNow or Salesforce to a Microsoft SQL Server database by changing some additional configurations in your agent.xml file.

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

Prerequisites


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

(warning) You will also need to create a ServiceNow dynamic share/bulk share or create a Salesforce 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 MS SQL Server that will store data replicated via the DataSync Agent.

Procedure

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


Install and configure your DataSync Agent

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

Access your agent.xml configuration file

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

Update database directives to point to MS SQL Server

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
<database_user>Username used to access your MS SQL Server database
<database_password>Password used to access your MS 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

Add the <skip_database_creation/> directive

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 an 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:

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
   <agent>
      <share/>
      <subscribe>
         <task instances="4">
            <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>

Save your agent.xml file

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

Run your DataSync Agent

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