HVR 05 - Setup HVR HUB with MySQL Database on Azure VM

You need to follow these steps to set up an HVR Hub with a MySQL database on an Azure VM.

Database, Agane, source, and destination details can be found in the previous blogs

Step 1: Create a VM on Azure

Ensure you have an Azure VM running Windows with administrative access.

Step 2: Install HVR HUB on VM

Download the HVR setup exe file

Install the HVR HUB on the Windows VM. 

Click next and choose HVR hub this time. 

Click "Next." The default port is 4340 for HTTP. If you want to use a certificate and run on HTTPS, the port will be 4341.

Click Next and choose “Local System Account”.

Click on the Check Box and Click Next to install the HVR Hub

Click "Finish" once everything is properly configured.

Open the inbound port on the VM to allow the HVR HUB to run on the browser.

Select "Inbound rules" and click on "Port."

Open ports 80 and 4340 for HTTP, and ports 443 and 4341 for HTTPS.

Give the name of the port config

Try opening the HVR hub using the VM's IP address on port 4341. If you see the screen below, then everything is set up correctly.

Step 3: Install HVR Repository

I have chosen the MySQL server for the HVR repository. HVR also supports other databases, so feel free to select any other supported database based on your preference.

I am creating a MySQL server on the Azure cloud.

Make sure to allow the port and IP address to access this MySQL server from your machine or any other location where you intend to manage this HVR repository.

I have installed MySQL Workbench to test the data.

Step 4: Connect HVR HUB to the MYSQL Repository

Open the HVR hub in your browser and select MySQL as the HVR repository server as described below. 

Enter the username, hostname, port, and password, and attempt to establish the connection.

If you get the above error then go to Azure Portal and change the “require_secure_transport” setting from OFF to ON.

Save the setting.

Now try connecting that again, you should be able to connect it successfully.

Upload the License: 

You need a valid license to use HVR. Once you have the license, simply upload it as instructed below.

Verify the license and finish the setup. You'll need to provide the HVR HUB username and password to log in from the browse.

After completing the setup, you should be able to view multiple tables created by the HVR HUB. These tables should be managed exclusively through the HVR HUB UI.

If you see the screen below, you are ready to proceed with creating locations.

Fivetran HVR 04 - Setup Destination Snowflake Database

Creating a Snowflake account and setting up a database involves several steps, including signing up for the service, configuring your environment, and creating the necessary database objects. Here's a step-by-step guide to get you started:

Step 1: Sign Up for a Snowflake Account

• Visit the Snowflake Website:

• Go to the Snowflake sign-up page.

• Fill in Your Details:
• Provide your name, company, email address, and other required information.
• Choose your cloud provider (AWS, Azure, or Google Cloud) and region where you want your Snowflake instance to be hosted.

• Complete the Registration:

• Submit the form and follow the instructions in the confirmation email you receive to activate your account.

Step 2: Log In to Your Snowflake Account

• Access the Snowflake Console:

• After activation, log in to your Snowflake account using the URL provided in the confirmation email.
• The URL typically looks like https://<account_name>.snowflakecomputing.com.

Step 3: Create a Database

• Open the Snowflake Web Interface:

• Once logged in, navigate to the Snowflake web interface.

• Navigate to Databases:

• In the top menu, click on Databases to open the databases section.

• Create a New Database:

• Click on the Create button to open the Create Database dialog.
• Enter the name for your new database and provide any optional comments.
• Click Finish to create the database.

Alternatively, you can use SQL to create a database:

CREATE DATABASE HVR_DESTINATION;

 

 

 

 

 

Fivetran HVR 03 - Setup HVR Agent on Source Server

 

HVR Agent Setup

Fivetran HVR (High Volume Replication) is a data integration and replication technology that focuses on real-time data movement, synchronization, and transformation across diverse environments, particularly those involving large volumes of data. Here's a detailed look at its components and functionality:

To install the HVR agent on a Windows VM, follow these steps:

Prerequisites:

• Windows VM: Ensure you have a Windows VM with administrative access.

• HVR Software: Obtain the HVR software package from the Fivetran HVR support portal or from your Fivetran representative.

We will use the same server where we installed the Postgres database in our previous blog.

Steps to Install HVR Agent:

• Download the HVR Software:

