Prepare Database Connection

Learn about configuring database connection, system, and parameter settings for Oracle GoldenGate for SQL Server.

Oracle GoldenGate for SQL Server connects to SQL Server databases using a pre-packaged ODBC driver. Connections can be established using a Data Source Name (DSN) or using a direct connection and supplying the database server host, port, database, and other information.

Using DSN connections requires connection details to be listed in an odbc.ini file, while using direct entries are entered manually when adding a database connection to the Administration Service's web interface or through the Admin Client.

Creating a Database Connection on Linux

Ensure that you have installed a supported version of the Microsoft ODBC driver by following the instructions under unresolvable-reference.html#GUID-4CD9511F-F3FC-4A23-8568-6EB9CF0855D0.

After the ODBC driver is installed, follow the example below to create an ODBC DSN for Linux:

  1. Create a template file for your data source(s):

    vi odbc_template_file.ini

  2. Describe the data source in the template file. Multiple unique DSN entries can be listed in the template file, if needed.

    In the following example, mydsn_2017_source is the DSN name, which will be used with DBLOGIN and SOURCEDB or TARGETDB to connect to the Extract or Replicat to the database. 

    [mydsn_2017_source]            
Driver = ODBC Driver 18 for SQL Server            
Server = myserver,1433            
Database = source_database
TrustServerCertificate=YES

    Note:

    For a complete list of available connection options, review the Microsoft documentation at the following link:

    https://learn.microsoft.com/en-us/sql/connect/odbc/dsn-connection-string-attribute?view=sql-server-ver16

  3. Install the data source using the following command.

    odbcinst -i -s -f odbc_template_file.ini

    This command adds the DSN to the system odbc.ini file. For more information, see:

    https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/connection-string-keywords-and-data-source-names-dsns?view=sql-server-2017

  4. To create the database connection for the deployment, see Add Database Credentials

Configure a DSN Connection in Windows

Before creating a database connection for Oracle GoldenGate processes running on Windows, install the latest version of Microsoft ODBC Driver 18 for SQL Server.

Follow these steps to create a system DSN on the Windows server where Oracle GoldenGate is installed.

To create a SQL Server DSN:

  1. Open the ODBC Data Sources (64-bit) application.
  2. In the ODBC Data Source Administrator dialog box, select the System DSN tab, and then click Add.
  3. Under Create New Data Source, select the ODBC Driver {version} for SQL Server and then click Finish. The Create a New Data Source to SQL Server wizard appears.
  4. Enter the following details, and click Next:
    • Name: Can be of your choosing. In a Windows cluster, use one name across all nodes in the cluster.
    • Description: (Optional) Type a description of this data source.
    • Server: Type the SQL Server connection string or server\instance name. For Always On connections, use the listener\instance name of the Always On Availability Group.
  5. For login authentication, select SQL Server authentication, specify the Login ID and Password information, and then click Next.
  6. Click Next again to go to the last configuration page and select the option for Trust server certificate, then click Back to proceed. You need to first enable the Trust server certificate before selecting the default database.
  7. Select Change the default database to, and then select the source or target database from the list. Enable the Use ANSI settings, and click Next.
  8. Leave the next page of the wizard as-is and click Finish.
  9. Click Test Data Source to test the connection.
  10. If the test is successful, close the confirmation box and the Create a New Data Source box.
  11. Repeat this procedure for each SQL Server source and target database, where Oracle GoldenGate process will connect.

Connecting to the Listener of a SQL Server Always On Configuration

Extract and Replicat can connect to the listener of an Always On configuration or directly to the current primary replica of the group.

The advantage of creating the connection to the listener is that Extract or Replicat can reconnect to the new primary replica upon failover without having to reconfigure the connection string to the new primary.

An Extract can also be configured to route its read-only queries to an available readable, synchronous mode secondary replica. By default, if Extract connects to a listener, all processing will be done against the primary replica, but if an Extract is configured with the TRANLOGOPTIONS ALWAYSONREADONLYROUTING parameter, its read-only queries are routed by the listener to an available readable secondary replica.

See TRANLOGOPTIONS and Requirements Summary for Capture and Delivery of Databases in an Always On Availability Group for more information.

If creating the DSN to connect to a Listener of an Always On configuration, enable the Multi-subnet failover option when creating a DSN. For Linux DSN connections, use the MultiSubnetFailover=Yes option in the DSN entry.