There are lots of places to get information about putting the Session State into a SQL Server database instead of in-memory. The information I have found most useful is the utility aspnet_regsql.exe. It performs a bunch of different tasks for ASP.NET and interaction with SQL Server and the membership interfaces.
For example if you want to get session persisted across SQL Server reboots you can issue this command:
aspnet_regsql.exe -ssadd -sstype p -E -S <servername>
The sstype p is the option that allows for the state to be persisted. You can execute aspnet_regsql.exe -?