Using SQL Server for ASP.Net session state

 1)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\


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


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 query4 - 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.
4.) Update the web.config file

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


        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
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