Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Dynamically chnage SQL server instance configuration when failover occurs

Posted on 2012-08-23
Medium Priority
Last Modified: 2012-09-06
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

Question by:crazywolf2010
LVL 15

Assisted Solution

Anuj earned 1000 total points
ID: 38328129
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.
LVL 28

Accepted Solution

Ryan McCauley earned 1000 total points
ID: 38329641
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.

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Screencast - Getting to Know the Pipeline
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question