data movement from AWS aurora to Azure synapse ADLSGen2

Question

data movement from AWS aurora to Azure synapse ADLSGen2

NK 40

hello everyone,

we have a scenario that requires a solution.

Our production replica database is hosted on AWS Aurora, which uses PostgreSQL.

We need to transfer this data to Azure Synapse Analytics, specifically to the Azure Data Lake Storage (ADLS) Gen2 for analytical purposes.

We're facing a challenge as the AWS team is requesting a static IP. Could you please advise on obtaining a static IP in Azure Synapse Analytics? Additionally, we would appreciate any suggestions on how to move the data from AWS Aurora to Azure Synapse within ADLS Gen2.

Would installing the Integration Runtime in AWS Aurora be helpful in this situation?

also need to transfer data on periodic bases...

Thank you.

NK 40 Reputation points

2023-07-13T15:55:40.8566667+00:00

Hi Kranthi,

For the above solution you mean to say that i need to create a virtual machine(windows) in azure and inside that VM , i need to install SHIR ?
from there i need to give my IP address of my VM machine to whitelist on aws aurora?
is that my understanding is correct?

is any other solution without creating VM?
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2023-07-13T17:39:51.9866667+00:00

@NK Yes, your understanding is correct. Since you need a static IP to be whitelisted, that will be the relevant approach. And that Integration runtime will be involved in data movement from AWS aurora, once it is whitelisted. I don't see any better alternatives apart from using SHIR.
NK 40 Reputation points

2023-07-16T11:26:02.7666667+00:00

hi kranthi,

Thanks for your response!

Do we need to install SHIR in AWS Aurora as well? Where can I find the static IP in Azure VM, or how can I configure a static IP in Azure VM? Please confirm if my steps below are correct:

Create a virtual machine in Azure VM resource.

Inside the Azure VM, install SHIR and use a web browser to login to the Azure portal and navigate to the Synapse workspace.

In the Integrate hub within the Synapse workspace, create a copy data tool and provide the necessary details of the Aurora database to establish a connection.

Copy all data from Aurora to Azure Data Lake Storage (ADLS) within the Synapse workspace.

Please confirm if the above steps are correct.

Also, please let me know how to perform the copying on a daily basis. Are there any automatic pipeline configurations or tutorials available? Thanks.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2023-07-17T17:26:41.1766667+00:00
@NK Thanks for getting back.

I'm not sure if we can install SHIR in AWS Aurora, but if you have a AWS VM with Windows OS, you may try installing the SHIR as SHIR is supported only in Windows OS.

Below are the prerequisite for SHIR support:

Once you create an azure VM, you can find the public IP in your portal as shown below.

Related documents and threads:

Manage a public IP address with an Azure Virtual Machine

Easier way of retrieving an Azure VM's Public IP address

Once you have the VM ready, you can follow this document to download and install the SHIR in that VM and follow the steps descirbed in it to associate your SHIR with your Azure Synapse workspace.

Create and configure a self-hosted integration runtime

Once the SHIR is configured and setup with your Synapse workspace, then you can whitelist the IP addess of your SHIR in your AWS aurora and create a Postgres linked service in your Synapse using SHIR and test the connection and then try creating a Postgres dataset and try to preview the data to ensure all the connections are working as expected. Once the test is complete then you can configure the next steps as per your requirement.

Hope this info helps.
NK 40 Reputation points

2023-07-19T13:08:19.2466667+00:00

thanks kranthi for your help
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2023-07-19T15:22:35.39+00:00

@NK You are welcome! If you have any follow-up questions, feel free to open a new thread and we will be happy to assist.

Please don’t forget to Accept Answer and click Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Accepted answer

0 additional answers

Your answer

NK 40 Reputation points

2023-07-13T15:55:40.8566667+00:00

Hi Kranthi,

For the above solution you mean to say that i need to create a virtual machine(windows) in azure and inside that VM , i need to install SHIR ?
from there i need to give my IP address of my VM machine to whitelist on aws aurora?
is that my understanding is correct?

is any other solution without creating VM?
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2023-07-13T17:39:51.9866667+00:00

@NK Yes, your understanding is correct. Since you need a static IP to be whitelisted, that will be the relevant approach. And that Integration runtime will be involved in data movement from AWS aurora, once it is whitelisted. I don't see any better alternatives apart from using SHIR.
NK 40 Reputation points

2023-07-16T11:26:02.7666667+00:00

hi kranthi,

Thanks for your response!

Do we need to install SHIR in AWS Aurora as well? Where can I find the static IP in Azure VM, or how can I configure a static IP in Azure VM? Please confirm if my steps below are correct:

Create a virtual machine in Azure VM resource.

Inside the Azure VM, install SHIR and use a web browser to login to the Azure portal and navigate to the Synapse workspace.

In the Integrate hub within the Synapse workspace, create a copy data tool and provide the necessary details of the Aurora database to establish a connection.

Copy all data from Aurora to Azure Data Lake Storage (ADLS) within the Synapse workspace.

Please confirm if the above steps are correct.

Also, please let me know how to perform the copying on a daily basis. Are there any automatic pipeline configurations or tutorials available? Thanks.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2023-07-17T17:26:41.1766667+00:00

@NK Thanks for getting back.

I'm not sure if we can install SHIR in AWS Aurora, but if you have a AWS VM with Windows OS, you may try installing the SHIR as SHIR is supported only in Windows OS.

Below are the prerequisite for SHIR support:

Once you create an azure VM, you can find the public IP in your portal as shown below.

Related documents and threads:

Manage a public IP address with an Azure Virtual Machine

Easier way of retrieving an Azure VM's Public IP address

Once you have the VM ready, you can follow this document to download and install the SHIR in that VM and follow the steps descirbed in it to associate your SHIR with your Azure Synapse workspace.

Create and configure a self-hosted integration runtime

Once the SHIR is configured and setup with your Synapse workspace, then you can whitelist the IP addess of your SHIR in your AWS aurora and create a Postgres linked service in your Synapse using SHIR and test the connection and then try creating a Postgres dataset and try to preview the data to ensure all the connections are working as expected. Once the test is complete then you can configure the next steps as per your requirement.

Hope this info helps.
NK 40 Reputation points

2023-07-19T13:08:19.2466667+00:00

thanks kranthi for your help
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2023-07-19T15:22:35.39+00:00

@NK You are welcome! If you have any follow-up questions, feel free to open a new thread and we will be happy to assist.

Please don’t forget to Accept Answer and click Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 1

@NK Welcome to Microsoft Q&A forum and thanks for reaching out here.

Since you would like to connect to your AWS Aurora PostgreSQL and load data to Azure Data lake gen2 using a static IP which can be whitelisted in AWS Aurora, you can install a Self Hosted Integration Runtime in an Azure VM or a regular VM and use that SHIR in Postgresql Connector in ADF or Synapse pipeline copy activity to pull the data from AWS Aurora and load it to ADLS Gen2 associated with your Synapse workspace. By installing a Self Hosted Integration runtime in a VM you can whitelist the Public IP of that in your AWS Aurora and it will allow you to establish connection and access the data.

Please note that, I haven't test the Postgresql connector to establish connection to AWS aurora PostgreSql. As ADF or Synapse doesn't have an dedicated connector for AWS Aurora Postgresql, I am suggesting to give a try with regular Postgresql connector.

Hope this info helps. Do let us know how it goes.

Thank you

Share via

data movement from AWS aurora to Azure synapse ADLSGen2

0 additional answers

Your answer