1

I'm in the process of configuring my first cluster using SQL Server 2017's availability groups. I have some experience with SQL Server failover clusters, but wanted to migrate to the Availability Group technology. I have created to Windows 2019 (DB01 and DB02) servers and configured them as a typical Microsoft Cluster. I have also installed SQL Server 2017 on both servers, using identical configurations. Data on drive D, logs on drive E, and backups (for now) on drive D:. So far, so good. The SQL services are running under a domain account named svcSQL. I've restarted both servers to verify that the service starts correctly.

I restored my two databases (called Lookup and Demo) on to DB01, and left DB02 empty. My intent was to let DB01 seed DB02. Using the Availability Group Wizard, I created a new availability group called Lookup, and added the DB02 server as a replica. DB02 shows up as a secondary, and I checked the "Automatic Failover" checkboxes for both servers. I left the "Endpoints" tab with the default values. All items on the "Backup Preferences" tab were unavailable, so I left them alone. I added a listener using a DNS name that was already created on the domain controller, and provided its matching IP address. I selected Automatic Seeding for the Data Synchronization panel.

The validation screen showed all green items, as did the Results panel. However, the dashboard showed that the availability group was disconnected. I did a "grant connect on endpoint..." to the SQL service account, which resolved that message. I have also temporary disabled the Windows firewall on both servers.

At this time, the dashboard shows a critical error and two warnings. The critical error states that the Availability group is not ready for automatic failover. That makes sense, as the synchronization process is not complete.

The two warnings are that "Some availability replicas are not synchronizing data" and "Some synchronous replicas are not synchronized." I gather the first warning is likely a result of the second warning.

I'm wondering if anyone can offer me some pointers on how to resolve this. The process seemed pretty straightforward, but something is definitely not right. I've tried creating the group a few times with variations on the steps above, but I seem to wind up with the same results each time.

Thanks in advance for any insights you can provide on this. I'll gladly provide any additional information that would help resolve this.

Thanks!

Improve this question
2

2 Answers 2

Reset to default
2

It turns out, the process was working normally, but I was missing a step at the end. Once I had configured the availability group, I didn't know that I then had to connect to the remote server and add the database. In the tutorials I had found online, none had included that step. It was a small but significant item.

Once I added the database on the remote server, the synchronization process started and the failover cluster worked as intended. Thanks to everyone who offered the suggestions on how to resolve this. I'll mark it as user error on my part. Sigh....

Improve this answer
0

Try connecting directly to DB02. If the Lookup and Demo databases are not present, then automatic seeding is failing for some reason, and you need to investigate why.

As an initial first step, you might just need to run this on the secondary:

ALTER AVAILABILITY GROUP yourAgName 
GRANT CREATE ANY DATABASE;

That's a step that's often missed, and is what allows the AG to create the secondary replica of your databases. Run this on the primary to attempt "restarting" seeding:

ALTER AVAILABILITY GROUP yourAgName 
MODIFY REPLICA ON 'DB02' 
WITH (SEEDING_MODE = AUTOMATIC)

If that doesn't work, check the SQL Server error log on both servers to see if there are any messages about automatic seeding, and why it isn't happening. Also, review all the detailed troubleshooting steps here:

Monitor automatic seeding availability group

In particular, sys.dm_hadr_automatic_seeding will give you an error message (failure_state_desc) which can be helpful.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.