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.
-
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.-
Log in to the Service Manager web interface.
-
From the left navigation pane, click Deployments and then select the Oracle GoldenGate SQL Server deployment. This expands the various settings for the deployment.
-
Click Configuration and then click the plus sign (+) next to Environment Variables.
-
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
-
Click Submit to create the new variable and then restart the deployment from the Deployments pane for the changes to take effect.
-
-
In the Oracle GoldenGate installation's
msodbc
folder, manually create anodbc.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, whereDriver
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, whereDriver
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: - Save and close the
odbc.ini
file. - 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:
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.