CODEWITHSUNDEEP
c#sqlperformancealwayson
D. Jonker
D. Jonker

Reputation: 103

Connecting to read only instance of Always On availability group

Good day,

I have a performance related question with regard to reading from a read-only instance of an availability group.

To my understanding (sorry I'm not very knowledgeable with Always On(AO)) you can connect to the read-only instance in two ways:

Method 1: Connecting directly to the AO availability group, which has Read-only routing setup correctly, by specifying the application intent to be read only.

Method 2: connecting directly to the read only instance.

I would like to know which of these would be less taxing on the server if you are constantly querying the database (every minute or so) or if it would make no difference.

Upvotes: 4

Views: 1703

Answers (1)

Alex
Alex

Reputation: 21766

The difference between both methods is insignificant, although connecting directly might be fractionally faster as some additional logic has to be executed when connecting to the availability group, for example looking up the read only routing. However, it is best practice to connect to the AO availability group as it abstracts away the implementation details. It allows for load balancing if there are more than one read-only replicas and it facilitates maintenance as the DBA can change the read-only routing without needing to update any connection strings.

Regarding your second comment, according the MSDN, when connecting to an availability group, the primary database of the availability group processes the incoming read-only routing request and attempts to locate an online, read-only replica that is joined to the primary replica and is configured for read-only routing. The client receives back connection information from the primary replica server and connects to the identified read-only replica. So in short, after rerouting, you get connected to the secondary server, so there is no difference between connecting to the availability group when using read only application intent as you end up with the same connection.

Upvotes: 3

Related Questions