3 Important Queries for Testing Your Availability Group Endpoints

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.

USE master;
GO
SELECT SUSER_NAME(principal_id) AS EndpointOwner
, name AS EndpointName
FROM sys.database_mirroring_endpoints;

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.

CREATE ENDPOINT Hadr_Endpoint  --default endpoint name for an AG endpoint
    STATE=STARTED   
    AS TCP (LISTENER_PORT=5022)   --5022 is the default port
    FOR DATABASE_MIRRORING (ROLE=ALL);  
GO

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.

USE master;
GO
SELECT SUSER_NAME(principal_id) AS EndpointOwner
, name AS EndpointName
FROM sys.database_mirroring_endpoints;

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’.

USE master;
GO
SELECT SUSER_NAME(principal_id) AS EndpointOwner
, name AS EndpointName
FROM sys.database_mirroring_endpoints;
USE master;
GO
Declare @SQL varchar(2048);
SELECT @SQL = 'ALTER AUTHORIZATION ON ENDPOINT::' + dme.[name] + ' TO sa;'
    FROM sys.database_mirroring_endpoints dme
    WHERE dme.type_desc = 'DATABASE_MIRRORING';
PRINT @SQL
--EXECUTE (@SQL) --uncomment if you wish to apply the changes and the script looks correct
GO
SELECT SUSER_NAME(principal_id) AS EndpointOwner
, name AS EndpointName
FROM sys.database_mirroring_endpoints;
GO

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 ‘domainuser.’ failed with error: ‘Connection handshake failed. The login ‘domainuser’ 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

DECLARE @SQL VARCHAR(1024),
        @SQLSvcAccount VARCHAR(128);
SELECT @SQLSvcAccount = dss.service_account
FROM sys.dm_server_services dss
WHERE dss.servicename NOT LIKE 'SQL Server Agent%';
SELECT ep.endpoint_id,
       p.class_desc,
       p.permission_name,
       ep.name AS EndpointName,
       sp.name AS Grantee,
       ep.type_desc,
       ca.service_account
FROM sys.server_permissions p
    INNER JOIN sys.endpoints ep
        ON p.major_id = ep.endpoint_id
    INNER JOIN sys.database_mirroring_endpoints dme
        ON ep.endpoint_id = dme.endpoint_id
    INNER JOIN sys.server_principals sp
        ON p.grantee_principal_id = sp.principal_id
    CROSS APPLY
(
    SELECT dss.service_account
    FROM sys.dm_server_services dss
    WHERE dss.servicename NOT LIKE 'SQL Server Agent%'
) ca
WHERE p.class = '105'
      AND ep.type_desc = 'DATABASE_MIRRORING';

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.

DECLARE @SQL VARCHAR(1024),
        @SQLSvcAccount VARCHAR(128);
SELECT @SQLSvcAccount = dss.service_account
FROM sys.dm_server_services dss
WHERE dss.servicename NOT LIKE 'SQL Server Agent%';
SELECT ep.endpoint_id,
       p.class_desc,
       p.permission_name,
       ep.name AS EndpointName,
       sp.name AS Grantee,
       ep.type_desc,
       ca.service_account
FROM sys.server_permissions p
    INNER JOIN sys.endpoints ep
        ON p.major_id = ep.endpoint_id
    INNER JOIN sys.database_mirroring_endpoints dme
        ON ep.endpoint_id = dme.endpoint_id
    INNER JOIN sys.server_principals sp
        ON p.grantee_principal_id = sp.principal_id
    CROSS APPLY
(
    SELECT dss.service_account
    FROM sys.dm_server_services dss
    WHERE dss.servicename NOT LIKE 'SQL Server Agent%'
) ca
WHERE p.class = '105'
      AND ep.type_desc = 'DATABASE_MIRRORING';
