Chapter 24: Business Connectivity Services in SharePoint 2010

What’s In This Chapter?

• An overview of BCS architecture
• Creating a BCS solution
• Service applications and BCS
• Design considerations for BCS solutions
• Upgrading the Business Data Catalog to BCS

Business Connectivity Services (BCS) represent a comprehensive set of capabilities that integrate and connect SharePointServer 2010 applications with data that resides in external systems. BCS, which is based on a set of services and features included in SharePoint Foundation 2010, provides out-of-the-box support that simplifies the development of solutions that use external data and services. BCS is a major enhancement to its predecessor in SharePoint 2007, the Business Data Catalog (BDC). Using BCS, you can design and build solutions that extend SharePoint collaboration capabilities to include external business data and the processes that are associated with that data.

BCS uses a new concept in SharePoint 2010 called external content types (ECTs), which should not be confused with the enterprise content types mentioned in other chapters. ECTs are a content type that is based on external data. Just as with any content type, they are associated with lists. In this case, the list is called an external list, and it provides the viewing and storage mechanism for the external data.

BCS solutions can be created using SharePoint Designer 2010 or Visual Studio 2010. Which tool is best depends on both the skill set of the creator and the features and complexity requirements of the solution. For example, you can create a BCS solution that uses Outlook 2010 to manage customer information that resides in a CRM system. This solution would use an external content type created with SharePoint Designer 2010, and an external list created using the web browser. The data in the external list can be taken offline into Outlook as a set of contacts. Updates to the contact list can be made in Outlook and synchronized with the external data source. Many types of BCS solutions can be created without writing any .NET code, which fosters the development of such solutions and encourages those without professional development skills to get involved. BCS also includes a runtime environment in which solutions that include external data are managed and executed.

This chapter provides a BCS introduction and overview in order to familiarize you with its capabilities. Because these capabilities are both wide and deep, expect to see entire books dedicated to BCS coming along soon.

Capability Overview

BCS is an enhancement to the functionality that was provided in the SharePoint 2007 Business Data Catalog. The BDC was a first-generation technology for connecting SharePoint to external data. Before looking at the specific capabilities of the SharePoint 2010 BCS, let’s review some of the gaps in the BDC:

• The BDC did not provide the capability to truly integrate external data into SharePoint 2007. BDC data could be displayed using four out-of-the-box Web Parts and by adding a column to a list with the Business Data data type. This provided a copy of the specific data.
• The BDC data was essentially read-only, unless you wanted to write the custom .NET code necessary to perform the write-back capability. External data was displayed using a number of different out-of-the-box Web Parts, but there was no direct provision for updating the back-end data system.
• There was no support for integrating external data with Office desktop client applications like Outlook, Word, or Excel. The BDC provided integration with SharePoint Server 2007. To provide client integration, you would need to create a web service that would access the BDC Object Model and then the client would interact with the web service to surface the data. This approach would work, but it required a lot of custom .NET development, and there was no capability to manipulate the data once in the client, such as a client object model, which also had to be developed.
• BDC content could not be taken offline or used in a disconnected fashion.
• The BDC did not support streaming BLOB content. This caused problems when the BDC was used to index content that was contained in SQL Server and stored as BLOBs.

The BCS represents a second-generation technology for connecting to external systems, and specifically addresses all of the aforementioned gaps:

• BCS uses content types and external lists to integrate external data in SharePoint 2010. SharePoint 2010 features and services that utilize list data can utilize the external list data as well.
• BCS provides complete CRUD (create, read, update, and delete) operation capability on data returned from external systems.
• BCS provides direct integration of external data with client applications like Outlook 2010, Word 2010, and SharePoint Workspace 2010. This integration does not require any custom programming.
• External list data can be taken offline in Outlook 2010 and SharePoint Workspace 2010. The external data source can be updated with changes made in the offline environment, and in general the two environments can remain synchronized.
• BCS provides the capability to index and search content stored in BLOBs. Specifically, this requires defining a method in the XML access model, defining a content source with the ECT, and having the necessary IFilters installed for the specific file types of interest.
• BCS provides a more extensive set of authentication mechanisms for connecting to the external systems and retrieving data. This also includes greater control over access to the external data once it has been retrieved.

