![](/style/images/good.png)
![](/style/images/bad.png)
Endpoints: 3 Great Queries to Test AG Endpoints | SQL RNNR
source link: https://jasonbrimhall.info/2021/10/12/top-3-queries-to-validate-availability-group-endpoints/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
3 Important Queries for Testing Your Availability Group Endpoints
In this article, I have shown the importance of performing three different validation tests against your Availability Group Endpoints. Each test also demonstrates what can be run in the event the validation test fails.
TSQL Tuesday
Time flies, seasons are flying by and seemingly changing day by day. We have slippery slopes here, there, and everywhere. Every now and again Database professionals need a quick moment to get away from the fray. As luck would have it, it is now time for a fabulous party – to get away from all of the reality the world is offering us these days. So, without further ado, let’s escape reality for a brief moment and party on with TSQL Tuesday as we delve into the validation of Endpoints!
This party, that was started by Adam Machanic, has now been going for long enough that changes have happened (such as Steve Jones (b | t) managing it now). For a nice long read, you can find a nice roundup of all TSQLTuesdays over here.
Invitation
This month, John McCormack (b | t) invites us to share our most powerful and biggest tools. Well, maybe that is just a half truth. While it may be valid, the actual invite is for each party-goer to share a story about their favorite handy go-to “short” scripts. Granted, “short” is a protected class and is really more of a perspective from the eye of the beholder. What is short for you may be long for somebody else (e.g. maybe some of you think a short script is anything less than 2000 lines of code).
Another take on the term “short”, could be that it takes just a short amount of time to pull out a saved script to perform the task at hand (some routine task you may perform but doesn’t quite rise to the level of automation such as what Aaron Bertrand shared here). Please go and check the invite from John – here.
John has given us an outstanding topic this month. This is a critical key to success for a DBA in my opinion. Every DBA really should have some sort of cache of saved “quick” / “short” scripts and every DBA should be able to pound out a quick three line script for quick info without too much thought. I must confess that I am not alone in this line of thinking.
Check out all of these topics from the community from past TSQL Tuesday challenges – here (along with some of my offerings: Essential Tools, XE Power Tools, and a litany of others. The moral of that sentiment is that a quality DBA should have a cache of tools to make him/her better at what they do!
Validate your Endpoints
After building an Availability Group in SQL Server, I like to run some validation checks in order to build out the Endpoints. I like to do this because I have run into issues in the past. Unfortunately, I have discovered that I still run into those issues (no matter what method is used to build out the Availability Group) if I don’t run these validation tests. The tests are rather simple. After doing it a few times, I combined them into a single script that I can pull out of my source control very quickly (thus still sort of meeting both types of “short” I described earlier in this post).
I will share the initial short scripts I had previously used to perform these validations and then conclude by sharing what the final script looks like that combines them all into a simple easy to run script for your toolbelt.
Validate Endpoints Exist
Symptom: Unable to add additional nodes to AG and buttons are greyed out in the GUI.
Checking for a missing endpoint (or any of the preceding symptoms) is rather simple which then leads to a very easy fix. We need to see if the endpoint is created. Even though you may have specified all of this information when setting up the AG and the AG does get created on the primary node, the Endpoint doesn’t create on the primary node and thus prevents the addition of any other nodes to this AG.
The Fix…
That is a pretty simple short script to check if the AG endpoint is present. If the script returns nothing, then it is time to go and create the endpoint. You could do it with another simple short script such as the following.
Validate Endpoints Owner
After the endpoint is created, you will want to check the owner of the endpoint. There should be no surprise here that the owner of the endpoint is the principal that was used to create the endpoint. This may be suitable in some environments, but is definitely not acceptable in many environments. And thus, if the endpoint owner is your principal, then you should change it.
Validation Script
To validate the owner, you can simply re-run the script I posted in the previous section – reposted here for simplicity.
The fix…
And then you can run the following to change the owner of that endpoint if it doesn’t quite match what you desire. Personally, I prefer to change the endpoint owners to ‘sa’.
So far so good, right? These are easy short scripts that anybody could use to validate their Availability Group endpoints. Let’s keep going and take a look at the third validation.
Validate Endpoints Permissions
The next validation I perform is due to errors that pop in the error log with the following text:
Database Mirroring login attempt by user ‘domain\user.’ failed with error: ‘Connection handshake failed. The login ‘domain\user’ does not have CONNECT permission on the endpoint. State 84.’.
This error message has most of the pertinent information that can help you figure out what is causing this error to be thrown. You would think this should always be applied properly when the endpoint is created for the Availability Group. Alas, sometimes it doesn’t get properly applied so we have to take additional manual steps.
When you see this error message, you will typically see that the service account is the account that is missing the connect permission. In addition, I typically see this when using a managed service account (or group managed service account). To verify the connect permission is properly applied, you can run this validation script.
Validation Script
In the preceding script, I am looking to see of Grantee and service_account have the same value. If they don’t then I want to grant “connect” to the service_account that is listed. Granting connect is essential to helping us resolve the aforementioned error.
The fix…
We can fix this problem with the following script.
Et voila! We have just run through three easy validations for the endpoints accompanied by the fix scripts for each one.
But wait, there’s more…
Easy Button
I didn’t much like running through each of those short scripts individually. So, I created a long script to combine all of the validations and their fixes. In addition, there is a little more logic built in to take away some of the manual aspects of what I just discussed in the prior three sections.
I like to see what has changed with each step along the way, so I have added outputs with each validation phase. This allows me to see step by step what changed and when. And, as a bonus, this is still a short script because I can just open the script and run it (as alluded to much earlier in this post).
Wrapping it Up
In this article, I have shown the importance of performing three different validation tests against your Availability Group endpoints. Each test also demonstrates what can be run in the event the validation test fails.
Feel free to explore some of the other TSQL Tuesday posts I have written.
Are you interested in more articles showing what and how to audit? I recommend reading some of my auditing articles. For some “back to basics” related articles, feel free to read here.
Author Jason BrimhallPosted on October 12, 2021October 12, 2021Categories News, Professional, Security, SQLBP, SSC, TSQL TuesdayTags Availability Groups, Endpoint, HADR, Scripts, Security, SQL Script, TSQL Tuesday
Leave a Reply Cancel reply
Your email address will not be published. Required fields are marked *
Comment *
Name *
Email *
Website
Save my name, email, and website in this browser for the next time I comment.
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Post navigation
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK