I had an issue where I was trying to create a new Availability Group. During the creation process, it sits at the “Joining ‘DBName’ to availability group ‘AGName’ on ‘servername’” step for a very long time. You are either forced to cancel and delete the AG or wait until it errors out. You get the same results whether you add the listener portion in the creation or not. You also get the same results when you try it going the other direction where the primary is on the other server. In Active Directory, I verified the listener was disabled and the cluster had full control of the listener. You could ping each server and it resolves the name to the IP address. This was done from each server. I could connect to both instances from one server within SSMS as well.
The picture shows the progress of creating the AG without creating a listener and where it sort of hangs before erroring out.
The picture shows the progress of creating the AG as well as the creation of the listener and where it sort of hangs before erroring out.
Error
If you let the wizard complete without canceling it, it will show the following errors.
The wizard finished with errors:
Error: Attempting to join the availability group resulted in an error.
Failed to join the database ‘DBName’ to the availability group ‘AGName’ on the availability replica ‘servername’.
An exception occurred while executing a Transaction-SQL statement or batch.
The connection to the primary replica is not active. The command cannot be processed.
You may find some of these errors within the SQL Log:
A connection timeout has occurred while attempting to establish a connection to availability replica ‘Servername’ with id [79C64582-852F-49A5-99AC-418E6D74B1FE]. Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.
The lease worker of availability group ‘AGName’ is now sleeping the excess lease time (164828 ms) supplied during online. This is an informational message only. No user action is required.
The fix
Even though I could ping the servers when I tried running the command nslookup, it resolved one servername but not the other. This told me it could not resolve the name via DNS.
You need to look at the DNS Manager to make sure you can see both servers in DNS. In my case, only one server was showing (the one that resolved when I ran nslookup). Most DBAs do not have access to look at DNS so you will have to work with your system administrator.
The SA or someone that has permission to create records within DNS will need to add a static Host(A) entry for the missing server.
After adding a new Host(A) entry for the missing server in DNS, run nslookup again against the server that was just added into DNS. It should now resolve. If your environment has multiple DNS servers, it may take some time for those changes to replicate so the entry may not resolve right away.
If you did not cancel the AG creation, the AG will be created but the DB may need to be joined to the Availability Group. Or you could delete the AG and recreate it.
To add the database to the AG, right click on the database that has an exclamation (!) mark in front of it and click “Join to Availability Group…“
The database should now be joined to the AG. If the database had lots of transactions since the issue first showed up you may need to apply transaction logs before joining it.
Adding transaction log backups to a database in Restoring State
Adding transaction log backups to a Database if the join fails. If you get an error that states the following, you need to load the transaction log backups. Error: The mirror database, “DBName”, has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database.
On the secondary expand Availability Groups, your AG, Availability Databases. You will see your database and it will have a Yellow (!) exclamation mark.
Look at your backup chain, find your transaction log backups and load them. You can script out the restore or use the GUI to load the transaction logs. Via the GUI, on the secondary, right click on the DB that shows Restoring, click Tasks, Restore, Transaction Log… Once you restored all of the transaction logs, keep the database in RESTORE IN NORECOVERY. The database has to be in Restoring status in order to join it.
To add the database to the AG, right click on the database that has an exclamation (!) mark in front of it and click “Join to Availability Group…“.
If you loaded all of the transaction logs, the database will join the AG successfully.
So, in this instance, a missing or deleted DNS entry caused the issue of not allowing a server to be added into an Availability Group. Don’t assume because you can ping the server, a DNS record exists. NSLookup will ultimately tell you if it exists or not. Work with your SA to get that record added back. But if multiple DNS servers exist it may take a little time for those changes to replicate to each DNS server.