We use SQL Server to maintain our Sitecore sessions which mostly works fine. However as part of the performance boosts recommended by Sitecore the session-state is moved to store session in SQL Server tempDB.
The process is explained here: https://doc.sitecore.net/sitecore_experience_platform/setting_up_and_maintaining/session_state/session_state/walkthrough_configure_a_shared_session_state_database_using_the_sql_server_provider
However, every time that SQL Server is restarted, it recreates tempDB and resets the access rights for users and the session tables. This will result in your Sitecore site going down and an error such as:
System.Data.SqlClient.SqlException: The SELECT permission was denied on the object ‘SessionState’, database ‘tempdb’, schema ‘dbo’. The INSERT permission was denied on the object ‘SessionState’, database ‘tempdb’, schema ‘dbo’. The UPDATE permission was denied on the object ‘SessionState’, database ‘tempdb’, schema ‘dbo’.
And also an angry client and customers :-(.
How do I solve this?
You could re-run the script from the Performance boost download provided by Sitecore (Sessions db performance boost.sql ) but this could happen late at night when no one is monitoring your site and cause downtime.
The best way to solve this and still use tempDB for sessions is to offload this manual task into a stored procedure that runs on Start-up.
The following script will create a Stored Procedure in the Master database that will run each time SQL Server restarts and add your session tables and database user back to tempDB with the right permissions.
USE [master]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE type = ‘P’
and name = ‘AddSitecoreSessionTempDBOwner’)
DROP PROC AddSitecoreSessionTempDBOwner
GO
CREATE PROC AddSitecoreSessionTempDBOwner
AS DECLARE @sql
VARCHAR(500)SELECT @sql = ‘EXEC [master].[dbo].[Sitecore_InitializeSessionState]’ + char(13) +’USE [tempdb]’ + char(13) +’IF NOT EXISTS(SELECT name FROM sys.database_principals WHERE name = ”Sitecore_Sessions”)’ + char(13) +’BEGIN’ + char(13) +’CREATE USER [Sitecore_Sessions] FOR LOGIN [Sitecore_Sessions]’ + char(13) +’ALTER ROLE [db_datareader] ADD MEMBER [Sitecore_Sessions]’ + char(13) +’ALTER ROLE [db_datawriter] ADD MEMBER [Sitecore_Sessions]’ + char(13) +’END’EXEC (@sql)
GO
EXEC sp_procoption ‘AddSitecoreSessionTempDBOwner’, ‘startup’, ‘true’
GO
Just remember to update ‘Sitecore_Sessions’ in the script above to the name of the user in your Sessions Database connection string, then run the script in SQL Server Manager to create the stored procedures that will run on start up.
How do I test this?
It is important to test this to ensure that on a restart of the SQL Server the script kicks in and works it’s magic. First check if the Stored Proceedure is set to run on Startup correctly by running this query:
SELECT name,create_date,modify_date
FROM sys.procedures
WHERE OBJECTPROPERTY(OBJECT_ID, ‘ExecIsStartup’) = 1
You should see the ‘AddSitecoreSessionTempDBOwner’ and ‘Sitecore_InitializeSessionState’ stored procedures listed. If you don’t try running the script above again as something went wrong.
Once these are listed correctly you should carry out a full test by scheduling in a time out of hours when you can reboot the SQL Server machine and check your script runs correctly.
Gotchas
One thing to ensure is running is the SQL Server Agent Service. This needs to be running in order for and startup stored procedures to be executed.
Check this is set to ‘Auto Run’ under startup type. This caught me out on my first test as my Stored Procedure didn’t run correctly on reboot of the SQL Server.
Lastly check your session state tables are collecting session data correctly using a query such as:
select count (*) from [tempdb].[dbo].[SessionState]
That should be it, no more problems with tempDB when SQL Server restarts.
