Saturday, October 21, 2017     Login


Questions?  Call:
1-888-215-0601
or
1-407-656-9955


dnn_benefactor_gold_130x49_border.gif

aspnet.gif

aspnetajax.jpg

VisaLogo.jpg
mastercard.jpg



 Guaranteed Host
Web Hosting

 
Official PayPal Seal

Setting up SQL Session State Server Minimize

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 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.

Final note:  As of May 16th, 2007 I have only just implemented this procedure on one of my client's sites.  It appears to work as advertised, but it would be prudent to monitor the size of your database and the size of the ASPStateTempSessions table until you are convinced that it is working correctly.  Please let us know if there are any problems with this script.

*This article and associated code are provided as is and without warrantee.

 Print   
See Also Minimize

ASP.NET Web Hosting
Forms Based Authentication
Getting ASP.NET 2.0 Support
Creating a Database

 Print   

Windows Web Hosting
ASP.NET Web Hosting
Setting Up Email

Copyright 2007 by DMB Consulting, LLC   |  Privacy Statement  |  Terms Of Use     Skin designed by   Alldnnskins.com