Consuming data from On-premises into Office 365

Office 365 is a great platform for any organization size. It provides you as the user access to all kinds of applications and services that can help and benefit your organization and business processes. One of the questions I get asked a lot is about consuming On-premises data from a line of business application up into Office 365. This comes from the need for most organizations to slowly migrate to the cloud yet still provide the dashboard type ability or even full create, read, update and delete (CRUD) mechanisms that they were used to with On-premises.

There are a few options for this type of implementation that work well when trying to extend your data reach into Office 365.

The first option would be to use the Microsoft Data Management Gateway. This can be downloaded here.

The Data Management Gateway can perform the following services:

On-premises Data using Hybrid – You can connect on-premises data to cloud services to benefit from cloud services while keeping the business running with on-premises data.

Secure Data Access – You can define which on-premises data sources are exposed with Data Management Gateway so that Data Management Gateway authenticates the data request from cloud services and safeguards the on-premises data sources.

Easy Management – You are provided with full monitoring and logging of all the activities inside the Data Management Gateway for management and governance.

The Gateway Service works without having to open the corporate firewall, connections are made using credentials that you own from your organization as well as ensuring that all data transferred is compressed and transferred in parallel. The following diagram outlines the command flow and data flow for Data Management Gateway.

Image Source: https://docs.microsoft.com/en-us/azure/data-factory/media/data-factory-data-management-gateway/data-flow-using-gateway.png

The second option would be to use Azure Data Factory. 

Image Source: https://azurecomcdn.azureedge.net/cvt-041bbcf179b13cb902344ce739d43e8de4af91df7331ee5efdf757329a0d2240/images/page/services/data-factory/data-factory-diagram.png 

You may have heard of this or not, but this is a core Microsoft Azure service that you can use. Azure Data Factory a cloud-based data integration service that orchestrates and automates the movement and transformation of data. You can create data integration solutions that can ingest input data from disparate data stores, transform/process the data, and publish output data to other data stores. The Azure Data Factory service lets you:

Easily work with diverse data storage and processing systems

Transform data into trusted information

Monitor data pipelines in one place

This process is done using Pipelines, Activities, Datasets along with schedules for executing the data collections. More details can be found here:

https://docs.microsoft.com/en-us/azure/data-factory/data-factory-create-pipelines

https://docs.microsoft.com/en-us/azure/data-factory/data-factory-create-datasets

https://docs.microsoft.com/en-us/azure/data-factory/data-factory-scheduling-and-execution

Lastly of course you could build something completely custom, and write RESTful API components that allow you to retrieve the data content you need from On-premises into Office 365. You have the ability to write an add-in or use the SharePoint Framework to consume the data no matter where it would reside. An example of using of an add-in talking to On-premises SQL can be found here:

https://blogs.msdn.microsoft.com/alimaz/2014/07/31/connect-sharepoint-online-to-an-on-premises-sql-server-from-an-azure-provider-hosted-app-using-hybrid-connections/

All in all, the real key is determining what the end goal is, how the data will be used and what experience you need for the users of the site.