External application to access Dynamics 365 SQL database

leoalbano

Hello everybody!

In our organization we have an "on premise - Dynamics 365 Business Central" server that runs our main business database.
It runs version 14, that is, Dynamics 365 Business Central Spring Release v14.00 I believe is also called Wave 1 2019.
We now use the "web" client (after many, many years of using the hard client, using it since it was Navision 2.50 late 1990's)

We also have a custom made web application that is written in php and uses its own MySql database.
Until relatively recently the php app wasn't linked to Dynamics 365 at all.
Then after a while, somebody specifically asked if it was possible to get some useful info from Dynamics 365.
I experimented a bit and successfully created a SQL user, connected the php app directly to the Dynamics 365 Microsoft SQL database and wrote some php to access the data they wanted.
That worked without any problems for months. Everybody is happy, yay!
After another while they asked me if it was possible to change a date on a certain table.
I explained that there are some more risks when you actually "write" data to the database (more security risks, data integrity and consistency risks).
They said "well you back up the database every day correct?"... Offcourse I do.
I then gave the php connection write access to a specific table in the Dynamics 365 SQL server database, wrote more php, and now it is possible to change that date field, either from Dynamics 365 or from the php web application... Everybody is happy again.
After a day or so some people started telling me that they couldn't make changes from Dynamics 365 to certain documents.
The specific documents coincidentally are the ones that are related to this date field that is changed from the php application.
By the way: the php I wrote executes an "update" query and then closes the connection, the whole transaction takes a few milliseconds.

Fortunately my question is not related to any data corruption or data breaching.
I did some investigation and it looks to me like there are some cache or buffering issues.
When the php application changes that famous date: then, if anybody opens that same document (even loading it from scratch hours after the change was made) Dynamics 365 shows the original (OLD) date that is no longer current in the Microsoft SQL database.
Then if they try to change it, (again for a span of hours) they get the following error messages from Dynamics 365.

The page has an error. Correct the error or try to revert the change.
Sorry, we just updated this page. Reopen it, and try again.

This literally persists for hours or perhaps even a whole day. Then, next day Dynamics 365 will finally show the actual data present in the Microsoft SQL database and unlock the record.

I understand this also is related to "Record locking" to avoid data inconsistency but it seems a little too prolonged.
It is more likely to be a caching issue.

My question is:
Is there a way to have Dynamics 365 access the data more like in "real time"? I understand there will be a performance penalty on doing so but with the current hardware we use and the fact that there is never more than a dozen users working concurrently, I believe it is irrelevant.
Is it possible to change some settings either in Microsoft SQL or in the Dynamics 365 instance settings that can mitigate this problem?

Thank you!

Leo

SanderDk

Hi Leo,

I have read you question but I think you are going a wrong way:
The Business central loads the data on a different scheduler (when in it needs to), nothing good come when changing the way this is done.

You surely can use what you have but in my optionen it is not a proper way of doing this. I would never write directly in BC own database, and most important not in the table uses by BC. This can cause a lot of bad headache later on.

In my opinion it would be best that you from your PHP web app make use of web service to update data in your BC.This way your Web App are communication directly with BC as any other users, this will also allow BC to execute the code that could be behind the field(s) that you are updating.

If you HAVE to use a SQL connection, I would recommend that a BC developer creates an entire new set of tables in BC, and then create a job to grab the data from the new SQL tables and import the data into your real BC table.

bbrown

To expand on what SanderDk (if you have to use a SQL connection), the BC developer should use Query Object to read these staging tables. As Query objects skip the NST cache and read the database directly.

SanderDk

Hi Leo,

I have read you question but I think you are going a wrong way:
The Business central loads the data on a different scheduler (when in it needs to), nothing good come when changing the way this is done.

You surely can use what you have but in my optionen it is not a proper way of doing this. I would never write directly in BC own database, and most important not in the table uses by BC. This can cause a lot of bad headache later on.

In my opinion it would be best that you from your PHP web app make use of web service to update data in your BC.This way your Web App are communication directly with BC as any other users, this will also allow BC to execute the code that could be behind the field(s) that you are updating.

If you HAVE to use a SQL connection, I would recommend that a BC developer creates an entire new set of tables in BC, and then create a job to grab the data from the new SQL tables and import the data into your real BC table.

bbrown

To expand on what SanderDk (if you have to use a SQL connection), the BC developer should use Query Object to read these staging tables. As Query objects skip the NST cache and read the database directly.

leoalbano

Thank you SanderDk and bbrown

About a web service. Is that a Nav component? or is more like a IIS component? I'm asking this because if is not a pure Nav component the Nav developer we use will not be familiar with it since she focuses specifically in the Nav Cside environment but not at all in IIS.

If that is the case I will use the second approach (creating a new set of tables) and have her create the "grab job" that will properly use the business code and data consistency checks to apply the data.
Using a "grab job", I would assume that it will not work in real time and some kind of scheduled task should be used to periodically check if the staging tables have changed. Is that right?

bbrown suggested using a query object (which I'm not familiar with). Would this option be different than a scheduled "grab job"?
And is it native for Nav? Would it be "real time"?

Which is the best option to use?

Thank you again!
Leo

SanderDk

The webservice is a pure NAV component, the NAV developer publish a NAV object as a SOAP OR OData service available for you to consume.

You are correct the "grab job" will not be 100 % real time data, it will have some latency (Depending on HOW to job is developed)

A Query object is a NAV object type that will allow the developer to skip the service tier buffer catch, the solution the bbrown suggested is one of many possible, the developer if he/she prefer can, also use a command called "SELECTLATESTVERSION", but still this will not be real time. This is just about how the "Grap job" could functional internal.

bbrown

The "SELECTLATESTVERSION" function by clearing the service tier cache.