Looking to confirm my understanding of these three products and how they can be used in an Azure environment, prior to our moving on-prem databases to Azure SQL. I'm fairly sure I understand the Data Gateway product, as we have several apps using it, it's the other two I'm less certain I understand the use cases.
Our current environment is SQL Server and the various apps on Azure VMs.
So my understanding of each is:
- Data Gateway: Installs on a host on the same network as the SQL Servers. Applications in Azure (PowerBI, PowerAPP) can pull data from the SQL Servers through the Data Gateway, but NOT write data back.
- Azure Self-Hosted Integration Runtime: Also requires an application to be installed on a host, provides access to SQL Server "on-premise" and data can be both read and written between the cloud and the on-premise database. The data is copied on a set schedule, it is not "live" data access.
- Azure-SSIS: This is a "lift and shift" replacement for SSIS Project Deployment Model tasks. Provides (nearly) all SSIS features found in SQL Server. This does require an Azure SQL Database to deploy the projects into. If the Azure SQL instance is on the same virtual network as our VMs with SQL Server, SSIS will be able to reach those servers (presuming no firewalls blocking said connections)
I'm not worried at this time about the "fiddly bits" to get everything working, right now I'm looking for a high-level overview that's slightly lower than what I've gotten from the MS Learn pages about these products.