Dynamically chnage SQL server instance configuration when failover occurs
Posted on 2012-08-23
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