locked
AlwaysOn listener: why from one listener I can access the database grouped under another listener? RRS feed

  • Question

  • Hello everybody. I created two different available groups, AG1 and AG2 with two different listener, the listener A and the listener B. The listener have different ip. I assigned The database A to the AG1 and the database B to the AG2. Why when my users connect to the listener A the can access the database B? Is this the normal behaviour? I supposed the only database available for connections through tht listener A should be the database A.

    What is wrong?

    Thankyou

    Wednesday, May 7, 2014 12:37 PM

Answers

  • Hello,

    Listeners are network layer abstractions and do not enforce any silos or otherwise containment of databases or other resources inside of SQL Server.

    What the listener is doing is saying, "Hey, I'm here with a well known name and port. When you connect to me you're connecting to the instance I am currently pointing to, whatever that may be." It also points to the location where a group of resources (your AG databases) are known to all reside, together.

    Now, taking your example - say I had the same setup but on 3 different servers where the secondaries were not readable. AG1 primary is on Host-A, AG2 primary on Host-B and nothing on Host-C. If you connect to the AG1, there is no guarantee that Database-B will be there at all, because it's part of a different availability group. If you would attempt to do something to Database-B, it would fail.

    The reason that your attempt worked is because both databases were on the same instance and readable at the same time. Starting moving things around and there is a calculable chance that doing the same thing again will succeed or fail. All you really know is that if you connect to Listener-A for AG-A that Database-A will always be there but can make no assumptions as to where Database-B would be.


    Sean Gallardy | Blog | Twitter

    • Proposed as answer by SQLCraftsman Wednesday, May 7, 2014 3:55 PM
    • Marked as answer by tracycai Thursday, May 8, 2014 9:47 AM
    Wednesday, May 7, 2014 1:49 PM
    Answerer

All replies

  • Hello,

    Listeners are network layer abstractions and do not enforce any silos or otherwise containment of databases or other resources inside of SQL Server.

    What the listener is doing is saying, "Hey, I'm here with a well known name and port. When you connect to me you're connecting to the instance I am currently pointing to, whatever that may be." It also points to the location where a group of resources (your AG databases) are known to all reside, together.

    Now, taking your example - say I had the same setup but on 3 different servers where the secondaries were not readable. AG1 primary is on Host-A, AG2 primary on Host-B and nothing on Host-C. If you connect to the AG1, there is no guarantee that Database-B will be there at all, because it's part of a different availability group. If you would attempt to do something to Database-B, it would fail.

    The reason that your attempt worked is because both databases were on the same instance and readable at the same time. Starting moving things around and there is a calculable chance that doing the same thing again will succeed or fail. All you really know is that if you connect to Listener-A for AG-A that Database-A will always be there but can make no assumptions as to where Database-B would be.


    Sean Gallardy | Blog | Twitter

    • Proposed as answer by SQLCraftsman Wednesday, May 7, 2014 3:55 PM
    • Marked as answer by tracycai Thursday, May 8, 2014 9:47 AM
    Wednesday, May 7, 2014 1:49 PM
    Answerer
  • Thankyou!

    It's clear.

    Wednesday, May 7, 2014 2:18 PM