We have a single host machine (lets call it OsSQLServer) for a client working as test environment for different applications and for that they have installed multiple SQL Server instances onto it.
Everything was working fine and then they had to use the OsSQLServer for another application which was a Java application and had the limitation that it didn’t have the option to put the Instance name in the connection string. What it means is it worked flawlessly only with OsSQLServer and not OsSQLServer\Instance1.
We tried all the methods to make the connection string work with the named instance but when nothing helped we decided to make the named instance for it act as default instance and listen with the OsSQLServer name.
For doing this we need to understand how we are even able to connect to the correct SQL Server instance when we have multiple different instances running on the same machine having dynamic ports.
When we begin the connection to a named instance first the client network library sends a UDP message to the server port 1434, SQL Server browser listens to this port and responds with the TCP/IP port of the requested instance. Based on the TCP/IP port received the client library makes the connection to the server using the port of the desired instance.
Default instance always uses the TCP/IP port of 1433 and so it doesn’t need to goto SQL Server browser if it is using 1433. So the solution for making a named instance behave or listen as default we have to make sure that we are using the port 1433. For many security compliance such as CIS we need to change the default ports and in such cases our solution will not work.
The solution is just to make the named instance listen at port number 1433 and change the TCP/IP port for the default instance to something other than 1433.
Once we changed this setting for our named instance the Java application was able to connect to the named instance of the SQL Server using the server name OsSQLServer.
Hope it helps!