Sql-server – the best way to mirror an ASPState database

mirroringsql-server-2008sql-server-2008-r2

I'm not having issues with the actual mirroring part, but because of the use of temporary tables, IIS is throwing a bit of a wobbler if a failover occurs (I'm still testing a few things, so it's me causing the failover).

Is it possible (or even a good idea) to mirror tempdb? Or should I just accept that some people might get kicked out if the principal database is not accessible for any reason.

I'm using ASPState so that the two web servers (behind a load balancer that enables sticky sessions) can both get to the same session data.

Best Answer

Mirroring tempdb, that's a no. Have you checked Step-by-step SQL Mirroring the ASPState database?

If the -sstype:p parameter is not specified, then by default, the ASP.NET sessions will be put into the TempDB database and not the ASPState database. This confused me for a while. This is fine for a normal non-mirrored environment because the ASP sessions will be cleared if the server is restarted. But, this is not fine for the mirrored environment, because we want to mirror the ASP session data itself, not just the Stored Procedures! Also, mirroring is not possible on the TempDB database. The -sstype:p parameter makes ASP.NET install the session tables into the ASPState database, and they'll be persisted if the server is restarted. This is exactly the behaviour we want for a mirrored environment.

Related Question