MS SQL contained availability group error

Question

I have a question regarding ALTER database, GRANT and REVOKE commands and CREATE database statements in contained availability groups (CAG). I tested all of the mentioned and I got some conflicting results, so I am opening a question here.

CREATE DATABASE

I know that this statement does not work. However is there any workaround to this "problem" (or let say CAG feature) , or do we really need to always create a database on the primary node? I had a couple of weird incidents with our vendors, where they wanted to rebuild a database inside of CAG. They couldn't do it inside the contained availability group, so I removed it from the group. However because they used some wizzard (which was a black box for them) to build the application and database, they were building the database and all its metadata, jobs and settings on the primary node. And later when I added the database back to the contained availability group, all the instance related changes were left outside of the group. Is there an alternative way of solving this issue from a DBA perspective (without them changing their code)? For use cases like these?

GRANT and REVOKE

Then there was an issue with GRANTING and REVOKING user securables. I wanted to add a new user to the availability group listener and grant him a VIEW SERVER STATE and VIEW ANY DATABASE. I tried adding the GRANT'S via MSSMS logins -> clicked on user properties -> securables, on one of the nodes and i got this error: Invalid Urn filter on server level: filter must be empty, or server attribute must be equal with the true server name. (Microsoft.SqlServer.Smo). When I tried it on another node it worked. I did some further tests and did a couple of failovers, when I revoked and granted the VIEW SERVER STATE permission and on that one node, it worked every time. And on the other one, it could never set the permissions.

Funny thing is meanwhile, TSQL GRANT AND REVOKE STATEMENTS worked on both nodes (not just on one, as through MSSMS).

ALTER DATABASE

I tried altering the database and putting it in read only mode. Which fails because as the error states the database is currently in availability group. Which is the same restriction as CREATE DATABASE STATEMENT. Then I tried changing COLLATION, and funnily enough that worked.

CHANGING DATABASE OWNER

On FCI it is pretty straight forward. You change it and thats that. But here the owner was in a weird state. The node where I changed it, had the right owner and when I failed over, the owner was not the newly selected one. It appears as even though having contained availability group, under the hood there are still some settings, which work like "normal" availabiltiy grups would work (which is kinda logical, since CAG are pretty much Availability groups with added features). But it is still mind boggling because of its inconsistency.

Question

The problem is the whole contained availability group feature looks pretty inconsistent to me, the more I use it. Since we are moving away from FCI to AG, it looked promising, since it ensures the users can connect only through the listener if they want to access their databases. And of course the automatic sync of server objects is a nice feature aswell. But I still don't know if CAG is safe enough to run in production, with the restritions I mentioned earlier. So my question is, how could I bypass the restrictions above or should I move all my contained availability groups to "normal" availability" groups. Because I feel pretty uneasy when I just know that some day there will be a ticket to ALTER some random database which I won't be able to do in this setup. Or even a vendor won't be able to move to our instance, because their installation wizzard won't be able to handle CAG restrictions.

• J.D.: Honestly there's probably more to unpack in your post than what's reasonable for a single post. To simplify and take a step back, why do you want to use Contained Availability Groups over regular Availability Groups?

Answer 1

So my question is, how could I bypass the restrictions above or should I move all my contained availability groups to "normal" availability" groups.

It seems that there is room to grow in understanding the feature. I say this as many/all of the issues you've reported in the OP seem to be confusions on how things work even in a regular AG.

I know that this statement does not work. However is there any workaround to this "problem" (or let say CAG feature) , or do we really need to always create a database on the primary node?

What's the expectation here? You can create new databases on any node, but they must be added into the AG on the primary node, nothing has changed between regular and contained AGs in this regard.

The reason you can't create a database is because you're not in the connection context of the instance, but rather the context of the contained AG. This is well (at least I believe so) documented that some items cannot be completed in the contained context.

Thus this isn't anything new, not really a contained AG issue, and seems to be a knowledge gap.

I wanted to add a new user to the availability group listener and grant him a VIEW SERVER STATE and VIEW ANY DATABASE.

There is no such thing as adding a user to a listener. You can add a login to the contained AG. You can add a user to a contained database. You can't do anything with users/logins and listeners. This seems to, again, be a knowledge gap issue and not one with the software.

On FCI it is pretty straight forward. You change it and thats that. But here the owner was in a weird state. The node where I changed it, had the right owner and when I failed over, the owner was not the newly selected one.

Again, this is typical of how AGs work and entirely depends on the connection context. The same behavior exists in regular AGs. This is nothing new.

Then I tried changing COLLATION, and funnily enough that worked.

Why are you surprised? There are things you can and can't do with a database in an AG. These items, again, have been there since 2012. This seems to be a knowledge gap.

Overall, I don't see anything that you've actually brought up which is a valid item for AGs, let alone specific to contained AGs. You'll need to take time, learn and understand the feature, before putting it into production... which based on the data in the OP leads me to believe there is a large amount left to learn and understand before using AGs of any kind in production.

Valid contained AG issues do exist, such as database mail behaviors and such. However, none of that has been brought up here, but I wanted to point out that such things do exist and that no feature is flawless, let alone a Microsoft V1 feature which is generally assumed to have problems.