Oracle GoldenGate (OGG) best practices dictate that the OGG Apply process run on the target database server. However, there are instances where this configuration is not practical. In this article we shall discuss a solution where OGG Apply may be configured on a mid-tier server and apply data over a network to a remote SQL Server Database.
A typical Oracle GoldenGate implementation that follows best practices guidelines is depicted below.
In this configuration an OGG Extract process captures transactions from the source database logs and writes them to a Local GoldenGate Trail. An Extract Data Pump reads from the Local Trail and transmits the data over TCP/IP to Remote GoldenGate Trails on a database server. On the target database server, an Oracle GoldenGate Replicat process reads the data sent across the network and applies the records to the target database. For Microsoft SQL Server, Replicat connects to the database via ODBC and OLEDB, via the SQL Server Native Client driver and can use either Shared Memory (if running on same host), Named Pipes or the TCP/IP network protocol.
In some instances it may not be practical to have the GoldenGate Replicat run on the target database server. Settings within the Microsoft SQL Server Network Connections provide a way for us to place the Replicat on a mid-tier server isolated from the target Microsoft SQL Server Database. For manageability and ease of use, this mid-tier server should be located within the same data center and network as the source database. One possible architecture for this scenario is depicted below.
To allow the network apply of data via SQL Server Native Client, several modifications must be made to the target SQL Server instance and Windows server.
Ensure that either the TCP/IP or Named Pipes Network Protocol is enabled for the target SQL Server instance. Enable the protocol via SQL Server Configuration Manager.
1. Start SQL Server Configuration Manager.
- Click Start
- Point to All Programs
- Click Microsoft SQL Server
- Click Configuration Tools
- Click SQL Server Configuration Manager
2. In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration.
3. In the console pane, click Protocols for <instance_name>
4. In the details pane, right-click TCP/IP, and then click Enable.
5. In the console pane, click SQL Server Services.
6. In the details pane, right-click SQL Server (<instance_name>), and then click Restart, to stop and restart the SQL Server service.
For this configuration, Oracle GoldenGate will require a database user for SQL Server Authentication. Setup the database user with the privileges specified in the Fusion Middleware Installing and Configuring Oracle GoldenGate for SQL Server documentation.
Allow SQL Server connections through the Windows Firewall. One method for doing this is to add a program exception to the firewall using the Windows Firewall item in Control Panel.
1. On the Exceptions tab of the Windows Firewall item in Control Panel, click Add a program.
2. Browse to the location of the instance of SQL Server that you want to allow through the firewall, for example C:\Program Files\Microsoft SQL Server\MSSQL12.<instance_name>\MSSQL\Binn, select sqlservr.exe, and then click Open.
3. Click OK.
Install Oracle GoldenGate for SQL Server on the mid-tier server.
Install SQL Server Native Client 11.0 on the mid-tier server. Create a System DSN to the target SQL Server Database instance.
1. Select Administrative Tools from the Control Panel.
2. Select Data Sources (ODBC).
3. Select the System DSN tab.
4. Click the Add button.
5. Select the SQL Server Native Client 11.0 Driver and click the Finish button.
6. Enter a name for this data source. This name will be used as the TARGETDB setting in Replicat.
7. Enter the server details for the target SQL Server Database.
8. Click the Next button.
9. Select With SQL Server authentication and enter the GoldenGate database user login credentials.
10. Click the Next button.
11. Click the Next button.
12. Click the Finish button.
13. Click the Test Datasource button.
If the test is successful, a connection was established between the mid-tier server and the SQL Server Database; continue setting up Oracle GoldenGate and test the architecture.
It should be noted that data apply performance will be (significantly) less than that of a best practices architecture. You should perform thorough testing before attempting to implement this architecture into a production environment.
By default, the SQL Server Native Client connection is unencrypted. To ensure your data is secure, select the Use strong encryption for data option when configuring the SQL Server Native Client DSN. This setting requires a security certificate be provisioned for the mid-tier and database servers.
In this article we discussed how to connect to a remote SQL Server Database from a mid-tier server running Oracle GoldenGate Replicat. Although this capability is present with SQL Server Native Client 11, you should always follow the best practice of having Replicat run on the target database server.