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.
—————————————————————————————————————————————————-
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;
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.
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 Twitter | Follow me on FaceBook
nice post