Managing Session In Azure VM SQL

session.png

If you want to store your Session information in SQL DB then the following article would be helpful.

First step would be configure the Session State Mode as OutProc and set the connection string for the OutProc SQL server.

To Configure SQL Server as OutProc we need to use Microsoft provided SQL Query (InstallSqlState.sql) to create the necessary DB and its tables. You can find this SQL script in your .NET Framework Folder as mentioned below:

InstallSQLState.png

Create a new Database and Run the sql script named ‘InstallSqlState.sql’. This will generate the below tables in the DB.

106.png

Next step is to define the OUTPROC in the webconfig as mentioned below:

connectionstring

After that there is no code change is required to use OUTPROC in your .net

To use SQL for managing Session information you may need to use SQL Standard Edition or higher because the OUTPROC method requires a sql job to run in background with the help of SQL AGENT to clear the session values when it expires.

If you are using SQL Server Express edition then you cannot run the SQL Agent in it. The SQL Agent is used for running the jobs of SQL server. Then the alternate way to clear the expired session is to write a Schedule Task to call in 1 minute interval and clear the Expired Sessions.

Please read the below article to know how to clear the expired session in SQL Server Express edition by your own.

Step 1: Create a bat file with the logic to clear the session

Step 2: Call the bat file in the Windows Task Scheduler to call in a regular interval (Alternative to SQL Agent Jobs)

Let us see how we can create a bat file with the logic to clear the Session. The DB that we have created before for managing the session information; you can find a Stored Procedure named ‘DeleteExpiredSession

107.png

Now the challenge is to execute the Stored Procedure named: DeleteExpiredSessions to remove the expired sessions from DB.

The below mentioned command can be used to create a bat file which runs SQL cmd and execute the Stored Procedure named DeleteExpiredSessions. Let us create the bat file as ClearSession.bat

sqlcmd /S <instancename> -d I<databasename> -q “Exec DeleteExpiredSessions”

After creating the bat file Schedule a Task to run the bat file using Windows Task Scheduler as mentioned below.

Open Task Scheduler

108.png

From Task Scheduler Create a Basic Task

109.png

In the General Tab, provide a Name and Description as mentioned below.1003

 

Next create Trigger for the Task, this will control when the Task should run. The trigger configured will call this Task in 1 minute interval.

111.png

Specify an action that will call the bat file that we have created before.

112.png

Finally save and it creates the Task as mentioned below.113.png

This Task will run in 1 minute interval and calls the SP to clear the expired sessions.

Hope you have enjoyed this article.

Happy Programming.

Advertisements
%d bloggers like this: