Enabling access to the database servers v7

Use these configuration steps to use Replication Server on various types of database servers.

No special steps are required to enable access to a Postgres database server.

Enabling access to Oracle

Note

The instructions in this section apply only if Oracle is used as the publication or subscription database.

An Oracle JDBC driver jar file, such as ojdbc8.jar, must be accessible to the Java virtual machine (JVM) on the host running the publication server and the subscription server. If the publication server and subscription server are running on separate hosts, the Oracle JDBC driver must be accessible to the JVM on each host. Use Oracle JDBC driver version ojdbc8 or later.

Step 1: Download the Oracle JDBC driver, for example, ojdbc8.jar, from the Oracle download site to the host that runs the publication server.

Step 2: Copy the file ojdbc8.jar to the directory XDB_HOME/lib/jdbc.

$ su root
Password:
$ cd /usr/edb/xdb/lib/jdbc
$ cp /home/user/Downloads/ojdbc8.jar .
$ ls -l
total 4032
-rw-rw-r-- 1 root root  355655 Jan 25 02:38 edb-jdbc14.jar
-rw-rw-r-- 1 root root  716209 Jan 25 02:38 edb-jdbc17.jar
-rw-rw-r-- 1 root root  317816 Jan 25 02:38 jtds-1.3.1.jar
-rw-r--r-- 1 root root 2091137 Jan 28 16:45 ojdbc8.jar
-rw-rw-r-- 1 root root  642809 Jan 25 02:38 postgresql-9.4-1201.jdbc4.jar
Note

You can also copy the ojdbc8.jar file to the jre/lib/ext subdirectory of the location where you installed your Java runtime environment.

Note

Make sure to set the ODBC driver permission to a minimum of 644.

Note

Make sure to copy xdb6.jar along with ojdbc8.jar at /usr/edb/xdb/lib/jdbc/ if one or more tables in Oracle Publication contains an XMLType column when using Oracle to EDB Postgres Advanced Server/PostgreSQL replication.

Step 3: If the subscription server is running on a host different from the publication server, repeat steps 1 and 2 for the subscription server host.

Enabling access to SQL Server

Note

These instructions apply only if you're using SQL Server as the publication or subscription database.

Replication Server now supports both Microsoft JDBC and jTDS JDBC drivers. A new parameter option useJtdsDriver is added with the default value of true in both xdb_pubserver.conf and xdb_subserver.conf configuration files. This option when true uses jTDS driver for SQL Server connection. To use a Microsoft specific JDBC driver, uncomment this option and specify the value false in both the configuration files.

The MS SQL JDBC driver jar file mssql-jdbc-10.2.1.jre8.jar or the jTDS JDBC driver jar file jtds-1.3.1.jar must be accessible to the Java virtual machine (JVM) on the host running the publication server and the subscription server depending on which driver is being used. If the publication server and subscription server are running on separate hosts, the Microsoft JDBC or jTDS JDBC driver must be accessible to the JVM on each host.

When you install Replication Server, there is no jtds-1.3.1.jar or mssql-jdbc-10.2.1.jre8.jar placed in the directory XDB_HOME/lib/jdbc by the Replication Server installation. The user has to copy the required JDBC driver and rename them as mssql-jdbc.jar and jtds.jar . Alternatively, the user can create a symlink in XDB_HOME/lib/jdbc for the required JDBC driver. The symlink names should be as below:

mssql-jdbc.jar
jtds.jar
Note

For windows, we recommended that the user copy the required JDBC jars to the XDB_HOME/lib/jdbc folder and rename them.

Step 1: Be sure SQL Server Authentication mode is enabled on your SQL Server database engine. SQL Server Authentication mode allows the use of SQL Server logins such as the built-in system administrator login, sa.

Using the default settings for SQL Server installation, only Windows Authentication mode is enabled, which uses the accounts of the Windows operating system for authentication.

To permit SQL Server Authentication mode, you must change the authentication mode to Mixed Mode Authentication, which permits both Windows Authentication and SQL Server Authentication.

You can do this using SQL Server Management Studio. Refer to the SQL Server Management Studio documentation.

Step 2: Be sure SQL Server is accepting TCP/IP connections. In the SQL Server Configuration Manager, under SQL Server Network Configuration, be sure the TCP/IP protocol for the SQL Server instance is set to Enabled. The typical, default SQL Server instance names are MSSQLSERVER or SQLEXPRESS.

Step 3 (Required only for a SQL Server publication database): Be sure SQL Server Agent is enabled and running. SQL Server Agent is a Windows service that controls job scheduling and execution with SQL Server.

Replication Server uses SQL Server Agent for certain operations such as for scheduled shadow table history cleanup (see Scheduling shadow table history cleanup).

You can start SQL Server Agent by using SQL Server Configuration Manager. Refer to the SQL Server Configuration Manager documentation.