AlwaysOn Replication

Vishu 1,516 Reputation points
2021-06-22T22:23:26.7+00:00

Experts , For a read-only / read intent database in secondary alwayson , can we have a sync replication and automatic failover between primary and secondary or will it be async only with a manual failover

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,583 questions
0 comments No comments
{count} votes

Accepted answer
  1. CathyJi-MSFT 21,066 Reputation points Microsoft Vendor
    2021-06-23T06:01:33.823+00:00

    Hi @Vishu ,

    > but does it lead to a lot of locking due to sync replication for the read requests

    No.

    > Also with node1\sql2019 being primary, will the replication be from node1\sql2019 -> node3\SQL2019 or can it be from node2\sql2019 -> node3\SQL2019 also

    The data replication is from node1\sql2019 -> node3\SQL2019. The replication is from primary replica to secondary replica.


    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. CathyJi-MSFT 21,066 Reputation points Microsoft Vendor
    2021-06-23T02:06:04.23+00:00

    Hi @Vishu ,

    We can have sync replication and auto failover between primary and secondary replica when we setting secondary replica for read only.

    I check this in my environment.
    108383-screenshot-2021-06-23-100123.jpg

    108345-screenshot-2021-06-23-100032.jpg


    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.

    0 comments No comments

  2. Vishu 1,516 Reputation points
    2021-06-23T03:04:52.097+00:00

    Thanks , but does it lead to a lot of locking due to sync replication for the read requests

    Also with node1\sql2019 being primary, will the replication be from node1\sql2019 -> node3\SQL2019 or can it be from node2\sql2019 -> node3\SQL2019 also

    0 comments No comments