This section describes the configuration requirements and options for leveraging the Perspectium SQL Subscriber Replicator Agent for replicating table changes made to a Service Now instance.
The current required directives available are outlined in the following table.
|task_name||<task_name>a unique name for the task</task_name>||Unique task name||Required|
|instance_name||<instance_name>the_name.service-now.com</instance_name>||Service now instance||Required|
|instance_user||<instance_user>serviceNowUser</instance_user>||User used to request a table schema||Required|
|instance_password||<instance_password>serviceNowUser</instance_password>||Instance user's password||Required|
|decryption_key||<decryption_key>shared secret key</decryption_key>||Secret key shared with the instance||Required|
The instance directives provide the agent with the data it needs in order to establish a session with the Service Now Instance to build and maintain a cache of information related to the tables or schemas that are being replicated.
<instance_name> will be the fully qualified domain name of the instance. <instance_user> is used to define the name of a valid user defined within the specified instance. <instance_password> is used to define the password associated with the configured instance user.
Starting in version 3.2.2, the ability to use a Proxy Server to communicate to the ServiceNow Instance is provided.
Several organizations use such a proxy to provide a more secure or controlled method in which to access servers within the Internet.
The following template is used when using the <instance_proxy> proxy configuration directive.
Port is optional and if it's excluded then no ':' should follow the host name or IP.
In the following example the traffic to the proxy is encrypted via an SSL connection.
In this next example the traffic is un-encrypted (using http not https) to a proxy name proxy.acme.com via TCP port 5678.
In this next example the traffic is encrypted https to a proxy named proxy.acme.com via TCP port 8443.
The following table reflects the available optional configuration directives:
|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.
|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|
The <database> directive allows you to specify or override the default database.
The database used is specified by using the <database_type> directive and you must specify one of the following types:
The <database_server> directive is used to provide either a fully qualified domain name of the database server or its IP address.
The <database_port> directive is used to override the standard port used by the servers.
The <database_user> directive is used to specify the user name that will be used to login to the database. The user configured must have permissions which allow it to create and modify tables.
The <database_password> directive is used to specify the password associated with the configured user.
The <database_parms> directive can be used to provide additional information to the database server during establishment of the connection.
The <database_sid> directive is optionally used to specify the SID for Oracle database.
The following configures each of the available directives.
<agent> <subscribe> <task> <task_name>SQL_replication</task_name> <instance_name>someones.service-now.com</instance_name> <instance_user>perspectium</instance_user> <instance_password>apasswordhere</instance_password> <schemas_directory>someones.schemas</schemas_directory> <cant_access_directory>someones.cantaccess</cant_access_directory> <database_server>18.104.22.168</database_server> <database_user>mke</database_user> <database_password>dbpassword</database_password> <database_port>1433</database_port> <database_type>sqlserver</database_type> <database_parms>autoReconnect=true</database_parms> </task> </subscribe> </agent>
Additional SQL Subscriber directives
The following table reflects additional directives that apply to all SQL type Subscribers.
NOTE: As of the Fluorine+ release, the Snowflake JDBC driver 3.8.0 will come packaged with your Perspectium Agent in order to address a known issue with use of OSCP for certificate revocation status.
In order to replicate to a Snowflake database, you will need to add the directive <ensure_table_exists> within the <task> directive. The <ensure_table_exists> directive is self-closing and does not require any attribute values. Refer to the example agent.xml configuration file below for configuration of your Agent for replication to a Snowflake database:
<agent> <share/> <subscribe> <task instances="4"> <task_name>OnDemandAgent_subscribe</task_name> <message_connection password="encrypted:vlOtU71yu8N/EFIJH85SSPbQ5/gZDZBSjYcHe6gjfJI=" queue="psp.out.replicator.aws_ondemand_datasync" use_basic_consume="true" user="perspectium.com.1561578334430/oda.ivan.kuo.1561578334430">amqp://nvirginia-ondemanddatasync-trial-rabbit.perspectium.net</message_connection> <handler>com.perspectium.replicator.sql.SQLSubscriber</handler> <decryption_key>Perspectium On-demand DataSync</decryption_key> <database_type>snowflake</database_type> <database_server>NC39170.us-east-1.snowflakecomputing.com</database_server> <database_user>Adminadmin1</database_user> <database_password>Adminadmin1</database_password> <database_parms>warehouse=example_warehouse&schema=example_schema&role=example_role</database_parms> <database>psp_repl</database> <use_cache/> <ensure_table_exists/> </task> </subscribe> <max_reads_per_connect>4000</max_reads_per_connect> <polling_interval>5</polling_interval> <skip_message_set_processing/> </agent>
The Postgres implementation does not dynamically create the database specified in the <database> configuration directive. The database must be created prior to being used by the agent.
In order to integrate with a Postgres database, you must obtain the JDBC driver from the Postgres website. Currently, Perspectium supports version 42.1.4 of the Postgres JDBC driver. Once you have obtained the driver, it needs to be placed into the Agent's Jars directory, which is a sub-directory within the Replicator Agent's application folder.
Postgres Server is offered as a premium service, please contact our support team email@example.com to inquire about this service.
In order to integrate with a SAP Hana database, you must use the JDBC driver (ngdbc.jar) that is installed as part of the SAP Hana client installation. Once you have obtained the driver, it needs to be placed into either the Agent's jars or extlib directories, which are sub-directories within the Replicator Agent's application folder.
SAP Hana is offered as a premium service, please contact our support team firstname.lastname@example.org to inquire about this service.
In order to ensure proper interaction between the Perspectium SQL subscriber and an instance of a MS SQL Server database, the user that is configured must have the ability to create a database and or the default database ‘psp_repl’ must be pre-created.
If you experience a connection timeout error you can add the following directive within your <task> definition to increase the number of seconds that the attempt will last before failing. In this example we've defined waiting for 30 seconds.
Because ntext is being deprecated in a future version of MS SQL Server, as of the Argon release, the agent will create large fields (fields defined with a size of 251+ characters) as nvarchar(max). This will also allow searching of these fields since nvarchar(max) fields support distinct queries that are not supported with ntext.
In order to ensure proper interaction between the Perspectium SQL subscriber and an instance of an Oracle 11g database server, the user that is configured must have the ability to create a table. Furthermore, an Oracle database with a service name must be pre-created. The database directive will be configured with the service name of the database.
As of v3.20.0 a TNS string can be used to connect to an Oracle database. The TNS string should be inserted into the database_directive and the agent will use it to connect. When using this the database_port and database_sid directives are no longer required. The database_user and database_password directive are still required.
In order to integrate with a Redshift database you must obtain the JDBC driver from AWS. Currently, Perspectium supports version 4.1 of the Redshift JDBC driver. Once you have obtained the driver from AWS, it needs to be placed into the Agent's Jars directory, which is a sub-directory within the Replicator Agent's application folder.
Starting with v3.22.0 of the Replicator Agent support for the AWS Redshift database has been enhanced. To take advantage of the enhanced processing requires additional configuration steps which are outlined below. You can however continue to use the previous support for Redshift by skipping this additional configuration.
The enhanced version of the agent uses an S3 bucket as a staging area where replicated data is stored and then subsequently used to populate a redshift table.
The agent uses AWS tools to efficiently populate a replicated table to the database from the S3 bucket. You must create the S3 bucket during setup by using AWS tools. Once created you'll then provide the bucket name and security credentials which will enable access to the S3 bucket during configuration of the agent.
Furthermore, the following entries must be included within each <task> element in which Redshift support is being configured.
The <handler> directive must specify 'RedshiftSQLSubscriber' as the handler. For example:
In addition, the following directives must also be placed within the <task> definition with the proper values. These additional directives are used to specify the location or region that hosts your redshift database and S3 bucket.
The <region> directive specifies the AWS region. Example:
The <access_key> and <secret_access_key> directives must have valid AWS access credentials in order to securely access your S3 bucket.
The <s3_bucket> directive is used to define the name of the S3 bucket you created to hold replicated data.
Additionally, in order to optimize throughput you should enable batch processing of row deletes by including the following directive within your <task> definition:
Redshift is offered as a premium service, please contact our support team email@example.com to inquire about this service.
The Perspectium SQL Subscriber Handler is capable of communication with any Relational Database Management System (RDBMS) which support the JDBC 2.0 specification. Click here to view a list of supported database types.
The Perspectium SQL subscriber is configured to specify the following:
After installation has been performed and the Replicator Agent is started, the agent establishes a connection to the database server using the credentials configured as outlined above. This task must be completed successfully before the agent will proceed with subsequent processing. Once the connection has been established, the agent will attempt to create the configured database. This step requires that the configured user has the proper permissions to create a new database. If this is not the case then an error will occur. As an alternative, the required database can be created by something or someone other than the agent. When this approach is taken you add the configuration directive
to the configuration and the agent will not attempt to create the database since this tell the agent that the database already exists.
Once replicated data is available, the agent will determine if the table associated with the replicated data exists, and if it's not it will create it. This means that the configured user must have the permissions required to create tables within the defined database.
Once the required table has been created the agent will perform an insert in order to populate the table with the replicated row. This means that the agent must have the permissions to perform an insert into the table.
When you share out data to be consumed by the Replicator Agent, you'll typically specify whether the data is being shared in bulk or dynamically. When the data is shared in bulk and based on how the agent has been configured, it will typically perform a query in order to determine if the row already exists in the database. The agent must therefore have permission to query the database and the associated tables. If the agent determines that the row already exist then it performs an update, otherwise it will insert the row. The agent must have permission to perform both these actions.
Additionally, the agent wants to ensure that all data is replicated. It's possible that the structure of the source table has changed. For example, an additional column is added to the table or the size of a column is increased. The agent performs the analysis required to make this determination and by default will alter the table in order to align it with the source table. Currently, Perspectium supports adding a column, increasing the size of a column to it's maximum and if baseed upon the database and the type of column altering the data type. The agent must therefore have the permissions to alter the table. It is possible to constrain the agent from altering tables by including the
directive within the SQL Subscribers
Lastly, in order to support the deletion of rows from a table, the database user must also have permission to delete a table row.
Subscriber handlers exist to obtain a message from a queue within the message bus, obtain the message and do something with it. For example, the MySQL subscriber provided by Perspectium, processes the message by converting it into a valid MySQL database statement and then inserting it into a MySQL table.
Subscriber handlers are configured to know about where the information they’re interested in obtaining is located. The information is located within a queue inside and instance of the Perspectium message bus and you must configure access to the message bus and the target queue. You start the configuration of a subscriber using the <subscribe> element directly following agent directives. A subscriber essentially is subscribing to a <task> that it monitors for work to be performed. How to find and interact with the queue is defined within the <task> tag.
<agent> <subscribe> <!-- Subscriber configuration directives --> <task> <task_name>myTask</task_name> <schedule>* * * * *</schedule> <handler>javaClassHandler</handler> <database_type>mysql</database_type> <decryption_key>shared pass phrase for decrypting</decryption_key> </task> </subscribe> </agent>
The following is only applicable for versions older than 3.2.1.
This section lets you know how to control which tables your subscriber is interested in and which ones it’s not. In some cases a common Message Bus Queue will be used to Share out multiple table changes. The following configuration directives allow you very fine grained control over which tables will be included for processing and which will be excluded.
It’s important to note that the approach used is referred to as lazy, which is to say by default the agent wants to ignore or not perform the work defined in the Message Bus Queue. That being the case you must include those tables that you’re interested in Subscribing to. In most cases it will be all items in the queue and all actions such as insert, update and delete associated with those items. In this case it’s easiest to leverage the wild card ‘*’ which means everything.
Let’s start with an example:
<tables> <includes> <include>*.insert</include> <include>*.delete</include> <include>*.update</include> </includes> <excludes> <exclude>cmdb_ci_servers.insert</exclude> </excludes> </tables>
This example says to process every table using the wild card character and does so for each action available. This configuration then states that if the table happens to be cmdb_ci_servers and the action is an insert to ignore or not process it.
Consider the following:
<tables> <includes> <include>*.delete</include> <include>*.update</include> </includes> <excludes> <exclude>cmdb_ci_servers.*</exclude> </excludes> </tables>
This configuration will not process any tables that have an insert associated with them. Recall the laziness of the approach; if it’s not told to do something explicitly it will not process the entry. This configuration also states to ignore or perform no action on the cmdb_ci_servers table.
Consider the following:
<tables> <includes> <include>cmdb_ci_servers.insert</include> </includes> </tables>
This configuration requests that only inserts of the table cmdb_ci_servers be processed, everything else will be ignored.
Handling of long table and column names
Since Oracle only permits a string of 30 characters by default, a table or a column name that has over 30 characters will be truncated upon replication. The algorithm used for the truncation is the first 15 characters of the name will be displayed, followed by a “_” and then the last 14 of the table name. The “_” denotes the truncated values between the first 15 and the last 14 characters.
For example: When replicating the table “Workflow Estimated Runtime Configuration” (41 characters total) to Oracle database, the replicated name will be “u_workflow_esti__configuration”.
Please Note: When replicating a table or column that have similar names, meaning if the first 15 and the last 14 of the name are the same, only one of the two records will be replicated since after truncation, both of the names will be the same.
The following is only applicable for versions NEWER than 3.2.1.
In the newer versions of the SQLSubscriber handler, all tables and actions are enabled by default. This means that for most cases you won't have to modify the configuration at all or be worried about it.
Perspectium recommends you contact support if you are interested in replacing the default configuration.