Configuring a Database Connection

Extract and Replicat Database Connectivity

Extract and Replicat connect to a SQL Server database using a system ODBC DSN (Data Source Name) and use ODBC for its metadata queries and transactional data processing.

Creating a Database Connection on Linux

Ensure that you have installed a supported version of the Microsoft ODBC driver by following the instructions under Prerequisites for Installing Oracle GoldenGate Microservices Architecture for SQL Server.

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

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

vi odbc_template_file.ini
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
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
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 either Microsoft ODBC Driver 17 for SQL Server or the 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:

Open the ODBC Data Sources (64-bit) application.
In the ODBC Data Source Administrator dialog box, select the System DSN tab, and then click Add.
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.
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.
For login authentication, select SQL Server authentication, specify the Login ID and Password information, and then click Next.
If configuring a connection using the Microsoft ODBC Driver 18 for SQL Server, click Next again to go to the last configuration page. Select the option for Trust server certificate and then click Back to proceed as you need to first enable the Trust server certificate before selecting the default database in the next step.
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.
Leave the next page of the wizard as-is and click Finish.
Click Test Data Source to test the connection.
If the test is successful, close the confirmation box and the Create a New Data Source box.
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, depending on the DSN connection used.

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 DSN 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 the DSN. For Linux DSN connections, use the MultiSubnetFailover=Yes option in the DSN entry.