3

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.

Improve this question

2 Answers 2

Reset to default
7

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.

Improve this answer
3
  • 3
    And this doc tells you all the possible options you can change using aspnet_regsql.exe (including changing the sstype). msdn.microsoft.com/en-us/library/ms229862%28v=vs.80%29.aspx Just wanted to note that you should be able to set this option after install, not just as a part of install as might be inferred from the CodeProject article.
    – Aaron Bertrand
    Commented Sep 6, 2011 at 13:53
  • Mark, I really need to buy you a drink. Thank you. I was actually following those instructions to start with the other day, but somehow managed to miss that bit. I was using the InstallSqlState.sql script to install the ASPState db
    – Piers Karsenbarg
    Commented Sep 7, 2011 at 14:32
  • Pleasure, honestly. I did a fair bit of head scratching the first time I tackled mirroring.
    – Mark Storey-Smith
    Commented Sep 7, 2011 at 22:49
3

From MSDN - Database Mirroring Overview: "You cannot mirror the master, msdb, tempdb, or model databases."

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.