Resources > Tech Guides

Getting Started with Real-Time Data Integration to Microsoft Azure SQL Database

Introduction

Striim in the Microsoft Azure Cloud enables companies to simplify real-time data integration into Azure by offering heterogeneous data ingestion, enrichment, and transformation in a single solution before delivering the data to Azure services with sub-second latency. Brought to you by the core team behind GoldenGate Software, Striim offers a non-intrusive, quick-to-deploy, and easy-to-iterate solution for streaming data integration into Azure.

In this Tech Guide, we’ll show you how to easily integrate your on-premise Oracle Database with Azure SQL Database in the cloud using Striim’s PaaS offering available in the Azure Marketplace. We will use Striim’s log-based change data capture (CDC) to deliver a solution that doesn’t impact your source Oracle Database’s performance.

Striim On-Premise to Azure Integration

Install Striim Cluster on Azure

 

  1. Login to your Azure dashboard, and select the “+ Create a resource” button in the upper left-hand corner
  2. Type in “Striim for Real-Time Integration to SQL Database” and select the marketplace offering. At the bottom of the page select “Create.”
  3. Choose a VM user name, VM user password (make note of the user name and password as you will need them to access the Striim server via ssh), and resource group name. You may use the same name for the VM user, resource group, and Striim cluster name.
  4. Optionally, change the location of the VM, then click OK.
  5. If you want to create multiple Striim server nodes, change Standalone to Cluster, set the number of nodes, and optionally choose a larger VM with more resources, depending on your expected workload.
  6. Choose your Striim cluster name and cluster password (make note of the cluster name and password, as you will need them to connect the Forwarding Agent), then click OK.
  7. Choose a unique domain name and Striim admin password (make note of the password as you will need it to log into the Striim web UI), then click OK.
  8. Azure will now validate your VM. When validation is complete, click OK.
  9. Click Purchase. Deployment may take several minutes. Note that when setting up Striim for the first time, you have a free 30-day trial to experiment with Striim.
  10. When the deployment is complete, click <cluster name>-masternode in your dashboard
  11. Make note of the DNS name label (<cluster name>:<Azure region>.cloudapp.azure.com) as you will need it for the remaining steps
  12. Now, a Striim Cluster is set up and ready to go on an Azure SQL Database VM. The next steps are to install a Microsoft JDBC driver, Striim Agent on Premise, and create an end-to-end streaming application in Striim from Oracle Database to Azure SQL Database using Striim’s web UI.

 

Install Microsoft JDBC driver in the Azure Striim Cluster (only required for Azure SQL Database solutions)

 

  1. Following the steps here: https://docs.striim.com/en/installing-the-microsoft-jdbc-driver.html in your favorite browser, download the latest SQL Server JDBC driver to your local machine.
  2. Open a command line window on your local machine, switch to the directory that the file was downloaded, and extract it.
  3.  Enter the following command to copy the driver to Striim running on Azure. This is based on my environment, so change to the directory of your latest SQL Server JDBC driver versionscp sqljdbc_6.2/enu/mssql-jdbc-6.2.2.jre8.jar <VM name>@<DNS name label>:~/
  4. When prompted, enter the VM password you chose earlier
  5. Now, enter the following command to log into the Striim VM via command line: ssh <VM name>@<DNS name label>
  6. When prompted, enter the VM password again.
  7.  Enter the following to install the Microsoft JDBC driver in Striim:
    • Sudo su
    • <VM password>
    • cp <driver name> /opt/striim/lib
    • Now, restart the Striim server node by using: sudo systemctl stop striim-node and sudo systemctl start striim-node
  8. If you created multiple Striim server nodes during the setup process in Azure, repeat steps 3 – 7 on each node. The VM names and DNS name labels for the other nodes are the same as the master node’s, but with digits, starting with 0, appended to the server name. For example, if the master node’s name was mycluster, the first additional node’s name would be mycluster0 and its DNS name label would be mycluster0.westus.cloudapp.azure.com.
  9. You’re all set! You now have a Striim Cluster set up in Azure with Microsoft JDBC Drivers and are ready to use the Azure SQL Database.

Install Striim Agent On-Premise

 

  1. Log in to the Striim web UI on your local machine.
    • Using a compatible web browser on your local machine (we recommend Chrome), go to http://<DNS name label>:9080, enter admin as the user name, enter the Striim admin password you set earlier during setup, and click Log In.
    • If you have any issues here not being able to connect to Striim’s web UI, ensure the Azure VM is running by navigating to Azure’s dashboard and clicking on your VM in the “All resources” pane.At this point, if you are new to Striim, we recommend you open the documentation from the Help menu in the upper right-hand corner and follow the Quick Start, beginning with “Viewing dashboards.”
  2. Once you log in, you should be directed to a create new app wizard. Click the Download Agent link in the menu bar on the left-hand side of the screen.
  3. You’ll be forwarded to a page that enables you to download the Striim Agent. Click on the version that matches your Striim cluster (Click Help -> About in the upper right-hand corner of the web UI), and download the zip file.
  4. Unzip the recently downloaded zip file. This process will vary depending on your operating system.
  5. Move the Agent directory to wherever you’d like on your local machine. Congratulations, you now have Striim’s lightweight agent installed!
  6. Before we start creating the Striim application, first you need to install an Oracle JDBC Driver in the Striim Agent. In order to connect to your Oracle Database, you need to install the corresponding Oracle JDBC Driver in the Striim Agent:
    • Navigate to http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html in your favorite browser and select your corresponding database version
    • Accept the license agreement, and download the ojdbc<version>.jar.
    • Navigate to where you placed the Striim Agent directory, and copy your recently downloaded ojdbc<version>.jar into the Agent’s lib directory.
  7. Now you’re all set to start the Agent. Open a new command line window, and switch directories to where you unzipped your zip file.
  8. Now inside of the Agent directory, start the Agent. The syntax to start is: ./bin/agent.sh -c -p -S
  9. Now that your Agent is started, and you can start transferring data from your on-premise Oracle Database to an Azure SQL Database.