BCS capability is provided by SharePoint Foundation 2010, whose predecessor is Windows SharePoint Services version 3, and by SharePoint Server 2010. Table 24-1 summarizes which BCS functionality is available in SharePoint Foundation and SharePoint Server 2010. SharePoint Server 2010 functionality also includes that provided by SharePoint Foundation. Details about each of the features are discussed later in the chapter.

Table 24-1: BCS Functionality for SharePoint Foundation and Server 2010

Platform	Functionality
SharePoint Foundation 2010	Business Data Connectivity Service — Manages and provides the storage and retrieval capability. External lists and External data columns — Enables the display of external data. BDC connectors — The actual binary logic that communicates with the data store. Custom connectors can be created and made available using the Connector Framework.
SharePoint Server 2010	Search Capability — To index and access external data. Secure Store Service — Stores access credentials to the external data. External Data Web Parts — Display external data. User Profiles — For managing and displaying external data. Workflow Capability — For using external data. Rich Client Integration — Integrates external data with Office 2010 client applications like Outlook and Word.

Architecture

The BCS architecture consists of service applications that manage and provide the connectivity to external systems; client and server runtime applications, which deliver the capability to the server and Office desktop clients; external content types, which provide the XML description that governs how to access the store and what to retrieve; and the built-in connectors, which contain the actual programming logic that communicates with the data store. Each of these architectural components is discussed in the following sections.

Service Applications

BCS utilizes two service applications, just as you’ve seen other SharePoint 2010 functionality use the service application architecture. These services are called the Business Data Connectivity Service (BDCS) and the Secure Store Service (SSS).

Business Data Connectivity Service

The BDCS provides a means for storing, accessing, and utilizing external data. Specifically, this includes external content types (ECTs) and related objects. This service provides connectivity capability to several different types of external systems, and there is out-of-the-box support for the following data sources:

• Databases
• Web services
• .NET Framework assemblies
• Custom connectors

This new service can be compared to the Business Data Catalog in SharePoint Server 2007, but there are some big differences. One of the differences is that this service is provided by a new service architecture, which is available in SharePoint Foundation 2010. Previously, in order to use the Business Data Catalog, you had to have SharePoint Server 2007 installed; this functionality was not available in Windows SharePoint Services version 3. From a licensing perspective, your users had to have the Enterprise client access license.

In SharePoint 2010, BCS functionality is provided by SharePoint Foundation and SharePoint Server 2010. The specific functionality provided by each was discussed earlier in the “Capability Overview” section. Note that this service also provides support for custom connectors, which the BDC did not. These connectors are discussed later in the section “Connectors and the Connector Framework.”

BDCS is implemented as a service application in SharePoint Foundation 2010. Like other SharePoint 2010 services discussed in previous chapters, there is an associated service application and proxy called Business Data Connectivity. You can view this service application and proxy from the Manage Service Application web page in Central Administration. Multiple instances of the BDCS can exist in the same farm, each with a unique set of administrators if necessary, and an instance of the BDCS can be shared across farms. The BDCS uses ECTs to define the connectivity and information to be retrieved.

ECTs define quite a bit of information. They are discussed in their own section a bit later, but the following is a brief summary of their contents:

• A named set of fields in the external data. This could include things like Customer, Products, and so on.
• The specific CRUD operations that will be used for interacting with the external data system.
• Connectivity information that BCS solutions will use to connect the external content type to the external system.

External content types are stored in a dedicated BDCS database, which by default is called Bdc_Service_DB and can be viewed using SQL Server Management Studio.

Secure Store Service

As you saw in Chapter 20 with PerformancePoint Services, the SSS securely stores credentials for external systems and then associates those credential sets with identities of individuals or groups. The SSS replaces the Single Sign-on capability that was available in SharePoint Server 2007. This service enables the BCS solution to authenticate users and groups on external data sources. It also enables the same user or group to have different user accounts and credentials for the different external systems compared to their enterprise log-in credentials.

For example, suppose John Doe logs into SharePoint Server 2010 using his enterprise username and password, and he uses a different set of credentials for logging into the corporate financial system. Using SSS, Mr. Doe’s financial system access credentials can be stored with his user profile. The benefit of this is that when Mr. Doe uses a BCS solution from within SharePoint Server 2010 to access data from the financial system, SharePoint server obtains his credentials from the SSS and provides a single sign-on mechanism. Thus, this eliminates the need for Mr. Doe to manually log into the financial application. You can also configure the SSS so that multiple users can access an external system by using a single set of credentials.

Another potential issue that the SSS can solve when accessing external systems is the double hop problem. This occurs because a SharePoint web page or Web Part tries to access resources that are located on a server other than the web server. You can use SSS to map the user’s account with his credentials for the external store. In the terminology of the SSS, external data stores are referred to as target applications. A target application needs to be configured in the SSS in order for the BCS solution to connect to and utilize the data.

SSS credential sets are stored in a dedicated, secure database, which by default is called Secure_Store_Service_DB. The BDCS data and the SSS data are also cached on client computers that are using the BCS solution. For more information, see Chapter 20 and the discussion on PerformancePoint Services and SSS.

Connectors and the Connector Framework

BDCS provides connectivity to the external data. It does this by utilizing ECTs and built-in connectors. The ECTs provide the metadata description of the connectivity information, and the connector provides the actual binary logic to communicate with the data store. External data that resides in a database such as SQL Server, Oracle, etc. is accessed by using the appropriate ADO.NET database provider. Data that doesn’t reside in a database but is accessible via web services is accessed using the Windows Communication Foundation connector for web services. There is also a .NET Framework Assembly connector for accessing .NET assemblies. All of these connectors are available out-of-the-box and don’t require any custom programming.

For those data stores that don’t comply with either an ADO.NET provider or a web service, or access via the .NET assembly connector, a custom connector can be built. The custom connector would plug into the connector framework, and then it could be utilized by the BDCS to provide access to the data source. The process for creating a custom connector is beyond the scope of this chapter and this book, but interested readers should begin by understanding the differences between the custom connector and the .NET assembly connector. Learn more about this at http://msdn.microsoft.com/en-us/library/ee554911(v=office.14).aspx.

BDC Client and Server Runtime

Different runtimes provide the necessary capability to access and utilize external data. One runtime exists on the SharePoint server, and the other exists on the client computer. Together, they deliver the server functionality to the desktop client. These different runtimes are discussed in the following sections.

Server Runtime

The Business Data Connectivity Server runtime understands how to reach into the back-end store and connect to data based on the external content types defined within the content type store. The server runtime exists on the SharePoint 2010 web front-end servers, and it utilizes two shared services: the BDCS and the SSS, discussed previously. It uses information from these services to access external systems and execute operations on the external systems for access by web browsers. The server runtime provides the connectivity to external sources such as SQL Server and other relational databases, web services, and custom data connectors. SharePoint websites display external data in the web browser using Business Data Web Parts and SharePoint external lists.

The runtime also contains a data cache called the metadata cache, which provides caching of the runtime BDCS data. This data can be encrypted for additional security if necessary. The runtime also provides the mechanism for clients to synchronize with the BDCS data and SSS data.

Client Runtimes

Two different runtimes provide BCS functionality on the client: the Business Data Connectivity Client Runtime (client runtime), and the Office Integration Client Runtime (integration runtime). These runtimes are installed along with the installation of SharePoint Workspace 2010, Word 2010, and Outlook 2010 desktop client applications. The integration runtime integrates with the client runtime to surface external data and functionality to the desktop client. The client runtime is a connector between the server runtime and the integration runtime.

The client runtime provides integration with the server runtime. This connectivity between the client and the server occurs via the client-side cache, which contains the BDCS data and SSS data to connect to and execute operations on external systems for access by supported desktop clients. The client-side cache is periodically refreshed to ensure that data is synchronized with the BDCS and SSS data. This cache provides the capability to take solutions offline and then update the server when reconnected. From a technology point of view, the client-side cache leverages the SQL Server Compact database as its durable store. This should provide a strong sense of reliability to developers because it leverages proven and widely adopted technologies.

The SSS is also accessible from client applications through the Client Secure Store Service, which enables end users to configure their client mappings in the credential database. The client runtime also supports connecting to SQL Server and other relational databases, web services, and custom data connectors.

External Content Types

External content types (ECTs) are a critical component of the BCS architecture and functionality. Recall that content types were introduced in SharePoint 2007. Content types provide a mechanism for standardizing and reusing metadata information for a specific type of content, as well as associating such functions as workflows and policies with the content. We will not review content types in this chapter, but for those who may need a refresher of the types of capabilities available to content types, you can browse to the Site Content Types web page from the Site Settings page of your SharePoint website, and select a content type such as Document. On the Document content type web page, you can review all the different aspects of content types.

What is important to us in this chapter are ECTs, which are an extension of the content type concept to external data. Specifically, they contain connectivity information for accessing external data, specifications for the type of data to be accessed, and actions that you want to apply to external data. An ECT is defined using XML markup. This is very similar to how an entity was defined with the Business Data Catalog in SharePoint 2007, so you may hear and read the terms ECT and entity used interchangeably, but the ECT has additional functionality, as you will see. In general, you can consider an ECT a data source. Using the ECT as a data source, you can view the external data on the server using an external list, an external data column, and external data Web Parts. These different approaches to viewing ECT data in SharePoint 2010 are described in Table 24-2.

Table 24-2: Viewing External Content Type Data in SharePoint 2010

Approach	Description
External List	An external list is a new type of SharePoint list that displays data using the ECT as the data source. This provides direct integration of the external data in SharePoint so it can be manipulated using CRUD operations just like any other SharePoint list data, and the list and external data can be kept synchronized. External lists can be taken offline using Outlook 2010 and SharePoint Workspace 2010. Unlike standard SharePoint lists, external list data is not stored in the content database, but remains in the external data store. Also, external lists do not have all the standard features of regular lists, such as versioning, check-in, check-out, workflows, and content type association.
External Data Column	The Business Data list column type introduced in SharePoint 2007 has been renamed the External Data Column in SharePoint 2010. This adds a single column of information from the ECT to standard SharePoint lists. Unlike the external list, External Data Columns provide all the other features of standard SharePoint 2010 lists.
External Data Web Parts	Just as SharePoint 2007 provided Web Parts that displayed data from the BDC, SharePoint 2010 provides five Web Parts that do not require any custom programming to use and display BCS data on SharePoint 2010 web pages: External Data List, External Data Item, External Data Item Builder, External Data Related List, and External Data Connectivity Filter Web Part. These Web Parts are read-only and do not provide write-back capability to the external data.

Security

BCS provides extensive authentication and authorization capability for accessing external systems and consuming data. BCS security will govern access to external data in a wide variety of scenarios, which can be categorized by the tool or application desiring access to the data:

• Web browser
• Office client
• Custom application

A comprehensive discussion of BCS security is beyond the scope of this one chapter, as it would involve the overall security architecture, both client and server, which authentication modes are available for external content types, the type and options for configuring permissions on objects, and the specific steps to configuring authentication and authorization. Interested administrators are encouraged to review the information referenced in Table 24-3. An understanding of this information is not required to get starting using BCS, and readers can complete the hands-on exercise in the section “Creating a BCS Solution” without this knowledge.

Table 24-3: BCS Security Topics and References