IF NOT EXISTS
(
    SELECT 1/0
    FROM sys.server_permissions p
        INNER JOIN sys.endpoints ep
            ON p.major_id = ep.endpoint_id
        INNER JOIN sys.database_mirroring_endpoints dme
            ON ep.endpoint_id = dme.endpoint_id
        INNER JOIN sys.server_principals sp
            ON p.grantee_principal_id = sp.principal_id
    WHERE p.class = '105' --ENDPOINT class_desc
          AND ep.type_desc = 'DATABASE_MIRRORING'
          AND sp.name = @SQLSvcAccount
          AND p.permission_name = 'CONNECT'
)
BEGIN
    SELECT @SQL = 'GRANT CONNECT ON ENDPOINT::' + ca.endpoint_name + ' TO [' + dss.service_account + ']'
    FROM sys.dm_server_services dss
        CROSS APPLY
    (
        SELECT [name] AS endpoint_name
        FROM sys.database_mirroring_endpoints dme
        WHERE dme.type_desc = 'DATABASE_MIRRORING'
    ) ca
    WHERE dss.servicename NOT LIKE 'SQL Server Agent%';
    PRINT @SQL;
    --EXECUTE (@SQL); --uncomment if you wish for the script to automatically run the fix
END;
SELECT ep.endpoint_id,
       p.class_desc,
       p.permission_name,
       ep.name AS EndpointName,
       sp.name AS Grantee,
       ep.type_desc,
       ca.service_account
FROM sys.server_permissions p
    INNER JOIN sys.endpoints ep
        ON p.major_id = ep.endpoint_id
    INNER JOIN sys.database_mirroring_endpoints dme
        ON ep.endpoint_id = dme.endpoint_id
    INNER JOIN sys.server_principals sp
        ON p.grantee_principal_id = sp.principal_id
    CROSS APPLY
(
    SELECT dss.service_account
    FROM sys.dm_server_services dss
    WHERE dss.servicename NOT LIKE 'SQL Server Agent%'
) ca
WHERE p.class = '105'
      AND ep.type_desc = 'DATABASE_MIRRORING';

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.

SET NOCOUNT ON;
DECLARE @AGEndpointName NVARCHAR(128) = 'Hadr_endpoint'
, @AGPort VARCHAR(10) = 5022
, @SQL VARCHAR(1024)
        , @SQLSvcAccount VARCHAR(128) = 'AMERICANORTHOsvc_SQLSvr1'
, @EndpointOwner VARCHAR(10) = 'sa'
, @SQLSvcAccount_current VARCHAR(128)
, @EndpointOwner_current VARCHAR(10)
, @Debug BIT = 1;

/* assign variable values
some items are commented out. debating on if they should be parameterized too
*/SELECT 
 @SQLSvcAccount_current = ca.service_account
, @EndpointOwner_current = SUSER_NAME(dme.principal_id)
--, p.permission_name --should always be CONNECT
--, ep.[name] AS EndpointName
--, te.[port] AS EndpointPort
FROM sys.server_permissions p
INNER JOIN sys.endpoints ep
ON p.major_id = ep.endpoint_id
INNER JOIN sys.tcp_endpoints te
ON te.endpoint_id = ep.endpoint_id
INNER JOIN sys.database_mirroring_endpoints dme
ON ep.endpoint_id = dme.endpoint_id
INNER JOIN sys.server_principals sp
ON p.grantee_principal_id = sp.principal_id
CROSS APPLY
(
SELECT dss.service_account
FROM sys.dm_server_services dss
WHERE dss.servicename LIKE 'SQL Server (%'
) ca
WHERE p.class = '105'
AND ep.type_desc = 'DATABASE_MIRRORING';
SELECT 
'Start' AS ProcessStep
, ep.endpoint_id
, p.class_desc
, p.permission_name
, ep.[name] AS EndpointName
, sp.[name] AS Grantee
, ep.[type_desc]
, ca.service_account
, SUSER_NAME(dme.principal_id) AS EndpointOwner
, CASE dme.[state]
WHEN 0 THEN 'STARTED'
WHEN 1 THEN 'STOPPED'
WHEN 2 THEN 'DISABLED'
ELSE 'ANARCHY'
END AS EndpointState
, te.[port] AS EndpointPort
, te.protocol_desc
FROM sys.server_permissions p
INNER JOIN sys.endpoints ep
ON p.major_id = ep.endpoint_id
INNER JOIN sys.tcp_endpoints te
ON te.endpoint_id = ep.endpoint_id
INNER JOIN sys.database_mirroring_endpoints dme
ON ep.endpoint_id = dme.endpoint_id
INNER JOIN sys.server_principals sp
ON p.grantee_principal_id = sp.principal_id
CROSS APPLY
(
SELECT dss.service_account
FROM sys.dm_server_services dss
WHERE dss.servicename LIKE 'SQL Server (%'
) ca
WHERE p.class = '105'
AND ep.type_desc = 'DATABASE_MIRRORING'
  AND ep.[name] = @AGEndpointName;
--If no database_mirroring endpoints are present. More than one database_mirroring endpoint is not supported. if more
--than 1 endpoint of this type is attempted to be created then an error will be thrown.
/*
Msg 7862, Level 16, State 1, Line 5
An endpoint of the requested type already exists.  Only one endpoint of this type is supported.  Use ALTER ENDPOINT or DROP the existing endpoint and execute the CREATE ENDPOINT statement.
*/IF NOT EXISTS (SELECT dme.[name] FROM sys.database_mirroring_endpoints dme)
BEGIN
--If endpoint not present in sys.database_mirroring_endpoints, then create one
IF NOT EXISTS (
SELECT 1/0
FROM sys.server_permissions p
INNER JOIN sys.endpoints ep
ON p.major_id = ep.endpoint_id
INNER JOIN sys.database_mirroring_endpoints dme
ON ep.endpoint_id = dme.endpoint_id
INNER JOIN sys.server_principals sp
ON p.grantee_principal_id = sp.principal_id
WHERE p.class = '105'
  AND ep.[type_desc] = 'DATABASE_MIRRORING'
  AND ep.[name] = @AGEndpointName
)
BEGIN
SELECT @SQL = 'CREATE ENDPOINT [' + @AGEndpointName + ']
STATE=STARTED
AS TCP (LISTENER_PORT = ' + @AGPort + ')
FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES);'
IF @Debug = 1
BEGIN
PRINT @SQL;
END
ELSE
BEGIN
EXECUTE (@SQL);
END
END;
END
ELSE IF @Debug = 0
BEGIN
SELECT 'ENDPOINT ALREADY EXISTS! NO FURTHER ENDPOINT CHANGES WERE PROCESSED. PLEASE REVIEW!'
SELECT 
'Existing Config' AS ProcessStep
, ep.endpoint_id
, p.class_desc
, p.permission_name
, ep.[name] AS EndpointName
, sp.[name] AS Grantee
, ep.[type_desc]
, ca.service_account
   , SUSER_NAME(dme.principal_id) AS EndpointOwner
, CASE dme.[state]
WHEN 0 THEN 'STARTED'
WHEN 1 THEN 'STOPPED'
WHEN 2 THEN 'DISABLED'
ELSE 'ANARCHY'
END AS EndpointState
, te.[port] AS EndpointPort
, te.protocol_desc
FROM sys.server_permissions p
INNER JOIN sys.endpoints ep
ON p.major_id = ep.endpoint_id
INNER JOIN sys.tcp_endpoints te
ON te.endpoint_id = ep.endpoint_id
INNER JOIN sys.database_mirroring_endpoints dme
ON ep.endpoint_id = dme.endpoint_id
INNER JOIN sys.server_principals sp
ON p.grantee_principal_id = sp.principal_id
CROSS APPLY
(
SELECT dss.service_account
FROM sys.dm_server_services dss
WHERE dss.servicename LIKE 'SQL Server (%'
) ca
WHERE p.class = '105'
  AND ep.type_desc = 'DATABASE_MIRRORING'
UNION
SELECT 
'Desired Config' AS ProcessStep
, ep.endpoint_id
, p.class_desc
, p.permission_name
, @AGEndpointName AS EndpointName
, @SQLSvcAccount AS Grantee
, ep.[type_desc]
, ca.service_account
   , SUSER_NAME(dme.principal_id) AS EndpointOwner
