I’m a big fan of People Who Share and Fabian Williams is one of them, which is why I was so excited to meet him at the Office 365 event in Redmond this past weekend. He delivered. 

Mr. Williams gave a talk on Windows Communication Foundation (WCF) and Business Connectivity Services (BCS) to migrate enterprise data into the cloud.

He opened his session with two objectives:

  • Understand the mechanics on how to surface enterprise data in the cloud through SharePoint Online, and
  • Show how to leverage the power of BCS through SharePoint Online (Office 365) using WCF and Azure.

His key takeaway: SharePoint Online and BCS make it easy to bring enterprise data into the familiar user experience of SharePoint and Office.

See the slide deck here to follow this session's synopsis.

BCS Overview

Mr. Williams offered his audience a stack illustration:

Working from the inside out, the external content types, or xml files, is the metadata store. The external list is the end product, which in turn creates the external content type repository through business data connectivity.

Meanwhile, the surface application, or BCS, is the plumbing piece that offers connectivity to the Office Suite. On the backend, the line of business database, Windows Communication Foundation (WCF) framework, CRUD data (Create, Read, Update, Delete), external system, or web 2.0 application.

Design tools (for example, SharePoint Designer or Visual Studio) help a Developer get there.

SharePoint and Office 365 have key investment areas:

  • Presentation, or extending Office and SharePoint’s UX and capabilities to external data and processes;
  • Connectivity, or read-write capable connectivity from the client and server to databases, WCF/WebServices and .NET sources; and,
  • Tooling, or integrated tooling experience scales from simple solutions to advanced pro-development applications with rich client packaging and deployment.

External content types are the building blocks.

Rich Office clients use BCS to map to external content types. BCS is used again by the thin client, SharePoint. External content types are vital to integrate external data into SharePoint and Office client applications. Note the external content type describes the schema and data access capabilities of an external data source and its behavior within Office/SharePoint.

BCS in SharePoint Online (Office 365)

First, Mr. Williams advises, get your Office 365 account.  Once you’ve swiped your credit card, developers, you will want to configure your environment for BCS with maximum security in mind. Your backend system will have its own security layer, but you still need to determine how to additionally protect it.

In SharePoint 2010, developers have two options: the Secure Store Service or connecting directly. He recommends the Secure Store Service. Of course, recall that the external content type inside the metadata store has its own security, then the site collection has its own security settings -- so you can take advantage of at least four levels of security. Setup your site collections. Give users access. Configure your business data connectivity: set permissions on the Metadata Store and import your ECTs. Remember, site collections can inherit permissions.

Out of the three ways that you can connect to your external system, there is really only one if you’re using Azure (WCF), because MS doesn’t support directly connecting to SQL in Azure anymore.

How does Azure Fit

Fortunately, if you’re not in the mood to buy, Azure has a three-month trial period. In his presentation Mr. Williams assumes you the developer are buying database server and hosting SQL online. Your data repository could be here, he says.

Important Considerations

Some considerations, though, because there are 15 VERY important things you may not know about SharePoint 2010 and Office 365:

  1. Each My Site gets 500MB of personal storage only.
  2. From a “My Profile” page, the user can click on the “My Content” section – and it’s not provisioned until the user clicks on it. Only after they do can a personal “My Site” site collection be created.
  3. BCS within SharePoint Online does not support a direct connection to SQL Azure. A WCF endpoint is required.
  4. Access reporting is not supported by SharePoint Online Access Services because SQL Server Reporting Services(SSRS) is not currently enabled for SharePoint Online.  Access reporting requires SSRS.
  5. SharePoint Online does not support auditing capabilities to log the opening and downloading of files in a document library. (Records Managers, listen up: this is important!)
  6. SharePoint Online does not support PerformancePoint Services.
  7. SharePoint Online does not support Excel Services and PowerPivot for SharePoint.
  8. PDFs and other types of files can potentially run scripts against sites. To prevent this security threat, SharePoint Online requires users to download these types of files. When the user tries to open a PDF document stored in a SharePoint Online document library, the user will see a “Save As…” window and must download the PDF file to their local directory.
  9. SharePoint Online does support Enterprise Search, but does not currently support FAST Search.
  10. Vanity URLs can only be applied to the public website. Customers cannot apply a vanity URL to an intranet zone site.
  11. SharePoint Designer 2010 cannot be used to edit a public website. This site can only be edited with the built-in Site Designer tool.
  12. SharePoint Online development patterns and practices are currently targeted at site collection level solutions.
  13. “Server Resources” quota, used to determine amount of processing power available to sandboxed solutions, is determined by the number of licensed user seats in a company's tenancy. To calculate server resource quota in Office 365, you can use the following equation: (#seats×200) + 300.
  14. SharePoint Online does not currently support customer use of Windows PowerShell for service administration.
  15. You cannot create property based search scopes in Office 365 (SharePoint Online).

Using SharePoint BCS in the Cloud

To perform CRUD (create, read, update, delete) operations -- or, at a minimum, read-list and read-item -- developers should have the fundamentals of external content types in place. Your data tier is where data lives. Plan and implement how it will be accessed through WCF.

Are you moving data out to the cloud or are you biting the Azure bullet? Is it hosted? If it’s in a DMZ (an area on the network not protected by a firewall), what kind of access protocol do you use? Ask, where does data live? It’s in WCF, yes? Once you create a WCF, that’s a service end point. Expose it. Then, assemble it in SharePoint Designer. Verify. As the administrator, go to your SharePoint Online account. You now have a new single sign-on using Secure Store Service managing your SharePoint site collection.

One audience member asked the question: using SharePoint Designer, can you see tables of metadata? Or, if you have a view that spans more than one table, can you view all? No, Mr. Williams responded, if you want to do full CRUD, you want to execute procedures in BCS.

To do this, first define your entities on your data contract in WCF. Annotate what should be exposed as a data member (there should be data members for every table inside the database). Data members can be accessed as an object later on in the service interface. You the developer need to decide what should be exposed -- you’re in charge of that. Hint: be very careful about what you expose. In other words, work in concert with your Security team.

Mr. Williams concluded his presentation with an acknowledgment of debt and sincere thanks to his SharePoint colleagues.