{"id":51,"date":"2013-11-13T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/managing-multi-instance-cluster-failovers"},"modified":"2026-03-18T21:50:40","modified_gmt":"2026-03-18T21:50:40","slug":"managing-multi-instance-cluster-failovers","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2013\/11\/13\/managing-multi-instance-cluster-failovers\/","title":{"rendered":"Managing Multi-Instance Cluster Failovers"},"content":{"rendered":"<p>A couple of weeks ago I was reviewing my daily news feeds and a featured post on <a href=\"http:\/\/www.sqlservercentral.com\/\">SQLServerCentral.com<\/a> caught my eye. <a href=\"http:\/\/www.sqlservercentral.com\/Authors\/Articles\/Michael_Lacy\/92931\/\">Michael Lacy<\/a>\u2018s post on <em><a href=\"http:\/\/www.sqlservercentral.com\/articles\/SQL+Server+Cluster+Management\/103090\/\">Managing MaxMemory Values on an Active-Active Two Node Cluster<\/a>\u00a0<\/em>provides a solution to the problem of dynamically managing maximum memory settings for SQL Server Fail-over Cluster instances during a node failure. A lot of us make sure that at least one SQL Server instance is on each node of our clusters so that we aren\u2019t wasting resources by leaving them in a passive state.<\/p>\n<p>While Lacy\u2019s solution will work, I like to be thorough and always ask myself, \u201cis this the <strong>best<\/strong> solution?\u201d So I performed additional research and a couple of my fellow tweeters drew my attention to <a href=\"http:\/\/sqlblog.com\/blogs\/aaron_bertrand\/default.aspx\">Aaron Bertrand<\/a>\u2018s post on <em><a href=\"http:\/\/sqlblog.com\/blogs\/aaron_bertrand\/archive\/2009\/09\/18\/managing-active-active-cluster-failovers-with-different-hardware.aspx\">Managing multi-instance cluster failovers with different hardware<\/a>.\u00a0<\/em>Bertrand\u2019s solution used a different method which I liked because it was rounding out my understanding of the options.<\/p>\n<p>Given the two different methods, I set out to evaluate which I\u2019d like to implement. I ended up creating a third option which includes elements of each. Below is an explanation of their processes and what I did and did not like about them and then a description of my process.<\/p>\n<h2>Michael Lacy\u2019s Flavor<\/h2>\n<p><strong>Reference<\/strong>: <a href=\"http:\/\/www.sqlservercentral.com\/articles\/SQL+Server+Cluster+Management\/103090\/\">Managing MaxMemory Values on an Active-Active Two Node Cluster<\/a><\/p>\n<p>Lacy\u2019s solution uses T-SQL SQL Agent jobs and linked servers to process the <a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/ms188787.aspx\">sp_configure<\/a> commands. In addition, he uses the presence of placeholder files and <a href=\"http:\/\/sql.richarddouglas.co.uk\/archive\/2009\/10\/finding-files-part-1-t-sql.html#axzz2kIgZSuPw\">xp_fileexists<\/a> on the clustered disks to evaluate where all of the instances live at any given moment.<\/p>\n<p>I like this solution but there are elements of it which don\u2019t match my preferences. For example, I prefer not to use linked server wherever I can avoid it. While I maintain that linked servers can be used appropriately; all too often poor code is developed for them and cause major performance issues on my servers. In addition, there is the lack of support for domain security groups and the complications with domain account delegation and difference in authentication abilities between NTLM and Kerberos.<\/p>\n<p>I also felt that the use of placeholder files to identify which instances reside on the local host was a bit less direct than I like. We would be relying upon a logical consequence of their location rather than directly identifying it. I also preferred to not have to maintain hard-coded file paths and naming conventions, any server migrations would cause either code change or code to break if drive letters and exact paths weren\u2019t maintained.<\/p>\n<h2>Aaron Bertrand\u2019s Flavor<\/h2>\n<p><strong>Reference:\u00a0<\/strong><a href=\"http:\/\/sqlblog.com\/blogs\/aaron_bertrand\/archive\/2009\/09\/18\/managing-active-active-cluster-failovers-with-different-hardware.aspx\">Managing multi-instance cluster failovers with different hardware<\/a><\/p>\n<p>Bertrand\u2019s solution appealed to me because of the use of a start up stored procedure rather than a SQL Agent job and because he queried\u00a0SERVERPROPERTY(\u2018ComputerNamePhysicalNetBIOS\u2019) instead of using the placeholder files.<\/p>\n<p>However, he stilled used linked servers which I could get over if it wasn\u2019t for the fact that some of the clusters I manage have 5+ nodes with 5-10 SQL instances on them. I really wanted to avoid creating up to 10 linked servers on each of the 10 instances to accomplish this goal. I also didn\u2019t want their existence to encourage other developers to use them and thus cause query tuning opportunities.<\/p>\n<h2>Powershell via SQL Agent Flavor<\/h2>\n<p><strong>Download PoSh Script: <\/strong><a href=\"http:\/\/www.sqlhammer.com\/blog\/wp-content\/uploads\/2013\/11\/cluster_sql_svr_mem_mgmt1.txt\">cluster_sql_svr_mem_mgmt.txt<\/a><\/p>\n<p><strong>Download T-SQL Job Script:<a href=\"http:\/\/www.sqlhammer.com\/blog\/wp-content\/uploads\/2013\/11\/job-cluster-memory-management.txt\">\u00a0<\/a><\/strong><a href=\"http:\/\/www.sqlhammer.com\/blog\/wp-content\/uploads\/2013\/11\/job-cluster-memory-management.txt\">job-cluster-memory-management.txt<\/a><\/p>\n<p><strong>Tested on: <\/strong>Microsoft SQL Server 2012 in a Windows 2008 R2 cluster<\/p>\n<p><strong>References:\u00a0<\/strong><\/p>\n<ul>\n<li><a href=\"http:\/\/serverfault.com\/questions\/28520\/which-permissions-rights-does-a-user-need-to-have-wmi-access-on-remote-machines\">Local server permissions required for the Get-WmiObject cmdlet<\/a><\/li>\n<\/ul>\n<ul>\n<li><a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/ms188787.aspx\">Access required to set the MaxServerMemory configuration<\/a><\/li>\n<\/ul>\n<ul>\n<li>How to setup <a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/ms190703.aspx\">credentials<\/a>\u00a0and <a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/ms189064(v=sql.105\">proxies<\/a>.aspx)<\/li>\n<\/ul>\n<p>In my Powershell flavor of clustered memory management we use a Powershell SQL Agent job to execute the script on start-up. The script is designed to be dynamic and to snap into any Windows cluster that you might have without having to configure any instance or host specific information. Once a SQL Agent proxy is created with Powershell access and server level permissions are applied all you need to do it let the script execute. So, let\u2019s get right down to the nuts and bolts.<\/p>\n<h3>Stand-a-lone Powershell script<\/h3>\n<p>One of the key features of this script is the function\u00a0<em>Get-SQLInstances <\/em>which<em> <\/em>is a slightly modified version of <a href=\"http:\/\/learn-powershell.net\/\">Boe Prox<\/a>\u2018s <em>Get-SQLInstance<\/em>, see it <a href=\"http:\/\/learn-powershell.net\/2013\/09\/15\/find-all-sql-instances-using-powershell-plus-a-little-more\/\">here at learn-powershell.net<\/a>. This function queries the registry to identify a list of SQL Server instances on the local host and then returns a String array.<\/p>\n<pre><code class=\"language-\">function Get-SQLInstances\n{\n    $Computer = <a href=\"\/hostname\">string<\/a> -replace '(.<em>?)\\..+','$1'\n\n<p>$Instances = $null\n    [string[]]$InstanceList = $null<\/p>\n\n<p>Write-Verbose (\"Checking {0}\" -f $Computer)\n    Try\n    {\n        $reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $Computer)\n        $regKey= $reg.OpenSubKey(\"SOFTWARE\\\\Microsoft\\\\Microsoft SQL Server\")\n        If ($regKey.GetSubKeyNames() -contains \"Instance Names\")\n        {\n            $regKey= $reg.OpenSubKey(\"SOFTWARE\\\\Microsoft\\\\Microsoft SQL Server\\\\Instance Names\\\\SQL\" )\n            $instances = @($regkey.GetValueNames())\n        }\n        ElseIf ($regKey.GetValueNames() -contains 'InstalledInstances')\n        {\n            $isCluster = $False\n            $instances = $regKey.GetValue('InstalledInstances')\n        }\n        Else\n        {\n            Continue\n        }<\/p>\n\n<p>If ($instances.count -gt 0)\n        {\n            ForEach ($instance in $instances)\n            {\n                $nodes = New-Object System.Collections.Arraylist\n                $clusterName = $Null\n                $isCluster = $False\n                $instanceValue = $regKey.GetValue($instance)\n                $instanceReg = $reg.OpenSubKey(\"SOFTWARE\\\\Microsoft\\\\Microsoft SQL Server\\\\$instanceValue\")\n                If ($instanceReg.GetSubKeyNames() -contains \"Cluster\")\n                {\n                    $isCluster = $True\n                    $instanceRegCluster = $instanceReg.OpenSubKey('Cluster')\n                    $clusterName = $instanceRegCluster.GetValue('ClusterName')\n                }<\/p>\n\n<p>$InstanceObject = New-Object PSObject -Property @{\n                                    Computername = $Computer\n                                    SQLInstance = $instance\n                                    isCluster = $isCluster\n                                    ClusterName = $clusterName\n                                    FullName = {\n                                        If ($Instance -eq 'MSSQLSERVER')\n                                        {\n                                            $clusterName\n                                        }\n                                        ElseIf ($Instance -ne 'SQLEXPRESS')\n                                        {\n                                            \"$clusterName\\$instance\"\n                                        }\n                                        Else { Continue }\n                                    }.InvokeReturnAsIs()}<\/p>\n\n<p>$InstanceList = $InstanceList + $InstanceObject.FullName;\n            }\n        }\n    }\n    Catch\n    {\n        Write-Warning (\"{0}: {1}\" -f $Computer,$_.Exception.Message)\n    }<\/p>\n\n<p>return $InstanceList;\n}<\/code><\/pre>\n<\/p>\n<p>Next we\u2019ll grab the local host name and use <a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/ee176860.aspx\"><\/em>Get-WmiObject<em><\/a> to return the total physical memory of the server. This is the cmdlet that requires local permissions to Windows. See <\/em><a href=\"http:\/\/serverfault.com\/questions\/28520\/which-permissions-rights-does-a-user-need-to-have-wmi-access-on-remote-machines\">local server permissions required for the Get-WmiObject cmdlet<\/a><em> for more details on how to grant granular permissions.<\/p>\n<pre><code class=\"language-\">#Get local machine\n[string]$Computer = <a href=\"\/hostname\">string<\/a>\n\n<p>#Get total physical memory\n[int64]$total_memory_MB = (Get-WmiObject CIM_ComputerSystem).TotalPhysicalMemory \/ 1024 \/ 1024;<\/code><\/pre>\n<\/p>\n<p>The next section sets up a couple variables and pulls in the results from Get-SQLInstances.<\/p>\n<pre><code class=\"language-\">#Populate table of instances and their clustered status\n$InstanceNames = $null\n$InstanceNames = Get-SQLInstances\n$LocalInstances = $null\n$LocalInstances = @()<\/code><\/pre>\n<p>Now that we\u2019ve gathered a list of the instances installed on the local host to include clustered instances, next we need to identify which of these instances are currently online on this host. We will do this by creating a SMO server object and comparing the $Computer variable (our local host name) with the ComputerNamePhysicalNETBIOS property of the SQL Server object. We\u2019ll populate a string array with a list of the instances which exist only on this server.<\/p>\n<pre><code class=\"language-\">#Populate current host and memory values\n[System.Reflection.Assembly]::LoadWithPartialName(\u2018Microsoft.SqlServer.SMO\u2019) | out-null\nforeach ($InstanceName in $InstanceNames)\n{\n $CurrentServer = $null\n $CurrentServer = New-Object ('Microsoft.SqlServer.Management.SMO.Server') \"$InstanceName\"\n\n<p>#Check for local host only\n if ([string]::Compare($Computer,<a href=\"\/$CurrentServer.ComputerNamePhysicalNETBIOS\">string<\/a>, $true) -eq 0)\n {\n $LocalInstances += $InstanceName;\n }\n}<\/code><\/pre>\n<\/p>\n<p>Now that we know how many instances are online at this time, we will calculate the amount of memory that we\u2019d like to allocate to each. A straight division equation won\u2019t be enough because we\u2019ll need some memory allocated for the operating system and other applications to use and I decided not to go with a percentage approach because something like 10% of a server with 2 GBs of memory only leaves 200 MBs which I find to be insufficient. On that same train of though, 10% of a server with 256 GBs of memory would be 25 Gbs which is likely more than necessary. So, I took a bracketed approach where I\u2019ve selected memory allocations based on static values for each bracket.<\/p>\n<pre><code class=\"language-\">#Calculate memory values\n[int64]$memory_per_instance_MB = $null\n[int64]$OS_memory_buffer_MB = $null\n\n<p>#Calculate OS memory buffer\nswitch -regex ($total_memory_MB)\n{\n \"[1-2]\\d{3}\" {$OS_memory_buffer_MB = 512;}\n \"[3-9]\\d{3}\" {$OS_memory_buffer_MB = 1024;}\n \"[1]\\d{4}\" {$OS_memory_buffer_MB = 2048;}\n \"[2]\\d{4}\" {$OS_memory_buffer_MB = 3072;}\n \"[3-4]\\d{4}\" {$OS_memory_buffer_MB = 4096;}\n \"[5-9]\\d{4}\" {$OS_memory_buffer_MB = 6144;}\n \"\\d{6}\" {$OS_memory_buffer_MB = 8192;}\n}<\/p>\n\n<p>#Evenly distribute memory across all instances after taking into account the OS\n$memory_per_instance_MB = ($total_memory_MB - $OS_memory_buffer_MB) \/ $LocalInstances.length;<\/code><\/pre>\n<\/p>\n<p>Finally, we\u2019ll check the current MaxServerMemory property of each instance and push the new memory value if it is different than the existing. This part of the script requires that the account of execution be able to perform the equivalent of the T-SQL sp_configure command. See\u00a0<\/em><a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/ms188787.aspx\">access required to set the MaxServerMemory configuration<\/a>*\u00a0for permissions details.\u00a0In the below snippet; the Write-Host commands are commented out because they cannot be executed in the SQL Agent job step. If you run this script via the Powershell console I recommend removing the comments so that you will gain some visibility into the changes that it is inducing.<\/p>\n<pre><code class=\"language-\">#Alter memory values\nforeach ($LocalInstance in $LocalInstances)\n{\n $CurrentServer = $null\n $CurrentServer = New-Object ('Microsoft.SqlServer.Management.SMO.Server') \"$LocalInstance\"\n\n<p>#Don't induce change if the net result is the same\n if ($CurrentServer.Configuration.MaxServerMemory.ConfigValue -ne $memory_per_instance_MB)\n {\n #Write-Host \"-----------------------------------------------------------------------------\" -ForegroundColor green\n #[int64]$CurrentMemoryValue = $CurrentServer.Configuration.MaxServerMemory.ConfigValue\n #Write-Host \"Changing $LocalInstance's maximum server memory from ($CurrentMemoryValue MB) to ($memory_per_instance_MB MB).\" -ForegroundColor magenta<\/p>\n\n<p>$CurrentServer.Configuration.MaxServerMemory.ConfigValue = $memory_per_instance_MB;\n $CurrentServer.Alter();\n #Write-Host \"$LocalInstance's maximum server memory is set.\" -ForegroundColor magenta\n }\n}<\/code><\/pre>\n<\/p>\n<h3>SQL Agent job<\/h3>\n<p>In the SQL Agent job T-SQL script, found <a href=\"http:\/\/www.sqlhammer.com\/blog\/wp-content\/uploads\/2013\/11\/job-cluster-memory-management.txt\">here<\/a>\u00a0and at the top of this section, you\u2019ll notice that it is basically a generated create job script with the above mentioned Powershell script in step 1. So, rather than go line-by-line, below are a list of the key properties of this job.<\/p>\n<ul>\n<li>The only step in the job is of the Powershell step type.<\/li>\n<\/ul>\n<ul>\n<li>This step needs to execute as a proxy account which uses credentials from a local system or domain account that can be granted local permissions on each node of the cluster. See <a href=\"http:\/\/serverfault.com\/questions\/28520\/which-permissions-rights-does-a-user-need-to-have-wmi-access-on-remote-machines\">local server permissions required for the Get-WmiObject cmdlet<\/a>\u00a0for permission details.<\/li>\n<\/ul>\n<ul>\n<li>That same account also requires SQL Server access to apply the memory capacity configuration. See\u00a0<a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/ms188787.aspx\">access required to set the MaxServerMemory configuration<\/a>\u00a0for details.<\/li>\n<\/ul>\n<ul>\n<li>The schedule is configured to execute when the SQL Agent starts. This is an indirect way of identifying a fail-over since the services will turn off on the failed node and come online on the new node thus triggering the job.<\/li>\n<\/ul>\n<hr \/>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/dh.jpg\" alt=\"\" \/><strong>Written by Derik Hammer of <a href=\"http:\/\/www.sqlhammer.com\/\">SQL Hammer<\/a><\/strong><\/p>\n<p>Derik is head of a Database Operations team for Liberty Tax service. He is responsible for the maintenance and health of all of his corporation\u2019s database servers, including production and two non-production environments. He implements and maintains high-availability and <a href=\"http:\/\/www.sqlhammer.com\/blog\/category\/general\/disaster-recovery\/\">disaster recovery solutions<\/a>. He is responsible for conducting, maintaining, and testing all database backup processes, and for the configuring, auditing, and performance tuning of all database servers.<\/p>\n<p>Derik is the owner and head writer of SQL Hammer, a <a href=\"http:\/\/www.sqlhammer.com\/\">Microsoft SQL Server resource<\/a>. He provides cutting-edge news, information, and commentary regarding the database industry and community.<\/p>\n<p>For more information, visit <a href=\"http:\/\/www.sqlhammer.com\/\">http:\/\/www.sqlhammer.com<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A couple of weeks ago I was reviewing my daily news feeds and a featured post on SQLServerCentral.com caught my eye. Michael Lacy\u2018s post on Managing MaxMemory Values on an Active-Active Two Node Cluster\u00a0provides a solution to the problem of dynamically managing maximum memory settings for SQL Server Fail-over Cluster instances during a node failure. [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":523,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-51","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/51","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=51"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/51\/revisions"}],"predecessor-version":[{"id":314,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/51\/revisions\/314"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media\/523"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=51"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=51"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=51"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}