Tuesday, November 25, 2008

Session maintenance using SQL Server for .Net Web applications

Hi,
I am listing all the steps how to configure the SQL Server so that our .Net application can store and retrieve the Session values in and from SQL server. All suggestions for improvement are heartly welcome.
Session maintenance in SQL Server for .Net Applications:
1. Run aspnet_regsql.exe from .Net command prompt. This will run a wizard.
2. Provide server name
3. Use windows authentication if you are having rights to create tables on your server else provide the sys admin user name and password
4. Use the AspState database
5. This will create set of tables in AspState and tempdb databases
6. Create logins for the application user on AspState and tempdb databases
7. Execute the following script:
use APSState
grant EXECUTE on GetHashCode to [user]
grant EXECUTE on GetMajorVersion to [user]
grant EXECUTE on TempGetAppID to [user]
grant EXECUTE on TempGetStateItem to [user]
grant EXECUTE on TempGetStateItem2 to [user]
grant EXECUTE on TempGetStateItem3 to [user]
grant EXECUTE on TempGetStateItemExclusive to [user]
grant EXECUTE on TempGetStateItemExclusive2 to [user]
grant EXECUTE on TempGetStateItemExclusive3 to [user]
grant EXECUTE on TempGetVersion to [user]
grant EXECUTE on TempInsertStateItemLong to [user]
grant EXECUTE on TempInsertStateItemShort to [user]
grant EXECUTE on TempInsertUninitializedItem to [user]
grant EXECUTE on TempReleaseStateItemExclusive to [user]
grant EXECUTE on TempRemoveStateItem to [user]
grant EXECUTE on TempResetTimeout to [user]
grant EXECUTE on TempUpdateStateItemLong to [user]
grant EXECUTE on TempUpdateStateItemLongNullShort to [user]
grant EXECUTE on TempUpdateStateItemShort to [user]
grant EXECUTE on TempUpdateStateItemShortNullLong to [user]
grant SELECT on ASPStateTempApplications to [user]
grant INSERT on ASPStateTempApplications to [user]
grant UPDATE on ASPStateTempApplications to [user]
grant SELECT on ASPStateTempSessions to [user]
grant INSERT on ASPStateTempSessions to [user]
grant UPDATE on ASPStateTempSessions to [user]

use tempdb
grant SELECT on ASPStateTempApplications to [user]
grant INSERT on ASPStateTempApplications to [user]
grant UPDATE on ASPStateTempApplications to [user]
grant SELECT on ASPStateTempSessions to [user]
grant INSERT on ASPStateTempSessions to [user]
grant UPDATE on ASPStateTempSessions to [user]

8. Do the following changes in Web.Config file:
allowCustomSqlDatabase="true" cookieless="false" timeout="20"/>
9. Make sure that all classes in the application, whose objects we are storing in Session, should have the [Serializable] attribute.
Now few points:
  1. We can store huge data in Session without effecting the Web server momory.
  2. We are shifting the burden of runtime memory to SQL database. So that we can now create the application which can handle huge Session data.
  3. Minus point is that every Session request will be redirected to DB Server from Web Server.