November 4, 2015 at 9:13 am
Hi,
We have a SQL 2014 active passive cluster with 5 instances. When the cluster was installed one of the nodes was a virtual machine. As we started to have problems and this is not a Microsoft supported configuration we decided to replace the virtual node with a phyiscal one. On 3 of these 5 instances we configured static ports (1433 TCP) this was required for applications and firewall implementation. Now with the physical node joined to the cluster we have issues with these three instances. Whenever one of these instances is moved to the passive node the server authentication is changed from mixed to windows only. I'm no SQL expert at all but to me it looks like the configuration of the instances are not replicated to the passive node? I found some similar problems on the net but these are mostly under sql 2008. Any ideas how to solve this are welcome.
November 4, 2015 at 4:31 pm
744 (11/4/2015)
Hi,We have a SQL 2014 active passive cluster with 5 instances. When the cluster was installed one of the nodes was a virtual machine. As we started to have problems and this is not a Microsoft supported configuration we decided to replace the virtual node with a phyiscal one. On 3 of these 5 instances we configured static ports (1433 TCP) this was required for applications and firewall implementation. Now with the physical node joined to the cluster we have issues with these three instances. Whenever one of these instances is moved to the passive node the server authentication is changed from mixed to windows only. I'm no SQL expert at all but to me it looks like the configuration of the instances are not replicated to the passive node? I found some similar problems on the net but these are mostly under sql 2008. Any ideas how to solve this are welcome.
These all named instances or a default and named instances. Generally it's not recommended to use port 143 for named instances.
Can you provide details of exactly what the issues were you faced
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 5, 2015 at 9:48 am
Hi,
we needed to change the ports on the 3 named instances because of firewall rules.
November 7, 2015 at 3:34 pm
I am not up to date on the latest cluster stuff, but I don't think this aspect of Windows clustering has changed since 2008. So, the last I heard the cluster's checkpoint process is responsible for backing up registry entries, in response to requests from a cluster-aware process (such as SQL Server and SQL Server Management Studio). The cluster's checkpoint process restores the backed up registry entries upon other nodes in the cluster, just before the clustered process is started upon the new node. When a change to a registry is made to a clustered process, but without using a cluster-aware process (such as regedit), the cluster's checkpoint process will be unaware of a need to replicate a registry change to the other nodes.... So, upon a node that is working, open SQL Server Management Studio, SQL Server properties, Security tab, pretend to change the authentication mode, click OK to confirm that a restart would be needed (don't restart), click OK to ensure the change gets committed, go right back and change the authentication mode back to the way you wish, failover SQL Server to the offending node whenever your opportunity arises, and check results (should be quickly obvious, since app will fail to authenticate if these steps failed to offer relief). If those steps failed to offer relief, inspect the cluster log for further details.
If you want to confirm that things have not changed, the key Windows Clustering phrase is "checkpoint process", or, you could test the above steps using a test cluster.
November 9, 2015 at 6:24 am
Hi,
thanks. I will try that later this evening during non-office hours.
Cheers,
Oliver
November 9, 2015 at 10:58 am
744 (11/5/2015)
Hi,we needed to change the ports on the 3 named instances because of firewall rules.
choose a free port for each instance from the upper port range and open the firewall to allow traffic
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 9, 2015 at 2:35 pm
When I did the port change I stopped the checkpoints using powershell, changed the ports on both nodes and reactivated the checkpoints. This was working fine on the old SQL node for all 3 instances. Somehow the newly joined node no longer replicates the changes. I changed the auth mode as you suggested but it wasn't replicated. Then I tried to change the logging mode to failed and successful on the node that didn't pick up the auth mode change and moved it back to the main node and the logging was not changed on that node... Is there any way to have the checkpointing re-enabled / fixed?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply