Overcoming Office 365/SharePoint Online Development Challenges – Part 1
If you have been involved in implementing solutions using SharePoint Online on Office 365, you have probably discovered that there are a number of areas where things don’t work quite as how you would except them to work or at-least what you were used to in an On-Premises SharePoint environment.
This blog is an attempt to document some of those challenges that I have run into lately and provide a workaround on how to overcome these challenges.
In part 1 of this series, I’ll discuss some of the Business Connectivity Services (BCS) challenges. For the most part, BCS is supported in the Office 365 and there are a number of blogs and articles that cover how to create BCS solutions with SP Online either with Azure SQL or On-Premises SQL Server. Here is an excellent article that covers the scenario using Azure SQL.
In the custom development scenarios where external data need to be stored in the SQL Server, I recommend using SQL Azure if possible. Using a Cloud-only option makes it much easier to create External Content Types (ECT) using SharePoint Designer. There is no need to write additional code or web service to consume the data in SharePoint online when using this option. It is also easier to maintain for the admins as there is no additional infrastructure and security to take care of.
Connecting to On-Premises SQL Server from SP Online takes some effort. Integrating internal SQL data with Office 365 / SharePoint Online traditionally requires a SharePoint hybrid farm, where cloud-based BCS runs as internal BCS by proxy. Unfortunately, this requires the organization to maintain an on-premises SharePoint farm. Another new option is using Azure Hybrid Connections. Azure Hybrid Connections will allow you to build a bridge between SharePoint Online and SQL Server data hosted on the internal corporate network. For details on the Azure Hybrid Connection see the following article:
https://azure.microsoft.com/en-us/documentation/articles/integration-hybrid-connection-overview/
While Azure Hybrid Connections solve the connectivity aspect of the problem, we will still need to create a web service on Azure using the OData protocol. Once deployed this service can integrate with the SharePoint Online environment. We will need additional development as well as we developing this service in an environment local to the On-Premises SQL Server using Visual Studio.
In the scenarios where you don’t have a SharePoint hybrid farm setup, as is the case with most of our customers, you will soon discover that you will not be able to search your external data. This is due to the fact that in SP Online you cannot crawl external content types as you can in an on-premises environment. Hybrid is the recommended way for Microsoft when you want to search external content. As I mentioned earlier, this option requires additional infrastructure and security setup as an On-Premises SharePoint farm will need to be set up. If you are interested in this option, here is the link to the Ignite conference session on Channel 9.
If hybrid is not an option for you, you are pretty much out of luck if you want to leverage OOB Search. One work around is to sync External data with an internal list with just the data that is needed for Search. The syncing can be done through power-shell or a custom app.
Another major issue with BCS in Office 365 is that you cannot use it to extend user profiles. One work around is to create custom User Profile properties and update these fields using a custom app. It all depends on your scenario. If you want to use Delve User Profile this approach may work for you for simpler use cases. However for most complex scenarios, you might find yourself writing a custom app to pull data from the user profile and mash it with data in SQL Server for example.
In part 2 of this series, I’ll cover some of the challenges I ran into when adding new managed properties to add refiners to the custom Search page.