October 13, 2009 at 9:34 am
Hello
First of all, I'm no expert in SQL. But need to implement a cluster solution running SQL Server 2005 and I have a simple doubt that I need an answer. Been searching about it all morning, but so far, didn't find a reliable conclusion.
It's very simple.
We have a Single Instance scenario with 2 nodes on a cluster in win2008.
We would like the SQL Server service to run on Node1. Analysis, Integration and Report services to run on Node2.
The major point is to distribute load on both machines(since the CPU's are not quite good,sometimes they jump to 100% load when reporting or analysis are running) and still have failover clustering running in case of any failure on one of the nodes.
Is it possible?
October 13, 2009 at 9:46 am
nunucorreia (10/13/2009)
HelloFirst of all, I'm no expert in SQL. But need to implement a cluster solution running SQL Server 2005 and I have a simple doubt that I need an answer. Been searching about it all morning, but so far, didn't find a reliable conclusion.
It's very simple.
We have a Single Instance scenario with 2 nodes on a cluster in win2008.
We would like the SQL Server service to run on Node1. Analysis, Integration and Report services to run on Node2.
The major point is to distribute load on both machines(since the CPU's are not quite good,sometimes they jump to 100% load when reporting or analysis are running) and still have failover clustering running in case of any failure on one of the nodes.
Is it possible?
Is this possible?
Personally without knowing the specification of the hardware, I would say it is ill-advised. if you have cpu spiking at 100% on a regular basis using SSAS or SSRS, i would think that maybe your hardware is not supporting it very well. and to make such hardware part of a cluster solution and High Availabilty?. a part from that how would it work if your primary node died and you had everything running on the secondary node. what would the performance be like.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 13, 2009 at 9:59 am
Problem is, there's no chance to upgrade hardware. It has been running on a single machine for a while (around 1year), yes, with 100% cpu load spikes once in a while.
Since we are moving it to a cluster for safety reasons (the other node will be an exact copy of the current machine), the only thing we can do is separate services as I said(or asked) on the first post. At least to minimize the cpu load on node1.
Still, I wonder if the services separation works, since it's only one instance.
October 13, 2009 at 10:05 am
nunucorreia (10/13/2009)
Problem is, there's no chance to upgrade hardware. It has been running on a single machine for a while (around 1year), yes, with 100% cpu load spikes once in a while.Since we are moving it to a cluster for safety reasons (the other node will be an exact copy of the current machine), the only thing we can do is separate services as I said(or asked) on the first post. At least to minimize the cpu load on node1.
Still, I wonder if the services separation works, since it's only one instance.
is this an active/active or active/passive cluster and what is the spec for the machines. you are aware that reporting services and integration services are not cluster aware?, not sure about analysis services.
So you are accepting the fact if the secondary node goes down, you have lost reporting services and analysis services.
I could be wrong in my comments, feel free anyone to step in, If i am talking rubbish. 😀
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 14, 2009 at 7:56 am
Can you do it? Yes. Should you do it?
You're running into the same situation that lots and lots of people have run into with clustering. They're trying to have it answer two needs, load balancing and failover support. Unfortunately, clustering is built for failover, not load balancing. The active/active cluster has a nickname, fail/fail. If you're running multiple apps that are all putting a reasonable load on the server, averaging 40-60% cpu with spikes, but then you combine two of these apps in a failover situation, now you're running the server 80-120% + spikes... But you can't go past 100%, so the active/active situation generates a lot of pain.
You can do what you're asking, but it's not recommended.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 14, 2009 at 8:24 am
Nicely put, makes a change for someone to agree with me 😀
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply