Thursday, July 24, 2008

ASP State and SQL Server: The Mirror Has Three Faces

In the spirit of Jonathan Lewis' recent post, Sometimes, Everything I Write Sucks!, comes today's installment of the Prestidigitation of Oracle. Today's prestidigitation is that I'm not going to write about Oracle (or why I need to blog more), but I'm going to focus on a topic I've been dealing with lately which is implementing the ASPState database with SQL Mirroring.

This is part one in the series and will go over the set up of a custom ASP session state database to be used with ASP.NET applications which is connected to a SQL Server database that is set up in a SQL Mirroring set up. This article isn't going to cover the actual SQL Mirroring setup. A lot of what I did was learned from an article on Storing Session State in a SQL Server Database.

The ASP session state database has three options you can install with. A non-persistent database that utilizes tempdb, A persistent database that uses a default database called ASPState, and a persistent database that has a name of your choosing. (i.e. Custom database). Hence, the mirror has three faces. Given the premise that we have a SQL Server 2005 database set up for SQL Mirroring and that we want to keep the session state persistent between restarts as well capable of mirroring. The only choice I see for this is to store the ASP session state information in the applications database. In other words, using the custom option. This keeps the session information encapsulated in the same database as the application itself, and allows the information to be mirrored to another SQL Server database.

Installing the ASP session state database is fairly straight-forward. The first thing you'll need is the ASPNET_REGSQL.EXE executable. I found this under the .NET Framework 2.0 directory. I my case, this was the C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 directory.
I prefer Windows authentication, so the installation is fairly straight-forward.

ASPNET_REGSQL.EXE -E -ssadd -sstype c -d -s

This will install a couple tables and a bunch of stored procedures into your database. Voila, you now have a custom ASP session state database that should be ready for SQL Mirroring.

One other step I performed is one of security. Since the ASP session state database is stored procedure based, I need to give the application user the ability to execute stored procedures (i.e. The app will not connect as dbo or sysadmin).

CREATE ROLE db_executor;
GRANT execute TO db_executor;

You now have a role that will assume execute privileges on any stored procedure in the database. Assign the role to your application user and everything should work excellent.

In the next installment, I hope to cover the testing of persistence in a SQL Mirror fail-over scenario. Don't expect it too soon (the prestidigitation continues) and look for more Oracle posts to come soon.
blog comments powered by Disqus