, CASE dme.[state]
WHEN 0 THEN 'STARTED'
WHEN 1 THEN 'STOPPED'
WHEN 2 THEN 'DISABLED'
ELSE 'ANARCHY'
END AS EndpointState
, @AGPort AS EndpointPort
, te.protocol_desc
FROM sys.server_permissions p
INNER JOIN sys.endpoints ep
ON p.major_id = ep.endpoint_id
INNER JOIN sys.tcp_endpoints te
ON te.endpoint_id = ep.endpoint_id
INNER JOIN sys.database_mirroring_endpoints dme
ON ep.endpoint_id = dme.endpoint_id
INNER JOIN sys.server_principals sp
ON p.grantee_principal_id = sp.principal_id
CROSS APPLY
(
SELECT dss.service_account
FROM sys.dm_server_services dss
WHERE dss.servicename LIKE 'SQL Server (%'
) ca
WHERE p.class = '105'
  AND ep.type_desc = 'DATABASE_MIRRORING';
IF @Debug = 1
BEGIN
PRINT @SQL;
END
ELSE
BEGIN
GOTO GetOut;
END
END
ELSE
BEGIN
SET @SQL = 'CREATE ENDPOINT [' + @AGEndpointName + ']
STATE=STARTED
AS TCP (LISTENER_PORT = ' + @AGPort + ')
FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES);';
IF @Debug = 1
BEGIN
PRINT @SQL;
END
ELSE
BEGIN
EXECUTE (@SQL);
END
END
/* start endpoint if not started */IF (SELECT se.[state] FROM sys.endpoints se WHERE se.[name] = @AGEndpointName) <> 0
BEGIN
    SET @SQL = 'ALTER ENDPOINT [' + @AGEndpointName + '] STATE = STARTED;';
IF @Debug = 1
BEGIN
PRINT @SQL;
END
ELSE
BEGIN
EXECUTE (@SQL);
END
END
ELSE IF @Debug = 1
BEGIN
    SET @SQL = 'ALTER ENDPOINT [' + @AGEndpointName + '] STATE = STARTED;';
PRINT @SQL;
END
/* Grant Connect on Endpoint to SQL Service Account */SELECT 
'Pre-Grant Connect' AS ProcessStep
, ep.endpoint_id
, p.class_desc
, p.permission_name
, ep.[name] AS EndpointName
, sp.[name] AS Grantee
, ep.[type_desc]
, ca.service_account
, SUSER_NAME(dme.principal_id) AS EndpointOwner
, CASE dme.[state]
WHEN 0 THEN 'STARTED'
WHEN 1 THEN 'STOPPED'
WHEN 2 THEN 'DISABLED'
ELSE 'ANARCHY'
END AS EndpointState
, te.[port] AS EndpointPort
, te.protocol_desc
FROM sys.server_permissions p
INNER JOIN sys.endpoints ep
ON p.major_id = ep.endpoint_id
INNER JOIN sys.tcp_endpoints te
ON te.endpoint_id = ep.endpoint_id
INNER JOIN sys.database_mirroring_endpoints dme
ON ep.endpoint_id = dme.endpoint_id
INNER JOIN sys.server_principals sp
ON p.grantee_principal_id = sp.principal_id
CROSS APPLY
(
SELECT dss.service_account
FROM sys.dm_server_services dss
WHERE dss.servicename LIKE 'SQL Server (%'
) ca
WHERE p.class = '105'
AND ep.type_desc = 'DATABASE_MIRRORING';
IF NOT EXISTS
(
    SELECT 1/0
    FROM sys.server_permissions p
        INNER JOIN sys.endpoints ep
            ON p.major_id = ep.endpoint_id
        INNER JOIN sys.database_mirroring_endpoints dme
            ON ep.endpoint_id = dme.endpoint_id
        INNER JOIN sys.server_principals sp
            ON p.grantee_principal_id = sp.principal_id
    WHERE p.class = '105' --ENDPOINT class_desc
          AND ep.type_desc = 'DATABASE_MIRRORING'
          AND sp.name = @SQLSvcAccount_current
          AND p.permission_name = 'CONNECT'
)
BEGIN
    SELECT @SQL = 'GRANT CONNECT ON ENDPOINT::' + @AGEndpointName + ' TO [' + @SQLSvcAccount_current + '];';
IF @Debug = 1
BEGIN
PRINT @SQL;
END
ELSE
BEGIN
EXECUTE (@SQL);
END
END;
ELSE IF @SQLSvcAccount_current <> @SQLSvcAccount
BEGIN
    SELECT @SQL = 'GRANT CONNECT ON ENDPOINT::' + @AGEndpointName + ' TO [' + @SQLSvcAccount + '];';
