SQL Server 2012数据库加入HA组超时及节点同步故障求助

Hey there, let's work through this sync issue together. It sounds like you're dealing with an Always On Availability Group (AG) scenario where a network-induced failover left your secondary node out of sync, and the "Join Only" wizard is timing out after 10 minutes. Here's a step-by-step breakdown of fixes to try:

1. Verify Network Connectivity First #

Since the original issue started with network problems, let's rule out lingering connectivity gaps:

• Test port communication between nodes: Run Test-NetConnection -ComputerName <SecondaryNodeName> -Port <AGListenerPort> (default AG port is 5022, but double-check your setup) on both the primary and secondary nodes. If this fails, your firewall (Windows or hardware) is likely blocking the port—double-check inbound/outbound rules for the AG and SQL Server ports.
• Ensure the SQL Server service accounts have network access to each other's file shares (if using manual seeding later) and can resolve each other via DNS.

2. Clear Backed-Up Transaction Logs on the Primary #

A huge unbacked-up transaction log can slow down seeding to a crawl, causing timeouts:

• Check log space usage with:

  DBCC SQLPERF(LOGSPACE)
  

• If your target database's log usage is high, take an immediate log backup to free up space:

  BACKUP LOG <YourDatabaseName> TO DISK = 'C:\Backups\<YourDatabaseName>_EmergencyLog.bak' WITH INIT
  

• Check the log send queue size on the primary to confirm backlog:

  SELECT 
      database_name = db_name(drs.database_id),
      log_send_queue_size,
      log_send_rate
  FROM sys.dm_hadr_database_replica_states drs
  WHERE drs.database_id = DB_ID('<YourDatabaseName>')
  

  A large queue means you might need to temporarily prioritize network bandwidth for AG traffic.

3. Increase Timeout Settings & Use T-SQL Instead of the Wizard #

The GUI wizard has strict default timeouts—switch to T-SQL with extended timeouts:

• First, bump the remote query timeout on both nodes (you can revert later):

  sp_configure 'remote query timeout', 3600; -- Set to 1 hour
  RECONFIGURE;
  

• Then, execute the join operation manually. If you're using automatic seeding:

  -- On primary node
  ALTER AVAILABILITY GROUP <YourAGGroupName> ADD DATABASE <YourDatabaseName>;
  
  -- On secondary node
  ALTER AVAILABILITY GROUP <YourAGGroupName> 
  MODIFY REPLICA ON N'<SecondaryNodeName>' 
  WITH (SEEDING_MODE = AUTOMATIC);
  
  ALTER DATABASE <YourDatabaseName> SET HADR AVAILABILITY GROUP = <YourAGGroupName>;
  

4. Check Secondary Node Resource Bottlenecks #

Timeouts often happen if the secondary can't keep up with seeding due to resource constraints:

• Open Task Manager to check CPU, memory, and disk IO usage. If disk reads/writes are maxed out, free up space or temporarily pause non-critical workloads.
• Check the SQL Server error log on the secondary (SSMS > Management > SQL Server Logs) for clues—look for errors related to disk access, permission issues, or HADR component failures.

5. Fall Back to Manual Seeding (Most Reliable for Large Databases) #

If automatic seeding keeps timing out, manual seeding avoids network-related bottlenecks during initial sync:

1. On the primary node, take a full backup + log backup:

   BACKUP DATABASE <YourDatabaseName> TO DISK = 'C:\Backups\<YourDatabaseName>_Full.bak' WITH INIT;
   BACKUP LOG <YourDatabaseName> TO DISK = 'C:\Backups\<YourDatabaseName>_Log.bak' WITH INIT;
   

2. Copy both backup files to the secondary node (use a fast network share or external drive).
3. On the secondary node, restore the backups with NORECOVERY:

   RESTORE DATABASE <YourDatabaseName> FROM DISK = 'C:\Backups\<YourDatabaseName>_Full.bak' WITH NORECOVERY, REPLACE;
   RESTORE LOG <YourDatabaseName> FROM DISK = 'C:\Backups\<YourDatabaseName>_Log.bak' WITH NORECOVERY;
   

4. Join the database to the AG:

   ALTER DATABASE <YourDatabaseName> SET HADR AVAILABILITY GROUP = <YourAGGroupName>;
   

内容的提问来源于stack exchange，提问作者mahabuba.mu