Damn the Windows Firewall
Automatic seeding is a convenient way to add databases to an Always On Availability Group — no need to manually restore a backup on the secondary, the engine handles it. I was relying on it heavily after a SQL Server upgrade, which made it all the more frustrating when it quietly stopped working.
After the upgrade, I performed a failover test and verified everything was working okay; after failing back to the original primary, I added a database to the AG via the wizard and chose automatic seeding. The wizard completed successfully and reported the database had been added. I checked the secondary and found no database. Say what?
After spending a day looking for failover cluster errors, testing failovers, adding databases to both nodes of the AG, and assorted other tasks, I was still no closer to understanding why the secondaries wouldn’t sync up. Keep in mind, all of this worked just fine when the AG was still running as a SQL 2014 environment. I mentioned this to one of our IT folks because I was at a loss.
The Fix
One of our IT admins found the problem, though it took them a couple of hours. Port 5022 — the AG mirroring endpoint — was blocked on the secondary replica. But why was it blocked when it worked before?
When the server was originally built and SQL was installed, a rule was created in Windows Firewall for inbound connections. Instead of creating the rule by port number, the rule was created to allow access to the SQL Server executable. Do you see what happened?
When I upgraded to SQL 2019, the executable path changed from:
<disk>:\<install location>\MSSQL12.MSSQLSERVER\...to:
<disk>:\<install location>\MSSQL15.MSSQLSERVER\...The old firewall rule pointed to a path that no longer existed. Port 5022 was silently blocked.
The fix was simple: change the firewall rule to allow connections on the port rather than by executable. We could have repointed the rule to the new path, but the next upgrade would have caused the exact same problem.
Key Takeaways
- Windows Firewall rules tied to executable paths break silently when the executable moves — use port-based rules for SQL Server endpoints.
- Port 5022 is the default AG mirroring endpoint; block it and automatic seeding fails without an obvious error.
- When troubleshooting AG sync issues after an upgrade, check the firewall before assuming it’s a SQL Server problem.
- Be humble — every so often we manage to shoot ourselves in the foot.