Topic	Reference
Planning SharePoint 2010 Authentication	http://technet.microsoft.com/en-us/library/cc262350(office.14).aspx
BCS Security Overview	http://technet.microsoft.com/en-us/library/ee661743(office.14).aspx.
BCS Authentication	http://msdn.microsoft.com/en-us/library/ms566523(office.14).aspx
BCS Authorization	http://msdn.microsoft.com/en-us/library/ms497953(office.14).aspx
BCS Permissions	http://blogs.msdn.com/bcs/archive/2009/11/24/ permissions-in-business-connectivity-services.aspx

Creating a BCS Solution

In this section, you’ll create an example solution that illustrates the concepts of external content types and external lists. BCS solutions are created using SharePoint Designer 2010 and Visual Studio 2010. These solutions can be packaged as a Visual Studio Tools for Office (VSTO) package that is easily distributed to SharePoint Workspace, Word, and Outlook 2010 client desktops by leveraging the ClickOnce capabilities in the .NET Framework. BCS also exposes APIs to extend solution packaging to target additional clients. A BCS solution can be created using SharePoint Designer 2010, an external data source, and SharePoint Server 2010. The following exercise creates a BCS solution that requires the following:

• The Business Data Connectivity Service application and proxy must be created and started. This can be checked by browsing to the Manage Service Applications web page in Central Administration. A service application instance can be created using the wizard as part of the installation, as shown in Chapter 4. A new service application instance can also be created manually or by using PowerShell. The manual approach was demonstrated in Chapter 20 and discussed in Chapter 7, so readers are encouraged to review this information if necessary.
• SQL Server 2008 must be installed on the same physical server as SharePoint Server 2010, including an instance of the Northwind database. The Northwind database can be downloaded and installed from http://msdn.microsoft.com/en-us/library/ms143221.aspx. If SQL Server is installed on a server other than the SharePoint server, then you need to create a target application in the SSS. This is also discussed in Chapter 20 as part of configuring PerformancePoint Services.
• SharePoint Designer 2010 must be installed on either the same SharePoint server or another computer that has access to the SharePoint server.

Creating an External Content Type

An external content type can be created using SharePoint Designer (SPD) 2010. In the following example, using SPD, you will create an ECT named Products that is based on the Products table in the Northwind SQL Server sample database:

Creating an External List

SPD can be used to create an external list but the following walk-through utilizes the web browser to illustrate this process:

This completes the exercise demonstrating how a BCS solution uses an external content type and an external list to view external data.

Service Applications That Use BCS

BCS solutions are a powerful mechanism for utilizing external data, but BCS capability extends beyond the discussion in this chapter. SharePoint Server 2010 uses BCS as the foundation for SharePoint 2010 Search crawl functionality and social computing by augmenting User Profile Service functionality. SharePoint Search is discussed in Chapter 14, and the User Profile Service is discussed in Chapter 17, so the information below is specific to how BCS affects these two services and their capability.

Search Service

SharePoint 2010 Search uses the BCS connector framework discussed earlier to crawl content. This is a change and improvement over the protocol handlers that were used in previous versions of SharePoint. Protocol handlers are very difficult to create and generally need to be written in C++, which negatively impacted handlers that were available for the wide variety of external systems that are in use today.

Connectors also support richer crawl options than protocol handlers in previous versions of SharePoint. For example, they support the full-crawl mode that was implemented in previous versions and they support timestamp-based incremental crawls. However, they now also support change-log crawls, which can remove items that have been deleted since the last crawl.

Connectors can also now crawl attachments and content in e-mail messages. When crawling a BCS entity, additional related entities can be crawled by virtue of the entity relationships. Item-level security descriptors can now be retrieved for external data. Connectors also perform better than previous versions of protocol handlers, as they implement concepts like inline caching and batching. SharePoint Designer 2010 and Visual Studio 2010 can be used to create external content types and entities, and these entities can be crawled.

User Profile Service

BCS augments the out-of-the-box User Profile Service properties as well as adding custom properties to the User Profile Service. This enables user data and attributes that reside in external systems to be used inside SharePoint Server 2010 for tasks like audience targeting.

