SQL Server with Mr. Denny

Feb 12 2018   4:00PM GMT

When Using Availability Group Listeners, Why Can I See Non-AG Databases?

Denny Cherry Denny Cherry Profile: Denny Cherry

Tags:

Yep. This is actually by design; and is because listeners can be tricky little fellas.  When using a SQL Server Availability Group Listener, you can see any databases on the server that is hosting the Availability Group Listener.  The reason for this is because each SQL Server Availability Group Listener merely is a connection to the instance that’s hosting that Availability Group.

https://www.flickr.com/photos/wonderferret/289264597/

You can think of Availability Group Listeners kind of like a DNS entry.  Whatever server the listener is pointed at that’s the server that all users can connect too.  If for example, you had a server configuration with two Availability Groups and two Availability Group Listeners; if those two listeners are hosted on the same node of the Availability Groups and a user connects to one of the listeners, then the user would be able to access any of the databases (assuming they have access to all of them).

This is by design, and everything is working exactly as it should be.

This same rule applies to databases which aren’t protected by the availability group.  If you have multiple databases protected by an Availability Group, and several databases hosted on the active server which aren’t members of the availability group and a user connects to the Availability Group listener, they’ll be able to see all the databases on the server.  This also means that as the Availability Group Listener is moved from replica to replica databases will come and go as the Availability Group is moved.

Hopefully, this explains why you might be seeing things you aren’t expecting.

Denny

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: