Cluster SQL 2005

Question

Post reply

Cluster SQL 2005

igor RENQUIN

Valued Member

Points: 69
More actions
September 13, 2007 at 1:34 am

#177468

Hello,
Does anyone inplement a SQl cluster server?
What is the technology based you will recommend to use? Windows cluster or SQL 2005 failover clustering?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sopheap Suy SSCertifiable Points: 7893 More actions · Answer 1

It would be a windows cluster and SQL Server 2005 sit on top of that cluster.

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 2

Microsoft has MSDN articles on how to do this.

http://msdn2.microsoft.com/en-us/library/ms179530.aspx

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

igor, the answer to your question is "it depends". How much money do you have to spend on the project? What is your allowable downtime? Do you care if you lose some transactions? Do you have knowledgeable IT staff that can support a complex system properly? There are more questions but I will stop with those.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

CinfulGentleman SSC Rookie Points: 26 More actions · Answer 4

To follow-up on what "SQLGuru" was saying there are a lot of variables to Clustering. You have to consider how your servers will both attach to the same media (SAN or SCSI-Attached), the licensing of both your Windows and SQL servers, and what type of clustering do you want to use (Active-Active or Active-Passive).

If you wanted to post on why you are considering it and what your goals are we might be able to get into specifics.

igor RENQUIN Valued Member Points: 69 More actions · Answer 5

Hello,

Thanks to everyone to spend your time on this forum.

I’m running 2 SQL servers under SQL 2000 they are both 4 CPU (2.8Ghz with HT) with 10 Gb of RAM with AWE activate and a third one dual CPU (3.4 Ghz with HT) and 5 Gb of RAM.They all have this HDD configuration:

RAID 1 for the logs and RAID 10 for the DATA of the DB and running on windows 2003 server 32bits.

Both server are charged at 60% during the all day and space on the HDD volume getting smaller and smaller.

This hardware slowly getting old and the support will expire next year.

So far they are very stables server with a reboot every month caused of the patch party organize by Microsoft every month.

On the side of my SQL knowledge I would like to precise that I also managing a Exchange cluster and recently implement an active passive windows cluster and set up the hardware part bladecenter / SAN. So without being an expert on SAN technology I know how to make it work.

The money won’t really be a big matter because of the renewal of the hardware I have to plan.

I really would like to improve is the reliability of my system. That is why I’m thinking about a SQL cluster based on a active/passive Windows Cluster with 2 or 3 nodes.

With the experience running an Exchange cluster I notice that before moving a node Exchange stopping all Exchange’s services and close the connections. I believe it’s the same thing with SQL cluster.

I wonder if this cannot be the cause of DB corruption?

I wonder if the fact to be connected to a SAN instead to have an internal RAID array won’t be an problem in term of HDD access time and in the end a performance issue.

Does anyone make any comparison on this matter?

About the server Hardware I’m thinking about going for blades server. With the new CPU dual or quad core I could go for servers with less physical CPU (dual CPU)and maybe less DB on each. Those has the advantage for me because I’ll buy the same number of SQL licenses.

The first interesting step for me will be to move to a 64 bits environment. I believe that can help to improve the performances of my DB!!!???

Then moving to a cluster environment can be an interesting way to make my system more available to my web application and my users!!!???

Let me know what do you think about it. I’ m really interested to know what do you think and what you are doing

Thanks,

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

I've had some exposure to clustered installs, but I hardly qualify as an "expert" in them. that being said, a few things I've learned:

Active/passive scenarios are fine for improving uptime, but they don't do anything per se to improve your performance while up. If you want to improve performance, and your data allows you to efficiently split databases to separate instances (i.e. not a huge amount of data-sharing across databases), you should look at active/active, or Active/active/passive, or more generically, "n+m" cluster configs (where n=active nodes, m=backup nodes).
Size your boxes appropriately. If you split a single instance of SQL server into 2 instances, each node needs to have the ability (i.e. hardware/horsepower) to take on BOTH instances, and not die under the activity level.
There are specific documents from MS on the best ways to configure for clusters. SAN disks are really not all that different than local disks. Assuming you have both configured right, there shouldn't be a lot of data latency, which is what would be likely to cause corruption. Your SAN vendor should also have some ideas about the best config for SQL servers: this is what these things were built for, so I'm sure that just about any SAN vendor out there tested with SQL extensively, and can give you some pretty good ideas what works best.
SAN disks can be faster than local disks IF CONFIGURED correctly. the huge buffering space in the DAE's can allow for some pretty wild performance, if the disk sets are set up right to keep up with what's going in.
Keep your SQL data as far away as you can from the Exchange traffic. If your SAN is based on arbitrated loop architecture (in networking - this used to be called Token Ring) - try to dedicate one of the loops to Exchange, the other to SQL server. Exchange creates a LOT of disk usage, so do what you can to isolate your SQL server from that.
Leverage your physical stripe sizes in your growth factors. Make your databases even increments of the stripe, and your growth factors should be in line as well.
If you can at all afford it, create a separate RAID set for log files, and one or more separate RAID sets for databases. I don't care how efficient a stripe set it - creating a large stripe set, and carving it into multiple "volumes" which are then used as log and data drive is just a sneaky way to make your hardware work AGAINST you, not for you. Your log and data drives should share as little hardware as possible (different disks isn't too bad; different SCSI channels is better; different enclosures/controllers, even better).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

I forgot one:

you REALLY need to think through the failover behavior at the windows cluster level. The default behavior (fail immediately to the other node, and if the service doesn't come up within 10 minutes, fail over again) can put your SQL server in a never-ending loop of "failing over" if an instance fails in the middle of a long-running transaction (like - 30 minutes in to a 45+minute transaction). We actually ending up coding for a WMI hook to check for that particular error (the "the cluster service failed to start within the established time" error), and to PAGE someone as soon as that error happened. We had a server "ping-pong" back and forth most of a weekend once due to that.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jennifer Utsler Valued Member Points: 59 More actions · Answer 8

I have just recently completed the setup of a Windows 2003 cluster with 8 instances of SQL Server 2005 Enterprise, SP2 with hotfix 3050, 64-bit version, using mount points to a SAN running as an active-passive cluster. Although this is not heavily used yet, it so far, seems be be pretty stable. Future plans are to make this active-active-passive, primarly due to Microsoft patching.

I also have 6 instances of SQL 2000 running on a Windows 2003 cluster that has been in production for quite some time. Our purpose for using a cluster was to consolidate many small, single SQL servers (about 70) into 1 environment (up to 3 different SQL collations). This is an Enterprise, 32-bit installation using 2 nodes as active-active. It has been very stable and when hardware problems do occur, it has reduced the downtime considerably. I am planning to add a third node as a passive node, once again primarly due to Microsoft patching.

I too, initially had issues with the SQL 2000 cluster getting in a failover loop and the way I solved this was to set the cluster service to failover once and then not to failback (this allows me to schedule the outage). Once the initial failover occurs, I am paged and can take whatever action is necessary based on the failure. This has worked very well for me so far.