1

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.

3 Answers 3

1

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.

0

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.

0

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...

1
  • 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, 2009 at 15:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .