Platform Administration and Architecture

Walkthrough: Configuring a private session state database using SQL

Abstract

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

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

You can use the Sitecore ASP.NET Session State Provider for SQL Server to configure a SQL Server database as your private session state store. 

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

Note

Content Management (CM) servers do not support using an external session state database. On CM servers you can only use in process session state.

This walkthrough outlines how to deploy a SQL Server session database, optimize SQL Server performance, configure Sitecore, and adjust private session state settings.

Deploy a SQL Server session database

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.

To deploy the SQL Server session database:

  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="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, Sitecore accesses the session-state store database multiple times. This can have a significant impact on the performance of your website. Therefore, you must install enough RAM to allow Microsoft SQL Server to keep the session state database in memory. We 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 Sessions db performance boost.sql file, replace all instances of Sitecore.Sessions with the name of your session database. For example, if your sessions database is named mySessionsDB, change [Sitecore.Sessions] to [mySessionsDB].

  3. After you have updated the USE statement to point to your session database, press F5 to execute the file.

These performance enhancements move the session-state store to SQL Server tempDB, which is the standard practice recommended by Microsoft.

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

Important

Every time SQL Server is restarted, it recreates tempDB and resets the access rights. You must ensure that users always have access to tempDB. 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, open the web.config file and locate the sessionState line:

     <sessionState mode="InProc" cookieless="false" timeout="20" 
        sessionIDManagerType="Sitecore.SessionManagement.ConditionalSessionIdManager">

    Change this line to:

    <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. 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 private session state settings

To adjust the private session state settings:

  • In the web.config file, edit 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 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 must 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="private"