We had a raid failure yesterday on a server that was hosting databases and IIS.

We're trying to determine the best way forward in terms of our IIS and MSSQL hosting environment.

We'd like redundancy on the IIS side and the SQL side.

For some of our applications we're using pfSense to load balance two web servers (sticky sessions don't work well so we've gone to session state in the database). These two web servers hit a single MSSQL instance running SQL Express (free, our databases are very small, the largest is 1GB).

I feel reasonably confident in our IIS load balancing as when we run patches, it "just works" and I don't really have to do much.

However, our SQL Server infrastructure has me worried.

What's the best way to build a fault tolerant sql stack?

Edit: now that I think about it, a cluster would not necessarily have solved our issue. We had two drives fail in a RAID5. One 100% failed, the other not yet dead, but marked for failure with bad sectors. We had one database that was in the bad clusters that we had to restore from a backup. A cluster would have either broken or put the bad data throughout the cluster. Argh!

  • 1
    Are these databases business critical? As in, if they go down, your business starts losing money? And if so, what kind of applications are these databases running? – smassey Mar 11 '12 at 4:10
  • Critical to me? No :) Critical to my clients? Yes in as much as their websites go down (only one is ecommerce, but very low volume). We have a project that will be deployed in the next month that will be critical and we'll be on the hook $$ wise if it goes down. – Sean Mar 11 '12 at 20:56
  • Will you be building a new infrastructure for this new project, or are you going to try and shoehorn it into your existing infrastructure? Is that part of the reason for your question? (Just trying to get an understanding of what you're asking so I can give as thorough of an answer as I need to...I saw below that the application effected by this outage is basically ELO'd). – smassey Mar 12 '12 at 2:47
  • A mix of new and existing. The project that's due to be released has me worried. – Sean Mar 12 '12 at 11:47

Although this isn't the focus of the question, I do want to point out that you're incurring a performance penalty by using session state in the DB. There are some nasty issues (with some workarounds) known to occur with that session storage model. It's worth the time to get sticky sessions working right so you can use InProc storage - even if it means somewhat asymmetric load on the web front-ends.

I was going to delve into merge replication, partitioning, peer-to-peer transactional replication, and other techniques for scaling/FT/HA with SQL Server, but those seem like overkill for your environment - and require non-Express SQL Server. SQL Server failover clustering with MS Cluster Services, limited as it is, is designed for fault-tolerance. However, the usual single-site configuration relies on shared storage, which doesn't help you at all.

I'm going to recommend that you focus on more quality storage, by using a SAN (or even a NAS, depending on your load and budget) with RAID 10 (or 6, if you can't afford it) with hot spares, frequent backups, and preferably on a device that actively scans for integrity issues - not just a cheap bunch of disks. (Although, you could do worse than OpenFiler on decent hardware.)

If you're limited to RAID 5 on HDDs in the servers (no shared storage), do look into a "multi-site" failover cluster using replication, although you may find the price to implement (SQL Server Enterprise Edition is a minimum requirement) very prohibitive.

| improve this answer | |
  • Sql server gets very expensive very quickly as you mentioned. We have a nas for backups but it simply doesn't have the performance required for running databases. I'm tempted to go to a shared storage solution (exactly as you mentioned with openfiler in a raid 1+0). Thanks for the answer. – Sean Mar 11 '12 at 3:38
  • Also we support some legacy apps that don't support sql server sessions at all. Those hit one of the web servers directly (inproc session but no auto host failover). – Sean Mar 11 '12 at 4:02
  • 1
    That's probably due to poor practices using sessions, like non-serializable data. Even using a StateServer (middle ground between InProc and SQL) wouldn't help with that. So, more ammunition to get pfSense's NLB to cooperate. – David Mar 11 '12 at 4:28
  • Well you know how legacy apps go... you support them as much as possible but you don't touch anything that's "not broke." We're actively trying to migrate people off the legacy apps, but none of them want to pay for the migration... I'm tempted to just run them one appserver with a db server and move on with my life. Tell them that they're using an EOL product and they need to either move on or upgrade (these are inherited clients, so what I want to do doesn't really matter, my partners dictate on their old customers). – Sean Mar 11 '12 at 20:58
  • You didn't mention this was all legacy. :) In that case, throw the whole mess on ESXi (free, if you like) and keep it backed up well. You could also go HA for a lot less than buying SQL Server. Let the cost of a contractual/SLA violation dictate how much you spend on reliability. – David Mar 11 '12 at 21:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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