creating read only second replica for SQL 2016

it seems I need to setup both:
what is the use of 'READ_ONLY_ROUTING_URL' and 'READ_ONLY_ROUTING_LIST'  ?

The 1st one (READ_ONLY_ROUTING_URL) indicates which server (Primary Replica) has the secondary read-only route list (READ_ONLY_ROUTING_LIST).

sorry , don't understand, they both need ?

To support read-only routing if one of the replicas currently owns the primary role. You have to set both. Below is an example script from MS:

ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER01' WITH  
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));  
ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER01' WITH  
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433'));  

ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER02' WITH  
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));  
ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER02' WITH  
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER02.contoso.com:1433'));  

ALTER AVAILABILITY GROUP [AG1]  
MODIFY REPLICA ON  
N'COMPUTER01' WITH  
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER02','COMPUTER01')));  

ALTER AVAILABILITY GROUP [AG1]  
MODIFY REPLICA ON  
N'COMPUTER02' WITH  
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));  
GO

if we have 3 x nodes:  SWVD02DSQLPOC, SWVD03DSQLPOC, SWVD04DSQLPOC, should I use a  script like this:


ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD02DSQLPOC'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));


ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD02DSQLPOC'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SWVD02DSQLPOC.loundy.lab:1433'));


ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD03DSQLPOC'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));


ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD03DSQLPOC'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SWVD03DSQLPOC.loundy.lab:1433'));


ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD04DSQLPOC'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));


ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD04DSQLPOC'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SWVD04DSQLPOC.loundy.lab:1433'));

ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD02DSQLPOC'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SWVD03DSQLPOC','SWVD04DSQLPOC')));


ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD03DSQLPOC'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SWVD02DSQLPOC','SWVD04DSQLPOC')));


ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD04DSQLPOC'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SWVD03DSQLPOC','SWVD02DSQLPOC')));

Yes. What you do is configure each node to how they will behave when they got the Primary Replica role.
Meaning that when they are the Primary Replica, they will allow read-only access to Secondary Replicas and in which which order.

"in which which order."


as it is round robin, order should not be that matter, right? except secondary on diff site and there are network concern ?

also my script above all run on primary node ?

one thing, after I run the script on primary nodes I found setting on AOG group properties is not reflecting new setting once failover, should the order in the list change accordingly ?


Now i see sth not very good that as my desktop domain and SQL server domain is different and if I set the READ_ONLY_ROUTING_URL using full server name, and I login to that serve locally and query the AOG via connector, it is working but not from the SSMS on my desktop! but I set READ_ONLY_ROUTING_URL using IP address, it only work from the SSMS on my PC but not  locally on that SQL server.

error is :


any way to combine both ?

...
MODIFY REPLICA ON N'SWVD02DSQLPOC'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://<server name>:1433'));

and

...
MODIFY REPLICA ON N'SWVD02DSQLPOC'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://<IP address>:1433'));

?

also how you test which server the read route routed to ?

one more thing is :

I read this one:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-access-on-an-availability-replica-sql-server#SSMSProcedure

and it say :

To configure connection access for the primary role, in the ADD REPLICA or MODIFY REPLICA WITH clause, specify the PRIMARY_ROLE option, as follows:

PRIMARY_ROLE ( ALLOW_CONNECTIONS = { READ_WRITE | ALL } )

where,

READ_WRITE

Select allOpen in new window

I think the write is already allowed on the primary , right ? why need to set it again?

this is my test SQL Aog Configuration.

"Did you connect to the new Primary Replica node after the failover? Or sometimes it's a SSMS's refresh issue and you may need to refresh it manually (F5)."

from the desktop SSMS, I can see read-only routing, but in local SQL server SSMS, I can't see that !  probably my desktop SSMS is SQL 2017 SSMS and server one is 2016.

what is the diff between read-intent and read only ...

I select red-intent but by this script for SQL 2016:

SELECT replica_server_name
	, read_only_routing_url
	, secondary_role_allow_connections_desc
FROM sys.availability_replicas

Select allOpen in new window

