1

In my work environment, I have two database machines:

  • DB01
  • DB02

Each database machine has three SQL Server instances:

  • MSSQLSERVER
  • FBG
  • Central

I've managed to set up availability groups with SQL Server listener for the 1st SQL Server instance on both machines, and created SQL Server replication and it's working fine.

Now, I would like to set up a new availability group for the second SQL Server instance FBG on both servers, and I did, but the problem is when I create the listener for the second availability group it goes for the first availability group, I must type listener name / instance name to get it working.

I wish to type only the availability group listener name. For example:

  • the first availability group name is AVG01 and its listener AvgList01
  • the second availability group name is AVG02 and its listener AvgList02

When I connect to AVG02 using the name AvgList02 it connects me to AVG01 although I'm not using its listener name.

I must use it this way to get it working AvgList02\FBG where the FBG is the name of the SQL Server instance.

The question

Where is the issue, and how to fix it?

  • 5
    by AVG, do you mean Availability Groups? If so, that is more commonly referred to as AG. AVG is the SQL Keyword for the average function – Max Vernon Jul 16 at 16:51
  • 2
    This seems like the intended behavior to be perfectly honest. I would not expect to be able to connect to a secondary instance of SQL without specifying the instance name. AG or not. Since MSSQLSERVER is the default, it makes sense to me that your AvgList01 does not require the instance name. Where AvgList02/FGB is your second instance. – Jacob H Jul 16 at 17:45
  • @JacobH But there's possibility of different instance name of primary and secondary replica. – Rajesh Ranjan Jul 17 at 6:06
  • It comes down to port numbers. What port are the instances running on? What port is the listener configured for? Etc. Check here for a more detailed rundown. – John Eisbrener Jul 18 at 13:39
0

Let me try answering the three concerns;

  1. I wish to type only the availability group listener name. For example:

    the first availability group name is AVG01 and its listener AvgList01

    the second availability group name is AVG02 and its listener AvgList02

Create an alias named AvgList02 for your named SQL Server instance FBG As it is AvgList01 works so no need to create an Alias

  1. When I connect to AVG02 using the name AvgList02 it connects me to AVG01 although I'm not using its listener name.

This is probably as a result of the TCP/IP properties for your default instance protocol in SQL Server configuration manager. By default the Listen All parameter is set to Yes and if i am not mistaken that will allow all the IP addresses on this server (AvgList02 inclusive) be able to connect to this instance with the port specified in IPALL tab of the TCP/IP properties

  1. I must use it this way to get it working AvgList02\FBG where the FBG is the name of the SQL Server instance.

This is one of the available options for connecting to a named instance. You can either create an alias (easier option) or review your TCP/IP configurations if you do not want to connect like this

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.