Monday 23 June 2014

SharePoint 2013 BCS Service Application Throttle Management

As we know the BCS Service Application is the out of the box way to connect SharePoint to external data sources. Once the "external data connection" is configured we can do Create, Read, Update, Delete and Query (CRUDQ) operations over the data. We can crawl the data, represent it as SharePoint list and so on.
In this post I will not explain how to set up the Service Application and how to use it, but I will show you how to configure the  BCS throttling in on-premise, non-multitenant SharePoint 2013 environment. Since this settings are working on service application proxy level I am not sure how this throttling works (if there is such) in SharePoint Online and I am unable to find any official information for multi-tenant SharePoint 2013. In multi-tenant SharePoint 2010 environment you cannot configure the BCS throttling per-tenant. If you want to do so, you can set up a unique proxy for each tenant, and associate those proxies with their respective web applications. 
The BCS throttling is enabled by default to prevent from Denial of Service attacks or just to limit the performance impact of some poorly designed solution on SharePoint or the external data provider system by stopping for example some huge transactions. It can be triggered by other conditions too as we will see below. But you may have the business need to change the values of the throttling rules or if you consider to turn it off. This rules are different from the "Resource Throttling" options in the Central Admin,Search Crawl throttling or the Office 2013 client application limitations. Before editing the default throttling rules, please consider the option to define filters when you create External Content Type if it is applicable in your case, for more information on the subject see following MSDN Article .
There are four Throttle Types and five throttle scopes. The Throttle Type is the 'metric' that will trigger the rule and the Throttle Scope is the connector type that the rule can be applied on. In below tables you can see the Types,Scopes and the applied rules.

Throttle Types:

ThrottleType Meaning
Items The number of records returned
Size The amount of data returned, in bytes
Connections The number of connections opened to the database, web service, or .NET assembly
Timeout The time until an open connection is terminated, in milliseconds

Throttle Scopes:

Scope Meaning
Global Applies to Database, Web Service, WCF, and .NET Assembly Connectors (not to Custom Connectors)
Database Applies to Database Connectors
WebService Applies to Web Service Connectors
Wcf Applies to WCF Connectors
Custom Applies to Custom Connectors

Throttle Rules:







You can see that there are no rules for the "Custom" scope. This is because this scope is reserved for advance scenario with custom connectors.
This may sound a bit confusing (it was confusing to me the first time), but I will try to make it more clear.
So, we cannot manage the BCS throttling rules from the web interface or SharePoint Designer, we can only do this in PowerShell or via public APIs and this should be done from one of the SharePoint servers. As we told earlier this settings are working on the proxy level, so we need to have the BCS Service Application Proxy first in order to see or change the throttling settings.
With below lines you can see the with Type: Items in Scope: Database :

$bcsProxy = Get-SPServiceApplicationProxy | where {$_.GetType().FullName -eq ('Microsoft.SharePoint.BusinessData.SharedService.' + 'BdcServiceApplicationProxy')}

Get-SPBusinessDataCatalogThrottleConfig -Scope Database -ThrottleType Items -ServiceApplicationProxy $bcsProxy

And here is the outcome of this command:

The Enforced property shows us, well if the rule is enabled or not. The Default is the limit that is applied on external lists, it can be overridden by custom web parts, but they are limited by the Max value.

Now what will happen if we leave the "Database" scope and chose ThrottleType "Connections"

We receive an error that tells us that such rule do not exist for the combination of Scope: Database and Type: Connections .
We have only two cmdlets for Get-SPBusinessDataCatalogThrottleConfig and Set-SPBusinessDataCatalogThrottleConfig, so there is nothing that can create new configuration. For Database connectors Scope we can use only throttling by Items (rows for database query) and Timeout. We can use "Connections" Throttling Type but it should be in combination with "Global" Scope and it will be applied on the rest of the connector types (see The Scope table) as well.

You can see the default values of 'default' and 'maximum' settings of the throttling rules:

In following example we have External Content Type (ECT) that is reading a table from MS SQL Database. The ECT has no filtering applied and the table contains 2517 rows. We are creating External List app so we can visualize the data from the ECT. The BCS throttling rules are with the default values. And we are receiving below message (you can see this in ULS log as well) :

Here comes the Set-SPBusinessDataCatalogThrottleConfig cmdlet, you can use below snippets to manipulate the throttling rule:

$bcsProxy = Get-SPServiceApplicationProxy | where {$_.GetType().FullName -eq ('Microsoft.SharePoint.BusinessData.SharedService.' + 'BdcServiceApplicationProxy')}

$dbRule = Get-SPBusinessDataCatalogThrottleConfig -Scope Database -ThrottleType Items -ServiceApplicationProxy $bcsProxy

#Default and Maximum must be provided together. This increases the limit for external lists to 3000. 
Set-SPBusinessDataCatalogThrottleConfig -Identity $dbRule -Maximum 1000000 -Default 3000

#This disables a throttling rule. 
Set-SPBusinessDataCatalogThrottleConfig -Identity $dbRule -Enforced:$false

#This enables a throttling rule. 
Set-SPBusinessDataCatalogThrottleConfig -Identity $dbRule -Enforced:$true

And after the change of the throttling rule we have all 2517 items from our table. After the change you may not receive immediate result, you may need to execute Get-SPBusinessDataCatalogThrottleConfig
against the rule you changed.

No comments:

Post a Comment