Walkthrough: Configuring a shared session state database using SQL

Abstract

How to use SQL Server as your shared session state store.

In shared session state, all the data that can be shared across multiple sessions, for example, the data related to contacts and devices, is collected and saved to the session state database.

A contact can make multiple parallel visits to a website in which case each visit has its own private session state. However, some data can be shared between visits, such as device and contact related information.

Information that is shared between parallel visits by the same contact is stored in the shared session state store. This data is still private to the contact but it is accessible from all current sessions made by the same contact.

Note

If you are using the xDB Cloud Edition, using the shared session state provider for MongoDB is not included as part of this service.

This walkthrough describes how to use a SQL Server database as your shared session state store using the Sitecore ASP.NET Session state provider for SQL Server, by doing the following tasks:

  • Deploy a SQL Server session database

  • Configure Sitecore

  • Adjust shared session state settings

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.

Note

You can store shared and private session state info in the same database. The database is able to distinguish between the types of session.

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 must 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. Go 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="sharedSession" connectionString="user
        id=_sql_server_user_;password=_user_password_;Data
        Source=_sqlserver_;Database = _sharedSession_database_name_"/>

Optimize SQL Server performance

For each web request, Sitecore accesses the sessionstate store database multiple times. This can have a significant impact on the performance of your website. Therefore, we recommend that you install enough RAM to allow Microsoft SQL Server to keep the session state database in memory. We also recommend that you 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, open the Sessions db performance boost.sqlfile. 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 the SQL Server tempDB database, which is the standard practice recommended by Microsoft. Every user must have access to tempDB. However, every time 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 Knowledge Base article. For more information see Session-State Modes on MSDN.

Note

We do not recommend or support using the Sessions db performance boost.sql file and the Microsoft TempDB system database to optimize performance on the Azure SQL Database service as this has not been tested.

Configure Sitecore

Note

Do not make changes directly to the configuration files. Instead, you must create a patch file that performs the required changes during run time.

The default shared session store uses the in-process provider, which stores data in memory and is implemented in the internal ASP.NET class InProcSessionStateStore:

<sharedSessionState defaultProvider="inProc">
    <providers>
    <clear/>
    <add name="inProc"
      type="System.Web.SessionState.InProcSessionStateStore"
      />
    </providers>

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

  1. In your website root folder, navigate to the Website\App_Config\Include folder.

  2. Open the Sitecore.Analytics.Tracking.Config file.

  3. Locate the line where you can define the default shared session state provider using the following path: sitecore/tracking/sharedSessionState.

  4. Change the defaultProvider from inProc to mssql. Also, change the name attribute value to mssql.

    <sharedSessionState defaultProvider="mssql">
    <providers>
        <clear/>
        <add name="mssql"
            type="Sitecore.SessionProvider.Sql.SqlSessionStateProvider,Sitecore.SessionProvider.Sql"
            connectionStringName="sharedsession"
            pollingInterval="2"
            compression="true"
            sessionType="shared"/>
    </providers>
</sharedSessionState>

Adjust shared session state settings

The Sitecore.Analytics.Tracking.Config file contains the following session state settings, which you can change using a patch file:

Setting

Description

Example

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.

connectionStringName="session"
pollingInterval

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

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.

compression="true"
sessionType

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

sessionType="shared"