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.
-> 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.
-> I was testing the failover of above setup, changed the commit mode to JBSAG3 to Synchronous and tried a failover from JBSAG1 to JBSAG3. It failed with below error,
TITLE: Microsoft SQL Server Management Studio Manual Failover failed (Microsoft.SqlServer.Management.HadrTasks) ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Failed to bring availability group ‘JBSAG’ online. The operation timed out. If this is a Windows Server Failover Clustering (WSFC) availability group, verify that the local WSFC node is online. Then verify that the availability group resource exists in the WSFC cluster. If the problem persists, you might need to drop the availability group and create it again. (Microsoft SQL Server, Error: 41131)
-> This issue occurs when [NT AUTHORITY\SYSTEM] account is not available or if the account lacks the necessary permissions on the SQL Server Instance.
-> If [NT AUTHORITY\SYSTEM] account is not available. Create it using below command,
USE [master]
GO
CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
-> Provide appropriate permission to [NT AUTHORITY\SYSTEM] using below command,
GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM]
GO
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM]
GO
GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM]
GO
-> Once above permission was provided, I was able to failover to JBSAG3 without any issues.
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.
-> 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.
