Is the ASP.NET 4.0 SQL session state mechanism backward-compatible with the ASP.NET 2.0 schema for session state, or should/must we create a separate and distinct session state database for our ASP.NET 4.0 apps?
I'm leaning towards the latter anyway, but the 2.0 database seems to just work, though I'm wondering if there are any substantive differences between the ASPState database schema / procedures between the 2.0 and 4.0 versions of ASP.NET. Thank you.
There was no quick answer on this from anybody, so I did some digging. I generated an ASPState database using the aspnet_regsql.exe tool from .NET 2.0, and then I did the same thing using the same tool but from .NET 4.0. Then, I generated scripts from each of those resulting SQL Server databases and used a comparison tool to isolate the differences.
What I found is: The only material difference between the ASPState schema from .NET 2.0 to .NET 4.0 versions is the dbo.DeleteExpiredSessions stored procedure. That's the stored procedure called periodically by a SQL Server Agent scheduled job also installed by the tool.
Consequently, it would seem that the schema for ASPState 2.0 and ASPState 4.0 are perfectly compatible and so it's not necessary, from a technical standpoint, to segregate ASP.NET 2.0 and ASP.NET 4.0 session state – but I'll likely do it anyway.
(This finding was a bit surprising, as ASPState changed a lot from .NET 1.1 to .NET 2.0.)
Details for each version's changed stored proc:
CREATE PROCEDURE dbo.DeleteExpiredSessions
AS
DECLARE @now datetime
SET @now = GETUTCDATE()
DELETE [ASPState].dbo.ASPStateTempSessions
WHERE Expires < @now
RETURN 0
GO
CREATE PROCEDURE dbo.DeleteExpiredSessions
AS
SET NOCOUNT ON
SET DEADLOCK_PRIORITY LOW
DECLARE @now datetime
SET @now = GETUTCDATE()
CREATE TABLE #tblExpiredSessions
(
SessionID nvarchar(88) NOT NULL PRIMARY KEY
)
INSERT #tblExpiredSessions (SessionID)
SELECT SessionID
FROM [ASPState].dbo.ASPStateTempSessions WITH (READUNCOMMITTED)
WHERE Expires < @now
IF @@ROWCOUNT <> 0
BEGIN
DECLARE ExpiredSessionCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
FOR SELECT SessionID FROM #tblExpiredSessions
DECLARE @SessionID nvarchar(88)
OPEN ExpiredSessionCursor
FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM [ASPState].dbo.ASPStateTempSessions WHERE
SessionID = @SessionID AND Expires < @now
FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID
END
CLOSE ExpiredSessionCursor
DEALLOCATE ExpiredSessionCursor
END
DROP TABLE #tblExpiredSessions
RETURN 0
GO
As for why the above change was necessary, I found the following MSDN blog post:
Excerpt, in reference to the older procedure:
...
This would take the locks on all the expired records for deletion and these locks may be promoted to page locks. This can give rise to deadlocks with other ‘session state write statements’ when the number of records marked for deletion increases. By default this stored procedure is supposed to run every minute. ...
Consequently, the newer version of the stored proc may be advisable for ASP.NET 2.0 apps, too.
One more thing I learned from the blog post that I did not know: ASP.NET 4.0 session state mechanism now offers compression. Search on compressionEnabled at sessionState Element (ASP.NET Settings Schema).
Finally, I also just found something relevant from Microsoft, at ASP.NET Side-by-Side Execution Overview. Excerpt:
...
If SQL Server is used to manage session state, all versions of ASP.NET (of the .NET Framework) that are installed on the same computer can share the SQL state server that is installed with the latest version of ASP.NET. The schema for session state is the same in all versions of ASP.NET.
(Though there are some differences in implementation not specific to the schema.)