User Tools

Site Tools


replicator_sqlreplication
UNDER CONSTRUCTION

SQL Subscriber Configuration

This documentation describes the configuration requirements and options for leveraging the Perspectiums SQL Subscriber Replicator Agent for replicating table changes made to a Service Now instance. This is a companion document to the Perspectium Replicator Agent Configuration Guide, which should be read prior to continuing with this document.

The Perspectium Replicator Agent uses an XML based format for defining its configuration. The configuration can contain multiple sections. The first section is for defining the behavior of the agent itself while the other two sections allow you to define the various handlers or tasks that will be scheduled by the agent. There are two types of handlers, subscribers and sharers.

Directive Use Default
instance_name <instance_name> No
instance_user <instance_user> No
instance_password <instance_password> No
schemas_directory <schemas_directory> schemas
cant_access_directory <cant_access_directory> cantaccess
database <database> psp_repl
database_type <database_type> No
database_server <database_server> No
database_port <database_port> No
database_user <database_user> No
database_password <database_password> No
database_parms <database_parms> No
skip_alter <skip_alter/> Defaults to No

The first three directives provide the agent with the data it needs in order to establish a session with the Service Now Instance in order to build and maintain a cache of information related to the tables or schemas that are being replicated.

The <instance_name> will be the fully qualified domain name of the instance. For example: dev068.service-now.com

The <instance_user> is used to define the name of a valid user defined within the specified instance.

The <instance_password> is used to define the password associated with the configured instance user.

The <schemas_directory> is optional but when the first three are configured a new directory that holds the obtained schemas will either be stored in the directory provided as part of the configuration or will default to a directory named ‘schemas’. If it doesn’t exist this directory will be created when required.

The <cant_access_directory> directive allows you to override the default directory ‘cant.access’ with a directory name of your choosing. The names of files which the agent does not have proper access rights to read will be stored here. If it doesn’t exist this directory will be created when required.

The next directives are used in conjunction with a SQL server subscriber. It’s best to consider all of the directives mandatory because doing so means the configuration reflects all of the parameters being used.

The <database> directive allows you to specify or override the default database. This is the only directive, which should be omitted from a configuration unless you’re certain you know what you’re doing.

The database used is specified by using the <database_type> directive and you must specify one of the following types:

Valid entries for <database_type>

  • mysql
  • oracle
  • hana
  • sqlserver
  • postgres

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.

For Example:

The following configures each of the available directives.

== <agent> <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>54.215.8.21</database_server>          <database_user>mke</database_user>          <database_password>dbpassword</database_password>          <database_port>1433</database_port>          <database_type>sqlserver</database_type>          <database_parms></database_parms>

“Subscribe and Share” statements… </agent> ==

The following example only configures the Service Now Instance access information.

<agent> <instance_name>ibmnvstrn.service-now.com</instance_name>         <instance_user>perspectium</instance_user>         <instance_password>p@ssw0rd1</instance_password> “Subscribe and Share” statements… </agent>

MS SQL Server

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. Oracle

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. Handlers

Each handler will have access to its section of the overall configuration. As already stated, there are two types of handlers that can be configured within the <agent> tag, the subscriber <subscribe> and the sharer <share>. Each of these directives will have zero or more nested <task> elements. Each <task> element will likely have unique directives that are required so the handler can perform its work. Each of these directives will be document along with the handler. Subscribers

Subscriber handlers exist to obtain a message from a queue, process 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 insert 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 phrased for encrypting</encryption_key>

</task> </subscribe> </agent> Subscribing to tables

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 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.

<tables> <includes> This directive encloses zero or more <include> directives. <include> An include directive is used to specify a table and action that you want to process. <excludes> <exclude> <includes> General SQL Replicator

replicator_sqlreplication.txt · Last modified: 2018/07/06 16:44 by paul