CODEWITHSUNDEEP
sql-serverazuressisazure-sql-database
SHB
SHB

Reputation: 87

Move Data from On-Premises SQL Server to Azure SQL VM

I have a Virtual Machine in Azure with SQL Server and looking to pull nightly data from on-premises SQL Server via SSIS in the SQL Azure Virtual Machine. I read that VNET needs to be setup for the Azure VM SSIS to access on-prem SQL Server data. Is that correct? If so, what is needed to set it up?

I understand Azure ExpressRoute is another option but since we don't have a huge volume of data to transfer every night, we believe it might not be ideal considering the high cost associated with it.

Upvotes: 0

Views: 923

Answers (1)

EagleDev
EagleDev

Reputation: 1865

Yes, VPN needs to be set up between Azure and your on-premises to let them 'see' each other. There are two types of VPN in Azure:

  • Point-to-site (P2S): this type of VPN gateway connection is often used when you want to create a VPN connection from clients to your Azure VNET where your virtual machine resides in. P2S supports IKEv2 or SSTP
  • Site-to-site (S2S): this type is more for enterprise or complex network infrastructure, also when security needs to be more controlled. Unlike P2S which uses VPN client to connect to Azure VNET, S2S requires VPN device located in your on-premises and this device needs at least one public IP address.

More about Azure VPN gateway specification: https://learn.microsoft.com/en-us/azure/vpn-gateway/vpn-gateway-about-vpngateways

The decision consideration depends on your data ingestion requirement. If data is not big and the velocity doesn't need to be high, then P2S is quite enough. Moreover, if your on-premises does not already have VPN device plus financial budget and IT resources are limited, you should go with P2S. With P2S, you don't need a VPN device, you just need to create a new VPN gateway in Azure VNET, then set up VPN client (just a free software) including a certificate. The steps of P2S setup is straightforward. This article gives you quick overview of each VPN type and supportability https://learn.microsoft.com/en-us/azure/vpn-gateway/vpn-gateway-plan-design

ExpressRoute is also an option if you really need to establish a private and direct connection between you and Azure datacenter which gives you fastest way to transfer data without going over Internet. Moreover, ExpressRoute is not available all countries.

Last but not least, you could have a look at Azure Data Factory if you like, including Data Management Gateway. If this is the case, chances are you need to think about a new approach to replace Azure VM SQL Server by PaaS

Upvotes: 5

Related Questions