SQL Server Always On Series - Always On Availability group database not synchronizing
SQL Server Always On Series - Always On Availability group database not synchronizing @jbswiki #sqlserver
If you are running a SQL Server Always On Availability Groups setup, you may come across the "The connection to the primary replica is not active. The command cannot be processed. (Microsoft SQL Server, Error: 35250)" error message. This error message indicates that the connection between the secondary replica and the primary replica in the availability group is not active. In this video, we will walk you through the steps to troubleshoot and fix this error message.
We will start by discussing the possible causes of the error message. These include network issues, SQL Server service not running, misconfiguration of the Always On Availability Group, and DNS issues. We will explain how each of these issues can cause the error message and what you can do to resolve them.
Next, we will go over specific troubleshooting steps. First, we will check the network connection between the primary replica and the secondary replica. We will then verify that the SQL Server service is running on both replicas. We will also check the configuration of the Always On Availability Group to make sure it is set up correctly.
If the previous steps do not resolve the issue, we will check DNS resolution to make sure the primary replica is being resolved correctly. We will also verify that the Windows Cluster service is running on both replicas. Additionally, we will check the configuration of the Always On Availability Group Listener to make sure it is set up correctly.
Finally, we will use the Always On Availability Group Dashboard to check the status of the group and see if there are any other issues that need to be addressed.
By the end of this video, you will have a better understanding of how to troubleshoot and fix the "The connection to the primary replica is not active" error message in SQL Server Always On Availability Groups. This video is perfect for SQL Server DBAs and developers who want to learn how to troubleshoot and resolve issues in their Always On Availability Group setup.
The connection to the primary replica is not active. The command cannot be processed. (Microsoft SQL Server, Error: 35250)
The data synchronization state of this availability database is unhealthy. On an asynchronous-commit availability replica, every availability database should be in the SYNCHRONIZING state. On a synchronous-commit replica, every availability database should be in the SYNCHRONIZED state.
The data synchronization state of this availability database is unhealthy. On an asynchronous-commit availability replica, every availability database should be in the SYNCHRONIZING state. On a synchronous-commit replica, every availability database should be in the SYNCHRONIZED state.
This secondary database is not joined to the availability group. The configuration of this secondary database is incomplete. For information about how to join a secondary database to an availability group, see SQL Server Books Online.
JBSAG1
A connection timeout has occurred on a previously established connection to availability replica 'JBSAG2' with id [2C0D9AB6-020C-41D1-A736-18CB1F61C0DC]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.
Always On Availability Groups connection with secondary database terminated for primary database 'JBSWiki' on the availability replica 'JBSAG2' with Replica ID: {2c0d9ab6-020c-41d1-a736-18cb1f61c0dc}. This is an informational message only. No user action is required.
Automatic seeding of availability database 'JBSWiki' in availability group 'JBSAG' failed with a transient error. The operation will be retried.
JBSAG2
The Database Mirroring endpoint has stopped listening for connections.
The Database Mirroring endpoint is in disabled or stopped state.
The connection between server instances 'JBSAG2' with id [2C0D9AB6-020C-41D1-A736-18CB1F61C0DC] and 'JBSAG1' with id [9E27DF0A-BC94-4C8B-8B1D-9D958D2D562F] has been disabled because the database mirroring endpoint was either disabled or stopped. Restart the endpoint by using the ALTER ENDPOINT Transact-SQL statement with STATE = STARTED.
Always On Availability Groups connection with primary database terminated for secondary database 'JBSWiki' on the availability replica 'JBSAG1' with Replica ID: {9e27df0a-bc94-4c8b-8b1d-9d958d2d562f}. This is an informational message only. No user action is required.
Check the status of Mirroring Endpoint,
:Connect JBSAG1
select @@servername,name, state_desc, port from sys.tcp_endpoints where type_desc='DATABASE_MIRRORING'
go
:Connect JBSAG2
select @@servername,name, state_desc, port from sys.tcp_endpoints where type_desc='DATABASE_MIRRORING'
go
:Connect JBSAG3
select @@servername,name, state_desc, port from sys.tcp_endpoints where type_desc='DATABASE_MIRRORING'
go
Start the Mirroring endpoint,
:Connect JBSAG2
alter endpoint [Hadr_endpoint] state = started
go