say all read_only ! so it is the same, then why in the readable replica setting, there is a 'yes' ? no need, right?

also, how can you TEST it and make sure that the read routing is reading from OTHER servers and which server it is reading from ?


"ALL is the default and it means it can also accept the Read-Intent connection as well. "


my script above only do this:

WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

Select allOpen in new window

should be ok ? no need to do:

WITH (PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

Select allOpen in new window

again on each server?

by running this:

SELECT	  AVGSrc.replica_server_name AS SourceReplica		
		, AVGRepl.replica_server_name AS ReadOnlyReplica
		, AVGRepl.read_only_routing_url AS RoutingURL
		, AVGRL.routing_priority AS RoutingPriority
FROM sys.availability_read_only_routing_lists AVGRL
INNER JOIN sys.availability_replicas AVGSrc ON AVGRL.replica_id = AVGSrc.replica_id
INNER JOIN sys.availability_replicas AVGRepl ON AVGRL.read_only_replica_id = AVGRepl.replica_id
INNER JOIN sys.availability_groups AV ON AV.group_id = AVGSrc.group_id
ORDER BY SourceReplica

Select allOpen in new window

what I got this:

SWVD02DSQLPOC	1	SWVD03DSQLPOC	TCP://SWVD03DSQLPOC.test.local:1433	SWVD03DSQLPOC,1433
SWVD02DSQLPOC	2	SWVD04DSQLPOC	TCP://SWVD04DSQLPOC.test.local:1433	SWVD04DSQLPOC,1433
SWVD03DSQLPOC	1	SWVD02DSQLPOC	TCP://SWVD02DSQLPOC.test.local:1433	SWVD02DSQLPOC,1433
SWVD03DSQLPOC	2	SWVD04DSQLPOC	TCP://SWVD04DSQLPOC.test.local:1433	SWVD04DSQLPOC,1433
SWVD04DSQLPOC	1	SWVD03DSQLPOC	TCP://SWVD03DSQLPOC.test.local:1433	SWVD03DSQLPOC,1433
SWVD04DSQLPOC	2	SWVD02DSQLPOC	TCP://SWVD02DSQLPOC.test.local:1433	SWVD02DSQLPOC,1433

Select allOpen in new window

should be ok ?

I am reading this now:

https://www.sqlshack.com/how-to-configure-read-only-routing-for-an-availability-group-in-sql-server-2016/

 and the answer to this:

should be ok ? no need to do:

WITH (PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

again on each server?

Select allOpen in new window

should be a no! agree?

and look at this:

https://www.sqlshack.com/how-to-configure-read-only-routing-for-an-availability-group-in-sql-server-2016/

it said when define the load balancing read only,, I need to change this :

ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD02DSQLPOC' 
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SWVD03DSQLPOC','SWVD04DSQLPOC')));

Select allOpen in new window

to this:

ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD02DSQLPOC' 
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('SWVD03DSQLPOC','SWVD04DSQLPOC'),'SWVD02DSQLPOC')));

Select allOpen in new window

one additional primary replica in this list can help and I can see why.

and I still can't test if the round robin read only by running select @@servername twice, or I MUST define "ReadOnly" in the connection string  ?  I think the listener can automatically DETECT all read only access and route it to all secondary replica which for read only ?

e.g.:

sqlcmd -S AG40VS,50000 -E -d test -K ReadOnly

Select allOpen in new window

OR

Server= tcp:<secondary replica>; Database=test; IntegratedSecurity=SSPI; MultiSubnetFailover=True; ApplicationIntent=ReadOnly;

Select allOpen in new window

 ApplicationIntent=ReadOnly; ?

so has to setup something in application string to know it is read only or not ? so there are change on application side to make it take effect?

now the final test today give me sth straight, from this link:

https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/22/modifying-alwayson-read-only-routing-lists/

it said:

The client must be using the latest providers that support ApplicationIntent parameter:

o SQL Server Native Client Support for High Availability, Disaster Recovery

o JDBC 4

o .NET 4.5

o .NET 4 + 4.0.2 (or higher) update / 4.0.2 / 4.0.3

o .NET 3.5 SP1

Select allOpen in new window

and from this link:

https://www.sqlshack.com/how-to-configure-read-only-routing-for-an-availability-group-in-sql-server-2016/

it seems we need this:

Server= tcp:AG40VS,50000; Database=test; IntegratedSecurity=SSPI; MultiSubnetFailover=True; ApplicationIntent=ReadOnly;

Select allOpen in new window

it seems right but one thing, I do simple query:

SELECT TOP (1000) [PackageTypeID]
      ,[PackageTypeName]
      ,[LastEditedBy]
      ,[ValidFrom]
      ,[ValidTo]
  FROM [WideWorldImporters].[Warehouse].[PackageTypes];

  select @@SERVERNAME;

Select allOpen in new window

and I hope the result by   select @@SERVERNAME; can show me diff return each time I press F5 but it is not!

it seems that I have to right click the AOG listener name from the SSMS and select new query, then I use the query above to try, then EACH NEW QUERY SSMS session will connect to a diff SQL nodes inside that AOG , is that the intended behavoiur ?

ApplicationIntent=ReadOnly; ?

so has to setup something in application string to know it is read only or not ? so there are change on application side to make it take effect?

Yes, this is only to be used by applications and the only way to test it is connecting from an application (website, reporting server, ...).
For that the application should have the entry in their configuration string. Example:

"Yes, this is only to be used by applications and the only way to test it is connecting from an application (website, reporting server, ...)."

it seems the SAME connection keep using the same AOG SQL node, right ?

so the application has to reconnect each time in order to use the AOG load balance?

"IntegratedSecurity=SSPI;"

I add this option when connecting, it has error, removing it will be ok, any idea?

how you test if the load balancing is working fine ? like batch it and returns a list on which server it is using ?

"Database=Db1"

also by this, this read only route connection will only round robin for any connection to Db1 user database only ?

what if an application will need to connect to more than one user database for round robin DB read connection ?

Victor,

from this: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-access-on-an-availability-replica-sql-server#SSMSProcedure


"MODIFY REPLICA WITH clause, specify the PRIMARY_ROLE option, as follows:

PRIMARY_ROLE ( ALLOW_CONNECTIONS = { READ_WRITE | ALL } )"

do you use this ? in my script I post above, I don't use that. what is it for ?

and if I setup this:

ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD02DSQLPOC' 
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('SWVD03DSQLPOC','SWVD04DSQLPOC'),'SWVD02DSQLPOC')));

Select allOpen in new window

so that if primary role is hosting at SWVD02DSQLPOC, read only replica will be load balanced between SWVD03DSQLPOC and SWVD04DSQLPOC ?

of I should only do this:

ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD02DSQLPOC' 
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SWVD03DSQLPOC','SWVD04DSQLPOC')));

Select allOpen in new window

it seems the SAME connection keep using the same AOG SQL node, right ?

so the application has to reconnect each time in order to use the AOG load balance?

No for both questions, if you provide the Listener name instead of the node name. In my example it's using the Listener name (MyAgListener).

I add this option when connecting, it has error, removing it will be ok, any idea?

What was the error? This SSPI is for use the current user domain account (Windows Authentication). I would expect that by now, majority of the applications will use this option.

how you test if the load balancing is working fine ? like batch it and returns a list on which server it is using ?

No. Check the activity in each node and confirm that both have more or less the same load.

what if an application will need to connect to more than one user database for round robin DB read connection ?

Depends on how your application needs to connect to the other databases.
If from the application itself then you'll need a connection string per database.
If from the SQL Server itself then it will connect to the database in the same SQL Server instance.

PRIMARY_ROLE ( ALLOW_CONNECTIONS = { READ_WRITE | ALL } )"

do you use this ? in my script I post above, I don't use that. what is it for ?

I've already answered that on the top of this question. If you don't provide this information, it will simple use the default option (ALL).

"No for both questions, if you provide the Listener name instead of the node name. In my example it's using the Listener name (MyAgListener).


I am using listener name too !

