Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
brianjensen
Starting Member
22 Posts |
 Posted - 2009-01-20 : 10:05:23
|
| I have set up high availability mirroring in SQL 2008 and have found a minor "problem":I was testing the different levels of protection (Ping, instance offline, database failure) which causes failover and did this as follows:The ping test and instance reach tests could be handled through firewall rules and had the desired successful output: The mirror became principle and as I enabled traffic through the firewall, the new mirror started syncing.The database availability test was done by creating the mirrored database on a disk and offlining that disk (I set this up on a virtual setup). The failover succeeded, but the SQL server did not reconnect the mirroring session and the old principle never became the mirror until I restarted the SQL service. This may be Windows having problems and not related to the SQL server... any ideas? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-20 : 16:22:01
|
| Didn't really understand your question?Can you elaborate clearly? |
 |
|
|
brianjensen
Starting Member
22 Posts |
Posted - 2009-01-22 : 02:58:48
|
| Simplified:If you have a mirrored database setup and one of the disks, holding your database goes offline and then comes back online again (for some reason), SQL does not automatically see the files on your disk. Hence your mirror is broken.I was just wondering if this was something that could be "fixed" by a server setting.I'm not really sure that this post even belongs here, since it seems to be a general problem and not really related to mirroring :) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
brianjensen
Starting Member
22 Posts |
Posted - 2009-01-23 : 03:45:32
|
| Well, that exactly my question too. When the disk comes back online, Windows server 2008 sees the disk fine but SQL Server 2008 does not seem to reconnect to the database files. I don't know if this is related to mirroring or the fact that the servers are virtual, I'll look further into this :)When I get a spare moment I will create another unmirrored database on the disk and see if the problem is more general. Not that this will ever happen the production environment, but I'm still curious about it (famous last words ;) ). |
|
|
|
brianjensen
Starting Member
22 Posts |
Posted - 2009-01-23 : 05:37:29
|
| Ok, I did some further testing on this and it seems that it has nothing to do with mirroring. I created a single database on the drive and offlined it.When I put it back online the database is inaccessible.Trying to use the database causes:Database 'testOfflineDisk' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.This error of course causes my mirror to break since the mirror db cannot add data from its redo log. The principal ends in (Principal, Suspended) mode.Restarting the SQL service the server with the offlined/onlined allows my unmirrored db to become available, but the principal db on the other server stays in (Principal, Suspended) mode. Restarting either SQL server service, does not fix this. UPDATE: I could resume the mirroring session, but had to do it manually.I not sure if I ended up in the suspended mode because I initiated a failover while the disk being inaccessible by the SQL server, but the mirror setup can't seem to automatically recover from this.The mirror db is marked as "restoring" as it should be. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-23 : 11:13:54
|
| Then you are looking for Disk Mirroring Feature (Like RAID 1 does) not Database Mirroring. |
|
|
|
brianjensen
Starting Member
22 Posts |
Posted - 2009-01-26 : 02:57:01
|
| sodeep: I believe the underlying SAN is running RAID 5, but my problem is not that the disk disappears because failover to mirror works flawlessly. My problem is that the SQL Server does not seem to be able to see when the disk containing its files comes back online without restarting the SQL server.A simple instance restart makes it connect right back and allows me to reestablish mirroring. |
|
|
|
|
|
|
|
|
(Minor) Problem in Mirroring in SQL 2008