June 1, 2017 at 3:58 am
Hi,
We have configured 4 Availability groups and when we connect with any of the listener name all the database are getting displayed and I am even able to query the data. As per my understanding, when we connect with a listener name, the databases which are added to the Availability Group has to be displayed and accessed.
Can somebody please provide more details on this and correct me if I am wrong.
🙂
June 2, 2017 at 2:59 am
Can somebody help me on this?
🙂
June 2, 2017 at 3:16 am
Can you make sure the DNS name is being used for the LISTENER connection in the 1st Script.
select * from sys.availability_group_listeners
SELECT
AG.name AS [AvailabilityGroupName],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole],
dbcs.database_name AS [DatabaseName],
ISNULL(dbrs.synchronization_state, 0) AS [SynchronizationState],
ISNULL(dbrs.is_suspended, 0) AS [IsSuspended],
ISNULL(dbcs.is_database_joined, 0) AS [IsJoined]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
ORDER BY AG.name ASC, dbcs.database_name
June 2, 2017 at 3:30 am
Ram:) - Thursday, June 1, 2017 3:58 AMHi,We have configured 4 Availability groups and when we connect with any of the listener name all the database are getting displayed and I am even able to query the data. As per my understanding, when we connect with a listener name, the databases which are added to the Availability Group has to be displayed and accessed.
Can somebody please provide more details on this and correct me if I am wrong.
This is normal- it's one of those things that always surprises people. You can technically hop onto an instance via any listener, and access databases that aren't in an AG at all, as well as those that are in a totally different AG (assuming you have the right permissions to the databases, of course). It's nothing to worry about.
June 5, 2017 at 4:56 am
Ram:) - Thursday, June 1, 2017 3:58 AMHi,We have configured 4 Availability groups and when we connect with any of the listener name all the database are getting displayed and I am even able to query the data. As per my understanding, when we connect with a listener name, the databases which are added to the Availability Group has to be displayed and accessed.
Can somebody please provide more details on this and correct me if I am wrong.
The listener connects you to the primary replica, you will see all objects you have permissions to see, not just the databases in the Availability group the listener is servicing
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply