Working with Orchard on a WinHost shared server and having access to only 500mb of memory on a ultimate account.

My Orchard application was exceeding the amount of memory usage in initial page load, and the server was flushing or refreshing the memory, and I was getting half of the page on the bottom a session state web.config error. the .css would not load properly. It occurred right when the browsers looked up https:// for my Amazon S3 account, had to go tom 56k dial-up to see that.

SQL Sessions for SQL 2014

Update On: The current date and time is: Sun Nov 16 2014 06:31:30 GMT-0600 (Central Standard Time)

Setting up In-Memory OLTP Database and objects.

This content contains the source code developed for ASP.NET to take advantage of SQL Server 2014 In-Memory OLTP as a session state store. The zip file contains source code for an optimized ASP.NET Provider for In-Memory OLTP. It also contains .sql scripts to create memory-optimized tables and natively compiled stored procedures in SQL Server for further performance gains.

The source code is provided for reference here. A downloadable package for deployment is also available via NuGet: https://www.nuget.org/packages/Microsoft.Web.SessionState.SqlInMemory.

Adding to the Web.config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <system.web>
    <sessionState mode="Custom" customProvider="SqlInMemoryProvider">
      <providers>
        <add name="SqlInMemoryProvider"
             type="Microsoft.Web.SessionState.SqlInMemoryProvider"
             connectionString="data source=sqlserver;initial catalog=ASPStateInMemory;User 
             ID=user;Password=password;" />
      </providers>
    </sessionState>
  </system.web>
</configuration>

• Adds an ASPStateInMemory.sql file that includes the script for creating the SQL Server database configured to support In-Memory OLTP.

Setting up In-Memory OLTP Database and objects

Open the T-SQL script file "ASPStateInMemory.sql" and update the 'CREATE DATABASE' statement to replace the 'FILENAME' attributes to specify a path that will exist in your SQL Server machine where the memory-optimized filegroup should exist. For further considerations on placement of this filegroup, see Books Online section Creating and Managing Storage for Memory-Optimized Objects.
	CREATE DATABASE [ASPStateInMemory]

	ON PRIMARY (

	NAME = ASPStateInMemory, FILENAME = 'D:\SQL\data\ASPStateInMemory_data.mdf'

	),

	FILEGROUP ASPStateInMemory_xtp_fg CONTAINS MEMORY_OPTIMIZED_DATA (

	NAME = ASPStateInMemory_xtp:, FILENAME = 'D:\SQL\data\ASPStateInMemory_xtp'

	)

	GO
• Adds an ASPStateInMemory.sql file that includes the script for creating the SQL Server database configured to support In-Memory OLTP.

Setting up In-Memory OLTP Database and objects

Open the T-SQL script file "ASPStateInMemory.sql" and update the 'CREATE DATABASE' statement to replace the 'FILENAME' attributes to specify a path that will exist in your SQL Server machine where the memory-optimized filegroup should exist. For further considerations on placement of this filegroup, see Books Online section Creating and Managing Storage for Memory-Optimized Objects.

After updating the 'FILENAME' attributes, run the entire script for creating the In-Memory tables and the natively compiled stored procedures.

Additionally, create a periodic task in SQL Server to run the stored procedure 'dbo.DeleteExpiredSessions'. This procedure removes the expired sessions and frees up the memory consumed.

NOTE: The memory-optimized tables are created with a durability of SCHEMA_ONLY to optimize for performance. If session data durability is required, then change the 'DURABILITY' attribute from 'SCHEMA_ONLY' to 'SCHEMA_AND_DATA'. More information can be found in Books Online sections Defining Durability for Memory-Optimized Objects and Durability for Memory-Optimized Tables.After updating the 'FILENAME' attributes, run the entire script for creating the In-Memory tables and the natively compiled stored procedures.

The zip file for Microsoft.Web.SessionState.SqlInMemory.1.0.1.zip

Microsoft SQL Server Help: https://msftdbprodsamples.codeplex.com/releases/view/125282

Cookie Less ASP.NET

At MSDN: http://msdn.microsoft.com/en-us/library/aa479314.aspx#cookieless_topic1

setting in web config for InProc cookieless domain

<sessionState cookieless="true" />

For Older 2012 or below SQL databases.

After the clearing of memory resources it was losing In Proc session state and logging me out all the time, So WinHost must be aware of this and they will run the session schema on the database that you choose for your dedicated SQL database SessionState Database  for nothing, nice support at WinHost.com.

