Dynamically chnage SQL server instance configuration when failover occurs

Posted on 2012-08-23
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

    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

    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

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

         When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
    After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now