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


Feb 12 2018   4:00PM GMT


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.

Found

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

   

RSS Feed
   

Email a friend


Source: http://itknowledgeexchange.techtarget.com/sql-server/when-using-availability-group-listeners-why-can-i-see-non-ag-databases/

software quote best quote

Contact Us Now!
We'll Give You Our Best Quote For Your Commercial Software Needs!

Contact Us