question

Vishu-2318 avatar image
0 Votes"
Vishu-2318 asked Cathyji-msft answered

AlwaysOn Replication

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-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @Vishu-2318,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @Vishu-2318,

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.




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Vishu-2318 avatar image
0 Votes"
Vishu-2318 answered Vishu-2318 edited

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.