"What was the error? This SSPI is for use the current user domain account (Windows Authentication)"

this is the error:

"

"No. Check the activity in each node and confirm that both have more or less the same load"

this means whoisactive that or so_who2 that.

"If from the application itself then you'll need a connection string per database.
If from the SQL Server itself then it will connect to the database in the same SQL Server instance.
"

from application itself can be understand but it means we keep doing more than one of this:

Server= tcp:<IP address>,port; Database=<DB>; IntegratedSecurity=SSPI; MultiSubnetFailover=True; ApplicationIntent=ReadOnly;

Select allOpen in new window

for each application ...


"If from the SQL Server itself then it will connect to the database in the same SQL Server instance."

I don't understand that. you mean the read only will only connect to the primary SQL nodes if relies on SQL listener ?

"I've already answered that on the top of this question."

I think I confused,  and you can see from Metehan Ozcullu, example, which also don't have it.

you told me this:

ALL is the default and it means it can also accept the Read-Intent connection as well. When you set to READ-WRITE it means that it won't accept Read-Intent connections (good to preserve the server load from Reporting, for example).

Select allOpen in new window

you mean this set the primary server on it accept read only or read write only too ?

now I do a little test:

I failover the AOG primary to SWVD03DSQLPOC, then I try to do select @@servername.

my read-only replica definition is :

ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD03DSQLPOC'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('SWVD02DSQLPOC','SWVD04DSQLPOC'),'SWVD03DSQLPOC')));

the result should return it is SWVD02DSQLPOC or SWVD04DSQLPOC, bu t result is SWVD03DSQLPOC.

so it means read only routing is not working .

the connection string I use is :

ApplicationIntent=ReadOnly

Select allOpen in new window

even I check the routing list, which seems to be correct :

SELECT	  AVGSrc.replica_server_name AS SourceReplica		
		, AVGRepl.replica_server_name AS ReadOnlyReplica
		, AVGRepl.read_only_routing_url AS RoutingURL
		, AVGRL.routing_priority AS RoutingPriority
FROM sys.availability_read_only_routing_lists AVGRL
INNER JOIN sys.availability_replicas AVGSrc ON AVGRL.replica_id = AVGSrc.replica_id
INNER JOIN sys.availability_replicas AVGRepl ON AVGRL.read_only_replica_id = AVGRepl.replica_id
INNER JOIN sys.availability_groups AV ON AV.group_id = AVGSrc.group_id
ORDER BY SourceReplica
 

Select allOpen in new window

result

SWVD02DSQLPOC	SWVD03DSQLPOC	TCP://SWVD03DSQLPOC:1433	1
SWVD02DSQLPOC	SWVD04DSQLPOC	TCP://SWVD04DSQLPOC:1433	1
SWVD02DSQLPOC	SWVD02DSQLPOC	TCP://SWVD02DSQLPOC:1433	2
SWVD03DSQLPOC	SWVD02DSQLPOC	TCP://SWVD02DSQLPOC:1433	1
SWVD03DSQLPOC	SWVD04DSQLPOC	TCP://SWVD04DSQLPOC:1433	1
SWVD03DSQLPOC	SWVD03DSQLPOC	TCP://SWVD03DSQLPOC:1433	2
SWVD04DSQLPOC	SWVD03DSQLPOC	TCP://SWVD03DSQLPOC:1433	1
SWVD04DSQLPOC	SWVD02DSQLPOC	TCP://SWVD02DSQLPOC:1433	1
SWVD04DSQLPOC	SWVD04DSQLPOC	TCP://SWVD04DSQLPOC:1433	2

Select allOpen in new window

now I tried to login to the SQL server and check if the result is the same as the result I run from SSMS desktop, I found interesting thing.

if I am here: SWVD02DSQLPOC  and it will be reading from 'SWVD04DSQLPOC' and I  failover to SWVD04DSQLPOC and try to see if other replica than 'SWVD04DSQLPOC' will be the reading comes from. it will stay the same as SWVD04DSQLPOC , so when failover to SWVD04DSQLPOC , the read only still on SWVD04DSQLPOC .

not a good result, huh ?

finally I found there are 2 x problems.

1) xxxxing funny that SSMS on the desktop of the same version can show DIFFERENT result than the one on local SQL server SSMS !
2) the load balancing of SQL 2016 read only is not good. e.g.  I am using node 2 and it should rout the read request to node 3 and then 4, it can be a long long time before it reroute to nodes 3, it may jump to node 4 first, if I stop the SQL server service on node 4 it jump to node 3, which is good but once I turn node 3 back on it don't jump back to node 3 any more.
3) I need to stop node 4 manually before it route to node 3.. it will hangs for 1-2 sec before goes to nodes 3 once i stop node 4.

so this is not a real load balancing, but it will route the read to other nodes and node 4 access will only come back after more than 10 minutes.

any idea on that ?

might be need to test SQL 2017 on that.

Weird error. What application are you using to connect to the SQL Server database?
You might need to replaced the parameter with Trusted_Connection=true:

you mean the read only will only connect to the primary SQL nodes if relies on SQL listener ?

Read Only connections won't go the Primary Replica when Secondary Replicas are configured for ReadIntent.

"Weird error. What application are you using to connect to the SQL Server database?

just SSMS.

"Read Only connections won't go the Primary Replica when Secondary Replicas are configured for ReadIntent."

that's why I don't understand why this should be here:

RIMARY_ROLE ( ALLOW_CONNECTIONS = { READ_WRITE | ALL } )

Select allOpen in new window

why we need to tell it again ? primary is for read write already.

as you can see from my last post, any way to improve the load balancing better so connection REALLY goes go EACH node evenly  ?

SSMS doesn't use connection strings.
Check this article to learn how to connect to a Secondary Replica with the ApplicationIntent=ReadOnly.

primary is for read write already.

That means it won't accept ReadOnly connections on the Primary Replica. Default values is ALL (when you don't provide nothing) that allows read-write and read-only connections on the Primary Replica.

"SSMS doesn't use connection strings."

it does:


I follow this link : https://www.sqlshack.com/how-to-configure-read-only-routing-for-an-availability-group-in-sql-server-2016/

"That means it won't accept ReadOnly connections on the Primary Replica"

 if primary replica do not accept write, what it do ? I think set it to READ_ONLY is good when it is a chained AOG as the second AOG primary shouldn't be read write, right ? but that case, second AOG's primary act like distributor, so should be read - write too, agree?


"Trusted_Connection=true:"

should this one give load balancing better ? for me, it still the same.


so by this :

Server= tcp:<SQL AOG listener>,1433; Trusted_Connection=true; Database=WideWorldImporters;  MultiSubnetFailover=True; ApplicationIntent=ReadOnly;

Select allOpen in new window

I am not sure if any WRITE operation will router to primary automatically as it seems only for read only operation, like SSRS.

so we only need this connection string for the reporting web application ?

I meant, you don't need to build a connection string as you're building for applications.
You just provide the parameters and internally it will create the necessary connection string. And the article I've posted before it shows how to do it.

if primary replica do not accept write, what it do ?

But it accepts. Why are you telling it doesn't?

"Trusted_Connection=true:"

should this one give load balancing better ? for me, it still the same.

No. This is only to use your current Windows account to connect to the database. But where are you using this parameter? Can't be in SSMS for sure.

I am not sure if any WRITE operation will router to primary automatically as it seems only for read only operation, like SSRS.

If you want to connect with write permissions then you mustn't use ApplicationIntent=ReadOnly. If you use it then it will connect to a Secondary Replica and never to the Primary Replica.

"But it accepts. Why are you telling it doesn't?
"

Yeah, but I tested it before.

http://www.sqlerudition.com/how-to-connect-ssms-to-alwayson-secondary/, just one 'ReadOnly' already can do the job ? ok, I am referring to connection parameter, is it diff from connection string you are talking about ?

then why needs so much ?

Server= tcp:<SQL AOG listener>,1433; Trusted_Connection=true; Database=<DB naem>;  MultiSubnetFailover=True; ApplicationIntent=ReadOnly;

Select allOpen in new window

I need to ask application team to put this in their application connection string:

Server= tcp:<SQL AOG listener>,1433; Trusted_Connection=true; Database=<DB naem>;  MultiSubnetFailover=True; ApplicationIntent=ReadOnly;

Select allOpen in new window

?

"But it accepts. Why are you telling it doesn't?"

 I means by default we need to re-route read only to other node, which is the feature of SQL AOG, I just don't believe why we need an option to set primary to read only too! primary should allow read write, right ? this give me additional concern.

"But where are you using this parameter? "

exactly the same place from your link : http://www.sqlerudition.com/how-to-connect-ssms-to-alwayson-secondary/

is not ? what I mean is, it doesn't make an diff to me.

"If you want to connect with write permissions then you mustn't use ApplicationIntent=ReadOnly"

you mean this make read only operation will route to secondary nodes already and the rest of operation, e..g write operation, already handle by SQL server automatically to primary nodes only! no need to worry about it ALSO route to secondary node too ?


how has one more last thing to do on SQL server 2016 AOG , other than read only routing, load balancing read only , what else should I implement on SQL Server 2016 AOG to let the company fully utilize that potential of SQL 2016 AOG?

I will test SQL 2017 on the same thing later.

I think I'm starting to understand what's confusing you.

By default, when you connect through a Listener, it will ALWAYS connect you to the Primary Replica. This is the default behaviour, when you don't use the ApplicationIntent=ReadOnly parameter neither use another Listener.

What's good with AOG is that you can redirect read only requests (as Reports) to a Secondary Replica, avoiding the load on the Primary Replica. For that you usually configure Secondary Replicas to accept the ApplicationIntent=ReadOnly, so in your Reports you must use this parameter in their connection strings to indicate the AOG to redirect this Report query to a Secondary Replica following the order that you've configured in the Primary Replica.
Another option is to create a Listener to point only to a specific Secondary Replica or Replicas and then use the connection string of the Reports to use this Listener to perform their queries, instead of the default Listener.

wait, is parameter is diff from connection string ? please give example here based on what I post. it is the SAME to me .

"when you don't use the ApplicationIntent=ReadOnly parameter neither use another Listener."

what is this mean, sorry ?

"Another option is to create a Listener to point only to a specific Secondary Replica or Replicas and then use the connection string of the Reports to use this Listener to perform their queries, instead of the default Listener."

I think this is what I am doing here, right ?:

Server= tcp:<SQL AOG listener>,1433; Trusted_Connection=true; Database=<DB naem>;  MultiSubnetFailover=True; ApplicationIntent=ReadOnly;

Select allOpen in new window

?
or you are saying different connector for diff purpose ? why need 2 if one already can serve ? the second listener also need parameter to work with for read only connection right ?

or you are saying different connector for diff purpose ?

Right. Is what I'm saying.

why need 2 if one already can serve ?

In another question, you was asking about working with many Listeners and now you're asking to work only with one :)
But you're right, if you can work with one, then better. I just gave you another option in case you want to separate Listeners functions (one for write, other one for read only).

the second listener also need parameter to work with for read only connection right ?

If you want to use it only for read only purposes, then yes.

"In another question, you was asking about working with many Listeners and now you're asking to work only with one :)"

man , I want to find out pros and cons.

so if I want the read only operation for all user database goes to the read replica, I shouldn't use this anymore as connection string:

Server= tcp:<SQL AOG listener>,1433; Trusted_Connection=true; Database=<DB naem>;  MultiSubnetFailover=True; ApplicationIntent=ReadOnly;

Select allOpen in new window

but this:

Server= tcp:<SQL AOG listener>,1433; Trusted_Connection=true;  MultiSubnetFailover=True; ApplicationIntent=ReadOnly;

which do not have this :

Database=<DB naem>; 

Select allOpen in new window

also , anyway to make the round robin read only operation more evenly load balance between secondary replica?

any other feature of SQL 2016 out of the box feature you setup out of the box and happy to suggest me to test it ?

