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.