I have a WinHost Ultimate account Mansbooks.com and Bibwoe.com an 2 other Orchard sites. Lots of memory used. So I created a new database of the minimum of 25mb and called it DB_?????_sessionstate, sent a support ticket to the WinHost staff requesting the session schema be ran against Db_?????_sessionstate, within 20 minutes I received a email from WinHost saying the schema had been installed

After the dedicated session state SQL database has its schema and is online then all you need to do is add one line in your web.config right below system.web with your connection information after the …..data source=YOUR STRING cookie-less=”false”…..

<system.web>
<sessionState allowCustomSqlDatabase="true" sqlCommandTimeout="1800" mode="SQLServer" sqlConnectionString="data source=tcp:s05.winhost.com;database= session
state;user id= sessionstate_user;password=*********" cookieless="false" timeout="60" />

My application took a couple of refreshes before it would load the .css.
I killed the warm up pages cleared the logs, refreshed again and no more session state problems with my Orchard 1.7.2 installation's. A session state schema produces the folling tables

SessionVariables

1

SessionID

char[MAX_SESSION_KEY_LEN]

Session name

2

VariableName

char[MAX_VARIABLE_NAME_LENGTH]

Variable name

3

VariableValue

varbinary[MAX_VARIABLE_VALUE_LENGTH]

Variable value

SessionReferences
ColumnNameTypeDescription

1

SessionID

char[MAX_SESSION_KEY_LEN]

Session name

2

LastAccess

Datetime

Date and time of last access

3

RefCount

INT

Count of current references on this session

4

TimeoutMS

Int

Timeout value for the session

 

The below was taken from beansoftware.com

ASP.NET Session State enables you to choose where you want to store website session data. There are four possible modes: InProc (default mode), StateServer, SQLServer and Custom mode. By default, InProc mode stores sessions in application process. On high traffic websites with thousands of visitors, session data will quickly overburden web server, slow down application and decrease performances. In the other hand, using of SQLServer mode is more scalable option. SQLServer mode uses SQL Server database to store sessions. Because sessions are stored in database and outside of ASP.NET process, web server memory remains free and application executes fast even on high traffic websites.

Another problem when default InProc mode is used, is that sessions are deleted every time when application restarts or stops. This could happen very often, see Restart and Stop of ASP.NET Application tutorial. Sessions stored in SQL Server database remains intact during web application restarts. Also, depending of configuration, sessions could survive SQL Server restarts too, or even Windows restarts. This fact makes SQL Server most reliable option for storing ASP.NET sessions.

How to install Session State schema on SQL Server using InstallSqlState.sql or InstallPersistSqlState.sql scripts

To use SQL Server as storage for sessions, first step is to create appropriate database schema on SQL Server. One of the ways to do this is to execute InstallSqlState.sql or InstallPersistSqlState.sql T-SQL scripts against desired SQL Server. Both script files are located in .Net framework version folder. For example, for .Net 4.0, the address could be:

C:\Windows\Microsoft.NET\Framework\v4.0.30319\InstallSqlState.sql

Please notice that you can't use script of ASP.NET 1.0 for use on ASP.NET 2.0 application.

Main difference between InstalSqlState.sql and InstallPersistSqlState.sql is in which database tables are created. For example when InstallSqlState.sql is executed, SQL Server will create new database named AspState and all needed stored procedures in it. But, two required tables: ASPStateTempApplications and ASPStateTempSessions tables will be located in tempdb database.

In the other hand, if we use InstallPersistSqlState.sql, after script is executed, AspState database will contain all procedures and tables in it.

So, only difference during installation is location where ASPStateTempApplications and ASPStateTempSessions tables will be created. That is the cause of main difference in run time: if two required tables are stored in AspState database, i.e. InstallPersistSqlState.sql is used, sessions will not be lost even if SQL Server machine is restarted. But, if tables are in TempDB database (InstallSqlState.sql is used), sessions will be lost when SQL Server restarts.

Installing Session State schema using aspnet_regsql.exe tool

 

ASPNET_REGSQL.EXE is a command line program located in .Net Framework version number folder. For example, for .Net Framework 4.0, path could be:

C:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_regsql.exe

You don't need to navigate to this folder. Faster way is to open Visual Studio Command Prompt which allows direct access to VS tools.

ASPNET_REGSQL.EXE supports several parameters:

ParameterDescription
-S [Server Name]Sql Server name or IP address, where sessions will be stored
-E Windows authentication is used to access SQL Server
-U [User Name]User name, if SQL authentication is used
-P [Password]Password, if SQL authentication is used
-D [Database Name]Database name. This parameter is used if parameter -sstype is equal to "c", which means that custom database is used as session storage.
-ssaddAbbreviation of "Session State Add". This option adds session state database.
-ssremove This parameter removes existing session state database.
-sqlexportonly [Script File Name or Full Path]If this parameter is used, program will not execute any SQL, but just create T-SQL script which can be executed later on server(s). This is useful in shared hosting scenario.
-sstype [t|p|c]

Type of session state storage. There are three possible values:

t, means temporary storage. Tables are created in temdb database. In this case session is lost if SQL Server restarts,
p, represents persistent storage. Session state tables are created in AspState database,
c, custom storage. In this case, you specify database name where sessions will be stored. Database name is set using -D parameter.

Some example configuration, which uses custom database named "YourCustomDatabaseName" for session storage and uses windows authentication to connect to SQL Server, would be:

aspnet_regsql -ssadd -sstype c -d YourCustomDatabaseName -E

If you run aspnet_regsql without parameters, it will show wizard as Windows application. If you prefer graphical user interface, you can use this wizard to specify each parameter and avoid using of command line arguments.

Installing Session State with SQLServer mode on shared web hosting

By default, Session state that uses SQLServer mode requires SQL Server Agent. SQL Server Agent runs job to delete data on session expiration. In most cases, Agent is not allowed by hosting providers in shared web hosting scenario. Also, on shared web hosting you are probably not allowed to create new database in code. So, only option to use SQLServer mode on shared web hosting, is to remove parts in SQL code that create job and create new database.

Like in previous example, we'll use command line tool aspnet_regsql.exe. When -sqlexportonly parameter is used, aspnet_regsql.exe will not execute SQL. It will just create .sql script of given name.

For example, to create T-SQL script named "SessionScript", which creates custom database "SessionDb", we'll use:

aspnet_regsql -ssadd -sstype c -d SessionDb -sqlexportonly c:\SessionScript.sql

Generated scripts include part for creating database. But, on shared hosting we'll create database using web hosting control panel or use some existing predefined databases. Because of that, we need to remove this part from script. T-SQL code which should be removed is located on top of the script and in our case will look like this:

USE master
GO

/* Create and populate the session state database */

IF DB_ID(N'SessionDb') IS NULL BEGIN
 DECLARE @cmd nvarchar(500)
 SET @cmd = N'CREATE DATABASE [SessionDb]'
 EXEC(@cmd)
END

Since shared hosting providers mostly don't allow SQL Server Agent too, you need to remove parts that create job for deleting of sessions. After that, script can be executed against database in shared hosting scenario.

But, we have another problem. Since we'll not use SQL Server Agent for deleting sessions, they will remain in database. We need other mechanism to delete expired session.

Expired sessions are deleted when DeletedExpiredSessions stored procedure is executed. By default, this procedure is called by Agent. As an alternative, you can call this procedure using line:

Exec [dbo].[DeleteExpiredSessions]

Possible solution is to add this line to the beginning of procedure TempGetAppID. In this case expired sessions will be deleted any time session data are requested. On high traffic web sites, this could be too often and possibly overburden SQL Server. Also, you probably don't need to check expired session so often.

Another option is to create scheduled task which will execute in regular time intervals and call DeleteExpiredSessions procedure. Scheduled task can be in form of ASP.NET page, web service, console application etc. For example, you can execute .aspx page every minute. Since this page is executed every 60 seconds, it will not delete sessions exactly on session timeout. It could be anywhere between 20 and 21 minutes. But, this option is much more scalable and 1 minute tolerance is usually good enough. You can execute task more or less frequently if needed. More about scheduled jobs in ASP.NET see in Scheduled tasks in ASP.NET tutorial.

Session State in web farm or web garden scenario

If SQLServer mode is used, Session state will work if website is hosted on multiple servers (web farm) or multiple processors (web garden). In case of multiple servers, you need to do two additional steps:

- Have same application path on all web servers.

- Use same machine key on all web servers. Machine key is used for encryption/decryption of session cookies. If machine keys are different, one server can't decrypt session cookie saved by other servers, so sessions could not be read.

Web.config changes to store sessions on SQL Server

