We have recently been working on migrating all of our systems over to asp.net (from classic ASP) and one of the opportunities was to do "proper" session state management, not only allowing us to increase performance of the session state, but minimise server memory load during peak times, so we investigated putting the session into SQL Server (on a separate server) so that it can be shared with multiple servers.
Its actually very straight-forward:
1. On the SQLServer, open a new command window (as Administrator, which it is by default in Server 2003/2008).
Go to the root by typing (after each line press Enter)
You should end up with:
Now type in the actual command to add the session to SQL Server (it is case sensitive)
aspnet_regsql -ssadd -S <servername> -U <sa or admin user on the SQLServer> -P <password for chosen account> -sstype p
The most common problem is that the SQLServer cannot be found – things to check are:
1. Is the server name correct – if you installed SQLServer and did not use the default instance then you need to put the instance name after the server name. e.g. SERVERNAME\SQLServer or SERVERNAME\MSSQLServer
2. Does the account you are using have access to create databases. In general, we have found that using the sa account works best, however if you encounter problems (like we have on numerous occasions) then creating a new account that has equivalent rights to create a database.
3. From a security standpoint we only enable the sa account to add session state and then instantly disable it again – it is highly dangerous to leave the sa account enabled, as this (as we have experienced) can lead to some rather nasty persons trying to brute-force break into the SQLServer