ASP.Net SQL Server Session State vs Persistent Session state

0
votes

I am researching and trying some things out with regards to the automatic saving of session variables into SQL Server. I have found this link discussing SQL Server session state (call it 'Option 1') and this link discussing Persistent SQL Server session state (call it 'Option 2').

The main difference seems to be that with Option 1, if the machine running SQL Server is turned off, the session data is lost, contrary to Option 2 which persists it. In Option 1 it seems that the process makes use of the tempdb, whereas, Option 2 seems to persist the session data through the use of the ASPState database.

I have a few questions that I can't seem to find any answers to:

  1. In the first option, the article discusses the configuration required in the web config with regards to the < sessionState > tag. This is not added to the persistent case (Option 2). Should the < sessionState > be the same in both cases?

I have tried the second option as that seems to be more in-line to my case scenario. I started by downloading PersistSQLState.exe which simply self-extracts the InstallPersistSqlState.sql and UninstallPersistSqlState.sql scripts. I ran the InstallPersistSQLState.sql which completed successfully and created the 'ASPState' database in the SQL Server. I set the < sessionState > tag using the format stated in Option 2 as discussed in point 1) above.

Upon running the app I got the below error:

"Unable to use SQL Server because either ASP.NET version 2.0 Session State is not installed on the SQL server, or ASP.NET does not have permission to run the dbo.TempGetVersion stored procedure. If the ASP.NET Session State schema has not been installed, please install ASP.NET Session State SQL Server version 2.0 or above. If the schema has been installed, please grant execute permission on the dbo.TempGetVersion stored procedure to either the ASP.NET application pool identity, or the Sql Server user specified in the sqlConnectionString attribute."

This outlines 2 things: the Asp.Net Version 2.0 Session state potentially being not installed yet, and the process being unable to run the dbo.TempGetVersion stored procedure (for various reasons).

  1. The stored procedure dbo.TempGetVersion does not exist in the ASPState database created by the InstallPersistSqlState.sql script. Is this stored procedure actually related to the tempdb (non-persistent) and therefore why the schema of the ASPState does not include it?

  2. Is this the Asp.Net Version 2.0 Session state that needs to be installed? If so what is the process to be followed?! Should it be installed in the ASPState database after running the InstallPersistSQLState.sql script? If not, what is to be done exactly? There is nothing related to this ".exe" in either Option 1 or Option 2!

  3. Finally, I have also tried uninstalling the persistent case instead run the scripts related to the Option 1 (non-persistent). In this case, the scripts provided in the first link above do not seem to work; returning 2 errors with jobs not being found ?!. Anyone has any idea why this would happen and do the correct scripts exist in some kind of online location?!

Any help would be appreciated as documentation seems poor on this subject.

1
asp.netsql-serversessionsession-state

1 Answers

2
votes

The Option 1 and Option 2 articles as listed in my question are misleading, which is why I ended up wasting a whole day trying to make it work with the wrong info: I just did not have the right article at hand!

The below is what I ended up doing to implement persistent session state. Hope it can prove helpful to anyone wanting to implement the same thing.

  1. On the machine open CMD in Administrator mode.

  2. Change directory to .Net framework folder:

    cd C:\Windows\Microsoft.NET\Framework\v4.0.30319

  3. Run this command:

    aspnet_regsql.exe -S "SQLServerInstanceName" -U "Username" -P "Password" -ssadd -sstype p

Small explanation of command in Point 2 above.

  • Aspnet_regsql.exe - is the .exe that installs Asp.Net Session state on the machine.
  • -S defines SQL Server instance name
  • -U defines Username -
  • -P defines Password
  • ssadd defines creating a new database for session state

  • sstype defines the type of session state. p is persistent.

    1. Finally, in the web.config of the Asp.Net application where persistent session state is to be implemented, change the tag to look like the below:

    < sessionState mode="SQLServer" sqlConnectionString="data source=127.0.0.1\sqltest;User ID=*****;Password=******" cookieless="false" timeout="20" / >

For full info visit this article and refer to the SQL Server mode.