Access on-premise SQL Server data from Azure Logic Apps via On-Premises Data Gateway

|  Posted: January 27, 2017  |  Categories: Microsoft Azure

These days we see a lot of companies looking into Azure, however most of these companies already have a lot of applications running on premises. This means they will probably have legacy systems which can’t be moved, but still do want to expose the data of these systems. Also, you might not be allowed to move certain data to Azure due to the company or government regulations, or because your on-premises systems cannot (yet) work with Azure databases. Luckily Microsoft is aware of this as well, and is focusing heavily on hybrid integration, which means you can seamlessly integrate your on-premises systems with your cloud solutions.

Hybrid-Integration

One of the solutions Azure offers for this is the On-premises data gateway, which allows us to connect our Azure solutions to our on-premises data sources like SQL Server, Oracle, SharePoint and more through a secure connection. For this, we install a gateway on the local machine, which is used by the Azure data gateway Cloud Service to send queries to the on-premises system, execute these queries, and send back the results.

On-Premise-Data-Gateway

In this post, I have an on premises SQL Server running with the Wide World Importers sample database. We will be using the on premises data gateway to use our local data in our Azure solutions, without having to move the database.

Install the On-Premises Data Gateway

We will start by installing the on premises data gateway on our local machine. An easy to use installer is provided.

Install-gateway

Once the gateway is installed, we need to provide the email which will be used to connect to Azure. This has to be a work or school email address. If you are using a Microsoft Account (e.g. outlook.com, live.com) you can use your Azure account to create a work or school email address by following these steps, which will show you how to create an organizational account in your Azure Active Directory.

On-Premise-Data-Gateway-Sign-In

Once signed in, choose to register a new gateway. Do note you can only install one gateway per machine. Give the gateway a descriptive name, and make sure to save your recovery key in a secure place.

On-Premise-Data-Gateway-Register

Create Azure Data Gateway Resource

Now that we have finished installation of our local gateway, we will register it in Azure as well. In the Azure portal, go to the On-premises Data Gateways blade. Choose to add a new gateway, give it a descriptive name, and make sure to select the On-premises gateway installation we have just installed.

Configure-Azure-Gateway

Logic Apps

As we have our on-premises data gateway completely in place, we can start using it from our Azure solutions. The on-premises data gateway can be used from various parts in Azure, like PowerBI, PowerApps, Flow and Logic Apps. For this post, I will show how we can use, retrieve and update data on our local database from a Logic App.

Retrieve Records

We will start by retrieving records from our local database. For this, we will create a new Logic App, which will receive an HTTP request, use the On-premises data gateway to retrieve all records from the StockItems table, and return these to the caller.

Create-Logic-App

To communicate with our local database, look for the SQL Server actions, in this case the SQL Server – Get rows action. As this is our first time connecting to the On-premises data gateway, we will have to create a new API connection. To do this, you can check the option to connect via our On-premises data gateway, and make sure to select the gateway we just created. The SQL Server Name is the local name of the server, and make sure to use a fully qualified domain name format for the username, even if you are using a local user.

Create-API

Now that we have our API connection in place, we will be able to use this from our other Logic Apps as well. We can now configure our Logic App to retrieve the records from the StockItems table, and return these in the response to the caller. When selecting the Table Name field, you will notice that the On-premises data gateway retrieves all the tables from our local database for you, and you can simply pick the table which you want to work with.

Return-StockItems-records

To test our flow, we can use an application like Postman. Copy the generated URL from the Request trigger in our Logic App into Postman, set the HTTP verb to POST and specify a body if needed, and press Send. You will now see the records, retrieved by our Logic App from our local database, through the On-premises data gateway.

Retrieve-Data

Update Record

Next, let’s see how we can manipulate existing data in our database. We will start by defining a json-schema on the request trigger, which can be generated from http://jsonschema.net. By setting this schema here, we will be able to use the contents from our incoming message as dynamic content when setting properties in the following actions. For the SQL connection, we can re-use the API connection we created in the previous step. The format that will be used in this request is as following, allowing us to add a comment to an existing order.

{
“OrderID”:1,
“Comments”:“My updated sample order”
}

To update the order, we will first get the current order, so we can set the current values in all fields except for the Comments field, which will be updated with the data we received in the request calling the Logic App.

Get-Existing-Order

Now that we have the data of the current order, we will update the order. Insert an Update Row action, and set all fields with the data we received from the Get row action, except for the Comments field, which we will use from the incoming request.

Update-Order

Update-Order-Comments

Now use Postman to post a message to the endpoint which was generated for this Logic App, and use the message specified earlier. The order in our database will now get updated with the new comments we sent in using Postman.

Conclusion

As we have seen here, it is very easy to expose our local data to our Azure solutions, in a secure way, and with minimal time needed. This gives us the option to move towards Azure, without the need to migrate all our data or applications, giving us the best of both worlds. In my opinion, this hybrid way of integration will be the way to go forward, as it gives companies the opportunity to move to Azure at their own pace, keeping those legacy systems they need on premises, while getting the added value of Azure when it comes to speed of deliverability and scalability.

Serverless360 is a one platform tool to operate, manage and monitor Azure Serverless components. It provides efficient tooling that is not and likely to be not available in Azure Portal. Try Serverless360 free for 30 days!

Free-Trial

Author: Eldert Grootenboer

Eldert is a Microsoft Integration Architect and Azure MVP from the Netherlands, currently working at Motion10, mainly focused on IoT and BizTalk Server and Azure integration. He comes from a .NET background, and has been in the IT since 2006. He has been working with BizTalk since 2010 and since then has expanded into Azure and surrounding technologies as well. Eldert loves working in integration projects, as each project brings new challenges and there is always something new to learn. In his spare time, Eldert likes to be active in the integration community and get his hands dirty on new technologies. He can be found on Twitter at @egrootenboer and has a blog at http://blog.eldert.net/