Dynamically chnage SQL server instance configuration when failover occurs

I have a 2 node MS 20008, SQL 2008R2   fail over cluster.
We often have to pull servers down for maintenance when a one node will end up running 2 instances. Each box have 32GB of memory and each instance is configured to have 14GB.

That is fine but what is the point when we have 32GB of memory and using only 14GB. I have observed very good performance gain when I allocate memory of 28GB  on each node. When fail over occurs things go wrong as single server can't provide 56GB when it has only 32GB.

I want to configure each instance with 28GB of memory.
I am looking for a config/script which will dynamically alter the memory of instance when failover occurs.

NO fail over at server1/2
- SQLINST1 = 28 GB
- SQLINST2 = 28 GB

SQLINST2 fails over to Server1
- SQLINST1 = 20 GB
- SQLINST2 = 10 GB

SQLINST1 fails over to Server2
- SQLINST1 = 10 GB
- SQLINST2 = 20 GB

Who is Participating?
Ryan McCauleyData and Analytics ManagerCommented:
Here's a post where the author does something similar:


In this case, he uses a command to fetch the name of the physical server the instance is running on, based on which you can do other configurations, like set the max server memory or do any other configuration you'd like to be node-specific.
AnujSQL Server DBACommented:
This can be done using custom stat-up stored procedure, that automatically configures the Max Server memory settings on each node based on the hostname in the the event of failure, this link will help you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.