SQL Server Always On Series - Failed to join Database to Always On Availability Group

In this video, we will be discussing a common issue that occurs when trying to join a database to an Always On Availability Group in SQL Server. This issue can be frustrating for database administrators, but fortunately, there are solutions available.

First, let's briefly explain what Always On Availability Groups are. They are a high availability and disaster recovery solution provided by SQL Server that allow for the replication of databases across multiple servers. This ensures that if one server goes down, another server can seamlessly take over with minimal downtime.

Now, let's discuss the issue at hand. When attempting to join a database to an Availability Group, it's possible to receive an error message that says the join failed. This can happen for a variety of reasons, but one of the most common causes is that port 5022 is not open.

In order to fix this issue, you will need to ensure that port 5022 or Always on Endpoint port is open on all the database server participating on Always On Availability group.

In conclusion, while encountering issues when trying to join a database to an Always On Availability Group in SQL Server can be frustrating, it's important to remember that there are solutions available. By ensuring that the Always on Endpoint is open, you can successfully add the database to the group and ensure high availability and disaster recovery for your system.

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.

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.

Always On: AG integrity check failed for AG 'JBSAG' with error 41044, severity 16, state 1.

Connection attempt failed with error: '10060(A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)'.

Failed to join the database 'JBSWiki' to the availability group 'JBSAG' on the availability replica 'JBSAG2'. (Microsoft.SqlServer.Smo)

Database 'JBSWiki' does not exist. Make sure that the name is entered correctly. (Microsoft SQL Server, Error: 911)

JBSAG1

Automatic seeding of availability database 'JBSWiki' in availability group 'JBSAG' failed with a transient error. The operation will be retried.
A connection for availability group 'JBSAG' from availability replica 'JBSAG1' with id [9E27DF0A-BC94-4C8B-8B1D-9D958D2D562F] to 'JBSAG2' with id [2C0D9AB6-020C-41D1-A736-18CB1F61C0DC] has been successfully established. This is an informational message only. No user action is required.
[DbMgrPartnerCommitPolicy::SetReplicaInfoInAg] DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 2C0D9AB6-020C-41D1-A736-18CB1F61C0DC:7:1
[DbMgrPartnerCommitPolicy::SetReplicaInfoInAg] DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 2C0D9AB6-020C-41D1-A736-18CB1F61C0DC:7:4

JBSAG2

Error: 41145, Severity: 16, State: 1.
Cannot join database 'JBSWiki' to availability group 'JBSAG'. The database has already joined the availability group. This is an informational message. No user action is required.
A connection timeout has occurred while attempting to establish a connection to availability replica 'JBSAG1'. Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.
The state of the local availability replica in availability group 'JBSAG' has changed from 'SECONDARY_NORMAL' to 'RESOLVING_NORMAL'.
Always On Availability Groups connection with primary database terminated for secondary database 'JBSWiki' on the availability replica 'JBSAG1' with Replica ID.
This is an informational message only. No user action is required.
The database 'JBSWiki' is marked RESTORING and is in a state that does not allow recovery to be run.
The state of the local availability replica in availability group 'JBSAG' has changed from 'RESOLVING_NORMAL' to 'NOT_AVAILABLE'.
The state of the local availability replica in availability group 'JBSAG' has changed from 'NOT_AVAILABLE' to 'RESOLVING_NORMAL'.
The state of the local availability replica in availability group 'JBSAG' has changed from 'RESOLVING_NORMAL' to 'SECONDARY_NORMAL'.
A connection timeout has occurred while attempting to establish a connection to availability replica 'JBSAG1'.
Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.
Error: 911, Severity: 16, State: 1.
Database 'JBSWiki' does not exist. Make sure that the name is entered correctly.