Walkthrough: Configuring a private session state database using the SQL Server provider

Last updated Friday, July 7, 2017 in Sitecore Experience Platform for Administrator, Developer

In private session state, all data related to a specific interaction, such as viewed pages, converted goals, triggered campaigns, or accumulated engagement points, is collected and saved to the session state database.

You can use either MongoDB or SQL Server as your private session state store. SQL Server might be an appropriate option if you are running the collection database (MongoDB) in the cloud as a service, or if you prefer not to run an on-premise MongoDB server instance.

This topic outlines how to configure a SQL Server database as your private session state store using the Sitecore ASP.NET Session State Provider for SQL Server:

Note

If you are running an on-premise solution with a MongoDB database as your collection database, you should use MongoDB as your session store.

The SQL Server provider supports the session-end event that is required by the xDB in order to track website visits.

Deploy a SQL Server session database

The Sitecore ASP.NET Session State Provider for SQL Server enables you to use SQL Server as your session state store. This provider supports the SessionEnd event that the xDB needs to track website visits.

To deploy the SQL Server session database:

  1. Start Microsoft SQL Server Management Studio 2012 or later.

    Important

    If you are using SQL Server 2008, you first need to download Microsoft SQL Server Management Studio Express before you can deploy the Session database.

  2. Connect to the server node that you want to install the Session database on.
  3. Expand the server node, right-click Databases, and then click Attach.
  4. In the Attach Databases dialog box, click Add.
  5. Browse to the Databases folder in your website root folder, select the Sitecore.Sessions.mdf database and click OK.
  6. In the Attach Databases dialog box, click OK. The session database now appears in your list of attached databases.
  7. Add the following connection string to the ConnectionStrings.config file:
    <add name="session" connectionString="user id=_sql_server_user_;password=_user_password_;Data 
    Source=_sqlserver_;Database = _session_database_name_"/>

Optimize SQL Server performance

For each web request, the session-state store database is accessed multiple times. This can have a significant impact on the performance of your website. Therefore, you should install enough RAM to allow Microsoft SQL Server to keep the session state database in memory. You should also put the database files on an SSD drive.

To achieve optimal performance, you can install an extension to the Sessions database.

To install the performance enhancements:

  1. In Microsoft SQL Server Management Studio 2012, open the Sessions db performance boost.sql file.

    This file is stored in the \Databases\Scripts folder of your Sitecore installation.

  2. In the first line of the Sessions db performance boost.sql file, replace USE [Sitecore_Session] with the name of your session database.
  3. After you have updated the USE statement to point to your session database, press F5 to execute the file.

Note

These performance enhancements move the session-state store to SQL Server tempDB which is the standard practice recommended by Microsoft. However, this is not supported on Windows Azure.

Every user must therefore have access to tempDB. However, every time that SQL Server is restarted, it recreates tempDB and resets the access rights. For information about how to ensure that users always have access to tempDB, see this KB article.

For more information see Session-State Modes on MSDN.

Configure Sitecore

To configure Sitecore to use the private session state provider for SQL Server:

  1. In your site root folder <sitename>\Website, open the web.config file and locate the sessionState section:
    <sessionState mode="Custom" customProvider="mssql" cookieless="false" timeout="20">
  2. Update the sessionState section by adding the SQL Server provider as shown in the following example. Also, change the name attribute value to mssql:
    <sessionState mode="Custom" customProvider="mssql" cookieless="false" timeout="20">
      <providers>
        <add name="mssql"
             type=" Sitecore.SessionProvider.Sql.SqlSessionStateProvider,
             Sitecore.SessionProvider.Sql"
             connectionStringName="session"
             pollingInterval="2"
             compression="true" 
             sessionType="private"/>
      </providers>
    </sessionState>

Adjust session state settings

In Sitecore, when you configure a session state, you have the following configuration options:

Setting

Description

connectionStringName

Contains the connection string that Sitecore uses to connect to the session database.

Edit to specify the session state database that you want to use. In the xDB, this database is called session.

For example:

connectionStringName="session"

pollingInterval

Specifies the time interval in seconds that the session-state provider uses to check if any sessions have expired.

For example:

pollingInterval="2"

compression

Indicates that you want session-state data to be compressed.

The default value is true. Compressing session state data reduces the amount of data that you need to transfer between the database and the Sitecore instance. This may cause some additional CPU overhead.

For example:

compression="true" />

sessionType

Indicates whether the type of session state is private or shared.

For example:

sessionType="private"