Enable read-only replica alwaysON & HA platform

Thor El Poderoso 51 Reputation points
2021-01-26T15:11:30.097+00:00

Hi

I'm setting up high availability for my SQL 2017 STD platform.
I currently have a WSCF with 2 nodes and the data on NAS. It works perfectly.
Now I have configured the AlwaysON on an additional server and the secondary replica has been successfully established.

1.- Do you think this platform is correctly configured for HA? or do you advise it to be all AlwaysON?
2.- How can I access the AlwaysON replica so that the reports and other apps can access it? I can't change the "Readable Secondary" value to YES

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,937 questions
0 comments No comments
{count} votes

Accepted answer
  1. Williams, Jeffrey A 481 Reputation points
    2021-01-26T22:30:25.187+00:00

    The purpose of Availability Groups in Standard Edition is to support a single database in either HA or DR. Since it does not support multiple databases or read-only it cannot be utilized for those purposes.

    Your configuration was already HA using a failover instance - such that one node fails and the other node takes over. The only benefit to a BAG in that scenario is possibly less time during that failover event. This is because the secondary instance is already running and you skip a lot of the startup processes.

    If you need read-only, then you have to upgrade the primary and secondary instances to Enterprise Edition.

    1 person found this answer helpful.
    0 comments No comments

5 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 102.4K Reputation points
    2021-01-26T22:52:56.707+00:00

    In addition to Jeffery's answer: Basic Availability Groups were introduced to provide customers with Standard Edition to move away from Database Mirroring which was deprecated when AGs were introduced.

    While a cluster can server as an HA solution, there is is only a single instance of the database in this case, so an AG gives better protection in case of corruption.

    1 person found this answer helpful.
    0 comments No comments

  2. Thor El Poderoso 51 Reputation points
    2021-01-26T15:29:35.057+00:00

    I answer myself:
    Standard Edition that only supports Basic Availability Groups, which do not allow read access on a secondary replica:

    My question, do you have to migrate everything to the Enterprise version or just the replica node?
    What is the point of AlwaysON with the STD version?
    This platform that I have configured WSCF + alwaysON is not valid for HA?

    0 comments No comments

  3. Cris Zhan-MSFT 6,611 Reputation points
    2021-01-27T06:45:11.347+00:00

    Hi,

    >do you have to migrate everything to the Enterprise version or just the replica node?

    All replicas in this AG should be enterprise edition.

    >What is the point of AlwaysON with the STD version?

    The Always On Basic Availability Groups replaces the deprecated Database Mirroring feature and provides a similar level of feature support.
    Note that the Standard Edition of SQL Server 2016 and above only supports the Basic availability groups and limited failover cluster instances(Support for 2 nodes) features .More details check this document.

    >This platform that I have configured WSCF + alwaysON is not valid for HA?

    This topology can provide high availability and disaster recovery.

    According to the description in the question, It seems that you have created SQL Server 2017 Failover Cluster Instances on this WSCF cluster before configuring the Always On availability groups. The SQL Server Failover Cluster Instances itself can provide high availability.

    Please note that SQL Server Failover Cluster Instances (FCIs) do not support automatic failover by availability groups, so any availability replica that is hosted by an FCI can only be configured for manual failover. Check the following doc for more considerations.
    Failover Clustering and Always On Availability Groups (SQL Server)

    0 comments No comments

  4. Thor El Poderoso 51 Reputation points
    2021-01-27T07:33:44.13+00:00

    So is it advisable to always use AlwaysON for SQL in HA than SQL in WSFC?

    The only solution to have a replica and unload the work of the WSFC, is to do a normal SQL replication without using Alwayson?

    0 comments No comments