Platform Administration and Architecture

Walkthrough: Configuring a shared session state database using SQL

Abstract

How to use the SQL service provider as your private session state store.

A contact can make multiple visits to a website from different devices or browser sessions at the same time. With shared session state the session state database collects all the data that can be shared across these multiple sessions. This includes, for example, data related to contacts and devices. This data is still private to the contact but it is accessible from all current sessions made by the same contact.

You must always configure shared session state on content delivery (CD) servers, regardless of whether you have a single CD server, multiple CD servers, or a clustered environment.

Note

Shared session state is not supported on content management (CM) servers.

You can configure shared session state to use any Session state store provider that extends the abstract SessionStateStoreProviderBase class (shipped with ASP.NET). The only additional requirement is that the Session state store provider must be able to invoke the SessionEnd event using the SessionStateItemExpireCallback delegate.

Note

The standard OutOfProcSessionStateStore class that ships with ASP.NET does not support the SessionEnd event and therefore  you cannot use it with shared session state.

This walkthrough describes how to use a SQL Server database as your shared Session state store provider using the Sitecore ASP.NET Session State Provider for SQL Server. It describes how to:

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 you must:

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

  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 Deploy Data-tier Application…

  4. In the Deploy Data-tier Application wizard, on the Select Package page, click Browse.

  5. Select the Sitecore.Sessions.dacpac file and click Next

    Note

    You can find the file in the Databases folder in the root of the Sitecore archive.

  6. In the Update Configuration step, specify the name of the database, click Next and, after validating summary information, click  Next. When the deploy is completed, the session database 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 session-state store database 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.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 SQL Server tempDB 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 KB article. For more information see Session-State Modes on MSDN.

Note

The tempdb system database is currently not supported by Azure SQL Database service.

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 runtime.

By default, Sitecore uses the in process provider, which stores data in memory and is implemented in the internal ASP.NET InProcSessionStateStore class:

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

To configure Sitecore to use the shared Session state store provider for SQL Server instead of the default:

  1. In your website root folder, navigate to the \App_Config\Sitecore\Marketing.Tracking 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 default provider from inProc to mssql and 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>

Note

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

Adjust shared session state settings

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

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

connectionStringName="sharedSession"
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 can cause some additional CPU overhead.

compression="true"
sessionType

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

sessionType="shared"