5

I need to set up a good database clustering solution for a system that I am putting into production. I was hoping to get some advice from the experts as I am a primarily a developer and not a database administrator.

Background
My team and I are replacing a legacy system with a .NET-based system. Currently, we are doing parallel testing and slowly migrating users over to the new system; however, it strikes me that our current database solution is not adequate to handle the load once thigs really start going. Right now I have our entire database on one Dell PowerEdge Server running SQL Server 2008 R2 on it. Its been fine for development and for basic testing but when we decide to pull the plug on the old system and go forth completely with the new system we will need things like failover, load balancing, redundancy, etc. I have never had to implement a cluster before because everywhere I worked this was already implemented, and was not my responsibility to maintain. Since we are implementing the PC-based system from scratch I am looking for a starting point.

Recommendations
What do you recommend for my current situation: We have about 50 internal users that will be doing everything from data entry, claims processing, and the like, and the possibility of about 1500-2000 concurrent connections from outside. Is it better to do a virtual or physical setup? Fibre Channel or SATA?

Improve this question

2 Answers 2

Reset to default
6

Failover is easy to do. That just requires shared storage, and a couple of servers.

SQL Server doesn't support scale out to multiple nodes. However a properly designed database with only a couple of thousand users should be able to run on a dual chip server pretty easily. My companies systems support about 35k transactions per second on a quad chip server (with 6 cores per chip).

Like any database server you'll want to use multiple LUNs for each part of the system. 1 for data, 1 for indexes, 1 for transaction logs, one for tempdb.

If you can't afford a SAN (you'll be looking at a six figure purchase for the SAN alone) then you should look at database mirroring instead. This when used with a witness server can give you automatic failover in the event of a failure, and doesn't require shared storage. The different volumes for storage should still be used.

Before you can design your high availability solution (clustering, mirroring, log shipping, etc) you need to figure out what you are trying to protect against. How much data are you willing to loose? How long can you afford to be down during failover?

Improve this answer
4
  • If you are using a SAN device then splitting data/indexes/whatever is obsolete advice.
    – Gaius
    Commented Feb 17, 2011 at 7:13
  • thanks for your response. We are storing mostly medical data but also doing things like billing, A/R, etc. So, I would suspect we couldnt be down for too long, maybe a few minutes at max. doctors and dentists submitting claims don't want to be shut out of the system and we don't want them to be either! Thanks again.
    – Nodey The Node Guy
    Commented Feb 17, 2011 at 15:20
  • @Gaius, no it isn't. It MIGHT be depending on how your storage array is designed. Even if the array stores all the data on all the disks, breaking the databases up into multiple LUNs give you better MPIO options as you are probably using the native Active/Passive MPIO driver that Windows provides, and it will also give Windows multiple disk queues, so that if one IO path stalls for some reason, IO can still be handled over other IO paths for the other LUNs.
    – mrdenny
    Commented Feb 18, 2011 at 0:26
  • @Steven, in order for an outage that short you'll want either clustering, or mirroring in sync mode (high safety). The fail over time will be a few seconds to a few minutes depending on the amount of data in the transaction log which needs to be rolled forward and back. SQL Enterprise Edition will help with this as it uses are different recovery procedure than the lower editions.
    – mrdenny
    Commented Feb 18, 2011 at 0:32
4

First things first. The essence of a cluster is that there are two or more nodes with access to common storage. Therefore, you will require a SAN device. There are many vendors to choose from, the one I am most familiar with is NetApp but there are plenty around. It is most likely that you will want to connect to this via iSCSI for which you will need an initiator. It's just easier to manage than an Emulex and that's probably overkill for your first SAN anyway.

On this device you will then create two volumes, a quorum disk and a storage disk. I usually mount these as Q: and S:. The quorum disk is used by the cluster; by pinging a block on the disk, the clusterware can know which nodes are alive and well. This can be very small, 100M is plenty. The other is the disk on which your SQL Server data and logfiles will live. Drive S: will only be mounted on the active node in the case of the classic active-passive failover pair along with a VIP to which your users actually connect. Should the second node detect via the quorum disk that the first node is not responding, it will master the SCSI, mount the disk, bind the VIP and restart SQL Server, perform recovery from the logfile, and resume servicing the users.

You can play with all this in a virtualized setup but for dedicated production use, I usually go with robust physical servers (redundant everything). I'd be surprised on modern hardware if with that many users you were compute-bound; you're more likely to run into the limitations of your SAN device first. It's worth treating that as an investment and establishing a relationship with whichever vendor you choose. Make a few calls and pick the brains of their pre-sales engineers for free...

Improve this answer
2
  • Excellent rundown and advice.
    – jcolebrand ♦
    Commented Feb 17, 2011 at 0:28
  • Especially the advise regarding pre-sales engineers! That's the creme of the coffee! But he's so right.. If you'll find idiots at first contact, you're statistically bound to finding similar guys later, when trouble is really burning your pants.
    – Marian
    Commented Feb 17, 2011 at 22:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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