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!

Unable to access availability database 'XXXX' because the database replica is not in the PRIMARY or SECONDARY role.


Issue:- Sometime issue comes like, when you try to access a database in AlwaysOn it gives error like "Unable to access availability database 'AdventureWorks2014' because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later. "
Error: 983


When you try to expand AlwaysOn High Availability Folder it will not expand..



TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Unable to access availability database 'AdventureWorks2014' because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later. (Microsoft SQL Server, Error: 983)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.4001&EvtSrc=MSSQLServer&EvtID=983&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------


Troubleshooting steps:- In order to troubleshoot this issue..
Go to 
1. Cluster Administrator on same Node
2. Connect to the Cluster
3.Ensure you are connecting to correct clusterName
4. In my case Cluster core group resources was down as shown in below image.
5  Try to bring Cluster core group resource online.


6. if Cluster core resources start immediately its fine, proceed with step 7, otherwise check check  clusterevent
below error may be appearing..
The cluster Resource Hosting Subsystem (RHS) process was terminated and will be restarted. This is typically associated with cluster health detection and recovery of a resource. Refer to the System event log to determine which resource and resource DLL is causing the issue.


Now we will check system event viewer for error like why it gave error like Failed to bring the resource 'Cluster Name' online.

when i checked system event viewer i got below error..

Error No : 10028


Description:
DCOM was unable to communicate with the computer Win2k12Cluster.Adven.com using any of the configured protocols; requested by PID      ae4 (C:\Windows\system32\ServerManager.exe).


Before troubleshooting Error No 10028, check if all nodes in cluster are pinging from each other or not.
Restart Cluster service of all nodes.

in my case when i tried to restart cluster service i got below error..


This indicate Cluster service on my 2nd node is not coming up.

when i see eventviewer.system i get below error message again..
DCOM was unable to communicate with the computer Win2k12Cluster.Adven.com using any of the configured protocols; requested by PID      ae4 (C:\Windows\system32\ServerManager.exe).

 Resolution:-


  1. Click Start, and then click Run.
  2. Type wf.msc, and then click OK. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.
  3. In the console tree, click Inbound rules.
  4. In the list of firewall exception rules, look for COM+ Network Access (DCOM In).
  5. If the firewall exception rule is not enabled, in the details pane click Enable rule, and then scroll horizontally to confirm that the protocol is TCP and the LocalPort is 135. 
  6. Close Windows Firewall with Advanced Security.


Then kill the clussvc.exe  from task manager

I did repeat the same on another 2 nodes of cluster.

 and then i was able to bring cluster core group resoources online and AlwaysOn Availability group online.


This is how i did resolve this issue. it looks Cluster service of one of node gone exhausted and even after restart it was not coming up, so the approach was just to restart 2nd node of Cluster service, it looks after successful restart of 2nd node Cluster service and later everything came up.


Thanks for Reading..






Labels: , , , ,