About Me

My photo
I am an MCSE in Data Management and Analytics, specializing in MS SQL Server, and an MCP in Azure. With over 19+ years of experience in the IT industry, I bring expertise in data management, Azure Cloud, Data Center Migration, Infrastructure Architecture planning, as well as Virtualization and automation. I have a deep passion for driving innovation through infrastructure automation, particularly using Terraform for efficient provisioning. If you're looking for guidance on automating your infrastructure or have questions about Azure, SQL Server, or cloud migration, feel free to reach out. I often write to capture my own experiences and insights for future reference, but I hope that sharing these experiences through my blog will help others on their journey as well. Thank you for reading!

Errors related to Database Mirroring\AlwaysOn or HADR endpoint


Errors related to Database Mirroring or HADR endpoint

Few facts about HADR endpoint
Ø  Endpoints are objects which are used to receive connections from other server instances.
Ø  They use Transmission Control Protocol (TCP) to communicate.
Ø  Database Mirroring endpoints needs to be created manually just to participate in DB Mirroring\AlwaysOn sessions.
Ø  Listens on a unique TCP port number

Ø  By default, endpoint requires encryption of data. We can disable encryption. If encryption is disabled, data is never encrypted when getting transferred between replicas. An endpoint without configured without encryption, cannot connect to an endpoint that requires encryption.
Encryption algorithms: RC4, AES, AES RC4, RC4 AES
RC4 is a deprecated Algorithm and by default AES is used.

If there are issues with endpoint configuration or the account with which they are authenticated then the connectivity between replicas will be impacted. When we will analyse the SQL errorlogs from both replicas then we may see following errors:

Case 1: The SQL Server service account running with ‘Network Service’ account
Primary Replica
2018-04-09 08:47:39.99 spid36s     A connection timeout has occurred while attempting to establish a connection to availability replica 'WIN2K12-3' with id [2711ED1C-C852-487C-9AEC-C424C08AB4E7]. Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.
2018-04-09 08:47:56.33 spid35s     Recovery completed for database AdventureWorks2014 (database ID 5) in 25 second(s) (analysis 0 ms, redo 0 ms, undo 0 ms.) This is an informational message only. No user action is required.

Secondary Replica
2018-04-09 08:47:30.00 Logon       Database Mirroring login attempt by user 'ADVEN\WIN2K12-1$.' failed with error: 'Connection handshake failed. The login 'ADVEN\WIN2K12-1$' does not have CONNECT permission on the endpoint. State 84.'.  [CLIENT: 192.168.1.101]
2018-04-09 08:47:32.21 Logon       Database Mirroring login attempt by user 'ADVEN\WIN2K12-1$.' failed with error: 'Connection handshake failed. The login 'ADVEN\WIN2K12-1$' does not have CONNECT permission on the endpoint. State 84.'.  [CLIENT: 192.168.1.101]

Ø  In this example, the secondary replica is not able to authenticate the connection coming from primary replica because the account which is sender of requests is not added as a login in secondary replica. Here, the primary replica is running with Network Service account so the host computer account (Domainname\ComputerName$) must be created in master of each of the other servers. Then need to give connect permission to the endpoints.

USE [master]
GO

CREATE LOGIN [ADVEN\WIN2K12-1$] FROM WINDOWS
GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [ADVEN\WIN2K12-1$]
GO

Replace the login name as per the account coming for you in the errorlog

 Your error will solve

 Thanks for reading.. 





Labels: ,