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

Published On: 2018-02-12By:

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

The post When Using Availability Group Listeners, Why Can I See Non-AG Databases? appeared first on SQL Server with Mr. Denny.

Leave a Reply

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.