Per gestirla su SQL Server ti basta lanciare sul DB server lo script InstallPersistSqlState.sql che trovi nella directory del framework e che provvederà a crearti tutte le tabelle necessarie. A questo punto tutti i web.config devono puntare a quel server.
Stesso discorso se vuoi gestirlo tramite StateServer, ma anzichè creare tabelle dovrai abilitare il servizio Windows sul server che manterrà le sessioni (sarà uno solo e tutti punteranno a quello).
In entrambi questi casi il tuo codice non cambia di una virgola: valorizzi e recuperi sempre utilizzando l'oggetto Session, attenzione però che ora tutto quello potrai mettere in Session dovrà essere serializzabile (la classe deve essere marcata Serializable).

NPersist e NHibernate sono progetti open source che si occupano di ORM (Object Relational Mapping) e persistenza, sono validi progetti, ma non credo che ti possano essere utili in questo caso.