Friday, May 26, 2006

using a custom database for persisted SQL Server ASP.Net session state

I am in the process of upgrading a major project from ASP.Net 1.1 to ASP.Net 2.0, and my ideal is to have 2 versions of the web application able to be run so that I can compare what's going on in the .Net 2.0 version to what should go on according to the .Net 1.1 version. This doesn't seem too difficult, just use a different web folder right? Well, this would be the case except that we're using SQL Server persisted session state and although they use a different schema between .Net 1.1 and .Net 2.0, the default database name is the same "ASPState". After a bit of fiddling around I eventually solved the problem.

Step 1 run the following command line from the appropriate .Net 2.0 directory replacing ASPState_2_0 with the database name you wish to use

aspnet_regsql -S localhost -E -ssa
dd -sstype c -d ASPState_2_0


This creates the session state database with the appropriate schema in the specified database name instead of the default.

Step 2 modify the following line in the web.config file accordingly

<sessionState mode="SQLServer" allowCustomSqlDatabase="true" stateConnectionString="tcpip=127.0.0.1:42424" sqlConnectionString="data source=localhost;Initial Catalog=ASPState_2_0;Trusted_Connection=Yes;" cookieless="false" timeout="15" />

Step 3 Manually give the ASPNet user (or whatever user you are running the ASP.Net web application as the appropriate priviledges on the database tables in the newly created session state databse.

4 comments:

  1. Anonymous4:31 am

    God Bless You!!!!! Thanks for publishing this!

    ReplyDelete
  2. Anonymous5:08 am

    nice1, going to use this in a live system next week. we've had the two versions of .net working off the same one for ages with no problems but i like the idea of splitting them. it also would allow for better tuning if needs be

    ReplyDelete
  3. Anonymous11:44 pm

    Thank you, this was exactly what I was looking for but couldn't find on msdn.

    ReplyDelete
  4. Anonymous9:22 am

    Thanks for the posting, this was like searching for the grain in the sand. Simply Superb!!!!

    ReplyDelete