Access issues with session database after applying the performance boost script

  • Description

    After applying the performance optimization script from the Optimize SQL Server performance section of this article, users can experience issues with session database read/write operations after restarting the SQL server. SQL users configured to have access to the tempDB created by the script might no longer have such access and Sitecore can throw session-related exceptions. This behavior is related to the fact that tempDB is recreated along with its access rights each time the SQL server is restarted.

  • Open the Sessions db performance boost.sql script and add the query marked as bold, in the following way:

    CREATE PROCEDURE [dbo].[Sitecore_InitializeSessionState] AS
    BEGIN
    EXECUTE [Sitecore.Sessions].[dbo].[CreateTables];
    EXEC('USE tempDb; CREATE USER [YOUR_USER] FOR LOGIN [YOUR_USER] WITH DEFAULT_SCHEMA=[dbo]; ALTER ROLE [db_datareader] ADD MEMBER [YOUR_USER]; ALTER ROLE [db_datawriter] ADD MEMBER [YOUR_USER]')
    END;
    GO

    After this, to execute the script, use the performance enhancement instructions from the Configuring a shared session state database using the SQL Server provider article.

  • Special thanks

    We would like to thank Rob Ahnemann from the Rock, Paper, Sitecore blog and his reader Shaun for the provided solution to this problem. The original article with this approach can be found by the following link: Sitecore Shared Session: Moving from the tempDB.

Applies to:

CMS 8+

June 28, 2017
June 28, 2017

Reference number:

167367

Keywords: 

  • CMS