While more and more of our software moves to the cloud, many of us feel like we might have to make the choice between going 100% cloud or staying on-prem, but that is not the case, we might still have some resources on some On-Premises servers, that we would like to be able to connect to from our Business Central this could be either a file share or a SQL database, so how do we connect to these resources? Well, we cannot connect directly from Business Central, however, we can use On-premises data gateways.
What are On-premises data gateways?
On-premises data gateways are services that you install on your local servers which will then allow you to access different resources from external systems, in our case we will use an On-premises data gateway to enable us to connect to our On-Premise resources using a logic app, which we then can connect to our Business Central, so the design is as follows.
Setup data gateway
The first thing that we need is to download and install the on-premises data gateway here you will need to login to your gateway using an Azure account, and choose to Register a new gateway on this computer
Give it a name and a recovery key:
Once it is installed head over to your Azure portal choose On-Premises data gateway:
Fill in the information and you should be able to see your gateway under installation Name:
And your gateway is now set up and connected, next, let us try to integrate some data from our local server to Business Central.
Local SQL to Business Central
On my Local PC, I have an SQL server with a database called OnPrem with a single table called Item:
I have also set up my SQL server to run mixed mode so that I can connect to it using SQL credentials (this is optional) so to connect to my SQL database I have created a Logic App, with a HttpTrigger that takes a payload with an ItemNo:
This will then trigger an SQL Server Get rows (V2)
Next setup your connection and choose your Gateway:
After creating your connection choose your table and create a Filter Query (this is an Odata query and not SQL query)
Lastly, add a response where you will respond with a list of items:
If I call this from Postman with a post against my endpoint and a payload like the following:
{
"ItemNo": "1896-S"
}
I get the following result:
{
"@odata.context": "https://logic-apis-northeurope.azure-apim.net/apim/sql/8ed8c3cc17d749a39334393ba48384a6/$metadata#datasets('default%2Cdefault')/tables('%5Bdbo%5D.%5BItem%5D')/items",
"value": [
{
"@odata.etag": "",
"ItemInternalId": "a72fed74-a957-4a3d-b68d-59dc1477d951",
"ItemNo": "1896-S ",
"External_x0020_No_x002e_": "TABLE "
}
]
}
Now let us try to call this Logic app from Business Central; I have extended the Item List with an action, and the code looks as follows:
pageextension 50100 ItemListExt extends "Item List"
{
actions
{
addlast(Functions)
{
action(CallOnPrem)
{
Caption = 'Call On Prem';
Image = Web;
ApplicationArea = all;
trigger OnAction()
var
Content: HttpContent;
Response: HttpResponseMessage;
Client: HttpClient;
header: HttpHeaders;
Jobject: jsonobject;
Jtoken: JsonToken;
Jarray: JsonArray;
body: text;
url: Label '{{URL}}';
I: Integer;
begin
Jobject.Add('ItemNo', Rec."No.");
Jobject.WriteTo(body);
Content.WriteFrom(body);
Content.GetHeaders(header);
header.Remove('Content-Type');
header.Add('Content-Type', 'application/json');
client.Post(url, Content, response);
response.Content().ReadAs(body);
Jobject.ReadFrom(body);
Jobject.Get('value', Jtoken);
Jarray := Jtoken.AsArray();
for I := 0 to Jarray.Count() - 1 do begin
Jarray.Get(I, Jtoken);
Jobject := Jtoken.AsObject();
Jobject.Get('External_x0020_No_x002e_', Jtoken);
Message(Jtoken.AsValue().AsText());
end;
end;
}
}
addlast(Promoted)
{
actionref(OnPremRef; CallOnPrem)
{ }
}
}
}
Local File Share to Logic App
Next, let us try to connect our file share to the cloud, it is done in much the same way as with our SQL database, only this time instead of creating a HTTP trigger we will use the, when a file is created trigger.
Set up your connection using a root path, and a valid Windows user.
Next, use the get Get content and set up the path.
And finally use the HTTP to call a REST endpoint with the content of your file.
Conclusion
And that is it for this post, what we have seen in this post is that even though you move some of your software to the cloud and some of your software is still running on a local server, then using On-premises data gateways you can connect your environments, so instead of thinking either or then you can start thinking why not both, and let each system play to their strengths and instead of only seeing limitations then start looking for opportunities, until next time stay safe.