Wrong Database During High Web Server Load

[Cory]

Hi all,

Our issue is a little complex. Our system is an update from a legacy system
that had our clients host their own databases and software. We are now
completely ASP.net based, but have each of our clients data in their own SQL
Server 2005 databases. Currently we have about 150 clients migrated and
therefore 150 DB's.

Each of our clients has a client id and all of users are related to a
client. So, when a user logs in, we know which client they belong to. That
client ID is then used to to point to the correct database for all future
requests. A table in an common database stores the relation between the
client id and the connection string that points to the clients database.

Now, instead of making a call to the DB to retrieve the connection for every
call required by the user, we cache the connectionstring using the
Microsoft.Practices.EnterpriseLibrary.Caching block. Which, has
seemed to work well. Recently though, as the load on the servers increases,
data from someone elses database is making it to the user. I am not sure
why, as everything in the code looks good. I am including a sample.

We have a base object that all our business objects inherit from. The
constructor requires the client id to be passed in when the object is created
and is stored as a module level variable. None of our classes are static.
A new one must always be instantiated with the client id passed in.

private long mClientID = 0;

public BaseProvider(long lClientID)
{
mClientID = lClientID;
}

It also has a method that looks for the clients connectionstring in the
cache. If it is not in the cache, it retrieves the value from the DB and
puts it in the cache.

protected string GetClientConnectionString()
{
CacheManager cache =
CacheFactory.GetCacheManager("ConnectionCache");

string strClientDB;

if (!cache.Contains(mClientID.ToString()))
{


ApplicationPathsDAL.ApplicationPaths.ApplicationPathsDataTable appPathsDT;
ApplicationPathsDAL.ApplicationPaths.ApplicationPathsRow
appPathsRow;

appPathsDT =
ApplicationPathsBLL.ApplicationPathsProvider.Adapter.GetDataByAppClientPathType(Convert.ToInt64(ConfigurationManager.AppSettings["ApplicationID"]),
mClientID, ConfigurationManager.AppSettings["DBPathName"]);

if (appPathsDT.Rows.Count == 0)
{

throw new ApplicationException(Messages.ER_NO_CONNECTION);
}
else
{
appPathsRow =
(ApplicationPathsDAL.ApplicationPaths.ApplicationPathsRow)appPathsDT.Rows[0];
}

strClientDB = appPathsRow.apa_path;

if (string.IsNullOrEmpty(strClientDB))
throw new ApplicationException(Messages.ER_NO_DB);


cache.Add(mClientID.ToString(), strClientDB);
}
else
{
strClientDB = cache[mClientID.ToString()].ToString();
}

return strClientDB;
}

This method just creates the connection.

protected System.Data.SqlClient.SqlConnection GetClientConnection()
{
//long lClientID;
string strClientDB;

System.Data.SqlClient.SqlConnection connection;


if (mClientID > 0)
{
strClientDB = GetClientConnectionString();

connection = new
System.Data.SqlClient.SqlConnection(strClientDB);
}
else
{
throw new ApplicationException(Messages.ER_MISSING_CLIENT);
}


return connection;
}

This is what one of methods look like that will get data for the request.

public ContactDAL.Contact.ConInfoDataTable GetAllConInfo(string sRelPkList)
{
using (ContactDAL.ContactTableAdapters.ConInfoTableAdapter
ConInfoAdapter = new ContactDAL.ContactTableAdapters.ConInfoTableAdapter())
{
ConInfoAdapter.Connection = GetClientConnection();

return ConInfoAdapter.GetAll(sRelPkList);
}
}

We are leaning to this being an issue with the cache as we do not see the
same issues when it is taken out and we make a call to the DB for the
connectionstring on every request. But, this is, of course, not a good
solution as it doubles the number of calls we need to make in order to get
the data. We are not able to replicate this issue in a test environment as
we are unable to create the same type of load, and therefore are unable to
call directly to support. This is a major issue for us.

We are using .Net Framework 2.0. Any help would be greatly appreciated.
Please let me know if there is any other information I can provide that would
help deduce the problem.

Thanks,

Cory

 

[Gregory A. Beamer]

We are using .Net Framework 2.0. Any help would be greatly
appreciated. Please let me know if there is any other information I
can provide that would help deduce the problem.

One thing I would consider, just from a safety standpoint, is look up
the connection once and throw it into the user's session instead of
trying a custom caching mechanism with each go. This will be guaranteed
to go out of scope when the session dies and is a GUID. This does not
solve the initial retrieval of the database connnection string, but once
per login should not be hard and it reduces pulling incorrectly from
cache, if that is the issue.

This will increase memory load on the web server a bit, but the amount
of information in a connection string is low and the number of users for
150 clients should be manageable.

As for solving the actual caching problem, I don't have enough
information except that Microsoft is assumed to be at fault. There is
also possibly something in your code that is corrupting the cache.

I am also not sure why you use this:

if (!cache.Contains(mClientID.ToString()))

If you would load the entire map into cache and set it to refresh, you
would find you don't need these types of constructs. The downside is a
new client can't log in until cache is refreshed, but if you set them up
in metadata first, they should be able to log in by the time you have
their database set up, so it is a workflow issue more than anything
else.

If the cache does not contain a client's information, you can set the
entire cache to rebuild, but adding to cache one item from a list at a
time invites problems, especially when the data is largely static.

Peace and Grace,


--
Gregory A. Beamer (MVP)

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************

 

[Nick Gilbert]

Why does the connection string need to be in a global cache? Wouldn't it
be better just to store the connection string (or even just the database
name) in a string variable in the user's session? The code would be
greatly simplified if you were to do that.

Nick..