Build On-Premise Oracle Database to Azure SQL Database Striim Data Flow

 

  1. Again, if you are new to Striim, we recommend you open the documentation from the Help menu in the upper right-hand corner and follow the Quick Start to learn more about Striim.
  2. In the web UI, navigate to the Apps page and press the blue Add App button.
  3. Select Start from Scratch to get started using Striim’s Flow Designer.
  4. Choose a Name for your app, and create a new Namespace. These can be whatever you want, as long as they are unique.
  5. Now, drag the Oracle CDC code block from the Sources tab on the left hand side of the web UI and configure the source with your Oracle Database configuration. These fields will be unique based on your own settings.
  6. Press Save to save the OracleReader source.
  7. While building your streaming application, it’s a great practice to deploy and test each code block while you’re going, so you know where it went wrong.
  8. Drag a Database Writer target from the Targets pane on the left-hand side and configure your output to Azure’s SQL Database.
  9. Your connection URL is located in the Azure Dashboard by selecting SQL databases -> your SQL Database -> Show Database Connection Strings -> JDBC. Copy the whole string, and delete password={your_password_here}.
  10. Now you’re ready to deploy and run your app. Go back to the Striim web UI, and select the Created drop-down menu at the top. For the agent or sourceFlow, make sure you select “agent” in the Deployment Group, as we want to read data from your on-premise Striim Agent and write to Azure SQL Database in the cloud.
  11. Press Start App to run the app.
  12. Now, go to your Microsoft Azure SQL DB and test it! Navigate back to the Azure dashboard, and press SQL databases on the left-hand menu bar. Select the SQL database that you are transferring data into.
  13. Press the Query editor (preview) option.
  14. Login with the credentials you specified when setting up the Azure SQL database.
  15. An easy way to test your streaming application is by select count(*) from your SQL database to count the number of rows in your database. As long as the number’s greater than 0, you’re good to go!
  16. In order to view the data in your Azure SQL Database,  simply execute select * from TEST4;
  17. Voila, you’ve now integrated data from an on-premise Oracle Database to an Azure SQL database, all while being able to take advantage of Striim’s advanced intelligence and dashboard tools in flight.

Conclusion

As shown above, with wizards-based PaaS offering, Striim helps you rapidly set up streaming data pipelines to Azure SQL Database from your enterprise data sources without impacting their performance. Striim is not limited to moving real-time data to Azure SQL Database. You can use Striim for other Azure targets including Azure Cosmos DB, Azure SQL Data Warehouse, Azure Blob Storage, Azure Event Hubs, Azure Database for MySQL,  Azure Database for PostgreSQL, and Azure Data Lake Storage. By leveraging reliable, secure, and highly-available streaming data pipelines, you can seamlessly extend your data center to Azure environments to modernize and transform your business.

 

We invite you to test drive Striim’s solution for Azure Cloud, either by downloading the Striim platform, or provisioning the Striim PaaS solution in the Azure Marketplace.

 

Striim Application Source Code

UNDEPLOY APPLICATION azureTest;

DROP APPLICATION azureTest;




CREATE APPLICATION azureTest;




CREATE FLOW agentFlow;




CREATE  SOURCE oracSource USING OracleReader  (

Compression: false,

DictionaryMode: 'OnlineCatalog',

StartTimestamp: 'null',

SupportPDB: false,

connectionRetryPolicy: 'timeOut=30, retryInterval=30, maxRetries=3',

FetchSize: 1000,

QuiesceMarkerTable: 'QUIESCEMARKER',

CommittedTransactions: true,

QueueSize: 2048,

ReaderType: 'LogMiner',

Username: ‘<username>’,

FilterTransactionBoundaries: true,

SendBeforeImage: true,

XstreamTimeOut: 600,

ConnectionURL: '<connectionURL>',

Tables: '<sourceSchema.sourceTable>',

OutboundServerProcessName: 'WebActionXStream',

Password: '<password>'

)

OUTPUT TO oraRawData;




END FLOW agentFlow;




CREATE FLOW targetFlow;




CREATE  TARGET testdb USING DatabaseWriter  (

PreserveSourceTransactionBoundary: 'false',

Username: '<username>',

BatchPolicy: 'EventCount:10,Interval:60',

CommitPolicy: 'EventCount:10,Interval:60',

ConnectionURL: '<jdbc connection url>',

Tables: '<SOURCESCHEMA.SOURCETABLE,targetschema.targettable>’,

Password: '<password>',

CheckPointTable: 'CHKPOINT',

Password_encrypted: true

)

INPUT FROM oraRawData;




END FLOW targetFlow;




END APPLICATION azureTest;