Setting up SQL Session State Server

There are several reasons why you might want to setup the SQL session server. The first one is, on a shared server the ASP.NET worker process often recycles. This can cause you to lose session variables and frequently even if the session has not expired. The other reason you might want to install a session server is because sessions consumer memory. By using a session server, you free memory for your application.

Unfortunately, the default scripts that Microsoft gives you require your ability to install a SQL job. Something most hosting companies, including mywinhosting, will not allow. However, with the other hosting companies all to tell you is how to modify the default script so that will work in a shared hosting environment.

The first thing will need to do is generate the default script. You can do this by running an executable in C:\Windows\Microsoft.NET\Framework\v2.0.50727 called aspnet_regsql.exe passing in the parameters:

-d [databaseName]
-sstype c
-sqlexportonly [filename]
-ssadd

for syntax check http://msdn.microsoft.com/en-us/library/ms229862(VS.80).aspx

For example, if your database is userName_DotNetNuke, you might run aspnet_regsql like this from the command line:

Aspnet_regsql -d userName_DotNetNuke -sstype c -sqlexportonly c:\sqlstate.sql -ssadd

Which will place the sql script to create the sql session tables and stored procs in your database named userName_DotNetNuke in the root of the C drive in a file named "sqlstate.sql"

Next, load up either SQL Enterprise Manager, if you have it, or SQL Server Management Studio Express. You can get SQL Server Management Studio Express from Microsoft as a free download at: http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en and use your sql connection information to connect to your database at MyWinHosting.com using TCP/IP. Next, load up the sqlstate.sql file. The remainder of the instructions will assume you are using SQL Server Management Studio Express.

The first thing we need to modify in the script is the fact that it is trying to create the database. Since you can't do that, and presumably your database is already created, you need to delete these lines from the sql script, located roughly at line 34 (at least it is in the one I generated)

USE master
GO

/* Create and populate the session state database */

IF DB_ID(N'username_dotnetnuke') IS NULL BEGIN
DECLARE @cmd nvarchar(500)
SET @cmd = N'CREATE DATABASE [username_dotnetnuke]'
EXEC(@cmd)
END

You'll also need to delete the lines immediately following this that remove the job if it exist. It doesn't exist and you couldn't remove it if it did. And since the tables have never been created before, you might as well delete the lines that delete the existing tables if they exist. To make this easy. Do a search for the next "Use" statement where it uses your database name and delete everything from there on up. The remaining lines that should be deleted will look something like this:

DECLARE @jobname nvarchar(200)
SET @jobname = N'username_dotnetnuke' '_Job_DeleteExpiredSessions'

-- Delete the [local] job
-- We expected to get an error if the job doesn't exist.
PRINT 'If the job does not exist, an error from msdb.dbo.sp_delete_job is expected.'

EXECUTE msdb.dbo.sp_delete_job @job_name = @jobname
GO

DECLARE @sstype nvarchar(128)
SET @sstype = N'sstype_custom'

IF UPPER(@sstype) = 'SSTYPE_TEMP' AND OBJECT_ID(N'dbo.ASPState_Startup', 'P') IS NOT NULL BEGIN
DROP PROCEDURE dbo.ASPState_Startup
END

USE [username_dotnetnuke]
GO

IF OBJECT_ID(N'dbo.ASPStateTempSessions','U') IS NOT NULL BEGIN
DROP TABLE dbo.ASPStateTempSessions
END

IF OBJECT_ID(N'dbo.ASPStateTempApplications','U') IS NOT NULL BEGIN
DROP TABLE dbo.ASPStateTempApplications
END

The next thing you'll want to do is to delete the script that creates the job to delete expired sessions. You can find this at the bottom of the script. The code you want to remove, looks something like this:

BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode int
DECLARE @nameT nchar(200)
SELECT @ReturnCode = 0

-- Add the job
SET @nameT = N'username_dotnetnuke' '_Job_DeleteExpiredSessions'
EXECUTE @ReturnCode = msdb.dbo.sp_add_job
@job_id = @JobID OUTPUT,
@job_name = @nameT,
@owner_login_name = NULL,
@description = N'Deletes expired sessions from the session state database.',
@category_name = N'[Uncategorized (Local)]',
@enabled = 1,
@notify_level_email = 0,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 0,
@delete_level= 0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job steps
SET @nameT = N'username_dotnetnuke' '_JobStep_DeleteExpiredSessions'
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @JobID,
@step_id = 1,
@step_name = @nameT,
@command = N'EXECUTE DeleteExpiredSessions',
@database_name = N'username_dotnetnuke',
@server = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 1,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job schedules
SET @nameT = N'username_dotnetnuke' '_JobSchedule_DeleteExpiredSessions'
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID,
@name = @nameT,
@enabled = 1,
@freq_type = 4,
@active_start_date = 20001016,
@active_start_time = 0,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 1,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_end_date = 99991231,
@active_end_time = 235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

The last thing you'll need to do is to provide a way to delete the expired session variables. The job we deleted normally does that, but since we don't have that job anymore, we obviously need another way. So do a search for the stored procedure name TempGetAppID and insert the following line as the first executable line:

EXECUTE DeleteExpiredSessions

The resulting script should look like:

DECLARE @cmd nchar(4000)

SET @cmd = N'
CREATE PROCEDURE dbo.TempGetAppID
@appName tAppName,
@appId int OUTPUT
AS
Exec [dbo].[DeleteExpiredSessions]
SET @appName = LOWER(@appName)
SET @appId = NULL

SELECT @appId = AppId
FROM [username_dotnetnuke].dbo.ASPStateTempApplications
WHERE AppName = @appName

IF @appId IS NULL BEGIN
BEGIN TRAN

SELECT @appId = AppId
FROM [username_dotnetnuke].dbo.ASPStateTempApplications WITH (TABLOCKX)
WHERE AppName = @appName

IF @appId IS NULL
BEGIN
EXEC GetHashCode @appName, @appId OUTPUT

INSERT [username_dotnetnuke].dbo.ASPStateTempApplications
VALUES
(@appId, @appName)

IF @@ERROR = 2627
BEGIN
DECLARE @dupApp tAppName

SELECT @dupApp = RTRIM(AppName)
FROM [username_dotnetnuke].dbo.ASPStateTempApplications
WHERE AppId = @appId

RAISERROR(''SQL session state fatal error: hash-code collision between applications ''''%s'''' and ''''%s''''. Please rename the 1st application to resolve the problem.'',
18, 1, @appName, @dupApp)
END
END

COMMIT
END

RETURN 0'
EXEC(@cmd)
GO

This should delete expired sessions every time session data is requested. This modification should work for most installations. If you happen to get a lot of traffic, you might want to modify the DeleteExpiredSessions procedure so that it is a bit more efficient. There are several modifications available on the web and googling for DeleteExpiredSession along with the word "asp.net" should turn up several suggestions.

Next, you'll want to change or add the session state elements in your web.config file. It should look something like the following:



mode="SQLServer"
allowCustomSqlDatabase="true"
sqlConnectionString="Server=yourServer; Database=yourDatabase; uid=userName_xyz; pwd=Abc123;"
cookieless="false"
timeout="20"
/>





Be sure to include the allowCustomSqlDatabase attribute or it will not work.