About Me

My photo
I am MCSE in Data Management and Analytics with specialization in MS SQL Server and MCP in Azure. I have over 13+ years of experience in IT industry with expertise in data management, Azure Cloud, Data-Canter Migration, Infrastructure Architecture planning and Virtualization and automation. Contact me if you are looking for any sort of guidance in getting your Infrastructure provisioning automated through Terraform. I sometime write for a place to store my own experiences for future search and read by own blog but can hopefully help others along the way. Thanks.

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: ,