• Download the HVR installation package for Windows from the Fivetran HVR website or your account manager. https://fivetran.com/dashboard/account/downloads

• Extract the Package:

• Extract the contents of the downloaded package to a directory on your VM.

• Install HVR:

• Open a command prompt with administrative privileges.
• Navigate to the directory where you extracted the HVR package.
• Run the installer by executing the following command:
• Follow the on-screen instructions to complete the installation process. You may be prompted to choose an installation directory and to agree to the license terms

 

Click on Next

 

Make sure you choose HVR Agent as mentioned above screenshot. Click Next

 

Choose Local System account. (You can also choose specific user as well but while connecting it from the HVR Hub you have to use that.) Click Next

 

Check the Environment box, and click Next.

 

Click on Finish.

You should be able to see that service running on the 4343 default HVR port.

 

Make sure you open the port from Azure NSG VM so that HVR Hub can access this Agent on the 4343 port.

Fivetran HVR 02 (Install Portgres on a Azure windows VM) - Source Setup Part 1

 

Step 1: Create an Azure Windows VM

Log in to Azure Portal:

• Go to the Azure portal (https://portal.azure.com/).
• Click on "Create a resource" and select "Virtual Machine".

Configure the VM:

• Choose an appropriate size and image (Windows Server).
• Configure the instance details, including the resource group, VM name, region, and size.
• Set up an administrator account (username and password).

Review and Create:

• Review the settings and click "Create" to deploy the VM. (you can update the networking rule while creating the VM or do it later as mentioned in the next step)

Set up Networking:

• Configure the networking settings, ensuring that the VM has a public IP address.
• Open the necessary ports in the Network Security Group (NSG). You need to allow:
• Port 3389 for RDP access.
• Port 5432 for PostgreSQL.
  
  

Step 2: Install PostgreSQL on the Windows VM

RDP into the VM:

• Use Remote Desktop to connect to the VM.
• Open the Remote Desktop Connection application and enter the public IP address of the VM.
• Log in with the administrator credentials.

Download PostgreSQL Installer:

• Open a web browser on the VM and go to the PostgreSQL download page: https://www.postgresql.org/download/windows/
• Download the PostgreSQL installer.

Install PostgreSQL:

• Run the installer and follow the installation steps.
• During the installation, set a password for the PostgreSQL superuser (Postgres).
• Complete the installation with the default settings.

Complete the Postgres setup on VM.

Step 3: Configure PostgreSQL to Allow Remote Connections

Edit the PostgreSQL configuration file:

• Open the file Postgresql.conf located in the PostgreSQL data directory (default: C:\Program Files\PostgreSQL\<version>\data).
• Find the line #listen_addresses = 'localhost' and change it to:

        listen_addresses = '*'

Edit the pg_hba.conf file:

• Open the file pg_hba.conf located in the PostgreSQL data directory.
• Add the following line to allow connections from any IP address (or specify a range for more security):

        host all all 0.0.0.0/0 md5

Restart PostgreSQL service:

• Open the Services application from the Start menu.
• Find the PostgreSQL service, right-click, and select "Restart".

Step 4: Configure Windows Firewall to Allow PostgreSQL Connections

Open Windows Firewall:

• Open the Control Panel, go to System and Security, and then Windows Defender Firewall.

Create a New Inbound Rule:

• Click on "Advanced settings" to open the Windows Firewall with Advanced Security window.
• Right-click on "Inbound Rules" and select "New Rule".
• Select "Port" and click "Next".
• Select "TCP" and specify port 5432. Click "Next".
• Allow the connection and click "Next".
• Specify when the rule applies and click "Next".
• Give the rule a name (e.g., "PostgreSQL") and click "Finish".

Add a rule 

I have allowed all the ports for the testing but ideally, you should only allow port 5432 for Postgres

Step 5: Access PostgreSQL from PGAdmin

Download and install PGAdmin:

• Download and install PGAdmin on your client machine from https://www.pgadmin.org/download/.

Add a new server in PGAdmin:

• Open PGAdmin and right-click on "Servers" to create a new server.
• In the "General" tab, provide a name for the server.
• In the "Connection" tab, enter the public IP address of your Azure VM, the username (Postgres), and the password you set earlier.
• Click "Save" to connect to the PostgreSQL server on your Azure VM.

 

You should now be able to access and manage your PostgreSQL database on the Azure Windows VM using PGAdmin from your client machine.

Create a table and add some data into the table which will be used for the replication 

drop table cars;

CREATE TABLE Cars (
  brand VARCHAR(255),
  model VARCHAR(255),
  year INT,
	creation_time TIMESTAMP
);

INSERT INTO cars( brand, model, year, creation_time) VALUES ('audi', 'a3', 2007, current_timestamp);
INSERT INTO cars( brand, model, year, creation_time) VALUES ('bmw', 'b1', 2012, current_timestamp);
INSERT INTO cars( brand, model, year, creation_time) VALUES ('merc', 'a4', 2107, current_timestamp);

select * from cars;

Fivetran HVR 01 - Introduction

Fivetran HVR (High Volume Replication) is a data integration and replication technology designed for real-time data movement, synchronization, and transformation across various environments, especially those handling large volumes of data. Here’s an in-depth overview of its components and functionality:

 

 

Key Features and Capabilities:

• Real-Time  Data Replication:

• HVR enables real-time data replication between source and target systems, ensuring that data changes are reflected almost instantaneously.

• High Volume Data Handling:

• It is designed to handle large volumes of data efficiently, making it suitable for enterprises with substantial data throughput requirements.

• Change Data Capture (CDC):

• HVR incorporates CDC to capture and replicate only the data that has changed in the source system, rather than performing full data loads. This increases efficiency and reduces latency.

• Support for Diverse Environments:

• It supports a wide range of databases, data warehouses, and cloud platforms, providing flexibility in terms of data sources and destinations.

• Data Transformation:

• HVR includes capabilities for transforming data during the replication process, allowing for data cleaning, enrichment, and formatting as it moves between systems.

• Minimal Latency:

• By capturing and replicating changes in real-time, HVR ensures minimal latency between data updates in the source and target systems.

Use Cases:

• Database Migration:
• HVR is often used to facilitate database migrations by ensuring continuous data synchronization between old and new systems until the migration is complete.

• Data Warehousing:
• Enterprises use HVR to feed data warehouses with real-time updates from transactional systems, ensuring that analytics and reporting are based on the latest information.

• Disaster Recovery:
• By replicating data to secondary systems in real-time, HVR supports disaster recovery strategies, ensuring data availability and resilience.

Fivetran's Integration:

Fivetran's acquisition of HVR enhanced its data integration platform by adding robust real-time data replication capabilities. This allows Fivetran to offer a comprehensive solution that combines automated data extraction, loading, and transformation with high-volume, real-time replication, making it a powerful tool for enterprises looking to streamline and scale their data operations.

In summary, Fivetran HVR is a robust solution for enterprises requiring efficient, real-time data replication and transformation across various systems, particularly in high-volume environments.

Azure Application Gateway with APIM Internal Mode: Set Up Part 3 (Exposing Developer Portal on Internet and using path-based routing for external API in Application Gateway)

We are going to change only the Developer Portal details so that we can access it through Application Gateway from the Internet. No change is required on API Manger Gateway as we have tested that from the Application Gateway.

There is an issue that we cannot use the same RootCA certificate for the Gateway and Developer Portal as one RootCA can cater to only one backend. We must change the RootCA certificate for the Developer Portal so that we can expose it. So, we will keep the old RootCA certificate for the Gateway and create a new RootCA and SSL cert for the Developer Portal, but we need to make sure that we don’t change the FQDN which is devportal.demo.com as we have used the same name in Private DNS Zone.

Create a new RootCA and SSL certificate:

• Let’s follow the same process I used in the first part of the blog.

C:\Work\study\APIM\demo\portal>openssl req -x509 -newkey rsa:4096 -keyout portal.key -out portal.crt -days 365 -nodes
...+.+..+...+....+.....+...+....+++++++++++++++++++++++++++++++++++++++++++++*.+......+............+........+...+....+++++++++++++++++++++++++++++++++++++++++++++*..+....+...+...+...+.....+.........+...........................+....+..+..........+.........+.....+...+.+.....+.........+...+..........+........+......................+.....+++++
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:NZ
State or Province Name (full name) [Some-State]:Wellington
Locality Name (eg, city) []:Wellington
Organization Name (eg, company) [Internet Widgits Pty Ltd]:student
Organizational Unit Name (eg, section) []:student
Common Name (e.g. server FQDN or YOUR name) []:fabrikamportal.com
Email Address []:

C:\Work\study\APIM\demo\portal>openssl pkcs12 -export -in portal.crt -inkey portal.key -out portal.pfx
Enter Export Password:
Verifying - Enter Export Password:

C:\Work\study\APIM\demo\portal>openssl req -newkey rsa:4096 -out demo.csr -keyout demo.key -nodes
...+....+..+.............+..+..........+...+.....+.+.....+.+........+.+............+....................+...+++++++++++++++++++++++++++++++++++++++++++++*...+....+.....+.+..+...+....+...+...+...+..+...+......+...+.+..+.......+......+..+.......+.....+...+..........++++++++++++++++++++
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:NZ
State or Province Name (full name) [Some-State]:Wellington
Locality Name (eg, city) []:Wellington
Organization Name (eg, company) [Internet Widgits Pty Ltd]:student
Organizational Unit Name (eg, section) []:student
Common Name (e.g. server FQDN or YOUR name) []:devportal.demo.com
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:password
An optional company name []:

C:\Work\study\APIM\demo\portal>openssl x509 -req -in demo.csr -CA portal.crt -CAkey portal.key -CAcreateserial -out demo.crt -days 365
Certificate request self-signature ok
subject=C = NZ, ST = Wellington, L = Wellington, O = student, OU = student, CN = devportal.demo.com

C:\Work\study\APIM\demo\portal>openssl pkcs12 -export -in demo.crt -inkey demo.key -out demo.pfx
Enter Export Password:
Verifying - Enter Export Password:

C:\Work\study\APIM\demo\portal>

    Commands:

• openssl req -x509 -newkey rsa:4096 -keyout portal.key -out portal.crt -days 365 -nodes
• openssl pkcs12 -export -in portal.crt -inkey portal.key -out portal.pfx
• openssl req -newkey rsa:4096 -out demo.csr -keyout demo.key -nodes
• openssl x509 -req -in demo.csr -CA portal.crt -CAkey portal.key -CAcreateserial -out demo.crt -days 365
• openssl pkcs12 -export -in demo.crt -inkey demo.key -out demo.pfx

Update the new SSL cert for the custom domain in the API Manager instance.

• Follow the same process I did in the first part

• Click the save button, it will take some time to get updated once done we will test the Developer Portal from the VM

• The certificate is saved now, let’s try to access the developer portal from the Virtual Machine.

All looking good from the VM, and we can access the developer portal after changing the certificate.

Open Application Gateway and add a new Listener for Developer Portal

Create a new listener for the portal with the below details, I am using port 80 to keep things simple

• Click on save
• Update the backend pool with the below details and make sure you use devportal.demo.com on your FQDN which we configured

Add Backend Setting:

Add a backend setting like the below details

• Change the certificate extension from .crt to .cer and upload it. This should be RootCA cert we just created for the developer portal

Your setting should be similar below

Add Rule:

• Add a new Rule for the Developer Portal
• Select PortalListner

• And update the Backend target and Backend Settings

• Click Save.

If all good, then you should be able to see your developer portal exposed on http port 80 over public IP address, let’s test this out

The developer portal is now exposed successfully.

Your Backend Health must be healthy as mentioned below.

Expose External APIs using path-based routing in Application Gateway.

I will be creating a separate blog for path-based routing, but at a high level what we do, I have written here

We have everything we need now, to secure internal and external API we can make use of path-based routing. For example, we have 2 below APIs running on VM and we want to expose only one API to an external vendor and want to keep one for internal.

·       https://<host_name>/api/produc

·       https://<host_name>/api/employee

We must create an API proxy for each service on API Manager with the below URL

 

API Management Proxies:

·       https://<host_name>/internal/api/product

·       https://<host_name>/external/api/employee

 

We can now create path-based routing for external API

Open Application Gateway

• Delete the existing External Rule
• Add new External Rule

• Add path-based setting

• Give /external/* value in the Path which makes sure that all the API which has the external keyword in the URL can be accessed from Application Gateway

• Click add

• We can now provide details of this API to external customers and publish it through Developer Portal