SharePoint Large Scale External Data Integration > 200,000 Items

​The Layer2 Business Data List Connector for SharePoint can be used to connect almost any external data source to native SharePoint lists, e.g. SQL, ERP/CRM, OData etc. It is made for large scale external data integration scenarios as well and can manage more than 200.000 external items without any issues. It helps to overcome the SharePoint limitations with out-of-the-box data integration using data synchronization.

​SharePoint out-of-the-box data integration tools like BCS come with a limitation of 2000 items per default. It can be increased, but it is definitely not made for 100,000 items and more. On the other hand, SharePoint lists can host up to 30,000,000 items with a view limit of 5,000 (list throttling). You can solve the issue by using the Layer2 Business Data List Connector for data synchronization.

Layer2 BDLC Performance Optimization

​Large scale external data integration with SharePoint remains a challange, even with version 2010/2013:

• Make sure that your SharePoint configuration meets the Microsoft specification in terms of CPU and memory.
• Scale out the SharePoint database (dedicated server). Make sure that there is room enough for the new data.
• Don't use "select *" in query, select only required fields. Include a primary key, e.g. customer number or GUID.
• Set this "primary key" (unique column) in BDLC configuration.
• Start with a "top 500" query to be sure, that everything works as expected.
• Check carefully, that the primary key works as expected. Change one record in data source. Update the list manually in the configuration interface. As a result you should have one item updated only - NOT ALL (as changes only should be processed). If all items are updated with each sync, please contact [email protected] for help.
• The first insert of all items is the most difficult task. SharePoint cannot compare to a SQL database in terms of insert / update / delete performance. In some cases it helps to process the insert step-by-step. Start with "top 25.000", increase to "top 50.000" etc.
• Timeouts after long running operations in web interface: Normally the SharePoint web interface has a timeout of 360 seconds (6 min). You can do the first insert as a background update (via timer job, without any timeout) or you can modify the timeout settings of the web interface as described below.
• Database timeouts: In some cases the database cannot deliver such large datasets to the SharePoint server in time. You can increase the database query timeout per connection directly in the "BDLC Configuration List", if required.
• SharePoint list view throttling: There is a list view limit of 5000 items per default. That is not a list item limit, that is around 30.000.000 (see SharePoint limitations). This limit of 5000 items does not count in case of BDLC sync. But you have to take care in the SharePoint list user interface, e.g. using views, indexed columns, managed metadata, SharePoint search etc. to find the desired items. For on-premise installation generally you can increase the list view limit. But there is no need for this. We especially advise to use SharePoint search in case of large lists, e.g. with certain scopes and attributes. If it's crawled once, it delivers results to users ultra-fast.
• Memory issues, W3P recycling: In some cases the first insert can abort with W3P recycling (depending on process configuration) if there is not enough memory. It leaves the connected list locked. You can unlock in the list settings manually, or you can setup an automatic unlock interval (e.g. after 1 hour). Best practice is to find the issue and fix, rather then to ignore with automatic unlock.

If the data is in the list once, life becomes more easy. Only changes are processed then and that should not take too long (depending on data amount and especially amount of changes).

Fig.: The Layer2 BDLC SharePoint add-on can connect external data sources to native SharePoint lists.  

How to increase web interface timeout settings for SharePoint

​To increase the web interface settings for timeout you can do the following:

Go to:

C:\Program Files\Common Files\Microsoft Shared\web server extensions\14\TEMPLATE\LAYOUTS

Find the following in web.config:

<system.web>
    <compilation batch="false" batchTimeout="600" maxBatchSize="10000" maxBatchGeneratedFileSize="10000" />
    <httpHandlers>
      <add verb="*" path="*.aspx" type="System.Web.UI.PageHandlerFactory, System.Web, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
    </httpHandlers>
    <customErrors mode="On" />
<httpRuntime executionTimeout="360" />
    <globalization fileEncoding="utf-8" />
  </system.web>

Change the httpRuntime executionTimeout (in seconds) to whatever you need.

SharePoint External Data Integration - Next Steps

To verify the options above please follow these steps:

• Register and download the Business Data List Connector on the product page.
• Connect a native list to your external data source following this video.
• Overcome the SharePoint out-of-the-box limitations listed here.

You can now work with your external data as usual with native SharePoint lists. You can write-back changes to data source, search, setup lookups, metadata, workflows and change notiofcations etc. Your users are working with the SharePoint lists only - not directly with the external data source.

The Layer2 Business Data List Connector for SharePoint

• simplifys the SharePoint architecture,
• gives best performance and
• increases availability of the configuration.

READY TO GO NEXT STEPS?