Creating session database for .net frame work in SQL Server

You may require to create session databases for .net framework in your SQL Server. In that case you can use ASP.NET SQL Server registration tool (Aspnet_regsql.exe) which is used to create a database that is used by the SQL Server providers in ASP.NET. The tool is also used to add or remove options from an existing database.

This tool is in the Microsoft.NET Framework directory. You need to select correct version of Aspnet_regsql.exe to create appropriate database for a specific .net frame work.

Please find the location of the tool for appropriate version as below:

.NET Framework version 2.0, version 3.0, and version 3.5 (32-bit systems) is at

%windir%\Microsoft.NET\Framework\v2.0.50727

.NET Framework version 2.0, version 3.0, and version 3.5 (64-bit systems) is at

%windir%\Microsoft.NET\Framework64\v2.0.50727

.NET Framework version 4 (32-bit systems) is at

%windir%\Microsoft.NET\Framework\v4.0.30319

.NET Framework version 4 (64-bit systems) is at %windir%\Microsoft.NET\Framework64\v4.0.30319

You can use Aspnet_regsql.exe directly from GUI (just by double clicking on it) or from command prompt.

If you run the tool from command prompt, then please refer to below details:

Parameters:

? -> Prints Aspnet_regsql.exe Help text in the command window.

-W -> Runs the tool in wizard mode. This is the default if no command-line arguments are specified.

-C -> Specifies the connection string to the computer running SQL Server where the database will be installed, or is already installed. This option is not necessary if you specify only the server (-S) and login (-U and -P, or -E) information.

-S -> Specifies the name of the computer running SQL Server where the database will be installed, or is already installed.

-U -> The SQL Server user ID to log in with. This option also requires the password (-P) option. This option is not necessary if you are authenticating using Windows credentials (-E).

-P -> The SQL Server password to log in with. This option also requires the user ID (-U) option. This option is not necessary if you are authenticating using Windows credentials (-E).

-E ->Authenticates using the Windows credentials of the currently logged-on user.

-sqlexportonly -> Generates a SQL script file that can be used to add or remove the specified features. The specified actions are not performed.

Options:

-d ->  Specifies the name of the database to store session state. This option must be used if -sstype is set to c.

-ssadd -> Adds support for SQL Server mode session state.

-ssremove -> Removes support for SQL Server mode session state.

-sstype t|p|c -> Specifies the type of session state to use:

t - Temporary. Session state data is stored in the SQL Server tempdb database. Stored procedures for managing session state are installed in the SQL Server ASPState database. Data is not persisted if you restart SQL. This is the default.

p - Persisted. Both session state data and stored procedures are stored in the SQL Server ASPState database.

c - Custom. Both session state data and stored procedures are stored in a custom database. The database name must be specified using the -d option.

Example of installing session database for .netframework 4 on a 64 bit SQL 2008 R2 EE server named “TESTSERVER”. The session database is named as ASPState4 and we are using SQL account testuser to create this database. Password for testuser is testman

Step 1: Open command prompt (cmd.exe)

Step 2: Browse to correct  Aspnet_regsql.exe (It is %windir%\Microsoft.NET\Framework64\v4.0.30319 in this example)

Step3: Execute below command:

         

aspnet_regsql -ssadd -sstype c -S TESTSERVER -d ASPState4 -U testuser -P testman

[Note: If you want to use windows authentication, use below command:    

aspnet_regsql -ssadd -sstype c -S TESTSERVER -d ASPState4 -E]

For more details, please visit to http://msdn.microsoft.com/en-us/library/ms229862.aspx