IF @Debug = 1
BEGIN
PRINT @SQL;
END
ELSE
BEGIN
EXECUTE (@SQL);
END
END;
/* Verify Endpoint Owner is properly set*/SELECT 
'Pre-Set Endpoint Owner' AS ProcessStep
, ep.endpoint_id
, p.class_desc
, p.permission_name
, ep.[name] AS EndpointName
, sp.[name] AS Grantee
, ep.[type_desc]
, ca.service_account
, SUSER_NAME(dme.principal_id) AS EndpointOwner
, CASE dme.[state]
WHEN 0 THEN 'STARTED'
WHEN 1 THEN 'STOPPED'
WHEN 2 THEN 'DISABLED'
ELSE 'ANARCHY'
END AS EndpointState
, te.[port] AS EndpointPort
, te.protocol_desc
FROM sys.server_permissions p
INNER JOIN sys.endpoints ep
ON p.major_id = ep.endpoint_id
INNER JOIN sys.tcp_endpoints te
ON te.endpoint_id = ep.endpoint_id
INNER JOIN sys.database_mirroring_endpoints dme
ON ep.endpoint_id = dme.endpoint_id
INNER JOIN sys.server_principals sp
ON p.grantee_principal_id = sp.principal_id
CROSS APPLY
(
SELECT dss.service_account
FROM sys.dm_server_services dss
WHERE dss.servicename LIKE 'SQL Server (%'
) ca
WHERE p.class = '105'
AND ep.type_desc = 'DATABASE_MIRRORING'
AND dme.[name] = @AGEndpointName;
IF NOT EXISTS (
SELECT 1/0
    FROM sys.database_mirroring_endpoints dme
WHERE dme.[name] = @AGEndpointName
)
BEGIN
SELECT @SQL = 'ALTER AUTHORIZATION ON ENDPOINT::' + dme.[name] + ' TO ' + @EndpointOwner + ';'
FROM sys.database_mirroring_endpoints dme
WHERE dme.[type_desc] = 'DATABASE_MIRRORING'
AND dme.[name] = @AGEndpointName;
IF @Debug = 1
BEGIN
PRINT @SQL;
END
ELSE
BEGIN
EXECUTE (@SQL);
END
END
ELSE IF @EndpointOwner_current <> @EndpointOwner
BEGIN
SELECT @SQL = 'ALTER AUTHORIZATION ON ENDPOINT::' + dme.[name] + ' TO ' + @EndpointOwner + ';'
FROM sys.database_mirroring_endpoints dme
WHERE dme.[type_desc] = 'DATABASE_MIRRORING'
AND dme.[name] = @AGEndpointName;
IF @Debug = 1
BEGIN
PRINT @SQL;
END
ELSE
BEGIN
EXECUTE (@SQL);
END
END
/* Final Config Check */SELECT 
'Final Configs' AS ProcessStep
, ep.endpoint_id
, p.class_desc
, p.permission_name
, ep.[name] AS EndpointName
, sp.[name] AS Grantee
, ep.[type_desc]
, ca.service_account
, SUSER_NAME(dme.principal_id) AS EndpointOwner
, CASE dme.[state]
WHEN 0 THEN 'STARTED'
WHEN 1 THEN 'STOPPED'
WHEN 2 THEN 'DISABLED'
ELSE 'ANARCHY'
END AS EndpointState
, te.[port] AS EndpointPort
, te.protocol_desc
FROM sys.server_permissions p
INNER JOIN sys.endpoints ep
ON p.major_id = ep.endpoint_id
INNER JOIN sys.tcp_endpoints te
ON te.endpoint_id = ep.endpoint_id
INNER JOIN sys.database_mirroring_endpoints dme
ON ep.endpoint_id = dme.endpoint_id
INNER JOIN sys.server_principals sp
ON p.grantee_principal_id = sp.principal_id
CROSS APPLY
(
SELECT dss.service_account
FROM sys.dm_server_services dss
WHERE dss.servicename LIKE 'SQL Server (%'
) ca
WHERE p.class = '105'
AND ep.type_desc = 'DATABASE_MIRRORING';

GetOut:

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.