Most of us who have been using BizTalk Server for a while use the SSO database as a secure store of configuration values. Microsoft made this much easier a few years ago when they released the SSO Configuration Application MMC Snap-In. Better yet, it comes with a sample class you can use to implement in your applications. This frees developers from having to add AppConfig segments to the BizTalk config file. In multi-server deployments, this gets really nasty because you have to change the config on every application server. SSO is by default available to the cluster. So, configuration changes are automatically made available to the entire BizTalk Group.
One common use of the SSO Config Store is a repository for database connection strings required by BizTalk maps performing database look-ups. A script component can call out to a class which wraps the client code that ships with the MMC Snap-In. Or you could use the BizTalk Mapper Extensions created by Sandro Pereira, which ship with a custom functoid to SSO config.
However, there is a problem. When you use the tool in a looping BizTalk map, it will make a call to the SSO database to retrieve config values for each element in your loop. In the case of a connection string, you are making unnecessary calls to SSO. Just open up the SQL Server Profiler, and you can watch all of the extra calls to the SSO database. With large messages, I have seen this severely degrade performance.
I have opted for creating a simple wrapper method, which uses a module-level dictionary object to cache configuration values needed by the map. This reduces the number of calls to the SSO database to one per config value. As you might expect, this significantly increases performance with even moderately large messages. The durability of the cache is only for the life of a single instance of the map. So, if you want something even more durable, you would need to rely on something like the AppFabric Cache.
Here is some sample code for caching values from the SSO config store…
private Dictionary<string, string> _ssoConfigurationValues = new Dictionary<string, string>();
public string GetSSOConfiguration(string appName, string propName)
{
try
{
string key = string.Concat(appName, propName);
string value = string.Empty;
if (_ssoConfigurationValues.ContainsKey(key))
{
return _ssoConfigurationValues[key];
}
else
{
value = SSO.Utility.SSOClientHelper.Read(appName, propName);
if(!_ssoConfigurationValues.ContainsKey(key))
_ssoConfigurationValues.Add(key, value);
return value;
}
}
catch (Exception ex)
{
throw new ApplicationException("GetSSOConfiguration function call read failed", ex);
}
}
John Glisson - Geek of the Cloth 