Generate SQL Script to Manage Session State in Custom SQL Database

Friends,

ASP.Net provides 3 different ways to store sessions. They are –

• InProc Session Mode
• StateServer Session Mode
• SQL Server Session Mode

In case you are interested to read further about sessions, you can read this excellent article by Abhijit Jana. In this post, we will see some specific details about SQL Server Session Mode. When we use SQL Server Session Mode, ASP.Net uses SQL Server to store and manage sessions which is helpful in various ways. However, by default script provided by Microsoft tries to creates a new database named ASPState which is good, but there are scenarios where you want to manage sessions in your custom SQL database instead of the default database provided by Microsoft.

In this post, we will see how we can generate a new SQL Script that uses our own custom database for managing sessions. To do so, we need to perform the following steps –

• Open Command Prompt
• Go to C:\Windows\Microsoft.Net\Framework\<Your Framework Version>\
• Type aspnet_regsql -S SQLSERVER -U User -P Password -d MyDB -sstype c -sqlexportonly f:\sqlstate.sql -ssadd
• You’re done.

Here is an explanation of each parameter in the aspnet_regsql command –

• -S — Name of SQL Server
• -U — Username to connect with SQL Server
• -P — Password to connect with SQL Server
• -d — Name of custom Database to use sessions
• -sstype — Type of session state support. We are using “c” as the Session support type becasue we are using custom database. Other valid values are “t” and “p”
• -sqlexportonly — Export the Script to a file name on your hard disk.

Hope you like this post. Keep learning and sharing! Cheers!