From further research, it appears the issue may be a result of older SQL clients performing an "InstanceValidity" check when connecting to SQL Server (specifically sqlsvr32.dll driver). The driver passes "MSSQLServer" as the instance to verify for the InstanceValidity check. In this case, since the instance name listening on the Default port (1433) is named "Instance1", the check fails because the Instance names do not match the InstanceValidity check.
Specifying the port number or instance name explicitly in the connection string works.
Windows Server Failover Clustering requires a minimum of two computers (Virtual Machines or Physical Machines). Lets refer to these as Node01
and Node02
.
Part of the install process for the Windows Server Failover Clustering role defines the name of the cluster. For our purposes let's call that MyCluster
.
Once you have created MyCluster
, you can add SQL Server instances to the cluster as independent "cluster resource groups". We'll refer to our SQL Server clustered instance as MyClusterSQL\INSTANCE
.
So, to clarify, we have:
╔════════════════════════╦═══════════════════════╗
║ Item ║ Names ║
╠════════════════════════╬═══════════════════════╣
║ Computers ║ Node01 and Node02 ║
║ Cluster Virtual Server ║ MyCluster ║
║ SQL Server Instance ║ MyClusterSQL\INSTANCE ║
╚════════════════════════╩═══════════════════════╝
Now, if you run SELECT SERVERPROPERTY('ComputerNamePhysicalNetbios')
it will return either Node01
or Node02
depending on which computer is hosting the SQL Server Instance.
SELECT SERVERPROPERTY('MachineName');
will return MyClusterSQL
. The Microsoft Docs for SERVERPROPERTY says this about "MachineName":
Windows computer name on which the server instance is running.
For a clustered instance, an instance of SQL Server running on a virtual server on Microsoft Cluster Service, it returns the name of the virtual server.
SELECT @@SERVERNAME;
will return MyClusterSQL\INSTANCE
.
Server Manager always returns the name of the computer you connect to. In our case, this will be either Node01
or Node02
. If the SQL Server Clustered Instance is running on Node02
, and you connect to it using Remote Desktop to the MyClusterSQL server, Server Manager will show Node02
, even though you connected to MyClusterSQL
.
Best Answer
Not at all. You can continue to have the backups on the FCI go to your network share. The log shipping job can be configured to copy from the network share to a local drive on the new instance, or read right from the network share.