May 10, 2021 at 8:10 am
Morning All,
I have an Availability Group that is configured (incorrectly) to allow ALL connections in the secondary role (that is to say that you don't need to set read-intent in order to connect). There are about 200 databases in this AG. Out of 600 total on that instance.
What is the best way to identify what databases in that secondary replica are being queried by user queries, rather than system.
Is there a way to trace or XE for processes where the database belongs to a secondary replica. I'd rather not have to key in individual dbid's or names into a trace filter.
Cheers
Alex
May 11, 2021 at 9:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
May 11, 2021 at 9:17 am
It has been a while since I worked with AG, but from what I remember if you specify the listener name in the connection string and you don't use read intenet in the connection string, then you will be connected to the primary DB. Could it be that in the connection strings that are being connected to the secondary the programmers used the actual IP or name of the secondary server?
Adi
May 11, 2021 at 5:35 pm
If the secondary has not been configured for read-intent, then even with the application intent set in the connection string the select statements would not be redirected to that secondary.
If the secondary has been set to the read-only role - then the only way to connect to the secondary is a direct connection to that instance.
You stated that the secondary was set to allow all connections - which I assume to mean it was set to read-only. That is not an incorrect configuration if the intent is to allow users the ability to run queries against the read-only secondary by directly connecting to that secondary.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 11, 2021 at 7:05 pm
Evening All, thanks for youre replies so far.
I dont think my original question was clear.
The AG is configured correctly for it use. People can connect direct to the instance and query the databases in the secondary role. That is by design.
But because this place is a free-for-all for most things there is no easy way to identify who is connecting to what.
In particular I want to trace or an extended event on the secondaries to track connections to the read only AG's/databases.
May 11, 2021 at 7:39 pm
Sorry - but it still isn't clear. Users don't connect to databases - they connect to an instance. I don't think you can build a trace or an XE to identify connections to a read-only database on a secondary.
If the login was created with a default database set to one of the read-only databases on that secondary, then you could monitor for those specific logins - but then you would already know which accounts have access. If the login has a different default database - then connections will show up with that database.
I would look at the logins configured on the secondary - and identify what databases each login has been setup/configured to access. That will at least identify the users that can access the database(s) - then you should be able to setup an XE to monitor connections to see which of those users are actually connecting to the system.
But that all depends on how access is being granted - are the accounts members of an AD security group and getting access through membership in the group? Or are these individual SQL accounts? Or individual Windows user accounts?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply