Environment

-> JBSAG1 and JBSAG2 are Azure IaaS virtual machines provisioned on East US region. Azure IaaS virtual machine JBSAG3 is provisioned on West US region.

-> Azure virtual machine JBSAG1, JBSAG2 and JBSAG3 are part of Windows cluster JBSWikiClust without shared storage. Azure virtual machine JBSAG1 and JBSAG2 will be configured using Synchronous commit with automatic failover. JBSAG3 will be configured using Asynchronous commit with Manual failover.

-> Resources related to Virtual Machine JBSAG1 and JBSAG2 will be hosted on a Resource Group called JBSWiki and resources related to virtual machine JBSAG3 will be hosted on resource group JBSWiki_WestUS.

-> This environment will have a database called JBSWiki added to availability group JBSAG with Listener JBSAPP.

-> Database Servers JBSAG1 and JBSAG2 will be configured using common Virtual network and subnet configuration. Database Server JBSAG3 will have a separate Virtual network and subnet configuration.

More articles on this Series,

-> Always On Availability Group Configuration on Azure IaaS Virtual Machine utilizing Multi Subnet
-> Configure appropriate Virtual Network and Subnet for Azure IaaS Virtual Machine – Part2
-> Deploy required Azure IaaS Virtual Machine – Part3
-> Configure Domain Controller and Windows Cluster on appropriate Azure IaaS Virtual Machine – Part4
-> Configure Azure Load Balancer – Part5
-> Configure Always On Availability Group with Listener – Part6

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

In this series of articles, we will be discussing on creating an Always On Availability Group on Azure IaaS Virtual Machine utilizing Multi Subnet.

-> Always On Availability Group Configuration on Azure IaaS Virtual Machine utilizing Multi Subnet
-> Always On Availability Group Environment on Azure IaaS Virtual Machine Introduction – Part1
-> Configure appropriate Virtual Network and Subnet for Azure IaaS Virtual Machine – Part2
-> Deploy required Azure IaaS Virtual Machine – Part3
-> Configure Domain Controller and Windows Cluster on appropriate Azure IaaS Virtual Machine – Part4
-> Configure Azure Load Balancer – Part5
-> Configure Always On Availability Group with Listener – Part6

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Environment

-> I was tasked to setup Always on Availability group on a newly provisioned database server.

-> Everything went fine from the “New Availability Group Wizard”, except the last step which was to “Join the Database to Availability Group”. This last step was executing indefinitely.

-> Checked for blockings and could not see anything either on JBSAG1 and JBSAG2.

->Checked SQL Server errorlog,

JBSAG1
_{^{2022-03-02 16:10:40.840 Logon Database Mirroring login attempt by user ‘JBSWIKI\JBSAG2$.’ failed with error: ‘Connection handshake failed. The login ‘JBSWIKI\JBSAG2$’ does not have CONNECT permission on the endpoint. State 84.’. [CLIENT: 192.168.1.4]}}

JBSAG2

_{^{2022-03-02 16:13:26.090 spid73 State information for database ‘JBDB’ – Hardened Lsn: ‘(38:114176:1)’ Commit LSN: ‘(0:0:0)’ Commit Time: ‘Mar 2 2022 3:57PM’
2022-03-02 16:13:26.100 spid73 State information for database ‘JBDB’ – Hardened Lsn: ‘(38:114176:1)’ Commit LSN: ‘(0:0:0)’ Commit Time: ‘Mar 2 2022 3:57PM’
2022-03-02 16:13:26.100 spid73 Starting up database ‘JBDB’.
2022-03-02 16:13:26.110 spid73 The database ‘JBDB’ is marked RESTORING and is in a state that does not allow recovery to be run.}}

-> Executed below query on both JBSAG1 and JBSAG2,

select r.replica_server_name, r.endpoint_url,
rs.connected_state_desc, rs.last_connect_error_description,
rs.last_connect_error_number, rs.last_connect_error_timestamp
from sys.dm_hadr_availability_replica_states rs join sys.availability_replicas r
on rs.replica_id=r.replica_id

JBSAG1

JBSAG2

-> Below is the error message,

An error occurred while receiving data: ’24(The program issued a command but the command length is incorrect.)’.

-> It is clear that that there is a connection issue where JBSAG2 is not able to connect to JBSAG1 when using the SeverName (having $ symbols at the end) to communicate. The connection uses SeverName (having $ symbols at the end) when the SQL Service account is Local System and that is when I realized that I have not used the appropriate Service account on JBSAG2.

-> I changed the SQL Service account on JBSAG2 to JBSWIKI\jbswiki and the issue resolved right away.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.