Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 1,644,772 hits
  • Select GETDATE()

    July 2019
    M T W T F S S
    « Apr    
  • Advertisements

Posts Tagged ‘AlwaysOn’

Solution : The connection to the primary replica is not active. The command cannot be processed.

Posted by blakhani on July 1, 2014

It has been close to a year since I published my first book (SQL Server 2012 AlwaysOnPaperback, Kindle) and since then I have been contacted by many DBA to troubleshoot various issue related to AlwaysOn Availability Groups. One of the most common error which I have seen is as below.

Msg 35250, Level 16, State 7, Line 1
The connection to the primary replica is not active. The command cannot be processed.

This error mostly appears when we try to join the database to availability group. by UI, T-SQL or PowerShell.


While trying to create new Availability Group, we might received below and “join” step would fail.


Here is the message in text format.

TITLE: Microsoft SQL Server Management Studio
Joining database on secondary replica resulted in an error.  (Microsoft.SqlServer.Management.HadrTasks)
Failed to join the database ‘Production’ to the availability group ‘ProductionAG’ on the availability replica ‘SRV2’. (Microsoft.SqlServer.Smo)
For help, click:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The connection to the primary replica is not active.  The command cannot be processed. (Microsoft SQL Server, Error: 35250)
For help, click:



Msg 35250, Level 16, State 7, Line 1
The connection to the primary replica is not active. The command cannot be processed.


Add-SqlAvailabilityDatabase -Path "SQLSERVER:\SQL\SRV2\DEFAULT\AvailabilityGroups\ProductionAG" -Database "Production"

Add-SqlAvailabilityDatabase : The connection to the primary replica is not active.  The command cannot be processed.
At line:1 char:28
+ Add-SqlAvailabilityDatabase <<<<  -Path "SQLSERVER:\SQL\SRV2\DEFAULT\AvailabilityGroups\ProductionAG" -Database "Production"
     + CategoryInfo          : InvalidOperation: (:) [Add-SqlAvailabilityDatabase], SqlException
     + FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.Hadr.AddSqlAvailabilityGroupDatabaseCommand


I have always suggested them to start looking at errorlog and check what is the error which most of the DBA have reported.

2014-06-30 17:29:33.500 Logon        Database Mirroring login attempt by user ‘HADOMAIN\SRV1$.’ failed with error: ‘Connection handshake failed. The login ‘HADOMAIN\SRV1$’ does not have CONNECT permission on the endpoint. State 84.’.  [CLIENT:]

In above message, HADOMAIN is my domain name and SRV1 is the host name of SQL Server hosting primary replica.

Here is what have solved the issue for them.

  • Change SQL Server service account to a domain account and grant connect permission to the instances. If we are using different domain accounts on each replica then we need to add service accounts of all secondary replicas to primary replica logins.
  • If we are using non domain account (like LocalSystem or NT Service\MSSQLServer account) as service account and we can’t change it to domain account then we need to create machine accounts as login and grant connect permission. In our case machine name is SRV1 so machine account is HADOMAIN\SRV1$ (notice that $ at the end is a computer account)


    create login [HADOMAIN\SRV1$] from windows;
    grant connect on endpoint::Mirroring to [HADOMAIN\SRV1$];

Note: Endpoint Name might be different. We need to pick as per below image: If you have configured via UI earlier, it should be Hadr_endpoint


If you are running firewall, please make sure that port used by availability group is not blocked. We can easily find port using below command:

te.port AS [ListenerPort],
te.is_dynamic_port AS [IsDynamicPort],
ISNULL(te.ip_address,'''') AS [ListenerIPAddress],
CAST(case when te.endpoint_id < 65536 then 1 else 0 end AS bit) AS [IsSystemObject]
sys.endpoints AS e
INNER JOIN sys.tcp_endpoints AS te ON te.endpoint_id=e.endpoint_id

Make sure that you have added exception for the port in firewall.

This is already documented in books online


If any server instances that are hosting the availability replicas for an availability group run as different accounts, the login each account must be created in master on the other server instance. Then, that login must be granted CONNECT permissions to connect to the database mirroring endpoint of that server instance.


Hope this would help you.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisements

    Posted in AlwaysOn, Troubleshooting | Tagged: , , | Leave a Comment »

    Help: AlwaysOn Setup Failing with Error! What should I do?

    Posted by blakhani on September 26, 2011

    While preparing for the Virtual TechDays Session, I received an email where AlwaysOn Setup was failing on Windows Server 2008 with below error message while attempting to use the create new availability group wizard in SQL Server Management Studio.


    Here is the text for message for better search.

    The local node is not part of quorum and is therefore unable to process this operation. This may be due to one of the following reasons:

    • The local node is not able to communicate with the WSFC cluster.

    • No quorum set across the WSFC cluster.


    My research of pre-requisites revealed that we need below patch to be available on the operating to make it work.
    1. KB 976097 ( Hotfix to add support for asymmetric storages to the Failover Cluster Management MMC snap-in for a failover cluster that is running Windows Server 2008 or Windows Server 2008 R2)

    – Supports SQL Server Failover Cluster Instances with AlwaysOn Availability Groups

    2. KB 2494036 (A hotfix is available to let you configure a cluster node that does not have quorum votes in Windows Server 2008 and in Windows Server 2008 R2)

    – Supports configuring optimal quorum with non-automatic failover targets

    This is already documented in

    See you at Virtual TechDays!

    Balmukund Lakhani
    Twitter @blakhani

    Posted in AlwaysOn, Denali | Tagged: , | Leave a Comment »