After running selected T-SQL script or using aspnet_regsql.exe tool, required database schema is installed. Next step is to edit <sessionState> element in web.config file. <sessionState> element will tell ASP.NET that we'll use SQLServer mode and will specify database connection string. Default mode is InProc (inside of ASP.NET process). To store session state on SQL Server, ASP.NET requires to change mode attribute to "SQLServer" and to specify sqlConnectionString attribute to your SQL Server. For example:

<sessionState
      mode="SQLServer"
      sqlConnectionString="data source=127.0.0.1;user id=username;password=password"
    />

That is all you need to do. If you start web application, session data should be visible in ASPStateTempSessions table. You can read or change data using SQL Server Management Studio or directly using ASP.NET code.

On shared hosting, you need to set allowCustomSqlDatabase parameter to true and add database name in sqlConnectionString parameter. Possible configuration for sessions stored in "SessionDb" database could look like this:

<sessionState
      mode="SQLServer"
      allowCustomSqlDatabase="true"
      sqlConnectionString="Database=SessionDb;data source=127.0.0.1;user id=username;password=password"
    />

ASP.NET Session State on SQL Server Express

On SQL Server Express, you need to enable SQL Server Agent extended stored procedures (Agent XPs). You can do it using following SQL code:

EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXECUTE sp_configure 'Agent XPs', 1
RECONFIGURE WITH OVERRIDE
GO

EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

Advantages of using SQL Server as Session State storage

- SQL Server is most reliable option. Sessions stored in SQL Server's database will survive ASP.NET restarting and recycling, or SQL Server restarts. There is an option to use SQL cluster to avoid "single point of failure" which makes this option even more reliable.
 - SQL Server is more scalable option, when compared to State Server and especially to InProc

Sql Server mode disadvantages

- Sessions using SQL Server database are usually little slower than State Server. This disadvantage is small (about 15-20%) and often ignored.
- SQLServer mode requires most work during implementation. InProc works by default, and State Server needs less configuration than SQLServer.
- If you don't use SQL Server for your website already, there is an additional cost for obtaining a new license. SQL Server is not free. In this case, you may consider Custom mode, and store Session variables to database you already have (e.g. MySQL, Oracle etc.).
- All objects stored in session variables must be serializable. With InProc mode, it is possible to store any .Net type in Session. But, SQLServer mode requires serialization/deserialization of object. Process of serialization and deserialization takes some time which could decrease performances. Often is faster to create several primitive types (strings, integers etc.) which don't need serialization, than to serialize/deserialize one complex class. If possible, store only primitive types in Session to avoid serialization and get fastest execution.
- Since T-SQL script creates database with strict name "AspState", this mode usually is not available on shared hosting. Also, if you have multiple web applications on same dedicated server, they will all use same database for sessions. However, these problems could be solved. To see how to specify database name in sqlConnectionString, read Microsoft's support fix: Using one SQL database for all applications for SQL Server session state may cause a bottleneck

How to uninstall Session State from SQL Server

Notice that in the same folder where InstallSqlState.sql is located, is also UninstallSqlState.sql file. As its name implies, it can be used to uninstall session state schema. If you try to run it on live website, script will return an error "Cannot drop the database 'ASPState' because it is currently in use". So, you must change session state mode, or sqlConnectionString of <sessionState> element, or completely stop ASP.NET process before uninstalling AspState database.

Conclusion

ASP.NET supports four modes to specify where Session state is stored. That are InProc, SessionState, SQLServer and Custom. SqlServer is often recognized as most reliable and scalable option. Reliability comes with cost, because SQL Server is also slowest mode (about 20% slower).

You can experience different errors and problems when try to enable sessions on SQL Server. In many cases, error is related to insufficient rights. Database user should be enabled to select, insert and update data in tables and right to execute stored procedures.

Although ASP.NET Session State is improved a lot, especially when compared to sessions in classic ASP, in many cases it is not best option for state management. Session variables are deleted when session expires, which could be a problem. There are other methods, like Profile properties or cookies that could be used to keep data between sessions. More about different options about how you can save visitor's data, you can find in ASP.NET Session State Alternatives tutorial.

Happy coding!

2 Comments

  • London Movers Residential

    I read this piece of writing completely on the topic of the comparison of newest and previous technologies, it's
    remarkable article.

    • free web hosting tk domain

      It's perfect time to make some plans for the future and it is time to be
      happy. I have read this post and if I could I wish to suggest you some interesting things or advice.

      Maybe you can write next articles referring to this article.
      I want to read more things about it!

Comments are Moderated, from, "(Spam-bots)", before they are posted.

 13380 Views