Sql-server – SQL Server 2017 Availability Group Deployment – Some availability replicas are not synchronizing data

availability-groupssql serversql-server-2017

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!

Best Answer

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....

Related Solutions

SQL Server 2012 – Fix AlwaysOn Availability Group Automatic Failover

If I disconnect DEV-AWEB5

Define "disconnect", if you will. My guess is you kept the box up but took SQL Server down.

I cannot connect to the Group Listener (DevListener), but I can ping it and it will respond to my ping

That's because the listener is just a virtual network name (VNN) within the WSFC cluster resource group for the represented availability group. Your DEV_AWEB5 node still owns the cluster resource group, but it's just the AG cluster resource most likely that is in a failed state. The VNN must still be online (expected behavior). It's simply pointing to whatever node is owning that resource group (in this case, DEV-AWEB5). In fact, if you had PowerShell remoting enabled, and you ran the following:

Invoke-Command -ComputerName "YourListenerName" -ScriptBlock { $env:computername }

Likewise, if you can RDP into DEV-AWEB5 (provided you have the capability and accessibility, etc.) then you'd be able to RDP using the listener name (mstsc /v:YourListenerName). It's just a VNN.

The return of that would be the computer name of your owning node.

By all of your symptoms, I'd be willing to bet that you've reached your failover threshold. The failover threshold determines how many times the cluster will attempt to failover your resource group in a specified time period. The default of these values max failovers n - 1 (where n is the count of nodes) in a period of 6 hours. You can see that through the following WSFC PowerShell command:

Get-ClusterGroup -Name "YourAgName" |
    Select-Object Name, FailoverThreshold, FailoverPeriod

That just gives you the settings (which you can modify if you so choose, of course).

The best way to prove that this is the case for you, you would need to generate the cluster log (the system event logs only go into detail as far as " has failed", or something like that).

Get-ClusterLog -Node "YourClusterNode" -TimeSpan <amount_of_minutes_since_failure>

That'll by default get put into the "C:\Windows\Cluster\Reports" folder, and the file is called "Cluster.log".

If you were to open up that cluster log, you should be able to find the following string in there, indicating exactly what happened and why it happened:

Not failing over group [YourClusterGroupName], failoverCount [# of failovers], failover threshold [failover threshold value], nodeAvailCount [node available count].

The above message is simply WSFC telling you that it will not failover your group because it's happened too much (you hit the threshold).

Why does this happen? Simply to prevent the Ping-Pong effect of cluster resources going back and forth too frequently between nodes.

Whereas this would be common to hit these thresholds in failover testing, in production it would typically point to a problem that should be investigated.

Sql-server – SQL Server 2012 AlwaysOn Availability Group

You can find here some design pattern for always on solutions http://blogs.msdn.com/b/sqlcat/archive/2013/11/20/sql-server-2012-alwayson-high-availability-and-disaster-recovery-design-patterns.aspx

just note that for automatic fail-over you may want to have synchronous commit so you dont loose data. also to have automatic fail-over you must have node majority to decide to do the fail-over. this mean you must have extra voter in the cluster. (eg' File Share Witness or another node)

this will prevent the problem known as Split Brain problem (https://en.wikipedia.org/wiki/Split-brain_(computing))

to summarize, this is very common need to have local automatic fail-over (because local LAN can serve synchronous mode) and another replica off-site with manual fail-over and asynchronous commit which can cause data loss when main site failed and not all data replicated yet to the another site.

I hope this have been informative for you. I will be glad to add information if you have any questions about that implementation.

Best Answer

Related Solutions

SQL Server 2012 – Fix AlwaysOn Availability Group Automatic Failover

Sql-server – SQL Server 2012 AlwaysOn Availability Group

Related Question