SQL 2005 Failover

[Jay]

Is there any way to have a single storage location with more than 1 server accessing the same data?

I am looking to do fail over for our SQL databases with 2 servers and 1 SAN but I’m not sure it’s actually possible.

I was looking to have the servers for grunt and the SAN for storage and decent IOPS, if one server fails (or needs taking offline for maintenance) I would like failover to happen seamlessly.

I am currently setting up a VM network to test this out but has anyone here actually ever done this?



I know I used the mail server Visio icon by accident!

[oolon]

I have run one, however we did get an expert to install it. SAN is the classic way to have the same storage in two places and for windows probably the only option, If you can live with windows VMs, you could look at network based block device replication ( DRDB). A two node cluster however always has problems, how does the other know the node has died rather than just the network link gone down (Split brain). You should also use a crossover on a second nic for the heart beat that way the switch can be reboot etc, the services can then also monitor the link status to see if the other server might be active. SQL server does an Active/Standby setup by default (Cheapest option) rather than a cluster. With a san you really should dual path /controller the connection else you have the same point of failer. Sans are configured by a process called lun masking so only some of the luns are seen by specific cards (otherwise everyone gets everyones storage). HP do a "My first cluster" which in my time had an MSA and 2 380g4s in it. The MSA is good as it comes with a switch, however bad if you want to link the storage to a bigger switch, as FC is not the same as ethernet all switches in a fabric become one big one so need to be compatible.

[Jay]

how are the log file shipped though? Do I still need replication between the SQL servers?

[oolon]

The log file is on the san, the server locks the lun on the san that gives one of the servers exclusive access to the filesystem, other servers it "greys out" as unavailable. Its rather cool to watch (and flip the power switch on the server). The Db has a Cluster IP address which is used for the shared service, if you can get your switch to write to both nics in a mirrored/team fashion that will help failover as it will take a while for the switch to learn the IP has moved to another port/mac.

[Moby-Dick]

Jay - of course you can , its called a SQL cluster

[Moby-Dick]

I have run one, however we did get an expert to install it. SAN is the classic way to have the same storage in two places and for windows probably the only option, If you can live with windows VMs, you could look at network based block device replication ( DRDB). A two node cluster however always has problems, how does the other know the node has died rather than just the network link gone down (Split brain). You should also use a crossover on a second nic for the heart beat that way the switch can be reboot etc, the services can then also monitor the link status to see if the other server might be active. SQL server does an Active/Standby setup by default (Cheapest option) rather than a cluster. With a san you really should dual path /controller the connection else you have the same point of failer. Sans are configured by a process called lun masking so only some of the luns are seen by specific cards (otherwise everyone gets everyones storage). HP do a "My first cluster" which in my time had an MSA and 2 380g4s in it. The MSA is good as it comes with a switch, however bad if you want to link the storage to a bigger switch, as FC is not the same as ethernet all switches in a fabric become one big one so need to be compatible.

In addition to lun masking you would also zone on the fabric switches.

[oolon]

In addition to lun masking you would also zone on the fabric switches.

Indeed, it would mean that the HBA could only see the controllers of the san rather than other HBA. That is always rather neat I feel, I wish I could do that on an ethernet switch it would make multiple servers in a DMZ much easier to manage.

[Moby-Dick]

Single initiator zoning can be pretty handy when you have multiple Storage devices and servers on a fabric - also means you are less likly to get path thrashing

OS Wise you'll need windows ent but you can use SQL standard to create a failover cluster.
nb. failover is not seamless the server has to stop ( or be detected as down ) , then the shared disk ownership , along with the IP and DNs name resources passed to the other node and SQL services restarted. You are looking at around 60 seconds service outtage on average to do a failover.

note that your front end servers fort eh application should be tolerant of a lack of database for a bit ( ie not drop all the sessions and bleat to be restarted before they will talk to the db again )

[oolon]

I cannot remember the complete mechanics of it, however I don't think it continues mid transaction and i cannot remember if sessions are preserved or need to be reconnected. I did the sys admin side rather than the software.

[Jay]

Single initiator zoning can be pretty handy when you have multiple Storage devices and servers on a fabric - also means you are less likly to get path thrashing

OS Wise you'll need windows ent but you can use SQL standard to create a failover cluster.
nb. failover is not seamless the server has to stop ( or be detected as down ) , then the shared disk ownership , along with the IP and DNs name resources passed to the other node and SQL services restarted. You are looking at around 60 seconds service outtage on average to do a failover.

note that your front end servers fort eh application should be tolerant of a lack of database for a bit ( ie not drop all the sessions and bleat to be restarted before they will talk to the db again )

60 seconds is better than what we have, its probably a few days if we lose the server (maybe even more)

[Moby-Dick]

If you wanted to use twice the storage , you could use mirroring with syncronous failover ( assuming you where on the same site ) - if your front end application ins SQL mirroring aware then you would have almost instant failover , but it will consume 2x storage.

[Jay]

Dell are sending a loan SAN out to me next week for me to have a play with, may even be a VX S. Pictures to come!

[pipTheGeek]

There appears to be several things being talked about in this thread. What you are asking about is failover clustering. Others that are talking about logs are talking about log shipping which is different. I've tried to explain a few of the pro's and con's below as well as some of my own experiance.

I hope my answer helps rather than confuse you further.

Database failover time depends on the time it takes the starting sql instance to perform recovery. It can be more than 60 seconds for a server that has many transactions to roll back.
On SQL 2005 you can't do rolling SQL upgrades on the cluster, so a SQL patch or service pack requires the instance to be offline while it is applied. I think this is no longer the case with SQL 2008 R2.

Database mirroring allows the switch to the mirrored server to be handled by the Sql driver on the client but only when the application is opening a connection. A switch will still cause all existing connections to be aborted.

Mirroring comes in two flavours, high performance and high security. High security means that a suddern failure of the primary won't lose any data (from committed transactions). However all transactions are committed using a two phase commit which can cause performance degradation.
High performance is more like log shipping, where a suddern failure of the primary may cause some committed transactions to be lost. Both high performance mirroring and log shipping do allow for a controlled switch the backup server with no data loss.
If you want mirroring to perform an automatic failover then you need to use the high availability varient of high security which requires a third server. (The third server can run SQL express and be very low end hardware)

Mirroring and log shipping do require two complete seperate servers so you need extra storage, however it doesn't need to be shared storage so it will probably be cheaper / simpler. (I'm a programmer not a sys admin, I'm not sure about that)

Mirroring and log shipping also allow the failover server to be distant. Failover clustering generally the two servers need to be near each other.

We use a combination of both at work. Non-important databases run on a single server. More important databases run on an active/active cluster. The really important ones are then mirrored from the cluster to a single server at a remote site.
We are about to do some uprades and intend to replace several of the existing single and clustered servers with a single 4 node cluster.

[Jay]

well I have the VMs setup in XEN and have full failover working, its pretty quick to pick up the loss of a server, 4 loss of pings to the cluster IP and the other server has picked up and is on line again with the iSCSI SAN connected and the SQL services started etc. very good.

[oolon]

Very nice work Jay, are all the disks for the cluster comming from the san? If so you can do live migration of the vms to a different machine.

[Jay]

actually the iSCSI SAN is also a virtual machine on XEN as well just to see how it all works.