Recently we had an issue with a sql server 2008 r2 HA mirror. I have tried to reproduce this in a lab environment and i came to the conclusion that i am missing something OR something is not possible.

I am using 2 sql server 2008 r2 servers. Both the servers have a iscsi storage connection to a storage server. Mirroring is done with a witness server in HS mode. i have followed a blog post by Glenn Berry about how to create a group failover, he told me on twitter it would also be usable in case of failing storage. However, somewhere i can't really get things to work.

What am i doing. I have a sql-01 as princiapl and a sql-02 as mirror. 2 databases running in HA mirror mode. I have a failover script running on WMI 7 and 8. When i go to my storage server to disable the storage of sql-01 i would suspect that eventually i get a failover to sql-02. but it doesn't...

Anyone can give me some hints on this?

Edit : Failover with a shutdown-ed storage for sql-01 looks working, because of a tempdb that is getting corrupt which needs recovery (according to logs). Therefor the instance has to shutdown (also in the logs), so probably that is my reason i can failover from sql-01 to sql-02, by accident and apparently not by design.

  • Do you have a witness server? And are you running in High-Safety mode? Both are required for automatic failover ( msdn.microsoft.com/en-us/library/ms189590(v=sql.105).aspx ) Commented Nov 8, 2012 at 14:32
  • Yes, didn't told it in my question, will update it. Commented Nov 8, 2012 at 14:39
  • Can you failover manually? When you disable the storage of sql-01 are the databases 'failing' and are you seeing anything in the SQL logs? Commented Nov 8, 2012 at 14:46
  • Yeah, manually failovering works. That is also exactly what i want to do in this particulair case, but with the use of a WMI Alert / SQL Agent job. The errors i see are only related to not having storage ("The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000130000 in file 'e:\MSSQL\Data\MirrorTest1.mdf'"). But that is when i try to query a certain table on that database on the server where the storage is down. Commented Nov 8, 2012 at 14:50
  • Is this the blog you're following? - sqlserverperformance.wordpress.com/2012/03/27/… - I'm wondering if it's because all the DBs are on the same 'failed' drive so the ALTER DATABASE in the sp is failing? Commented Nov 8, 2012 at 15:07

1 Answer 1


Just to move my part into an Answer. It would appear the "problem" is that the script in the blog ( http://sqlserverperformance.wordpress.com/2012/03/27/sql-server-database-mirroring-tips-and-tricks-part-3/ ) is using an ALTER DATABASE dbname SET PARTNER FAILOVER; to failover the rest of the 'failover' group.

As all the dbs were on the same disk, this fails as SQL SERVER can't run the ALTER DATABASE command if it can't get in to the database. So moving the databases onto seperate disks fixes that.

But that sort of makes sense. If all the databases are on the same disk then they should failover automatically as a 'group' as they all need to be fail overed. The referenced script would make sense if you did have databases on seperate disks that needed to failover together if just one of the disks failed.

  • Makes sense! However, getting all databases on separate storage isn't a solution for me in this case, since the problem originally was on a system with 2 storage subsystems, one for log and one for data. I will put this in my report for this customer though! Commented Nov 9, 2012 at 8:08
  • Apparently this is the only way :) Commented Nov 15, 2012 at 9:34
  • For reference, i managed to get it to work by using 1 database with different files. On each disk i made a file and i periodically insert/truncate stuff in/from it. Don't know for sure if i can manage to get it to work without this inserting/truncating, but with i can failover much faster (no insert = failover in my case) Commented Feb 21, 2013 at 5:54

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.