Session maintenance in SQL Server for .Net Applications

Sbanerjee
Posted by in ASP.NET category on for Intermediate level | Views : 6805 red flag

Listing all the steps to show how to configure the SQL Server so that our .Net application can store and retrieve the Session values in and from SQL server.

Here, 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.

Steps to follow for 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:

      <sessionState mode="SQLServer" sqlConnectionString="Data Source=ServerName; 

       User Id=UserId; Password=Pwd; Initial Catalog=ASPState;Persist Security Info=True;"
       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:
We can store huge data in Session without effecting the Web server momory. Here, 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. Minus point is that every Session request will be redirected to DB Server from Web Server.

Page copy protected against web site content infringement by Copyscape

About the Author

Sbanerjee
Full Name: Soumen Banerjee
Member Level: Starter
Member Status: Member
Member Since: 6/9/2008 6:54:20 AM
Country: India

http://www.ethicaldeveloper.blogspot.com/
Learning has no end, keep spreading knowledge as it increases self knowledge. Cheers

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)