Fixing SQL Server Error 35250: The Replica is Not Joined in a Hybrid Cloud AlwaysOn Setup
Quick Fix Summary
TL;DRRe-join the replica to the availability group using T-SQL.
Error 35250 indicates a secondary replica in an Always On Availability Group is not joined, preventing data synchronization and failover capability.
Diagnosis & Causes
Recovery Steps
Step 1: Verify Replica State and Connectivity
Confirm the replica is not joined and check network/firewall connectivity between all nodes.
-- On the PRIMARY replica, check AG status
SELECT r.replica_server_name, r.endpoint_url, rs.role_desc, rs.operational_state_desc, rs.connected_state_desc
FROM sys.dm_hadr_availability_replica_states rs
JOIN sys.availability_replicas r ON rs.replica_id = r.replica_id
WHERE rs.is_local = 0;
-- Test basic connectivity from the problematic replica (run on replica OS)
Test-NetConnection <Primary_Replica_IP> -Port 5022Step 2: Re-join the Replica to the Availability Group
Connect to the secondary replica instance and join it to the availability group.
-- Execute on the SECONDARY replica instance
ALTER AVAILABILITY GROUP [YourAGName] JOIN;
GO
ALTER AVAILABILITY GROUP [YourAGName] GRANT CREATE ANY DATABASE;
GOStep 3: Resume Data Movement on the Replica
After joining, resume data synchronization for each database in the AG.
-- Execute on the SECONDARY replica for each database
ALTER DATABASE [YourDatabase] SET HADR RESUME;
GO
-- Verify synchronization state
SELECT database_name, synchronization_state_desc, synchronization_health_desc
FROM sys.dm_hadr_database_replica_states;Step 4: Validate Hybrid Cloud Endpoints and Certificates
In hybrid setups, ensure the database mirroring endpoint is accessible and certificates are valid on all replicas.
-- Check endpoint status on all replicas
SELECT type_desc, state_desc, port FROM sys.tcp_endpoints WHERE type_desc = 'DATABASE_MIRRORING';
-- Verify certificate expiration (run on each replica)
SELECT name, expiry_date FROM sys.certificates WHERE name LIKE '%HADR%';
-- If using AWS/Azure, ensure security group/NSG allows port 5022 between all node IPs.Step 5: Restart the SQL Server Service on the Replica (If Needed)
If the join operation fails, a controlled service restart may clear transient state issues.
# On Windows (Run as Admin on the replica server)
Restart-Service -Name 'MSSQLSERVER' -Force
# On Linux
sudo systemctl restart mssql-serverArchitect's Pro Tip
"In hybrid cloud setups, this error often occurs after a network blip or cloud provider maintenance that resets connectivity. Always check cloud security groups (AWS) or NSGs (Azure) first—they silently drop connections without logging on the SQL Server side."
Frequently Asked Questions
Will re-joining the replica cause data loss?
No. The re-join operation does not delete data. Once rejoined, synchronization will resume from the last hardened LSN, and the replica will catch up automatically.
Do I need to restart the primary replica?
No. This operation is performed entirely on the affected secondary replica. The primary remains online.
The join command fails with a permission error. What's wrong?
Ensure you are connected to the secondary replica instance (not the primary) and are using a login with ALTER ANY AVAILABILITY GROUP permission or higher (like sysadmin). Also verify the AG name is correct.