Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am reasonably good at dealing with SQL Server clusters; I am wondering if folks have experience, good or bad, using a mix of different models of servers from the same vendor in one SQL 2005 cluster.

Suppose:

  1. I have one more powerful, more RAM, more shizzle box and one less powerful, less memory, less shizzle box bound together in a 2-node cluster. These would be HP DL380 and 580 machines (not that it should matter)

  2. I understand AND automate the process of managing memory for each SQL instance, so there's no memory contention when SQL instances fail over. Basically I am thinking a CLR proc will monitor the instances and self-regulate memory caps on each instance, so that they won't page or step on one another.

  3. I get the fact the instances might be slower and or under memory pressure if they share a "lesser" node, and that's OK. The business can deal with a slower instance in a server-problem scenario.

Reasonable? Any "gotchas" to watch out for?

More info 10/28: doing some experiments with a test cluster I find that reconfiguring max/min memory is OK PROVIDED the instance isn't already under memory pressure. If I torture the system with a huge query that demands a big chunk of RAM, and simultaneously adjust the memory allocation to a smaller value than what is being actively used, it's possible to run the instance out of memory and have it halt and restart itself (unhappy situation). Many ugly out-of-memory messages in the error log, crashing, burning... It's an extreme case, but good to know.

Seems, then, that it would only be really safe to set this on startup of the instance, as in have a startup script that says "I am on node1, so my RAM settings are X or I am on node two, so they are Y," like this:

http://sqlblog.com/blogs/aaron_bertrand...

Update: I am testing a SQL Agent + PowerShell solution described in more detail here.

share|improve this question
add comment

3 Answers

Well according to Server Cluster Best Practices...

Server Error in '/WINDOWSSERVER' Application.
--------------------------------------------------------------------------------
Runtime Error 
Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine. 

Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off".

<!-- Web.Config Configuration File -->
<configuration>
    <system.web>
        <customErrors mode="Off"/>
    </system.web>
</configuration>

Notes: The current error page you are seeing can be replaced by a custom error page by modifying the "defaultRedirect" attribute of the application's <customErrors> configuration tag to point to a custom error page URL.

LOL, but seriously... even though I have always used matching hardware, I don't see any issue so long as SQL is still responsive/usable on the slower node.

share|improve this answer
add comment

The only issue would be that for a Windows 2003 cluster to be supported is has to be one the HCL. Windows 2008 doesn't have this issue as the only requirement is that it passes validation. However for Windows 2003 they are pickier.

share|improve this answer
add comment

We did this with the machines you mentioned. We just capped the memory to the amount the smaller machine had.

Basically I am thinking a CLR proc will monitor the instances and self-regulate memory caps on each instance, so that they won't page or step on one another.

What do you meant by this? I've seen recommendations from microsoft that you do not use mismatched memory amounts as it could cause major problems on the failover. This doesn't seem to be something you could adjust on the fly for a few reasons

  1. The primary node fails...boom, your on node 2, no time to adjust
  2. Lowering memory max requires an agent restart.

Maybe I'm misunderstanding...

share|improve this answer
    
My thinking is to automate this process: 1. "Ask" which node each instance is running on by select serverproperty( 'ComputerNamePhysicalNetbios' ) 2. Compute appropriate memory setting for the current instance from comparing those results 3. Do this, inserting the right memory value: EXEC sys.sp_configure N'min server memory (MB)', N'1024' GO EXEC sys.sp_configure N'max server memory (MB)', N'1024' GO RECONFIGURE WITH OVERRIDE GO –  user24161 Oct 28 '09 at 15:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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