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