Joining database on secondary replica resulted in an error

Hi Friends,

Always On Availability Groups provides an enterprise-level alternative to database mirroring and it gives organizations the ability to manually or automatically fail over a group of database(s) as a single unit with support for up to four secondary(s). Many organizations at present are opting for this excellent feature as their HA solution that was shipped with SQL 2012.

In this blog post I’ll discuss about an issue that one of my friends came across while implementing ‘Always On’ in one of his fresh Windows Server 2012 environments. At the final step of configuration the wizard failed to join respective database on secondary replica. It popped up following message box however I extracted error message for reference.

1_SQL_Server_Joining_database_on_secondary_replica_resulted_in_an_error

—————————————————————————————————————————————————-

Joining database on secondary replica resulted in an error. 

(Microsoft.SqlServer.Management.HadrTasks)

——————————

ADDITIONAL INFORMATION:

Failed to join the database ‘YourDatabaseName’ to the availability group ‘YourAvailabilityGroup’ on the availability replica ‘Servername\Instance’.

(Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3000.0+((SQL11_PCU_Main).121019-1325+)

&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExc

eptionText&LinkId=20476

——————————

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)

—————————————————————————————————————————————————-

As configuration failed, you will observe that Primary replica is Online however Secondary replica is in Offline state with a red mark on SSMS. When tried joining secondary replica to the availability group following message box was displayed by the wizard;

2_SQL_Server_Joining_database_on_secondary_replica_resulted_in_an_error2_SQL_Server_Joining_database_on_secondary_replica_resulted_in_an_error2_SQL_Server_Joining_database_on_secondary_replica_resulted_in_an_error2_SQL_Server_Joining_database_on_secondary_replica_resulted_in_an_error2_SQL_Server_Joining_database_on_secondary_replica_resulted_in_an_error2_SQL_Server_Joining_database_on_secondary_replica_resulted_in_an_error2_SQL_Server_Joining_database_on_secondary_replica_resulted_in_an_error

As mentioned before, we were trying this setup on a new Windows Server 2012 VM as such I thought of checking which port is used by Availability Group and when checked, it was the default port i.e. 5022.

3_SQL_Server_Joining_database_on_secondary_replica_resulted_in_an_error

Important point is to keep this port opened on all Nodes in the Windows Firewall and it wasn’t the case here. So, we did that i.e. allowed 5022 passing through firewall and end result was all good. Same requirement is mentioned on MSDN which you can read here.

 

Regards

Kanchan Bhattacharyya

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

 
Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

One Comment on “Joining database on secondary replica resulted in an error”

Leave a Reply

Your email address will not be published. Required fields are marked *