3

I know this has been a topic that has been discussed in AWS forums before

and SO How to setup sessionState with SQL Server mode on Amazon RDS

As mentioned on the above thread and on a couple of stack overflow articles it would seem like there is a way to get a SQL session using AWS RDS. I used the above stackoverflow as guidance to try and set up my ASPState database. Using the pastebin script in the above stackoverflow article for a "jobless" InstallSqlState.sql still caused me issues as it tries to use "tempdb" and "master". I don't have access to these databases neither can I grant myself permissions to do so.

However had a working site that used the sessions table on an EC2 server. As per the above stackoverflow article I used the SQL import/export tool.

The database seemed to copy over okay, tables and stored procedures all seem to be present and correct.

In my web.config I have:

<sessionState mode="SQLServer" allowCustomSqlDatabase="true" cookieless="false" timeout="45" sqlConnectionString="data source=RDSIP;initial catalog=ASPState;user id=myuser;password=mypassword" />

However when I run my site I get the error:

Invalid object name 'tempdb.dbo.ASPStateTempApplications'.

Is there anyone who has managed to achieve a session state using SQL on AWS RDS or can point me to a resource that can explain the steps I need to take?

Community
  • 1
  • 1
Jacob Polden
  • 73
  • 1
  • 8

3 Answers3

2

After some digging around I realised that the stored procedures being generated by the pastebin script are still making reference to the tempdb. By doing a simple find replace of [tempdb] to [ASPState] and then re-running the script recreated SP with the correct DB name.

I also changed the "USE" statements to databases I had permissions for.

Jacob Polden
  • 73
  • 1
  • 8
  • Do sessions still expire if the jobs aren't created? MSDN says this: `If entries in the ASPStateTempSessions table are not removed after the related sessions expire, make sure that the SQL Server agent is running. You can implement this functionality through stored procedures that are scheduled through jobs in SQL Server. The SQL Server agent manages these jobs.` – DrDeth Nov 06 '14 at 16:29
  • 1
    It certainly looks like they are. We are using this on our production site now and we aren't running into any issues. – Jacob Polden Nov 07 '14 at 17:01
0

I've solved my issue create session db on Amazon RDS.

1 Step:- Create ASPState DB on AWS RDS and database and schema migration using Following Tool SQLAzureMW v5.15.6 Release Binary for SQL Server 2014.

2 Step:- change DB Name on sessionState mode="SQLServer" allowCustomSqlDatabase="true" sqlConnectionString="data source=amazon-server-name;initial catalog=ASPState;persist security info=True;user id=userid;password=password" cookieless="false" timeout="100">

please feel free to contact us about any of your queries

Thanks

Amit Verma

Nonu Thakur
  • 36
  • 1
0

I solved the issue by generating scripts for the whole schema (Tasks -> Generate Scripts) from a locally create session database, and then running the same on the RDS instance on a newly created session database. For the job, I created it manually by copying the exact steps from my local instance.

The setup is running fine till now.

Ahmad Zaib
  • 13
  • 4