Skip to main content

How to Store Sessions in SQL Server

We can maintain the session for asp.net application in the following three ways.
  1. Inproc : This will store the session in the memory of the web server. This is mostly used and will be lost when the application crashes. But the advantage with this is the performance. As the session is stored in the web server itself, this is very fast compared to other methods.
  2. State Server: This will store the session in the memory of a machine, which is dedicated for the purpose of storing sessions. This is efficient and scalable.
  3. SQL Server: This will store the session in SQL Server. To store a session in SQL Server, we need to configure the sql server. This is also efficient, scalable and secure implementation of storing a session.
Now Discuss about configuring the SQL server to store the state:

This can be done by executing a simple file named 'aspnet_regsql.exe'. This file is located in the .NET framework folder.

Follow the steps to configure the Database server,
  1. Open the command prompt and goto OperatingSystemDrive\WINDOWS\Microsoft.NET\Framework\v2.0.50727
    • Note: The file is available in v2 only. Though you are using Asp.net Framework v3 or v3.5 you need to goto 2.0.50727 only.
    • As There wont be any change in the sql queries to create a database tables and stored procedure with the version of asp.net framework, They haven't provided with the file in v3 and v3.5 folder.(This is my understanding, the actual reason may be different)
  2. If you just type aspnet_regsql.exe and press enter, this will open a GUI to specify the Database server and the name of Database. If you don't specify any database name the default name will be 'sqldatadb'.
  3. This will create a database with tables and stored procedures, but the database is not yet ready to store the sessions.
  4. For completing the configuration we need to run the command aspnet_regsql.exe -ssadd -sstype c -d DATABASENAME -E.(Give the Database Name if you specity any in step 2).
  5. For more details related to options for this command can be found here.

With this the database is configured to store the sessions. Though it is verymuch scalable and secure, this has performance issues when compared to 'inproc' implementation.

Note: We can run step4 mentioned above in Visual Studio Command prompt to created the Database to store sessions in one shot.
Labels:

Comments

Post a Comment

Popular posts from this blog

Solution Array for List in WCF

Here is another common problem encounters with WCF Service in initial days. Suppose there is a method which returns a List of Objects the proxy will be generated as method which returns an array of objects. Reason: This problem is due to the default interpretation of collections. While generating proxy for the service all the collections are by default interpreted as arrays. That is why all the lists will be converted to arrays. Solution: If you are generating proxy by adding service reference from visual studio directly, there will be a 'advanced' button. Click that button and select the System.Generic.List as your Collection. This will resolve the problem. If you are generating proxy with svcutil here is the command to do the job. svcutil.exe http://ServerName/ServiceName/Servic.svc?wsdl /collectionType:System.Collections.Generic.List`1 or in short svcutil http://ServerName/ServiceName/Servic.svc?wsdl /ct:System.Collections.Generic.List`1 for more information go through this ...
1 comment
Read more

Linq: Query for multiple join conditions in Linq to SQL

This is common to join a table with another on multiple conditions, Like Select * from Customer c Join Invoice i on c.CustomerID = i.CustomerID and c.CustomerTypeID = i.CustomerTypeID I need to write a Linq to SQL query for the same scenario. But the syntax for this query is a bit different to expect. The Linq query is as follows: From c in DataContext .Customers Join i in DataContext .Invoices on new { c.CustomerID, c.CustomerTypeID } equals new { i.CustomerID, i.CustomerTypeID } *DataContext in the above query should be replaced with actual datacontext name.
3 comments
Read more

Troubleshoot WCF service returning object with null fields

When you are working with WCF service for the first time, generally you will get this error for some reason. Here is the description of the error: WebServiceReference . ServiceClient client = new WebServiceReference . ServiceClient (); WebServiceReference . ImplementationRequest req = new WebServiceReference . ImplementationRequest (); req = client.GetRequest(); Here the Method GetRequest will return an object of ImplementationRequest. Everything will run without any error but the req object will contain all null fields/ majority null fields. Solution: Those nulls are due to error in generating the proxy for the Data Contract defined by the service. Verify that [DataMember] attribute tag is given for all the data fields and [DataContract] for the Class. If those attributes are missing, the object will be generated with null values. If all fields have the tags but the issue is not resolved, then the service reference should be re-added and the proxy should be re-generated. Then t...
1 comment
Read more