I want to find out pros and cons.

There are none. It only depends on how do you want to use them. Anyone that you choose will work correctly. You might like one more than the other but there are not really pros and cons of one over another.

so if I want the read only operation for all user database goes to the read replica, I shouldn't use this anymore as connection string:

No. That's a wrong statement. If you want to use ReadOnly operation then ApplicationIntent=ReadOnly is required. If you don't provide a database name, it means it will connect to the default database for that user (by default is master).

anyway to make the round robin read only operation more evenly load balance between secondary replica?

I don't think SQL Server provides this feature.

"If you don't provide a database name, it means it will connect to the default database for that user (by default is master)."

so if their application connect to more than one DB for read only , what should we do on the connection string ?

"I don't think SQL Server provides this feature."

ok, I think then my SQL server 2016 installation and configuration is done.

"One connection string per database"

I am not sure if an application can have multiple connection string, can't be, right ?

"Or then, provide the complete path for other database objects. Example:"

by link server you mean ?

I am not sure if an application can have multiple connection string, can't be, right ?

Why not? Since you have a connection object for each of the databases, that shouldn't be a problem

by link server you mean ?

You'll only need Linked Server to connect to a database in another SQL Server instance. If it's in the same instance you just need to provide database name.

"Why not? Since you have a connection object for each of the databases, that shouldn't be a problem"

as I am not developer,  I really didn't know it.

so by reading all your message, I think there will be at least 2 x connection string per application as one without 'ApplicationIntent=ReadOnly;' , that one use for write operation to primary, right?


"You'll only need Linked Server to connect to a database in another SQL Server instance. If it's in the same instance you just need to provide database name."

yeah, by link server anyway.

so by reading all your message, I think there will be at least 2 x connection string per application as one without 'ApplicationIntent=ReadOnly;' , that one use for write operation to primary, right?

No, man. There's no rule for the number of connection strings. It only depends on your needs or the application needs.

you know, by that read only string, it seems that one only used by read only operation but not write, in order to make system operatable, I think I need one more for write operation, agree?

or just one with read-only, that's it ?

you know, by that read only string, it seems that one only used by read only operation but not write, in order to make system operatable, I think I need one more for write operation, agree?

I think that you're making a confusion here. You don't need the ReadOnly bit to connect to an AOG database. That's only used if you want to send a respective part of the application (e.g. Reports) to query a Secondary Replica to off load the Primary Replica.

"That's only used if you want to send a respective part of the application (e.g. Reports) to query a Secondary Replica to off load the Primary Replica."

e..g reporting service /application only ?

if it is normal read write connection, no connection string is needed?

if it is normal read write connection, no connection string is needed?

Ofc is needed but then you just provide the Listener Name instead of the Instance Name. The ReadOnly bit it's only a way to say "who's connecting through this connection can't make changes on the records" and that's mostly happens when you just need to perform Reports.

"The ReadOnly bit it's only a way to say "who's connecting through this connection can't make changes on the records" and that's mostly happens when you just need to perform Reports."

ok, I will tell tester to consider 2 x connection string:

1) normal connection if no read only application
2) read only application.

tks

tks

how can award the other contributor ? Metehan  ? EE close ticket method recently keep changing.

hi,

I found out that when I selecting a lot of data from a table and during this time the AOG failover, SSMS will gives error and I have to do the select again.

how can AOG help us to transparent this kind of error message and let the select wait there until failover done and read /write operation keep going ?

What's the error? Timeout?
Anyway, I would suggest you to open a new question about that since you won't get new Experts to check this one.

this is network connection message:

Msg 10054, Level 20, State 0, Line 14
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 。)

Select allOpen in new window

That's  a typical error message when a connection looses the contact from the server.
Definitely open a new question so all Experts can have the same chance to help you with this.

this one: https://www.experts-exchange.com/pendingQuestions/51763/select-operation-drop-when-SQL-server-AOG-failing-over.html#questionAdd 

please wait until it is port.

also I have question on SQL server on Azure and compare with MariaDB on AWS, please take a look.