As I’ve been spending more time working with Contained Availability Groups (CAGs), I’ve run into something that I think is an issue as they become more prevelant in the wild. To be honest, I do not have a lot of clients running CAG’s so I do not know how wide spread they might be but this is something I noticed.
To be blunt, it’s an SSMS problem. Specifically, there is no obvious visual indicator that tells me whether I’m connected to a SQL Server instance or connected to a Contained Availability Group. The icons within SSMS are the same regardless of which context you are connected to.
Below is an example of a connection to a CAG and you can see that the icons are the exact same as if you connected to the instance itself.
Because of that, I’ve submitted a suggestion to Microsoft:
Ironically, Allan Hirt of Microsoft, who if you know him is just flat out a good human being and fantastic technologist, recently posted a question on LinkedIn regarding improving high availability within SQL Server. I sent him the feedback link, but I think this is one of those things that would definitely help this.
If you want to give Allan feedback, here’s his post.
Why This Matters
One of the key concepts behind Contained Availability Groups is that they introduce their own contained versions of master and msdb. This allows logins, SQL Agent jobs, credentials, and other traditionally instance-level objects to move with the availability group.
The problem is that when you’re working in SQL Server Management Studio, the Object Explorer experience looks almost identical whether you’re connected to:
- A traditional SQL Server instance
- A listener for a Contained Availability Group
The only clue is often the server name you used when connecting. As a secondary issue, when utilizing CAG’s, the naming convention of the listener is going to be important to ensure clarity when connecting.
A Real-World Scenario
Imagine you’re troubleshooting an Availability Group and have multiple Object Explorer windows open.
One window is connected directly to the SQL Server instance.
Another is connected to the Contained AG listener.
You expand Security and see Logins.
You expand SQL Server Agent and see Jobs.
Everything looks normal.
But are you looking at the instance-level objects or the contained objects?
Without stopping and carefully checking your connection, it’s easy to lose track. This is going to be even more so if you have to manage a large SQL Server estate, ie: 1000’s of SQL Server instances, potentially many all having contained availability groups.
Even worse, you could create a login or SQL Agent job in the wrong location and not realize it until a failover occurs.
What I’d Like to See
I don’t think this requires a major redesign.
A few simple changes could make a huge difference:
- A distinct icon for Contained Availability Group connections
- A different color scheme in Object Explorer
- A visual badge or label indicating “Contained AG”
- A status bar indicator similar to what Azure Data Studio uses for different connection types
Anything that immediately tells the user:
You are connected to a Contained Availability Group, not the SQL Server instance.
What Do You Think?
Have you started using Contained Availability Groups yet? If so, have you found yourself double-checking whether you’re connected to the instance or the AG?
I’d be interested to hear how others are handling this today, and if you agree, I’d appreciate an upvote on the suggestion.