Best practice in clearing session state in SQL Azure

Question

Hi guys,

I've read from this SQL Azure Tam Blog

and only followed partially where I've 1 SQL Azure subscription, created the ASPState database, tables and store proc with the script.

As for clearing the session, I didn't follow the idea from the Doing the Clean up

Instead, what I did was to have a web service to call the stored proc DeleteExpiredSessions to clear the temp session table. this web service is consumed by a VB script that is triggered daily.

Also, I noticed when I logged in to the SQL Azure database, when the DeleteExpiredSessions store proc is executed, the size of the table doesn't change but the number of rows have been reduced.

What could be the issue?

I would like some opinions on what would be the best practice for clearing up session state which is stored in SQL Azure.

Thanks.

Answer 1

Hi FbLover2011,

The clean up process described in that article you shared is basically making use of a WorkerRole and have the DeleteExpiredSessions Stored Proc. executed every X minutes. 

Your solution is also OK, although you need to create/maintain extra Web Services, VB Scripts and Scheduled Tasks to achieve the same goal. So, in short words, the solution in the article is more elegant. But, your solution also gets the job done.

As for the issue you are mentioning: I don't see any issues at all - If you check that Stored Procedure's (SP) code you'll see that it will only remove the rows for the sessions that are expired. Therefore, if you see less rows after running the SP, then it should be fine!

Hope this helps!

---

Best Regards,
Carlos Sardo

Answer 2

hi Carlos,

Yes, I do agreed with you that my approach indeed need more attention for maintainability compared with using a worker role.

Though I'm not sure how difficult it is to just add in an extra worker role onto my production website without affect the the site.

In regards to the size of the table, I noticed that somehow it takes a while for the size of the table to be reduced after the number of rows have been reduced through the store proc. Not sure is it a slight delay for SQL Azure to display the result on the SQL Azure database management portal, but the number of rows is updated correctly unlike the table size (in MB).