An Availability Group Listener identifies a connection endpoint rather than a separate engine instance. I query where this connection actually arrived.
SELECT @@SERVERNAME AS RegisteredServerName, SERVERPROPERTY('ServerName') AS CurrentServerName, SERVERPROPERTY('InstanceName') AS InstanceName, SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS PhysicalComputer;@@SERVERNAME reports the registered local name. That can be stale after an uncorrected machine rename. SERVERPROPERTY adds server, instance and physical-host context. InstanceName is NULL on a default instance.
Intent and routing can direct a connection to the primary or a readable secondary. These properties identify the present destination. They don’t inventory every replica or predict the next connection after failover.
Correlate the intended database with replica-state DMVs and appropriate permissions. An Object Explorer label does not establish primary role. Single-instance property output also does not demonstrate listener routing.
Related reading
- Comprehensive Database Performance Health Check
- SQL in Sixty Seconds
- Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
- One Scan for 3 Count Sum – SQL in Sixty Seconds #178
- SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
- COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
- COUNT(*) and Index – SQL in Sixty Seconds #175
- Index Scans – Good or Bad? – SQL in Sixty Seconds #174
- Optimize for Ad Hoc Workloads – SQL in Sixty Seconds #173
- Avoid Join Hints – SQL in Sixty Seconds #172
- One Query Many Plans – SQL in Sixty Seconds #171
A listener label is not proof of primary role, it is an endpoint whose routing must be verified.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