Profile import connections can be set up for external systems to import properties into the SharePoint Server 2010 user profile database. You should be aware of the following considerations when using BCS to supplement the User Profile Service:

• BCS cannot be used as the primary connection for a User Profile import. It can only work in conjunction with other primary profile import connections.
• BCS profile connections cannot be used to write back to the external system. SharePoint Server 2010 introduced the capability to write back to User Profile property source connections, but this functionality is not yet available for BCS connections.
• A common property is required between the primary user profile import connection and the BCS import connection. The property is used to identify and associate the properties with the appropriate user profile when performing a user profile import from BCS.

Design Considerations for BCS Solutions

As part of implementing BCS solutions, you need to be aware of several design considerations that will affect the efficiency and effectiveness of these solutions:

• Assign appropriate permission to users who are managing the BCS service application.
• Define and govern the number of people who can create external content types.
• Define and implement appropriate throttling limits for external lists. These limits are likely different from throttling limits defined for normal lists on the Central Administration website.
• Create limit filters on external content types to retrieve a limited amount of data from external stores.
• Use SSS as the preferred mechanism to authenticate against external systems.
• Implement write-back capability via external content types only when absolutely necessary and with caution.
• Use Feature and deployment packages for installing external content types.
• External lists based on an external content type do not have all the functionality of standard SharePoint lists. The following limitations may affect your design:
  • Workflows cannot be configured directly on external lists.
  • Item-level permissions cannot be configured from the list properties page.
  • Versioning and item history are not available.
  • Ratings, RSS feeds, and Datasheet view are not available.

Upgrading the Business Data Catalog to BCS

There are several differences between the functionality of the Business Data Catalog in SharePoint Server 2007 and the BCS in SharePoint Server 2010. Table 24-4 summarizes what you need to consider both as you upgrade and after the upgrade is complete. Refer to Chapter 5 for details on the different approaches to upgrading SharePoint Server 2007 to SharePoint Server 2010. These should only be considered guidelines; these recommendations will likely be revised as the product is used more extensively.

Table 24-4: Factors to Consider When Upgrading BDC Applications to BCS Solutions

Business Data Catalog	Points to Consider
Upgrading external system metadata in the application definition file	Use the database upgrade approach, as it automatically upgrades the metadata to the new schema used in BCS. This also migrates all permissions in the definition files. Once the database attach upgrade approach has been completed, SharePoint Server 2007 application definition files cannot be imported. It is possible to manually re-create the definitions using SPD, but a better approach is to use an in-place upgrade. Use your original SharePoint Server 2007 data and perform an in-place upgrade to SharePoint Server 2010. Then export the models from the upgrade and import them into the SharePoint Server 2010 farm that was originally created using the database attach method.
Single Sign-on Service	The SSO service in SharePoint Server 2007 Business Data Catalog has been replaced by the Secure Store Service. This may require creating target applications, so the SSS will need to be tested to ensure it is working properly.
Profile Pages	Profile pages in SharePoint 2007 need to be re-created in SharePoint 2010 if the database attach upgrade process was used.
Application Security	If the upgraded application is going to use claims-based identity, you need to ensure that the permissions defined in the metadata model will work as expected after an import.

Summary

Business Connectivity Services (BCS) are a set of capabilities that SharePoint Server 2010 and Office 2010 clients use to provide access to external data. This chapter was designed as a general introduction to BCS and its capabilities, not a comprehensive guide to its use, so administrators are encouraged to delve deeper into the topic. Creating and implementing BCS solutions requires a number of different tools, so you will need to be aware of their use and the potential impact they may have on the overall SharePoint environment. One such tool is SharePoint Designer 2010. In the example, SPD was used to create an external content type. You also saw that BCS is utilized in combination with two other service applications. Even though we are very early in the experience cycle for SharePoint 2010 usage, the chapter gave several recommendations to consider when planning to use BCS.