Logic App / On-Premise SQL connectivity with & without Integration Service Environment (ISE) – Part 2

This post is a replica or you can say an extension of the previous one. We would be using the Integration Service Environment to create the Use Case we implemented in our previous post.

Since, using the Integration Service Environment was my 1^st experience, deleting and creating the ISE environment couple of times made me used all my subscription amount. Now I had to wait entire month for the next billing cycle to get free credits.

Luckily my friend came to rescue and he allowed me to use his subscription. If you are using the ISE Environment (DEV SKU) for practice purpose on your free Azure trial subscription, my advise would be to delete it after you are done or else you will end-up  disabling your subscription and you have to wait until the next subscription cycle.

Couple of challenges I faced while executing the above scenario –

• remote connections (SQL Server VM) on SQL server machine not enabled – https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-access-server-configuration-option?view=sql-server-2017
• No file connector for ISE(Maybe we can create a custom connector). – So for file connectivity had to install the on-premise data gateway
• Had to define rules and add my NSG to the default subnet so that the VM can be accessible. NSG – Network Security Group
• Adding Outbound security rules so that the ISE Logic app connector can access SQL Server from default subnet. Refereed below Link – https://docs.microsoft.com/en-us/azure/logic-apps/connect-virtual-network-vnet-isolated-environment

• Mandatory to have 4 empty subnets for ISE setup
• Bad Gateway which was resolved when appropriate rules were configured + Corrected my SQL Connection string

Comparing with the previous blog post in this case everything will reside under the Virtual Network ( ISE – Logic App, SQL). You can have a look at the following post on how to setup the Integration Service Environment (ISE).

https://azurebiztalkread.wordpress.com/2019/07/14/integration-service-environment/

https://azurebiztalkread.wordpress.com/2019/07/27/integration-service-environment-part-2/

Once the setup is in place now we would be able to connect everything which is within your Virtual Network. In our case or ISE Logic App must be able to connect SQL ISE connector without the use of on-premise data gateway. As mentioned above there were couple of challenges faced which was solved and you can refer above.

The Logic App looks exactly the same as above. We use ISE connector’s.

 

Once everything was in place we drop the XML to the file share and it split’s and insert 50 individual records to SQL.

• The Execution time for the logic App was approximately 1.17 minutes as compared with the above scenario which was 12.65 seconds.

I can summarize saying that Integration Service Environment provides a great capabilities especially if you are concerned about the security aspects. In my opinion I found ISE – Logic App bit expensive and apart from that ISE Logic App took more execution time when compared with the traditional Logic App. This was one kind of a scenario and I need to make  more use of both before I can come to a conclusion. This was my take on ISE Vs Non-ISE Logic App.