One of the first questions asked by potential adopters of Microsoft Office 365 is:
“How easy is it to access our critical line of business data stored on premise from SharePoint in the cloud?”
Unfortunately, with the initial versions of SharePoint Online, there really wasn’t a great answer. Transforming external data into the metadata required custom development. This alone deterred many organizations from making the move to the cloud or a hybrid cloud solution. Thankfully, as Microsoft evolves its’ cloud platform Azure, the O365 community gets some cool new toys to play with; specifically, Azure SQL, Azure Data Sync and Business Connectivity Services (BCS) which specifically addresses this data access challenge.
For those of you unfamiliar with Azure SQL, it is a hosted service of Microsoft’s SQL Server. Pricing is per hour and you pay for storage and backups, but not the underlying virtual machine or operating system like you would with a standard SQL Server virtual machine. Azure SQL dramatically reduces footprint and maintenance costs, but these cost benefits do come with some limitations that I’ll address in a future blog.
Azure Data Sync allows you to synchronize data and schema between SQL Server and Azure SQL without any custom development. While it also has some limitations and requires one to follow a series of configuration steps, it works well considering it is still in “Preview”.
In most cases, a SQL View on existing tables can be used to work around a restriction of either Azure SQL or Data Sync.
Much like Azure SQL, the O365 version of SharePoint has some limitations compared to its’ on premise counterpart, but it also includes some unique tools such as Business Connectivity Services (BCS). BCS allows you to create External Content Types and External Data Columns which function like a standard Lookup Column, but are backed by a SQL database instead of a SharePoint List.
Though, as you might have guessed, the current version of BCS cannot communicate with on premise SQL Servers. It can however, connect to Azure SQL instances.
Putting all the pieces together, Azure Data Sync moves data from on premise to an Azure SQL instance to which BCS can connect to and expose the data via External Content Types Simple, clean and no custom development required.
The visualization below illustrates how these systems can be integrated.
As Microsoft continues to develop these services and releasing new tools, the differences between on premise SharePoint and O365 will continue to dwindle. Likewise, the argument against migrating to O365 will be much harder to support, especially now that you can easily connect to your line of business data.