Connecting to named instances without using the instance name
I was recently asked a question about how failover from a primary to a secondary SQL Server could work for a named instance, when the named instance is in the connection string, and the instances on the primary and secondary have different names. The hostname wasn't the problem - it was a virtual IP, so switchable in the network layer - just the instance name which meant once this IP switch happened, the connection would fail.
This is a good question. There's a few ways to address it:
- We could configure two different connection strings and on failover, the alternative is used (by any arbitrary application mechanism)
- For mirroring, we can use the Failover Partner= element in the connection string - but this is transactional replication being used for DR, and failover should be manual
- We could set the instance names to be the same - however in this case it was too late since changing the instance name cleanly in SQL Server is practically impossible and breaks all sorts of things
None of these solutions was suitable. However, on each server, there was only one named instance and no default instance. What can we do?
It's not common knowledge, but you can connect to named instances without using the instance name by overriding the default port on which SQL Server listens not just for the default instance but for named instances too. In SQL Server Configuration Manager, in Protocols -> TCP/IP, in the IPAll port field, this is typically 1433 for the default instance and left blank for named instances, since for the latter, the SQL Browser deals with inbound connection requests. However, we can override this and re-specify port 1433 in this field.
What does this do? Well, for a start, we can replace our connection string with just a reference to the server, and it'll work just fine.
But - you can also connect to the instance using the full name - essential for replication to function correctly, since SQL Server requires the actual server name to make a connection.
Communication ports: Make sure you open ports TCP 445 (File and Printer Sharing, but also handles named pipe communication); UDP 1434, for SQL Browser, and TCP 1433 for ordinary communication. If you're using a firewall, ensure rules exist to let this traffic through.
In our scenario, this means the same connection string can be used to connect to both primary and secondary, once the manual change has been made to the virtual IP during failover. Be aware that you'll cause some problems if you have multiple named instances.
If this was useful for you, please comment or share!