Cannot connect to named instances on AG all listening on 1433 using DNS records

TiborKaraszi

We have a setup with a handful of instances on an AG cluster. The cluster has two nodes, each node has about 6 MSSQL instances. Each instance is listening to port 1433 on its own IP address. So instead of connecting to machine_name\instance_name, we connect to only instance_name. (since ythis is an AG cluster, the client connects in the end to the AG group name (the listener), but I understand that SQL Monitor doesn't connect using the AG group name. However we try to setup SQL Monitor, it is enumerate the instanced on the machine and then connect to machine\instance - which. 

How can we specify the machine name it tries to connect to.

(I have already tried to create client aliases in the registry where the client alias is named machine\instance and it points to instance, but SQL Monitor still won't connect to the instance. 

The machine connections work fine - we can see for instance the perfmon counters for all SQL instances. But none of the data that comes from inside SQL server.

Alex B

Hi @TiborKaraszi,

The default behaviour as you have seen is to connect to machine_name\instance_name (i.e. only connecting to the instance based on the IP of the host server.

There is the ability to specify a connection string directly into the repository which I'll provide directions for below.  We always suggest taking a backup of the repository before modifying it directly.  If you run into any issues please revert this by setting the ConnectionString column back to NULL.

To update the ConnectionString column, performing the following:

1. Get the Id of the specific SQL instance to update by looking for it in the settings.SqlServers page. The instance on a cluster node will be machineName\instanceName (or just machineName\ if the default instance).  You may also want to confirm the ParentId matches the top level entity which you can find in the settings.Clusters table ON settings.SqlServer.ParentId = settings.Clusters.Id
   

   SELECT Id FROM settings.SqlServer 
   WHERE Name = 'myMachine\myInstance';

2. Update the ConnectionString column for the given instance by Id
   

   UPDATE settings.SqlServers
   SET ConnectionString = 'Data Source=111.222.333.444;'
   WHERE Id = '<Id for specific Sql Instance>' ;

   
   

Kind regards, Alex

TiborKaraszi

That sounds promising. Thanks Alex! I'll get back when I've tested it. :-)