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.

Configure a DSN Connection in Linux

The following steps demonstrate how to create a DSN connection on Linux using an odbc.ini file.

  1. For Oracle GoldenGate 23ai versions prior to 23.6, you will first need to add a new ODBCINI environment variable for the SQL Server deployment using the steps below. Versions beginning with 23.6 automatically set this variable during deployment creation.

    1. Log in to the Service Manager web interface.

    2. From the left navigation pane, click Deployments and then select the Oracle GoldenGate SQL Server deployment. This expands the various settings for the deployment.

    3. Click Configuration and then click the plus sign (+) next to Environment Variables.

    4. Provide the following information in the two available fields. For the Environment Variable Value field, ensure it is the absolute path of the odbc.ini file:

      Environment Variable Name = ODBCINI

      Environment Variable Value = ${OGG_HOME)/msodbc/odbc.ini

    5. Click Submit to create the new variable and then restart the deployment from the Deployments pane for the changes to take effect.

  2. In the Oracle GoldenGate installation's msodbc folder, manually create an odbc.ini file and add data sources in this file using the following information and example.

    Example for Oracle GoldenGate 23ai versions prior to 23.6, where Driver must be set to ‘ODBC Driver 18 for SQL Server’
    
[mssql_source]
Driver = ODBC Driver 18 for SQL Server
Server = myserver,1433
Database = source_database
TrustServerCertificate=yes
    Example for Oracle GoldenGate 23ai versions 23.6 or later, where Driver must be set to ‘Microsoft ODBC Driver for SQL Server’
    
[mssql_source]
Driver = Microsoft ODBC Driver 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. Save and close the odbc.ini file.
  4. To create the database connection for the deployment, see Add Database Connections.

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.