I've just setup multiple availability groups within the same group of servers, simply for separation more than anything else, the databases in the new one are for Skype for Business so it comes with a handful of databases.
Anyway, it all works fine, I set everything up, I set up a new listener for it and then something happened I didn't expect. Using both the old and new listeners gives me all the databases on that instance, from both groups. I wasn't expecting this, I thought in my head (and I will hold my hand up and admit I'd totally made this up myself) that by assigning a listener to each of the groups, I would only see the databases in that group when using that listener.
Anyway, my question is… what would be the usual best practice around this? If each listener will give you all the databases anyway, should I create one for each group? or just one for the first group? or have I fundamentally missed something and there should be a way of me setting up each listener to only see the databases in the group associated with it?
I'm happy to be getting the "There's no point creating multiple blah blahs for doing that" messages if that's the case, no worries, but please only if you actually answer my main question.
Best Answer
TLDR version: the availability group listener points to the instance where the database sits, not the database itself.
Right now that that's out the way. If I'm reading this right you have multiple databases set up with Always On High Availability(AOHA) across two (or more)instances
There are a few settings to consider here. Firstly the unchangeable, you never connect to a database directly, you connect to an instance and then to the database within that instance, if you want to be restricted to only access the one database you need to do that with user/group level permissions and have different accounts to access each database separately.
The setting you have control over is what happens with the non primary instance, there are a few options: Connections in Primary Role (Allow all, Allow read/write) Readable Secondary (Yes, Read Intent Only, No)
If you turn readable secondary to No, then you'll only ever be able to use the database if it is the primary node (note this will save on licensing as a server with all of the databases in this mode does not require to be licensed(few conditions)) If this is set to 'yes' or 'read intent only' then users connected to that instance will be able to see and read data from that database (obviously read intent only will only allow read intent connections). The connections in primary role is used to re-direct read intent connections to the secondary node without having to know about the read-replica
On the note of separate listeners that depends on how you are using the databases, if they're completely separate and can exist within different instances (or their operations can be done with linked server connections) then being able to move the primary node to separate servers may allow you to unburden a server under heavy load. If the databases are dependant on being active on the same box and instance then there isn't much point in having multiple listeners as they will all always point to the same place which is ultimately the instance itself, it would be better to use different users (if possible) to lock off different databases (unless you wanted to do some really really complicated stuff with login triggers granting / removing permissions on the fly any time someone connects (which I would heavily not recommend and don't know if its actually possible)
Hope that helps