Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
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 ) – Stuart Moore Nov 8 '12 at 14:32
Yes, didn't told it in my question, will update it. – Mark Kremers Nov 8 '12 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? – Stuart Moore Nov 8 '12 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. – Mark Kremers Nov 8 '12 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? – Stuart Moore Nov 8 '12 at 15:07
show 4 more comments

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
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! – Mark Kremers Nov 9 '12 at 8:08
Apparently this is the only way :) – Mark Kremers Nov 15 '12 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) – Mark Kremers Feb 21 at 5:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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