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
To create a DSN connection for Oracle GoldenGate processes, you will
first need to add a new environment variable for the Oracle
GoldenGate for SQL Server deployment and then create an
odbc.ini
file to store the connection
attributes.
-
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/deployment/mssql/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
deployment
folder, manually create anodbc.ini
file and add data sources in this file using the following information and example.Example:[mssql_source] Driver = ODBC Driver 18 for SQL Server Server = myserver,1433 Database = source_database TrustServerCertificate=yes
-
Save and close the
odbc.ini
file. -
To set up the database connection from Oracle GoldenGate for a SQL Server 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.