Important Note:
This is a configuration change that is designed to resolve an issue introduced in 5.1.1 and resolved in 5.3.0. This should only be considered by users of 5.1.1, 5.1.2, 5.2.0, 5.2.1, 5.2.2 or 5.2.3 , and only if you're experiencing problems with sql timeouts during upgades/module installs.
It's also important that you revert this configuration change with 5.3.0, as the fix introduced in 5.3.0 would cause this to stop working and would cause then cause this to be an issue in 5.3.0 and above.
Background
When under extremely heavy load there is a possibility that Microsoft ado.net classes may return "stale" data i.e. the results of an old query rather than the result of the query that was just executed. This is quite a rare case and only exhibits under extreme load or/and insufficent resources. As the error comes from code external to DotNetNuke i.e. somewhere within the .net framework, ado.net or the database drivers themselves our options were limited. From 5.1.1 DotNetNuke introduced code to try to mitigate against this by introducing database segmentation i.e all queries that are executed by DotNetNuke have an application name appended to the connection string. This means that "stale" results from other database processes cannot contaminate DotNetNuke data, thus cutting this issue down as much as possible.
Issue and workaround
The drawback to this fix was that it introduced a problem. DotNetNuke supports the concept of installing under one user (the upgradeConnectionString) but running under another (the connectionString) e.g. a site could install/upgrade under a database user with db_owner permissions, but run only one who only had permissions to execute stored procedures. The majority of sites only use 1 user but for certain installations this support for 2 users is important. This issue was introduced when code that compares these strings got out of sync, so to resolve this issue it's possible to define an upgrade connection string that will resolve as the same string as the connection string. As DotNetNuke believes these are both the same there is no need for it to run the optional GrantStoredProceduresPermission and GrantUserDefinedFunctionsPermission functions which can cause the application to be slower than expected during installs/upgrade.
To do this in your install you'll need to take a copy of the value of the SiteSqlServer and append the segmentation string to it e.g. in my install I have the connection strings with values similar to below
<add key="SiteSqlServer" value="Data Source=(local);Initial Catalog=dnn523;User ID=dotnetnuke;Password=dotnetnuke" />
To workaround the segmentation issue I then location the SqlDataProvider definition. This will have an empty upgradeConnectionbString (see below for an example)
<add name="SqlDataProvider" type="DotNetNuke.Data.SqlDataProvider, DotNetNuke.SqlDataProvider" connectionStringName="SiteSqlServer" upgradeConnectionString="" providerPath="~\Providers\DataProviders\SqlDataProvider\" objectQualifier="" databaseOwner="dbo" />
I now replicate what the DotNetNuke code does by first of all ensuring it ends in a semi-colon ";" and then adding the segmentation string "Application Name=DNNCore;". In my case this will end up as this string.
<add name="SqlDataProvider" type="DotNetNuke.Data.SqlDataProvider, DotNetNuke.SqlDataProvider" connectionStringName="SiteSqlServer" upgradeConnectionString="Data Source=(local);Initial Catalog=dnn523;User ID=dotnetnuke;Password=dotnetnuke;Application Name=DNNCore;" providerPath="~\Providers\DataProviders\SqlDataProvider\" objectQualifier="" databaseOwner="dbo" />
If I now save the web.config and try to do my install/upgrade/module installation, the logic within the code will see the strings as the same and not execute the optional logic, leading to much reduced database time, and cases where timeouts can occur on large installs.