I started a new consulting gig this week, and while attempting to setup a web project on my laptop so I could start work, I got to do something I've never attempted to do before.  Setup a database for storing ASP.NET Session State.

This itself is not incredibly exciting, but what was interesting is, the developer who was helping me get the site configured couldn't remember how to get the database created.  This is understandable, as you do it once on your machine, and never do it again.  The second interesting part is, when we asked google how to do it, we were lead to several sites that pointed us to a sql script located in the various folders for the different versions of the Framework (3.0 and 3.5 excluded I have found out, only 2.0 and earlier).  However, this developer I was paired up with swore he ran a command line utility to create the database. After asking google a bit more, I found the answer.

The command line utility is aspnet_regsql.exe, and the details can be found on this MSDN article.  The default behavior of this application is to run a wizard that will create tables and sprocs for Membership, Roles, Profiles, etc that ASP.NET's application services require.  It does not create the tables and sprocs required for storing session state.  To do that, you must use some command line switches.

aspnet_regsql -S [server] -E -ssadd -sstype c -d SessionStateDB

It's important to note that the switches seem to be case sensitive.  The first time I ran this command, I used a lowercase s to specify the server, and it complained it was an unknown switch.  The -E parameter tells the program to use the current user to authenticate against the database.  You can also specify a username and password with the -U and -P switches respectively.  The switch -ssadd tells the app to create the session state database structure.  Next we tell the app how to create the structure with the -sstype switch.  There are three possiblities: t, p, and c.

Using 't' will Add an ASPSptate database with procedures to store session state in the temp db.  This is the default behavior.  Using 'p' will stored the session data in the AspState database along with the procs.  Finally, 'c' will install the procs and tables to a custom database.  When using 'c' you must also specify the database to use with the -d switch.  The database will be created if it does not already exist.

Note that this utility only works in this way when using .NET 2.0+ (though the utility itself only lives in the 2.0 framework folder).  If you are using a previous version, then the instructions found here will apply instead.  There are two possible scripts to run, one is InstallSqlState.sql which is equivilent to the 't' option of -sstype.  The other is InstallPersistentSqlState.sql which is the equivilent of the 'p' option.

The final step is to include the necessary data in your web.config.  Here is a sample configuration block:

<sessionState

    mode="SQLServer"

    allowCustomSqlDatabase="true"

    sqlConnectionString="Data Source=.;Initial Catalog=SessionStateDB;"

    cookieless="false"

    timeout="20"

/>