Post reply

Always On Secondary replicas

  • February 23, 2023 at 4:25 pm

    Go to Answer

    #4153333

    Hi friends,

    Question: How many readable replicas may I have in an AlwaysOn Availability group? If I have synchronous nodes and one asynchronous node. Is it possible to have all as readable replicas?

    Thank you very much for your feedback,

    Best Regards,

  • February 24, 2023 at 5:10 pm

    #4153804

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • February 24, 2023 at 9:09 pm

    Answer

    #4153867

    You can find the answer here: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-availability-groups-sql-server?view=sql-server-ver16

    The answer is going to depend on the edition and version of SQL Server.  For 2014 I think the limitation was 3 synchronous and up to 8 replicas in total, with only 2 replicas allowed for automatic failover.

    Any replica can be set to read only - so you could have all 8 replicas configured for read only.  With that said, be aware that every replica that is set to read only must be fully licensed.  Since this would be Enterprise Edition only - then it could get quite expensive.  That would all depend on how each replica is going to be used - and how they are provisioned.

    Assuming you have a 3-node cluster, with 2 nodes set to synchronous and automatic failover and a 3rd node set to asynchronous and manual failover as a DR node - and each node has 16 core, then you would need 48 cores licensed.  In that configuration, all 3 nodes can be set to read-intent or read-only.  You can then setup read-only routing to determine the order of systems to be selected for application intent connections to be routed.

    If set to read-only, users can also access those nodes directly - if set to read-intent then access is only allowed through the listener when the connection has specified application intent = readonly.

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply