Using Oracle Cloud Infrastructure (OCI) GoldenGate with Azure SQL and Microsoft SQL Server Databases

OCI GoldenGate is a fully managed service providing a real-time data mesh platform, which uses replication to keep data highly available and enable real-time analysis. We recently made a new deployment type available for Azure SQL and Microsoft SQL Server databases in OCI GoldenGate. In this article, we will learn more about this new deployment type and connection types for Azure SQL Database, Azure SQL Managed Instance, Microsoft SQL Server, and Amazon RDS for SQL Server.
OCI GoldenGate makes moving data in and out of Azure SQL and Microsoft SQL Server databases simpler than ever.

The OCI GoldenGate Microsoft SQL Server deployment type includes support for the following databases:

• Azure SQL Database (target only)
• Azure SQL Server Managed Instance (source/target)
• Microsoft SQL Server 2012, 2014, 2016, 2017, 2019 (source/target)
• Amazon RDS for SQL Server 2012, 2014, 2016, 2017, 2019 (source/target)

Creating a Microsoft SQL Server Deployment

You can create a Microsoft SQL Server deployment using the OCI Console, CLI, or API. From the OCI Console menu, go to Oracle Database, click GoldenGate, and click Create deployment.

Go through the deployment creation screens, and select Microsoft SQL Server when prompted for a technology. Finally, click Create to start the creation of the deployment.

Create a Connection to Azure SQL and Microsoft SQL Server Databases

To create a Connection, go to the Overview or Connections page and click Create connection. Pick Azure SQL Database, Azure SQL Managed Instance, Microsoft SQL Server, or Amazon RDS for SQL Server as the Type and click Next.

In this article, we will use Azure SQL Managed Instance, but the definition of the connection is the same across all SQL Server technologies.

Enter your database information manually to connect to any of the Azure SQL or Microsoft SQL Server connections. Provide the Database name, Host, and Port (1433 by default), then enter a Username and Password. Finally, specify the SSL details, if any.

Enable Network connectivity via private endpoint if your database can only be accessed using a private IP address.

Click Create to proceed with the Connection creation.

Assign a Connection to a Deployment

A Connection must be assigned to a Deployment before it can be used. The assignment can be done from the Deployment or the Connection. You don’t need to wait for the Connection to be Active to assign it to a Deployment.

Open your Deployment, click Assigned connections, then click Assign connection.

Select your Connection in the list and click Assign connection to create the assignment.

 
Note: Connection types are compatible with specific deployment types. For example, Microsoft SQL Server Connections can only be assigned to SQL Server Deployments. In heterogeneous use cases, you must use different Deployments and connect them using Distribution Paths.
So, if you plan to send data from an Azure SQL Managed Instance database into an Autonomous Transaction Processing (ATP) database, you will need two OCI GoldenGate Deployments:

• One for Azure SQL Managed Instance (deployment type: Microsoft SQL Server)
• One for ATP (deployment type: Oracle)

Create data replication processes using Azure SQL and Microsoft SQL Server Databases

You can start designing your Extracts and Replicats once you have assigned the required Connections to your Deployment(s). I recommend reviewing this documentation chapter to understand the GoldenGate requirements for SQL Server databases.

Note: Azure SQL Database is not currently supported as a source. You can only use it in your Replicats as a target. The other connection types (Azure SQL Managed Instance, Amazon RDS for SQL Server, and Microsoft SQL Server) can be used in Extracts and Replicats as sources and targets.

Go to your Deployment details page and click Launch console to start the OCI GoldenGate Console. Open the menu, click Configuration, review your Credentials, and click Connect to test the connectivity to your sources and/or targets.

Once connected, you must click on the plus sign next to TRANDATA information to enable GoldenGate to acquire the transaction information from the transaction records. Provide a table name or schema name along with wildcards, then click Submit.

 
Now click Overview and Add Extract (plus icon) in the Extracts panel to create either an Initial Load Extract or a Change Data Capture Extract.
We will start with an Initial Load Extract to initialize the target schema. Click Next.

Enter a Process Name, specify the Credential Domain and Alias, and type the Trail Name. Click Next.

Specify which tables and schemas GoldenGate will capture data from in the Parameter Files page, then click Create and Run.

 
Next, we will create a CDC Extract. Add an Extract and select Change Data Capture Extract. Enter a Process Name and choose the Credential Domain and Alias. Provide the Trail Name, and optionally alter the other Extract options.

Click Next.

 
Specify which tables and schemas GoldenGate will capture data from in the Parameter Files page, then click Create and Run.

 
You should now have two Extracts listed on the Administration Service page. The Initial Load Extract will stop once it has processed all the records.

Creating a Replicat follows a similar process: click Overview and Add Replicat (plus icon) in the Replicats panel. Select a Replicat type from Classic, Coordinated, or Parallel. You can find more information about the different Replicat types here. We will select Parallel in this example and click Next.
 
Enter a Process Name, select a Credential Domain and Alias, specify the initial load Trail Name and enter a Checkpoint Table name (previously created at the Configuration > Credentials level). Click Next.

Specify how the source and target tables will be mapped by the Replicat and click Create and Run.
 
Once the Replicat consuming the trail from the Initial Load Extract has finished processing the records, stop it and add a Replicat to consume the trail file created by the CDC extract. Follow the same steps outlined above to configure this new Replicat.

We have seen how easy it is to replicate data from and into Azure SQL and Microsoft SQL Server databases with OCI GoldenGate. You can follow similar steps to integrate these technologies with any source and target supported by GoldenGate on-premises and OCI GoldenGate. As a next step, I recommend going through the following quickstart to get familiar with OCI GoldenGate and its integration with Azure SQL databases: Replicate data from Azure SQL Managed Instance to Autonomous Transaction Processing. Stay tuned for new blogs coming up soon!