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.
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.
Prerequisites
First, you will need to install DataSync Agent.
You will also need to create a ServiceNow dynamic share/bulk share.
Make sure to stop running your DataSync Agent before making any Agent configuration changes.
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:
Follow the steps to install the DataSync Agent.
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 update the following database-related directives:
Directive | Value 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
|
Locate the <task> directive(s) in your <subscribe> directive, nest the following directive:
Directive | Description |
---|---|
<skip_database_creation/> | Bypasses the dynamic creation of the replicated database
|
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="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>
Save the changes you made to your agent.xml file and close the file.
After configuring your agent.xml file to support replication to your MS SQL Server database, start running your DataSync Agent again.