Hi,
This blog post summarizes When & Why storing Session Information in SQL-Server makes sense.
Refrerences:
http://idunno.org/articles/277.aspx
Why use SQL?
· Once you start running multiple web servers for the same web site (Web Gardne/Web Farm), the default asp.net session state, InProc, is no longer useful, as you cannot guarantee that each page request goes to the same server.
· It becomes necessary to have a central state store that every web server accesses.
· Other Reason: To restore the
TradeOff with InProc/StateServer:
· The SQLServer approach is not as efficient as InProc and StateServer mode, because you must store the information in a different process or on a different server.
· However, this option may be more efficient than using the StateServer mode / aspnet_state service, depending on the actual workload and the database configuration.
· Using SQL is slower than using InProc session state. When storing basic data types (string, int, etc), ASP.Net can take 10%-25% longer to store their values. Complex types take even longer. Of course because you are connecting to a separate server it does use bandwidth on your network.
· Generally the out-of-process
· Generally the out-of-process state service is usually about 15 percent slower than the InProc because of the serialization overhead and marshaling.
Of course your mileage will likely vary. Don’t let these numbers concern you too much. Be sure to do scalability testing on your applications before you panic and make inappropriate decisions
Serialization :
· When using SQL Server mode, objects stored in session state are serialised and deserialised when a request is processed.
· So any objects which do not support serialisation cannot be stored in session state.
· To declare any class as Serializable, either implement Iserializable or make it serialized using the [Serializable] attribute.
Type of session state support:
· t: temporary. Session state data is stored in the “Tempdb” database. Stored procedures for managing session are installed in the “ASPState” database. Data is not persisted if you restart SQL. (Default)
· p: persisted. Both session state data and the stored procedures are stored in the “ASPState” database.
· c: custom. Both session state data and the stored procedures are stored in a Custom database. The database name must be specified.
Steps Required:
You must complete the following two steps to enable SQL Server Session state:
· Configure your database to support SQL Server Session state.
· Configure your application to use SQL Server Session state.
To configure the DB support:
Required is to enables SQL Server Session state for a database server. To do this required is to use the aspnet_regsql [\WINDOWS\Microsoft.NET\Framework\[version]\aspnet_regsql.exe] tool to add the necessary tables and stored procedures.
The syntax to configure DB differs according to the type of session state support (as mentioned above)
temporary | aspnet_regsql -C "Data Source=YourServer;Integrated Security=True" -ssadd |
persisted | aspnet_regsql -S localhost -U sa -P xp -ssadd -sstype p aspnet_regsql -C "Data Source=XIPL149;Integrated Security=True" -ssadd -sstype p |
custom | aspnet_regsql -S localhost -U sa -P xp -ssadd -sstype c –d MyDB aspnet_regsql -C "Data Source=XIPL149;Integrated Security=True" -ssadd -sstype c –d MxSessionStateDB Use - Persist Security Info=False in place of Integrated Security=True, in case of any problems |
After this below are created in the SQL-Server.
· TempDB/ASPState/Custom database
o ASPStateTempApplications Tables
o ASPStateTempSessions Tables
Series of stored procedures : To support moving the session back and forth from SQL to memory
To configure the Application:
After you configure your database server to support Session state, you must configure your ASP.NET application to connect to your database. You can use the web configuration file in Listing 22.17 to connect to a database named YourServer.
<?xml version="1.0"?>
<configuration>
<system.web>
<sessionState
mode="SQLServer"
sqlConnectionString="Data Source=XIPL149;Initial Catalog=MyCustomDB;Integrated Security=True;User ID=sa;Password=xxx"
allowCustomSqlDatabase="true" // Required only in case the DB is Custom DB and not the TempDB / ASPState
sqlCommandTimeout="30" />
<machineKey
decryption="AES"
validation="SHA1"
decryptionKey="306C1FA852AB3B0115150DD8BA30821CDFD125538A0C606DACA53DBB3C3E0AD2"
validationKey="61A8E04A146AFFAB81B6AD19654F99EA7370807F18F5002725DAB98B8EFD19C7113
37E26948E26D1D174B159973EA0BE8CC9CAA6AAF513BF84E44B2247792265" />
</system.web>
</configuration>
Usability:
· Storing the
· But again at the same time, storing
· When Close/Cross button is fired – Site will close. Once Relogin in new window the User will found fresh session.
· When Timeout - Storing Session State in SQL Server will work fine in case of Session TimeOut, which does not happen in case of ‘InProc’.
i.e When Session Timeout happens – Flow will take back to Login page window – After Login using the same window, the User will be able to see the
· When SignOut – There are two different aproaches been used to SignOut.
1. One is using FormsAuthentication.SignOut() only. This does not clears the
2. Second is using FormsAuthentication.SignOut(); and Session.Abandon(); both.
In first case, after logout - Flow will take back to Login page window – After Login (Using the same window), the User will found the same session state, which was before the Sign out. i.e The Session Information will be restored. But if new window is used to relogin the Session state will not be restored and user will find a fresh session. I’m not sure how it treats with Tabs in Firefox.
In the second case, whether same or new window, the Session state will not be restored and user will find a fresh session.
· I mean to say it will not work like ‘Profiles’.
· Reason behind why the Session is not restored in such cases, is as per the schema of tables stored Session data in the table is recognized using the junk characters (Value of the cookie ASPNet_SessionID). This Session Id is generated new for every New Session ie. For every new request using new window. Below is the explanation.
o When you login for the first time to Yahoo site, a new row is inserted with some SessionID value.
o When you logout (assuming Session.Abandon is also used), the row still remains in the table, unless the Session Times out.
o When you login for the second time, again a new row is inserted with some different SessionID value.
o Due to this reason, there is no mechnism to relate that the same user has logged in and hence extract the already stored data.
This is a big catch. Hence, by default SQL Server is a good use in case of –
Below is the figure deleneating SQL Server Table structure and data.
Solution to the mentioned Catch: ‘Custom’ mode
Now to achieve Restoring of Session State, need is twick the soultion i.e - If somehow, instead of the junk characters if we can insert some meaningful value like the login Id, then the SessionState can be restored when the user logs in again. Do as below.
· Store the Id of the Logged-In user in place of the auto generated SessionId(Junk Characters).
· In this case, if the user logs-In again, the Session will be able to relate and will restore the Session data already stored in the DB.
· To achieve this you’ll be required to create a class implementing ISessionIDManager interface [See below figure]. By doing this, your code will be able to generate the SessionID as the LoggedIn Id instead of the default junk characters as SessionID.
To utilize this new class, we need to change the
<sessionState
mode="Custom" sessionIDManagerType="Mx.MD.Consumer.WebUi.CustomSessionId"
sqlConnectionString="Data Source=XIPL149;Initial Catalog=MyCustomDB;Integrated Security=True;User ID=sa;Password=xxx"
allowCustomSqlDatabase="true" // Required only in case the DB is Custom DB and not the TempDB / ASPState
sqlCommandTimeout="30" />
In case if you are required to update the provider as well and use the custom one, then the below changes are required in web.config.
<sessionState
mode="Custom" sessionIDManagerType="Mx.MD.Consumer.WebUi.CustomSessionId" customProvider="MxProvider"
sqlConnectionString="Data Source=XIPL149;Initial Catalog=MyCustomDB;Integrated Security=True;User ID=sa;Password=xxx"
allowCustomSqlDatabase="true" // Required only in case the DB is Custom DB and not the TempDB / ASPState
sqlCommandTimeout="30">
<providers>
<add name="MxProvider" type="Mx.MD.Consumer.WebUi.MxSqlSessionStateStore"/>
</providers>
</sessionState>
Making such changes will serve below requirements.
· When Close/Cross button is fired – Site will close. Once Relogin in new window the User will the
· When Timeout – Same as in SQL Server default State. i.e Session will be restored.
· When SignOut – There are two different aproaches been used to SignOut.
1. One is using FormsAuthentication.SignOut() only. This does not clears the
2. Second is using FormsAuthentication.SignOut(); and Session.Abandon(); both.
In first case, after logout - Flow will take back to Login page window – After Login (Using the same window), the User will found the same session state, which was before the Sign out. i.e The Session Information will be restored. But even if new window is used to relogin the Session state will be restored. i.e Whether new or same window is used for relogin, the Session inforamtion wil be resored.
In the second case – Same as in SQL Server default State -Whether same or new window, the Session state will not be restored and user will find a fresh session.
Here is the actual implementation:
public override string CreateSessionID(HttpContext context)
{
try
{
if (context.Request.Cookies["LoginId"] != null)
{
return context.Request.Cookies["LoginId"].Value.ToString();// This will be specific to the loggedIn user
// This is already been first stored at the time of Logging In
}
}
catch
{
return " MxSessionId ";
}
return " MxSessionId "; // This will be common to all the users before login
}
public new string GetSessionID(HttpContext context)
{
string id = null;
id = CreateSessionID(context);
if (!Validate(id))
id = null;
return id;
}
public override bool Validate(string id)
{
try
{
if(id.Equals("MxSessionId "))
{
return true;
}
}
catch
{
}
return true;
}
protected void loginPayroll_Authenticate(object sender, AuthenticateEventArgs e)
{
HttpCookie cook = new HttpCookie("LoginId");
cook.Value = lgnPayroll.UserName;
HttpContext.Current.Request.Cookies.Add(cook);
e.Authenticated = true;
}
Hope this helps.
Thanks & Regards,
Arun Manglick Tech Lead +91 20 30230500 Ext: 620 +91 9850901262
