0

We're trying to move ASP.NET session state for one of our Azure web apps into a database, and it seems like the aspnet_regsql.exe tool is the way to go. Unfortunately, I'm getting stuck on a few issues below. It's an Azure SQL database, and I'm connecting using the server's admin account.

  • I initially wanted to add the session tables to our existing database, so I ran .\aspnet_regsql.exe -U adminusername -P adminpassword -S servername.database.windows.net -d databasename -ssadd -sstype c. Which throws the exception "Database 'databasename' already exists. Choose a different database name"
  • Omitting the database name and running it again throws the exception: "Execution Timeout Expired" after about 30 seconds, which is just the default for SqlCommand.CommandTimeout. This occurs while executing the "CREATE DATABASE" command. I tried creating a database manually, and it takes about 50 seconds for some reason. This database is S0 tier and is not under any load
  • Running aspnet_regsql again on the already-created database (because it's idempotent, right?) leads to the "Database already exists" error, as does pre-creating an empty database for it to start from.
  • There's no flag that lets me increase the timeout, and I can't set command timeout using the -C (connection string) flag
  • Adding the -sqlexportonly flag to generate a script and just running that directly doesn't work either (yes, I know I'm not supposed to run InstallSqlState.sql directly). It throws a whole load of error messages saying things like:
    • Reference to database and/or server name in 'msdb.dbo.sp_add_job' is not supported in this version of SQL Server.
    • USE statement is not supported to switch between databases.
    • Which makes me think this script might have some issues with an Azure SQL database...

Does anyone have any ideas?

Update:

It looks like all the errors involving 'msdb' are related to removing and re-adding a database job called 'Job_DeleteExpiredSessions'. Azure SQL doesn't support database jobs, so the only options I can see are

  1. Run SQL on a VM instead (vastly more expensive, and I'd rather stick with the platform services than have to manage VMs)
  2. Implement one of those "Elastic Job Agents"
  3. Perhaps move the same functionality elsewhere (e.g. a stored proc)?
Matt Wanchap
  • 841
  • 8
  • 20

1 Answers1

0

Turns out Microsoft has an article about how to do exactly what I need, which I somehow missed during my searching yesterday. Hopefully this answer saves someone else a few hours of frustration. All the info you need is at https://azure.microsoft.com/en-au/blog/using-sql-azure-for-session-state/ earlier.

Note that YMMV since it's from 2010 and also says in scary red letters

"Microsoft does not support SQL Session State Management using SQL Azure databases for ASP.net applications"

Nevertheless, they provide a working script that seems to do exactly what I need.

Matt Wanchap
  • 841
  • 8
  • 20