Home » Knowledgebase » Programming » ASP.NET » Using SQL Server for ASP.Net session state

Using SQL Server for ASP.Net session state

Using SQL Server for ASP.Net session state

Setup/use SQL Server for ASP.Net session state

This tutorial will go through steps to setup/install the session state service for ASP .Net application with custom database.
1.) Create the MSSQL Database for session state
2.) Tool to install the session state template
3.) Installation the session state service on custom database
4.) Update the web.config file
5.) Create the job to delete expired sessions

1.) Create the MSSQL Database for session state

Create MSSQL database for the session state service.
control panel -> databases -> MSSQL Admin -> Create New DB

This tutorial will go through with follow MSSQL DB info:
Database Server Address: sql999.mysite4now.com
Database name: statedemoDB_62760
Database User ID: statedemoUser_62760
Database Password: statepassword
*You should create/use your database to setup the session state service

2.) Tool to install the session state template
You can install the session state service to custom database with the installation template query:
InstallSqlStateTemplate.sql

By default, InstallSqlStateTemplate.sql located in following folders:
C:\Windows\Microsoft.NET\Framework\version_ID\

For example:
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\InstallSqlStateTemplate.sql

Copy this sql file to another location, and update the template file:
- find and replace all reserved string DatabaseNamePlaceHolder within the template
- replace this string to the database name which install the session state service, for this tutorial - statedemoDB_62760

3.) Installation the session state service on custom database
Connect the database with Microsoft SQL Server Management Studio

Currently, this database is a blank database:

Execute the InstallSqlStateTemplate.sql via the Management Studio

1 - Click "New Query"
2 - Copy and paste the contents of updated InstallSqlStateTemplate.sql as sql query
3 - "Execute" the query
4 - The result of the query execution

Query execution result:

-------------------------------------------------
Starting execution of InstallSqlStateTemplate.SQL
-------------------------------------------------

--------------------------------------------------
Note:
This file is included for backward compatibility
only. You should use aspnet_regsql.exe to install
and uninstall SQL session state.

Run 'aspnet_regsql.exe -?' for details.
--------------------------------------------------
If the job does not exist, an error from msdb.dbo.sp_delete_job is expected.
Msg 229, Level 14, State 5, Procedure sp_delete_job, Line 1
The EXECUTE permission was denied on the object 'sp_delete_job', database 'msdb', schema 'dbo'.
If the category already exists, an error from msdb.dbo.sp_add_category is expected.
Msg 229, Level 14, State 5, Procedure sp_add_category, Line 1
The EXECUTE permission was denied on the object 'sp_add_category', database 'msdb', schema 'dbo'.
Msg 229, Level 14, State 5, Procedure sp_add_job, Line 1
The EXECUTE permission was denied on the object 'sp_add_job', database 'msdb', schema 'dbo'.

--------------------------------------------------
Completed execution of InstallSqlStateTemplate.SQL
--------------------------------------------------

Normally, the core tables/stored procedures have installed on the database after execution
Above error message due to insufficient user privilege to add the scheduled job.
Which is a scheduled job to delete expired session state record of the session state database.

Execution of InstallSqlStateTemplate.sql installed session state related tables/stored procedures:

4.) Update the web.config file

Update the web.config to use the custom session state server/database:
Update/insert sessionState code:

<sessionState
        mode="SQLServer"
        allowCustomSqlDatabase="true"
        sqlConnectionString="data source=sql999.mysite4now.com;initial catalog=statedemoDB_62760;user id=statedemoUser_62760;password=statepassword"
        cookieless="false"
        timeout="20"
/>

** Use mode = SQLServer
** To use custom DB instead of default database "ASPSTATE" , need to enable option allowCustomSqlDatabase="true"
** sessionState : S is uppercase, case sensitive
** sqlConnectionString : C , S are uppercase, case sensitive

Session state record update within the table:

5.) Create the scheduled job to delete expired sessions record in the database

Refer to steps 3.)
To setup the scheduled job for the session state database,
you need to purchase the scheduled Task service via control panel:
control panel -> Site Admin -> Schedule Task

After that submit a ticket to our support team and they will give further assistance to setup the schedule job.

Tags:

ASP.NET, sessionstate

User Comments

by Thomas Boyle added 6/17/2009 3:51 AM

Can you provide us with a sample script for deleting expired sessions?

Helpful?

Yes

by Mark added 6/20/2009 6:20 AM

There is a stored proc called "DeleteExpiredSessions" built with above setup. You can call this proc to kill expired sesion data. Non-expired record will not affected. <br />

Helpful?