Hi...

I'm trying to get my arms around when to use an SQL Server 2014 Availability Group. Here are the characteristics about my platform:

2 physical servers (Windows Server 2012 / SQL Server 2014)

Both servers connected to same LAN

External SAN storage connected DIRECTLY to each physical server via fibr3-channel. (No fibre-channel switch)

Database resides on SAN storage.

I've set up a failover cluster between the 2 physical servers.

I've created a high availability group with a Primary/Secondary and synchronization.

Both Primary/Secondary are green and show synchronized. The concern I have is that the Primary says Synchronizing (No Data Loss) and the Secondary says Not Synchronizing (Data Loss). When I use the Failover Wizard to failover, it tells me that I will have data loss on the Secondary.

So my questions are these, do you need more than one Secondary node to have an effective Availability Group? If I only plan to have the 2 physical servers, should I be setting up a traditional Clustered SQL Server installation.

I've used the traditional Clustered SQL Server in the past and used the Active/Passive licensing for the SQL Server software but the Always On Availability Group looked interesting to me, but NOT if it requires more than 2 physical servers and more than 2 SQL Instances (and licenses) to provide proper failover capability.

All input will be appreciated.

Thanks,

Brett

• Changed type Brett Zamora-CeRTNA Friday, February 13, 2015 9:52 PM

Hi Brett,

An AlwaysOn Availability Group is created between several standalone SQL Server instances, you dont need to set up a traditional clustered SQL Server installation when configuring AlwaysOn Availability Group. Also you can have an effective Availability Group with only one Secondary node.

From your description, you have an synchronous-commit availability secondary replica and it says Not Synchronizing. This issue can be caused by the following:

The availability replica might be disconnected.

The data movement might be suspended.

The database might not be accessible.

There might be a temporary delay issue due to network latency or the load on the primary or secondary replica.

Please resolve any connection or data movement suspend issues. You can check the events for this issue using SQL Server Management Studio, and find the database error.

Reference:
Data synchronization state of some availability database is not healthy
Availability databases in unhealthy data synchronization state (Error: 35285, Severity: 16, State: 1.)


Thanks,
Lydia Zhang

If you have any feedback on our support, please click here.