I have an extremely strange issue with an SSRS report i'm working on. We use SQL Server 2012 availability groups and as number of groups and the nodes they run on has swelled, confusion on the part of users accessing them has increased, so I've created a report that, for a given DB, returns the correct primary and lists other related DBs as well.
The issue relates to displaying the AG. certain records were returning a null AG name, and having done some investigation, I've narrowed it down to the results from the following
Select * from sys.availability_groups AG
I have created a simple table in SSRS to take these results and when running the query in SSMS and BIDS, I get 13 records. When I deploy it and run the report, I get somewhere between 10 and 12 records, depending on the node I run it against. I am frankly at a loss how to proceed from here. I feel I've reduced the complexity of the query down to it's most simple form. I've checked there is no filtering taking place. Even performing a count(*) instead yields a result of 10 rather than 13. There is also no commonality to what is being omitted; naming conventions are standard across all AGs, they're not all on the same primary, but other AGs on the same primary do show up, and as I said, running against different primaries varies the number of records returned
Any suggestions?
Best Answer
So for anyone who is interested, I figured out this was a permissions issue. As this is a report, it runs under a limited permissions login. I'd granted that login read permissions to Master and View Server State, which I thought would be enough, and sure enough, the report loads, but it seems like for some reason, if you don't have View Any Definition, your results may not be complete. I suspect (though I don't really have time to prove it) that each visible AG had at least one DB with some sort of relevant permissions, bringing it into view, but anyway, View Any Definition fixed it :) thanks for all the suggestions though