---
title: "Walkthrough: Configuring a private session state database using the SQL provider"
slug: "walkthrough--configuring-a-private-session-state-database-using-the-sql-provider"
abstract: "How to use the SQL service provider as your private session state store."
---
# Walkthrough: Configuring a private session state database using the SQL provider

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 the xDB requires 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.

<details>
<summary><h2>Deploy a SQL Server session database</h2></summary>

> [!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:

    ```xml
    <add
        name="session"
        connectionString="User ID=<SQL server username>;password=<user password>;Data Source=<SQL server>;Database=<session database name>"
    />
    ```

8. The connection string user must be allowed to execute stored procedures on the session database. No other privileges are necessary. A system administrator can grant this privilege with these SQL statements:

    ```

    USE <session database name>;
    GRANT EXECUTE TO <SQL server username>;
    GO
    ```

</details>

<details>
<summary><h2>Configure Sitecore</h2></summary>

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:

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

    Change this line to:

    ```xml
    <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`:

    ```xml
    <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>
    ```

</details>

<details>
<summary><h2>Adjust private session state settings</h2></summary>

To adjust the private session state settings:

- In the `web.config` file, edit the following configuration options:

| Setting | Description | Value |
| --- | --- | --- |
| `connectionStringName` | Specifies the connection string that Sitecore uses to connect to the session database.<br><br>Edit to specify the session state database that you want to use. In the xDB, this database is called session. | For example:<br><br>`connectionStringName="session"` |
| `pollingInterval` | Specifies the time interval in seconds that the session-state provider uses to check if any sessions have expired. | For example:<br><br>`pollingInterval="2"` |
| `compression` | Specifies that you want session-state data to be compressed.<br><br>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. | For example:<br><br>`compression="true"` |
| `sessionType` | Specifies that the type of session state is `private`. | Only valid value:<br><br>`sessionType="private"` |

</details>