SQLTeam.com | Weblogs | Forums

HA-Availability Group 2014-Comparison Between Read Only And Read only Routing


#1

I read about Read only Routing. Are these Comparison (Between Read only And Read only Routing) true?

1- In read Only secondary (where we have a Listener)
- Our App Connect to Listener.
- Listener Lead app to Primary.
- All Connection , are received by the primary And if we want to ReportsPart-App connection To Secondary, We should do this manualy.
- Both servers (Primary And secondary) are available for connect from app or tsql from Sql server console
- If we have fail on primary , we have some seconds disconnection and then we connect to secondary as new primary

2- In read-intent Only (where we have a listener and set a Read only Routing)
- Our App Connect to Listener.
- Listener Lead (Update,insert and delete) Of app to Primary And (Select) of App to Secondary
- some Connection , received by the primary and some by secondary. Automaticaly reports connect to Secondary
- secondary are not accessible in any way. (else of listener)
- and in fail primary, we dont have disconection


#2

I'm having a hard time understanding the bullet points due to how they are worded. Do you have the original article that these are from?


#3

In a simple way :
Please tell me who to use secondary server for our reports (and some function that are and read only ) in these ways
(How to set our app)
1- In read only secondary role
2- in Read only routing secondary role
Thank you


#4

It depends on your database driver. If you are using a database driver that supports the Application Intent option, then you would add that to the application's connection string. In addition, you need the read-only routing urls in place for SQL Server to redirect the ApplicationIntent=ReadOnly queries to the proper replica.

If you don't have a database driver that supports read-intent, you can connect directly to the replica's @@SERVERNAME. But this means there'll be some manual intervention if a failover occurs. We use a DNS alias in this scenario so that we don't have to touch the connection strings again.


#5

Thank you TaraKizer
Another question, Please
1- In first scenario, Do I have one connection string for total application?
(For insert update forms and reports and ...) If it,s true , It will Means that sql server automaticly redirect, selects to secondary and changes to primary ?
2- in second scenario , What do you mean about DNS alias of sql server ?
thank you again TaraKizer And sorry that I'm weak in english.


#6
  1. There'd be at least 2 connection strings. For the read workload that you want to offload to the readable secondary, it would have the ApplicationIntent=ReadOnly attribute added to the connection string. Everything else will use the regular connection string. SQL Server does not just send the read workload to the readable secondary. You have to tell it.
  2. Please lookup DNS aliases. It is not a SQL Server technology. I'm referring to the DNS server. You can ask your server/network admin about it. A DNS alias would be added that would point to the SQL Server name. The applications would use the DNS alias instead of the SQL Server name in the connection string. The DNS alias would be updated when a failover occurs so that the applications could still connect.