June 6, 2016 at 7:14 am
This is interesting, in a weird kind of way.
Server A (SvrA) and Server B (SvrB) are clustered windows servers. Server A is the primary node for a clustered SQL instance (CI_1). Server A also hosts a standalone SQL instance (SA_1) and Server B hosts a standalone SQL instance (SA_2).
I've got the aliases in SQL Config Mgr set up so that CI_1 uses port 1433, SA_1 uses port 1436, and SA_2 uses port 1435. The TCP/IP properties (ListenAll = Yes) are set not to use the dynamic port (at the bottom) but to use the ports in the aliases. I can connect to SA_2 using just SA_2,1435. I can connect to SA_1 using SvrA\SA_1 but not SA_1,1436 or SA_1 only. I can connect to CI_1 using just CI_1. This is all remote connections.
Local connections (on the servers themselves) allows me to connect to SA_2 using SA_2 only, SA_1 using SA_1 only and CI_1 using CI_1 only.
When I fail CI_1 over to SvrB, I can connect to it locally and remotely using it's alias CI_1. I can connect to SA_2 locally just as SA_2 and remotely as SA_2,1435. But here's the weird part. Suddenly I can't connect to SA_1 at all (locally or remotely) using any combination of possible connection points. SvrA\SA_1, SvrA\SA_1,1436, SA_1,1436, or SA_1. It's as if SA_1 has just disappeared even though it's not using the clustered drives that CI_1 uses.
Here's what I get when trying to connect to SA_1:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)
No connection could be made because the target machine actively refused it.
And yet, as soon as I fail CI_1 back to SvrA, suddenly I can connect to SA_1 again.
So, SA_1 is using direct attached storage for its system databases (no user dbs yet installed). These drives are not part of the cluster resource. SA_1 was installed after the clustered SQL instance was installed. I've triple checked the protocols for SA_1. Obviously it's set up for remote connections if I can remotely connect when CI_1 is on Server A. What I don't understand is why SA_1 would suddenly become untouchable if CI_1 fails over to the other node.
Does anyone have an explanation for this?
EDIT: Apparently there are two user databases installed, but they are also on the direct attached storage.
June 6, 2016 at 7:39 am
GAH! Nevermind. Figured it out.
Of all the freakin' ridiculous things to do, I set the server name in the aliases as the virtual clustered server name (SvrA) instead of the literal physical server name.
When the CI_1 is on SvrA, I can use SvrA\SA_1 to connect remotely to the instance, but when CI_1 is on SvrB, I can use ServerA\SA_1 to connect remotely to the instance. And once I fixed the SQL aliases in configuration manager, I was able to connect to SA_1 locally on the server.
Thank you everyone who read this thread.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply