Hybrid solution - SharePoint online and SQL on-prem

Question

My client have some PII data in their on-premise SQL DB. They have only O365 SharePoint Online, they don't want to have data inside SP as list and library since their site is cloud. want to use same SQL DB. How their old (Java + SQL)application can be migrated to SPO.

If at all we put a provider hosted app, will we be able to use any of the SharePoint features like Workflows, SP Search, etc.

Answer 1

In highlights you could do the following, which seems to be the best approach for the future.

• Keep the SQL database onpremises
• Develop an api in Azure and through a VPN connect it to your onpremises database
• Secure the api with Azure AD in the same tenant as SharePoint online
• Use clientside code to get data from the API

This post thread might be helpfull: https://techcommunity.microsoft.com/t5/SharePoint-Developer/Client-side-web-parts-accessing-external-data/td-p/53307

Another option would be to use the add-in model. I do think that's less durable towards the future.

If you want to use features like workflows, search etc, your only option is to put the data into SharePoint lists and libraries.